Query to get GRN Details in Oracle APPS R12

SELECT    

       DISTINCT rsl.to_subinventory

           ,rts.invoice_status_code

   ,phs.org_id

   ,phs.segment1 po_number

   ,rts.transaction_date

           FROM po_vendor_sites_all pvss

               ,po_vendor_sites_all pvss2

               ,po_vendors pvds

               ,po_headers_all phs

               ,po_lines_all pls

               ,po_line_locations_all plls

               ,rcv_shipment_headers rsh

               ,rcv_shipment_lines rsl

               ,inl_ship_lines_all isl

               ,rcv_transactions rts

               ,mtl_system_items_b msi

          WHERE rts.shipment_header_id = rsh.shipment_header_id

            AND rts.po_header_id = phs.po_header_id

            AND rts.po_line_location_id = plls.line_location_id

            AND rts.po_line_id = pls.po_line_id

            AND rts.shipment_header_id = rsl.shipment_header_id

            AND rts.shipment_line_id = rsl.shipment_line_id

            AND rts.lcm_shipment_line_id = isl.ship_line_id(+)

            AND rts.lcm_adjustment_num = isl.adjustment_num(+)

            AND phs.vendor_id = pvds.vendor_id

            AND phs.vendor_site_id = pvss.vendor_site_id

            AND rsh.receipt_source_code = 'VENDOR'

            AND rts.source_document_code = 'PO' 

            AND phs.segment1 = '1234567890'           --- Purchase Order Number/LPO Number

            --AND NVL (rts.invoice_status_code, 'NA') IN ('PENDING', 'REJECTED')

            AND (   (rts.transaction_type = 'RECEIVE')

                 OR (    'N' = 'Y'

                     AND rts.transaction_type = 'CORRECT'

                     AND rts.timecard_id IS NOT NULL

                     AND EXISTS (

                            SELECT 1

                              FROM apps.rcv_transactions prt

                             WHERE prt.transaction_type = 'RECEIVE'

                               AND prt.source_document_code = 'PO'

                               AND prt.transaction_id =

                                                     rts.parent_transaction_id

                               AND prt.invoice_status_code = 'INVOICED'

                               AND prt.last_update_date <= rts.creation_date)

                    )

                )

            AND pvss.pay_on_code IN ('RECEIPT', 'RECEIPT_AND_USE')

            AND phs.pay_on_code IN ('RECEIPT', 'RECEIPT_AND_USE')

            AND NVL (plls.consigned_flag, 'N') <> 'Y'

            AND pvss2.vendor_site_id = NVL (pvss.default_pay_site_id, pvss.vendor_site_id)

            AND NVL (rsh.asn_type, ' ') <> 'ASBN'

            AND rts.po_release_id IS NULL

            AND rsl.item_id = msi.inventory_item_id

            AND rts.organization_id = msi.organization_id

            AND rts.transaction_date >= '01-JAN-2024'

Query to get POR is Enabled at Supplier Site and PO Level in Oracle APPS R12

SELECT DISTINCT apss.pay_on_code por_supp_site

               ,pha.pay_on_code por_po

           FROM ap_suppliers aps

       ,ap_supplier_sites_all apss

   ,po_headers_all pha

          WHERE aps.vendor_id = apss.vendor_id

            AND pha.vendor_id = apss.vendor_id

            AND pha.vendor_site_id = apss.vendor_site_id

            AND pha.segment1 = '1234567890'  --- Purchase Order Number/LPO Number

Query to get Requisition to PO Details in Oracle Fusion

