List of Open PO

SELECT     pha.segment1 "PO_NO",
               pha.creation_date "PO_CREATION_DATE",
               aps.segment1 "VENDOR_CODE",
               msib.segment1 "MATERIAL_CODE",
               msib.description "MATERIAL_DESCRIPTION",
               plla.need_by_date,
               pla.unit_meas_lookup_code "UOM",
               plla.quantity "PO_QTY",
                SUM(plla.quantity_received) "RECEIVED_QTY",
                   SUM(plla.quantity_accepted) "ACCEPTED_QTY",
                   SUM(plla.quantity_rejected) "REJECTED_QTY",
              (plla.quantity-plla.quantity_received) "BALANCE_QTY_TO_BE_RECEIVED"
FROM      po_headers_all pha,
              po_lines_all pla,
              po_line_locations_all plla,
              po_distributions_all pda,
              mtl_system_items_b msib,
              ap_suppliers aps
WHERE pha.vendor_id=aps.vendor_id
AND     pha.po_header_id=pla.po_header_id
AND     pla.po_line_id=plla.po_line_id
AND     pla.item_id=msib.inventory_item_id
AND     pha.org_id=:ORGANIZATION
AND     TRUNC (pha.creation_date) BETWEEN NVL (:FROM_RECEIPT_DATE,TO_DATE
         (pha.creation_date,'DD-MON-RR'))AND NVL (:TO_RECEIPT_DATE,TO_DATE
         (pha.creation_date,'DD-MON-RR'))                    
AND     pha.vendor_id=:VENDOR
AND     pha.segment1='7015'
GROUP BY    pha.segment1,
                  pha.creation_date,
                  aps.segment1,
                  msib.segment1,
                  msib.description,
                  plla.quantity,
                  plla.need_by_date,
                  pla.unit_meas_lookup_code,
                  plla.quantity,
                  plla.quantity_received,
                  plla.quantity_accepted,
                  plla.quantity_rejected,
                  plla.quantity,
                  plla.quantity_received

APIL Payment details for a given Purchase Order

SELECT   pha.segment1 "PO_NO" ,
               pha.creation_date "PO_DATE",
                  SUM(pla.quantity) "PO_QTY",
               rsh.receipt_num "GRN_NO",
               rt.transaction_date "GRN_DATE",
               SUM(plla.quantity_received) "RECEIVED_QTY",
                  SUM(plla.quantity_accepted) "ACCEPTED_QTY",
                  SUM(aila.quantity_invoiced) "INVOICED_QTY",
               aia.amount_paid "PAYMENT_AMT",
               apt.name "PAYMENT_TERMS",
               apsa.due_date "PAYMENT_DATE",
               aca.payment_method_code "PAYMENT_MODE",
                   DECODE(aca.payment_method_code,'CHECK',aca.check_number,
                  'NETTING',aca.check_number) "CHECK_NUMBER",
                  DECODE(aca.payment_method_code,'CHECK',aca.check_date,
                  'NETTING',aca.check_date) "CHECK_DATE"        
FROM       po_headers_all pha,
                po_lines_all pla,
                po_line_locations_all plla,
                po_distributions_all pda,
                rcv_transactions rt,
                rcv_shipment_headers rsh,
                rcv_shipment_lines rsl,
                ap_invoices_all aia,
                ap_invoice_lines_all aila,
                ap_invoice_distributions_all aida,
                ap_invoice_payments_all aipa,
                ap_payment_schedules_all apsa,
                ap_checks_all aca,
                ap_terms apt,
                mtl_system_items_b msib,
                org_organization_definitions ood
WHERE     pha.po_header_id=pla.po_header_id
AND         pla.po_line_id=plla.po_line_id
AND         plla.line_location_id=pda.line_location_id
AND         rsh.shipment_header_id=rsl.shipment_header_id
AND         pda.po_distribution_id=rsl.po_distribution_id
AND        rsl.shipment_line_id=rt.shipment_line_id
AND         aia.invoice_id=aila.invoice_id
AND         aia.invoice_id=aida.invoice_id
AND        aipa.invoice_id(+)=aia.invoice_id
AND        aia.invoice_id=apsa.invoice_id
AND        aipa.check_id=aca.check_id(+)
AND        apt.term_id=aia.terms_id
AND        pla.item_id=msib.inventory_item_id(+)
AND        ood.organization_id(+)=msib.organization_id
AND        rt.transaction_id=aida.rcv_transaction_id
AND        ood.organization_id=NVL(:ORGANIZATION_ID,ood.organization_id)
AND        TRUNC(rt.transaction_date) BETWEEN NVL(:FROM_RECEIPT_DATE,to_date
               (rt.transaction_date,'DD-MON-RR')) AND NVL(:TO_RECEIPT_DATE,to_date
               (rt.transaction_date,'DD-MON-RR'))
AND         pha.segment1=NVL(:PURCHASE_ORDER_NO,pha.segment1)
GROUP BY pha.segment1,
                 pha.creation_date,
                 rsh.receipt_num,
                 rt.transaction_date,
                 aia.amount_paid,
                 apt.name,
                 apsa.due_date,
                 aca.payment_method_code,
                 aca.check_number,
                 aca.check_date          

APIL unpaid invoices for materials received against PO

SELECT   pha.segment1 "PO_NO",
               pha.creation_date "PO_DATE",
               aps.vendor_name,
               SUM(pla.quantity) "PO_QTY",
               rsh.receipt_num,
               rsl.quantity_received "RECEIPT_QTY",
               (rsl.quantity_received * pla.unit_price) "RECEIPT_VALUE",
               (rsl.quantity_received - rsl.quantity_shipped) "PENDING_QTY",
               (pla.unit_price * pla.quantity) "PO_VALUE",
               plla.promised_date "DELIVERY_SCHEDULE",
               rt.transaction_date "RECEIPT_DATE",
               apsa.amount_remaining "INVOICE_VALUE",
               aia.invoice_num "INVOICE_NUMBER",
               aia.invoice_date,
               rt.attribute2 "LR_NO",
               rt.attribute3 "LR_DATE"           
FROM     po_headers_all pha,
              po_lines_all pla,
              jai_po_taxes jpo,
              po_line_locations_all plla,
              po_distributions_all pda,
              ap_suppliers aps,
              rcv_shipment_headers rsh,
              rcv_shipment_lines rsl,
              rcv_transactions rt,
              ap_invoice_lines_all aila,
             ap_invoices_all aia,
             ap_payment_schedules_all apsa,
             hr_organization_units hou,
             mtl_system_items msi
WHERE    pha.vendor_id= aps.vendor_id
AND         pha.po_header_id=pla.po_header_id
AND        jpo.po_line_id(+) = pla.po_line_id
AND        pla.po_header_id = plla.po_header_id
AND        pha.po_header_id = pda.po_header_id
AND        pla.po_line_id = plla.po_line_id
AND        plla.line_location_id = pda.line_location_id
AND        rsh.shipment_header_id=rt.shipment_header_id
AND        rt.shipment_line_id= rsl.shipment_line_id
AND        pda.po_distribution_id=rt.po_distribution_id
AND        pda.po_distribution_id = aila.po_distribution_id
AND        aila.invoice_id = aia.invoice_id
AND        apsa.invoice_id = aia.invoice_id
AND       hou.organization_id = pha.org_id
AND       pla.item_id = msi.inventory_item_id
AND       plla.ship_to_organization_id = msi.organization_id
AND       TRUNC (rsh.creation_date) BETWEEN NVL (:FROM_RECEIPT_DATE,TO_DATE
             (rsh.creation_date,'DD-MON-RR'))   AND NVL (:TO_RECEIPT_DATE,TO_DATE
             (rsh.creation_date,'DD-MON-RR'))                     
                                                                                                                               
AND      pha.segment1=:PO_NUMBER
AND      aps.vendor_name = NVL (:VENDOR_NAME, aps.vendor_name)
GROUP BY pha.segment1,
                 pha.creation_date,
                 aps.vendor_name,
                 pla.quantity,
                 rsh.receipt_num,
                 rsl.quantity_received,
                 rsl.quantity_received, 
                 pla.unit_price,
                 rsl.quantity_received,
                 rsl.quantity_shipped,
                 plla.promised_date,
                 rt.transaction_date,
                 apsa.amount_remaining,
                 aia.invoice_num,
                 aia.invoice_date,
                 rt.attribute2,
                 rt.attribute3