SELECT DISTINCT PRHA.REQUISITION_NUMBER,

                PHA.SEGMENT1 PO_NUMBER,

                PAH.OBJECT_TYPE_CODE,

                PAH.OBJECT_SUB_TYPE_CODE,

                PAH.SEQUENCE_NUM,

                PPTF.FULL_NAME REQUESTER,

                PAH.ACTION_CODE,

                PAH.ACTION_DATE

  FROM POR_REQUISITION_HEADERS_ALL PRHA,

       POR_REQUISITION_LINES_ALL PRLA,

       POR_REQ_DISTRIBUTIONS_ALL PRDA,

       PO_HEADERS_ALL PHA,

       PO_LINES_ALL PLA,

       PO_DISTRIBUTIONS_ALL PDA,

       PO_ACTION_HISTORY PAH,

       PER_PERSON_NAMES_F PPTF

 WHERE 1 = 1

       AND PRHA.requisition_number IN

              ('REQ-12345')

       AND PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID

       AND PRLA.REQUISITION_LINE_ID = PRDA.REQUISITION_LINE_ID

       AND PRDA.DISTRIBUTION_ID = PDA.REQ_DISTRIBUTION_ID

       AND PDA.PO_HEADER_ID = PHA.PO_HEADER_ID

       AND PDA.PO_LINE_ID = PLA.PO_LINE_ID

       AND PRHA.REQUISITION_HEADER_ID = PAH.OBJECT_ID

       AND PPTF.PERSON_ID = PAH.PERFORMER_ID

       AND PPTF.name_type='GLOBAL'

       AND TRUNC(SYSDATE) BETWEEN PPTF.effective_start_date AND PPTF.effective_end_date

AP_SUPPLIER_SITES_ALL.PAY_GROUP_LOOKUP_CODE or PO_VENDOR_SITES_ALL.PAY_GROUP_LOOKUP_CODE in Oracle APPS

SELECT *

  FROM apps.fnd_lookup_values flv

 WHERE     flv.lookup_type = 'PAY GROUP'

       AND flv.LANGUAGE = USERENV ('LANG')

       AND flv.enabled_flag = 'Y'

       AND SYSDATE BETWEEN NVL (flv.START_DATE_ACTIVE, SYSDATE)

                       AND NVL (flv.END_DATE_ACTIVE, SYSDATE)

LOOKUP_CODE will be AP_SUPPLIER_SITES_ALL.PAY_GROUP_LOOKUP_CODE or PO_VENDOR_SITES_ALL.PAY_GROUP_LOOKUP_CODE

Query to extract Purchase Document Types in Oracle APPS

  SELECT haou.name operating_unit,

         flv1.meaning || ' ' || flv2.meaning document_type,

         pdtav.type_name name,

         xtv1.template_name document_types_layout,

         xtv2.template_name contract_terms_layout,

         DECODE (pdtav.can_preparer_approve_flag,  'Y', 'Yes',  'N', 'No')

            can_preparer_approve,

         INITCAP (pdtav.security_level_code) security_level,

         pdtav.document_type_code,

         pdtav.document_subtype

    FROM hr_all_organization_units haou,

         po_document_types_all_vl pdtav,

         fnd_lookup_values flv1,

         fnd_lookup_values flv2,

         xdo_templates_vl xtv1,

         xdo_templates_vl xtv2

   WHERE 1 = 1

         AND haou.organization_id = pdtav.org_id

         AND pdtav.document_type_code = flv1.lookup_code(+)

         AND pdtav.document_subtype = flv2.lookup_code(+)

         AND flv1.lookup_type(+) = 'DOCUMENT TYPE'

         AND flv2.lookup_type(+) =

                DECODE (pdtav.document_type_code,

                        'REQUISITION', 'REQUISITION TYPE',

                        'RFQ', 'RFQ SUBTYPE',

                        'QUOTATION', 'QUOTATION SUBTYPE',

                        'DOCUMENT SUBTYPE')

         AND flv1.language(+) = USERENV ('lang')

         AND flv2.language(+) = USERENV ('lang')

         AND flv1.view_application_id(+) = 201

         AND flv2.view_application_id(+) = 201

         AND flv1.security_group_id(+) = 0

         AND flv2.security_group_id(+) = 0

         AND pdtav.document_template_code = xtv1.template_code(+)

         AND pdtav.contract_template_code = xtv2.template_code(+)

ORDER BY haou.name

        ,pdtav.document_type_code

        ,pdtav.type_name

API to delete Purchase Orders in Oracle APPS

Note: We had a requirement to delete Purchase Orders which are in INCOMPLETE status during migration. Please check with oracle or your team for your requirement. This is just to give some idea.  Do not execute in production.

DECLARE

l_result  BOOLEAN;

BEGIN

FOR i IN (SELECT po_header_id

,type_lookup_code 

FROM po_headers_all 

       WHERE authorization_status = 'INCOMPLETE' 

AND org_id = 12345)

LOOP

DBMS_OUTPUT.put_line ( 'API PO_HEADERS_SV1 Call to delete PO with header :'||i.po_header_id);

l_result := po_headers_sv1.delete_po (x_po_header_id     => i.po_header_id

,x_type_lookup_code => i.type_lookup_code

,p_skip_validation  => 'N'

);

IF l_result = TRUE THEN

COMMIT;

DBMS_OUTPUT.put_line ( 'PO with header: '|| i.po_header_id||',Deleted Successfully');

ELSE

ROLLBACK;

DBMS_OUTPUT.put_line ( 'PO with header: '|| i.po_header_id||',Failed to Delete');

END IF;

END LOOP;

DBMS_OUTPUT.put_line ( 'Deletion Process Over');

   

EXCEPTION

WHEN OTHERS THEN

    DBMS_OUTPUT.put_line ( 'Error : '|| SQLERRM);

END;

Query to get PO With Approval , Invoice and Payment Details in Oracle APPS

SELECT inv.org_id

      ,supp.segment1 supplier_number

      ,supp.vendor_name supplier_name

      ,UPPER (supp.vendor_type_lookup_code) supplier_type

      ,supp_site.vendor_site_code supplier_site_code

      ,supp_site.address_line1

  ,supp_site.address_line2

  ,supp_site.address_line3

  ,supp_site.address_line4

      ,supp_site.city 

      ,supp_site.country

      ,TO_CHAR (po_hdrs.creation_date, 'DD-MON-RRRR') po_creation_date

      ,po_hdrs.segment1 po_number

      ,po_hdrs.type_lookup_code po_type

      ,po_dist.quantity_ordered

      ,po_dist.quantity_cancelled

      ,po_lines.item_id 

      ,po_lines.item_description item_description

      ,po_lines.unit_price

      ,(NVL (po_dist.quantity_ordered, 0) - NVL (po_dist.quantity_cancelled, 0))

       * NVL (po_lines.unit_price, 0)

          po_line_amount

      ,(SELECT DECODE (ph.approved_flag, 'Y', 'Approved'

                                   , 'R', 'Requires Reapproval'

   , 'F', 'Failed Approval'

   , 'N', 'Never Approved'

   )

          FROM po.po_headers_all ph

         WHERE ph.po_header_id = po_hdrs.po_header_id)

          po_approved_status

      ,inv.invoice_type_lookup_code invoice_type

      ,inv.invoice_amount invoice_amount

      ,TO_CHAR(inv.invoice_date, 'DD-MON-RRRR') invoice_date

      ,inv.invoice_num 

      ,(SELECT DECODE (ap_inv_dist.match_status_flag, 'A', 'Approved'

                                                , 'T', 'Tested but not Validate'

,'Never Validated')

          FROM ap.ap_invoice_distributions_all ap_inv_dist

         WHERE ap_inv_dist.invoice_distribution_id = inv_dist.invoice_distribution_id) invoice_approved_status

      ,inv.amount_paid

      ,inv_pmt.amount

      ,inv_pmt.check_id

      ,inv_pmt.invoice_payment_id payment_id

      ,chk.check_number cheque_number

      ,TO_CHAR(chk.check_date, 'DD-MON-RRRR') check_date

  FROM ap_invoices_all inv

      ,ap_invoice_distributions_all inv_dist

  ,po_distributions_all po_dist

  ,po_headers_all po_hdrs

  ,ap_suppliers supp

  ,ap_supplier_sites_all supp_site

  ,po_lines_all po_lines

  ,ap_invoice_payments_all inv_pmt

      ,ap_checks_all chk

 WHERE     inv.invoice_id = inv_dist.invoice_id

       AND inv_dist.po_distribution_id = po_dist.po_distribution_id(+)

       AND po_dist.po_header_id = po_hdrs.po_header_id(+)

       AND supp.vendor_id(+) = po_hdrs.VENDOR_ID

       AND supp_site.vendor_site_id(+) = po_hdrs.vendor_site_id

       AND po_hdrs.po_header_id = po_lines.po_header_id

       AND po_dist.po_line_id = po_lines.po_line_id

       AND inv.invoice_id = inv_pmt.invoice_id

       AND inv_pmt.check_id = chk.check_id

       AND supp_site.vendor_site_id = chk.vendor_site_id

       AND po_dist.po_header_id IS NOT NULL

       AND inv.payment_status_flag = 'Y'

       AND po_hdrs.type_lookup_code != 'BLANKET'