purchase Requisition Query

SELECT prha.segment1 "REQ NO",
                prha.creation_date "REQ DATE",
                prla.quantity "REQ QTY",
                pha.segment1 "PO NO",
                pha.creation_date "PO DATE",
                sum(pla.quantity) "PO QTY",
                pla.unit_price "PRICE",
                rsh.receipt_num ,
                rt.transaction_date "RECEIPT DATE",
                rt.quantity "RECEIPT QTY",
                rt.po_unit_price "PRICE",
                aia.invoice_num,
                aia.invoice_date,
                apc.check_date,
                apc.check_number
FROM    po_requisition_headers_all prha,
                po_requisition_lines_all prla,
                po_req_distributions_all prda,
                po_headers_all pha,
                po_lines_all pla,
                po_distributions_all pda,
                rcv_transactions rt,
                rcv_shipment_lines rsl,
                rcv_shipment_headers rsh,
                ap_invoices_all aia,
                ap_invoice_lines_all aila,
                ap_invoice_distributions_all aid,
                ap_invoice_payments_all aip,
                ap_payment_schedules_all apsa,
                ap_checks_all apc
WHERE  prha.requisition_header_id=prla.requisition_header_id
AND       prla.requisition_line_id=prda.requisition_line_id
AND       pda.req_distribution_id=prda.distribution_id
AND       pla.po_line_id=pda.po_line_id
AND       pha.po_header_id=pla.po_header_id
AND       pda.po_distribution_id=rt.po_distribution_id
AND       rsh.shipment_header_id=rt.shipment_header_id
AND       rt.transaction_id=aid.rcv_transaction_id
AND       aila.invoice_id=aid.invoice_id
AND       aia.invoice_id=aila.invoice_id
AND      aia.invoice_id=aip.invoice_id
AND      aia.invoice_id=apsa.invoice_id
AND      apc.check_id=aip.check_id
AND      prha.segment1='14663'
--and      pha.SEGMENT1='7162'


GROUP BY  prha.segment1,
                       prha.creation_date,
                       prla.quantity,
                       pha.segment1,
                       pha.creation_date,
                       pla.quantity,
                       pla.unit_price,
                       rsh.receipt_num ,
                       rt.transaction_date,
                       rt.quantity,
                       rt.po_unit_price,
                       aia.invoice_num,
                       aia.invoice_date,
                       apc.check_date,
                       apc.check_number

How to Identify the On-Hand Quantity

select  ITEM_ID,
        sum(TARGET_QTY) quantity  
FROM(SELECT moqd.inventory_item_id item_id,           
     SUM (primary_transaction_quantity) target_qty
     FROM   mtl_onhand_quantities_detail moqd
     WHERE  moqd.organization_id = 204
     AND    moqd.owning_tp_type = DECODE (2, 2, 2, moqd.owning_tp_type)
     GROUP BY  moqd.inventory_item_id
  
UNION
    
SELECT mmt.inventory_item_id item_id,
       SUM (primary_quantity) target_qty        
FROM   mtl_material_transactions mmt,
       mtl_txn_source_types mtst,
       mtl_parameters mp
       WHERE   mmt.organization_id = 204 AND mp.organization_id = 204
                                         AND transaction_date >= (sysdate + 1)
       AND NVL (mmt.owning_tp_type, 2) =
                 DECODE (2, 2, 2, NVL (mmt.owning_tp_type, 2))
       AND mmt.transaction_source_type_id =
                    mtst.transaction_source_type_id
       AND NVL (mmt.logical_transaction, 2) <> 1
       GROUP BY  mmt.inventory_item_id)
       GROUP BY   ITEM_ID

PRAGMA(Pragma Autonomous_transaction)

PRAGMASignifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They do not affect the meaning of a program; they simply convey information to the compiler.

1) Autonomous Transaction

Autonomous Transactions is the child transaction, which are Independent of Parent transactions. In Our Example, p1 is child transaction, which is used in the Parent transaction. 

Example: -

CREATE or REPLACE Procedure p1 IS
Pragma Autonomous_transaction;
BEGIN
INSERT INTO TEST_T VALUES (1111,’PHANI1’);
COMMIT;
END;

In the Declaration section, you will declare this Transaction as the Autonomous Transaction.

DECLARE
A NUMBER;
BEGIN
INSERT INTO TEST_T VALUES (2222,’JACK’);
P1;
ROLLBACK;
END;

NOW Table has (1111,’PHANI’) Record. COMMIT in the PROCEDURE P1 have not commit the Outside (p1) DML operations. It will just commit p1 transactions.

The ROLLBACK will not rollback PHANI record, it will just rollback the JACK record.

CREATE or REPLACE Procedure p1 IS
BEGIN
INSERT INTO TEST_T VALUES (1111,’PHANI1’);
COMMIT;
END;

If I remove the Pragma Autonomous_transaction From the declaration section, then this transaction will become the normal transaction. Now if you try to use the same parent transaction as given below.

>> delete from TEST_T;

DECLARE
A NUMBER;
BEGIN
INSERT INTO TEST_T VALUES (2222,’JACK’);
P1; -- This transaction has ended with the COMMIT;
ROLLBACK;
END;

After executing the above transaction, you can see BOTH records got Inserted (PHANI and JACK records). Here COMMIT in P1 will commit both transactions (PHANI and JACK Records Insert) And then Rollback. Since, there are no transactions happening between COMMIT and ROLLBACK. Our ROLLBACK is not doing any ROLLBACK.

Note: - IF COMMIT is not given in P1 then, the ROLLBACK will do the ROLLBACK both the INSERT transaction (PHANI Record which is in p1 procedure and JACK Record).

 

2) Pragma Restrict_references

It gives the Purity Level of the Function in the package.

CREATE OR REPLACE PACKAGE PKG12 AS FUNCTION F1 RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (F1, WNDS,
RNDS,
WNPS,
RNPS);
END PKG12;

CREATE OR REPLACE PACKAGE BODY PKG12 AS
FUNCTION F1 RETURN NUMBER IS
X NUMBER;
BEGIN
SELECT EMPNO INTO X FROM SCOTT.EMP
WHERE ENAME LIKE ‘SCOTT’;
DBMS_OUTPUT.PUT_LINE (X);
RETURN (X);
END F1;
END PKG12;

You will get the Violate It’s Associated Pragma Error. This in Purity Level, we said

It cannot read from the database. RNDS (In Our Function F1, we have SELECT STATEMENT which is reading the data from the database).

 

3) Pragma SERIALLY_REUSABLE

In my 5 Years of Experience in the Oracle Applications, I have never found the requirement to use this feature :). But, I found this feature is used in some standard Oracle Packages. We may use this feature for improving the performance or to meet certain requirements.

This pragma is appropriate for packages that declare large temporary work areas that are used once and not needed during subsequent database calls in the same session.

You can mark a bodiless package as serially reusable. If a package has a spec and body, you must mark both. You cannot mark only the body.

The global memory for serially reusable packages is pooled in the System Global Area (SGA), not allocated to individual users in the User Global Area (UGA). That way, the package work area can be reused. When the call to the server ends, the memory is returned to the pool. Each time the package is reused, its public variables are initialized to their default values or to NULL.

Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are called from SQL statements. If you try, Oracle generates an error.

Examples

WITH PRAGMA SERIALLY_REUSABLE

The following example creates a serially reusable package:

CREATE PACKAGE pkg1 IS

PRAGMA SERIALLY_REUSABLE;

num NUMBER := 0;

PROCEDURE init_pkg_state(n NUMBER);

PROCEDURE print_pkg_state;

END pkg1;

/

CREATE PACKAGE BODY pkg1 IS

PRAGMA SERIALLY_REUSABLE;

PROCEDURE init_pkg_state (n NUMBER) IS

BEGIN

pkg1.num := n;

END;

PROCEDURE print_pkg_state IS

BEGIN

dbms_output.put_line('Num: ' || pkg1.num);

END;

END pkg1;

/

begin

pkg1.init_pkg_state(10);

pkg1.PRINT_PKG_STATE;

end;