PO_HEADERS_ALL.APPROVED_FLAG in Oracle APPS

SELECT *

  FROM fnd_lookup_values

 WHERE 1 = 1 

      AND lookup_type = 'PO APPROVAL'

      AND enabled_flag = 'Y'

      AND language = USERENV('LANG')

      AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE)

Here lookup_code is APPROVED_FLAG in PO_HEADERS_ALL.

Query to get all Purchase Requisition without a Purchase Order in Oracle APPS

SELECT prh.segment1 requisition_number,

         TO_CHAR(prh.creation_date, 'DD-MON-RRRR') created_on,

         TO_CHAR(prl.creation_date, 'DD-MON-RRRR') line_creation_date,

         prl.line_num,

         msi.segment1 item_code,

         prl.item_description description,

         prl.quantity ,

         TO_CHAR(prl.need_by_date, 'DD-MON-RRRR') required_date,

         requestor.full_name requestor_name,

         buyers.agent_name buyer_name

    FROM po.po_requisition_headers_all prh,

         po.po_requisition_lines_all prl,

         apps.per_people_f requestor,

         (SELECT DISTINCT agent_id, agent_name 

    FROM apps.po_agents_v) buyers,

         po.po_req_distributions_all prd,

         inv.mtl_system_items_b msi,

         po.po_line_locations_all pll,

         po.po_lines_all pl,

         po.po_headers_all ph

   WHERE     prh.requisition_header_id = prl.requisition_header_id

         AND prl.requisition_line_id = prd.requisition_line_id

         AND requestor.person_id = prh.preparer_id

         AND prh.creation_date BETWEEN requestor.effective_start_date

                                   AND requestor.effective_end_date

         AND buyers.agent_id(+) = msi.buyer_id

         AND msi.inventory_item_id = prl.item_id

         AND msi.organization_id = prl.destination_organization_id

         AND pll.line_location_id(+) = prl.line_location_id

         AND pll.po_header_id = ph.po_header_id(+)

         AND pll.po_line_id = pl.po_line_id(+)

         AND prh.authorization_status = 'APPROVED'

         AND pll.line_location_id IS NULL

         AND prl.closed_code IS NULL

         AND NVL (prl.cancel_flag, 'N') <> 'Y'

         AND prh.org_id = 123456

ORDER BY 1, 2

Query to get Cancelled Requisitions in Oracle APPS

SELECT prh.requisition_header_id,

       (SELECT full_name 

           FROM per_all_people_f 

          WHERE person_id = prh.preparer_id

            AND SYSDATE BETWEEN effective_start_date AND effective_end_date) preparer,

       prh.segment1 requisition_num,

       TO_CHAR(TRUNC (prh.creation_date), 'DD-MON-RRRR') creation_date,

       prh.description,

       prh.note_to_authorizer

  FROM apps.po_requisition_headers_all prh

      ,apps.po_action_history pah

 WHERE action_code = 'CANCEL'

   AND pah.object_type_code = 'REQUISITION'

   AND pah.object_id = prh.requisition_header_id

   ORDER BY prh.creation_date

Query to get all Internal Requisitions that do not have an associated Internal Sales Order

  SELECT rqh.segment1 req_num,

         rql.line_num,

         rql.requisition_header_id,

         rql.requisition_line_id,

         rql.item_id,

         rql.unit_meas_lookup_code,

         rql.unit_price,

         rql.quantity,

         rql.quantity_cancelled,

         rql.quantity_delivered,

         rql.cancel_flag,

         rql.source_type_code,

         rql.source_organization_id,

         rql.destination_organization_id,

         rqh.transferred_to_oe_flag

    FROM po_requisition_lines_all rql

    ,po_requisition_headers_all rqh

   WHERE rql.requisition_header_id = rqh.requisition_header_id

     AND rql.source_type_code = 'INVENTORY'

     AND rql.source_organization_id IS NOT NULL

     AND NOT EXISTS