Num: 10

begin

pkg1.PRINT_PKG_STATE;

end;

Num: 0

Note: - The first block is changing the value of the variable (num) to 10 and if I check the value in same block then it is showing the changed value that is 10. But, if I try to check the value of the (num) variable then it should the default value given to it (i.e.) “0”

WITHOUT PRAGMA SERIALLY_REUSABLE

CREATE OR REPLACE PACKAGE pkg1 IS

num NUMBER := 0;

PROCEDURE init_pkg_state(n NUMBER);

PROCEDURE print_pkg_state;

END pkg1;

CREATE PACKAGE BODY pkg1 IS

PROCEDURE init_pkg_state (n NUMBER) IS

BEGIN

pkg1.num := n;

END;

PROCEDURE print_pkg_state IS

BEGIN

dbms_output.put_line('Num: ' || pkg1.num);

END;

END pkg1;

begin

pkg1.init_pkg_state(10);

pkg1.PRINT_PKG_STATE;

end;

>>Num: 10

begin

pkg1.PRINT_PKG_STATE;

end;

>>Num: 10

Note: - Now, you may noticed the difference. The second block is giving us the changed value.

DROP PACKAGE pkg1;

Concurrent Request Status

SELECT   fcr.phase_code,
         DECODE (fcr.phase_code,'C', 'Completed', 'P', 'Pending', 'R', 'Running',
         'I',  'Inactive', fcr.phase_code) phase,
         fcr.status_code,
         DECODE (fcr.status_code,'A', 'Waiting',
                                 'B', 'Resuming',
                                 'C', 'Normal',
                                 'D', 'Cancelled',
                                 'E', 'Error',
                                 'F', 'Scheduled',
                                 'G', 'Warning',
                                 'H', 'On Hold',
                                 'I', 'Normal',
                                 'M', 'No Manager',
                                 'Q', 'Standby',
                                 'R', 'Normal',
                                 'S', 'Suspended',
                                 'T', 'Terminating',
                                 'U', 'Disabled',
                                 'W', 'Paused',
                                 'X', 'Terminated',
                                 'Z', 'Waiting',
                                 fcr.status_code) status,
         request_date,
         fat.description, frt.responsibility_name, fu.user_name,
         fu.description, fcpt.user_concurrent_program_name, fcpt.description,
         fcr.request_id, fcr.request_date, fcr.priority, fcr.requested_start_date, fcr.hold_flag,
         fcr.number_of_arguments, fcr.number_of_copies, fcr.save_output_flag,
         fcr.printer, fcr.parent_request_id, fcr.description,
         fcr.resubmit_time, fcr.resubmit_end_date, fcr.argument_text,
         fcr.argument1, fcr.argument2, fcr.argument3, fcr.argument4,
         fcr.argument5, fcr.argument6, fcr.argument7, fcr.argument8,
         fcr.argument9 org, fcr.argument10, fcr.argument11, fcr.argument12,
         fcr.argument13, fcr.argument14, fcr.argument15, fcr.argument16,
         fcr.argument17, fcr.argument18, fcr.argument19, fcr.argument20,
         fcr.argument21, fcr.argument22, fcr.argument23, fcr.argument24,
         fcr.argument25, fcr.output_file_type, fcr.cancel_or_hold,
         fcr.completion_code, fcr.ofile_size, fcr.lfile_size,
         fcr.logfile_name, fcr.logfile_node_name, fcr.outfile_name,
         fcr.outfile_node_name
    FROM fnd_concurrent_requests fcr,
         fnd_user fu,
         fnd_responsibility_tl frt,
         fnd_application_tl fat,
         fnd_concurrent_programs_tl fcpt
   WHERE (fu.user_id = fcr.requested_by)
     AND (fat.application_id = fcr.program_application_id)
     AND (fcr.concurrent_program_id = fcpt.concurrent_program_id)
     AND (fcr.responsibility_id = frt.responsibility_id)
     AND fat.LANGUAGE = 'US'
     AND frt.LANGUAGE = 'US'
     AND fcpt.LANGUAGE = 'US'
     AND fcr.request_id = NVL (:request_id, fcr.request_id)
ORDER BY fcr.request_date DESC