(SELECT 'Y'--Existing Internal Order

   FROM oe_order_lines_all lin

       ,oe_order_sources order_source

  WHERE lin.source_document_line_id = rql.requisition_line_id

     AND lin.source_document_type_id = order_source.order_source_id

AND order_source.name = 'Internal'

)

ORDER BY rqh.requisition_header_id

        ,rql.line_num;

Query to get Position Hierarchy details in Oracle APPS

SELECT DISTINCT hpx.business_group_id,

                hpx.entry_grade_id,

                hpx.job_id,

                hpx.location_id,

                hpx.organization_id,

                hpx.fte,

                hpx.max_persons,

                hpx.name position,

                ppx.full_name,

                hier.lev

  FROM hr_positions_x hpx,

       per_assignments_x pax,

       per_people_x ppx,

       (    SELECT pse.parent_position_id, LEVEL lev

              FROM per_pos_structure_elements pse

             WHERE 1 = 1

                   AND pse.pos_structure_version_id =

                          (SELECT ppsv.pos_structure_version_id

                             FROM per_position_structures pps,

                                  per_pos_structure_versions ppsv

                            WHERE pps.position_structure_id =

                                     ppsv.position_structure_id

                                  AND pps.name = :p_hierarchy_name

                                  AND TRUNC (SYSDATE) BETWEEN ppsv.date_from

                                                          AND NVL (

                                                                 ppsv.date_to,

                                                                 TO_DATE (

                                                                    '31-Dec-4712',

                                                                    'DD-MON-YYYY')))

        START WITH pse.subordinate_position_id =

                      (SELECT position_id

                         FROM hr_positions_x

                        WHERE name = :p_position_name)

        CONNECT BY PRIOR pse.parent_position_id = pse.subordinate_position_id

          ORDER BY LEVEL ASC) hier

 WHERE     hier.parent_position_id = hpx.position_id

       AND pax.position_id = hier.parent_position_id

       AND pax.person_id = ppx.person_id

Import Standard Purchase Order Program or Import Price Catalog Failed in Oracle APPS

Below solution is from Oracle Support Doc ID 1632117.1

Caution: Before doing any changes please validate it from Oracle Support.

1)  Verify the executable method in the below PDOI concurrent programs:

     Import Standard Purchase Orders
     Import Price Catalogs
     Import Contract Purchase Agreements

     Steps:
     1. Navigate to System Administrator -> Concurrent -> Program -> Define
     2. Query for the program for example 'Import Standard Purchase Orders'
     3. Check the Executable method defined for the program. If it is 'SQL*Plus' then select the LOV beside Executable 'POXPDOI' and reselect the executable as'POXPDOI'.
     4. Confirm that the executable method has been changed as 'PL/SQL Procedure'
     5. Do the same for the other programs.

2)  Check whether the attribute 'MULTI_ORG_CATEGORY' is set to 'S' in all the below concurrent programs:

     Import Standard Purchase Orders
     Import Price Catalogs
     Import Contract Purchase Agreements

     Steps:
     1. Navigate to System Administration > Concurrent > Programs
     2. Query for the Program for example 'Import Standard Purchase Orders'
     3. Click on Update pencil icon
     4. Click on Request tab
     5. For field Operating Unit Mode - The value should be 'Single'
     6. Do the same for the other programs.

3)  Test and run the program import standard purchase orders. Program will get completed successfully and should not get any error

      Ex:  'Routine fdpsql cannot read the file
             /u16/oracle/XX/apps/apps_st/appl/po/12.0.0/sql/PO_PDOI_GRP.start_process
 

Purchase Order Type(PO_HEADERS_ALL.TYPE_LOOKUP_CODE)

 select lookup_code, meaning

  from fnd_lookup_values 

  where lookup_type = 'POO:DOCUMENT_TYPE'