Programmatically Close Purchase Order using PO_ACTIONS API

DECLARE
   x_action         CONSTANT VARCHAR2 (20)  := 'FINALLY CLOSE';
   -- Change this parameter as per the requirement
   x_calling_mode   CONSTANT VARCHAR2 (2)   := 'PO';
   x_conc_flag      CONSTANT VARCHAR2 (1)   := 'N';
   x_return_code_h           VARCHAR2 (100);
   x_auto_close     CONSTANT VARCHAR2 (1)   := 'N';
   x_origin_doc_id           NUMBER;
   x_returned                BOOLEAN        := NULL;

BEGIN
 
   apps.mo_global.set_policy_context ('S', 204);
   fnd_global.apps_initialize (1015932, 50578, 201);
   DBMS_OUTPUT.put_line
             ('Calling PO_Actions.close_po for Closing/Finally Closing PO =>');
   x_returned :=
      po_actions.close_po (p_docid              => 131583,
                           p_doctyp             => 'PO',
                           p_docsubtyp          => 'STANDARD',
                           p_lineid             => NULL,
                           p_shipid             => NULL,
                           p_action             => x_action,
                           p_reason             => NULL,
                           p_calling_mode       => x_calling_mode,
                           p_conc_flag          => x_conc_flag,
                           p_return_code        => x_return_code_h,
                           p_auto_close         => x_auto_close,
                           p_action_date        => SYSDATE,
                           p_origin_doc_id      => NULL
                          );
   COMMIT;

   IF x_returned = TRUE
   THEN
      DBMS_OUTPUT.put_line
                   ('Purchase Order which just got Closed to Finally Closed. ');
      DBMS_OUTPUT.put_line (x_return_code_h);

   ELSE
      DBMS_OUTPUT.put_line
                      ('API Failed to Close/Finally Close the Purchase Order');
   END IF;

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

Architecture of Order Management System in Oracle Apps R12

https://drive.google.com/open?id=1tEQjFO-X9Z_5h1RTZX0WvIv82yEmXkbe

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

Key SQL Queries for Purchase Requisition, PO, and Receipt in Oracle Apps R12

SELECT prha.segment1 requisition_number,
       prha.type_lookup_code,
       prha.authorization_status,
       prla.item_description,
       prla.quantity,
       pha.type_lookup_code,
       pha.segment1 po_number,
       pha.document_creation_method,
       pla.item_id,pla.list_price_per_unit,rsh.receipt_num
FROM  po_requisition_headers_all prha,--REQUISITION HEADERS TABLE
      po_requisition_lines_all  prla,--REQUISITIONS LINES TABLE
      po_req_distributions_all prda,--REQUISITIONS DISTRIBUTION TABLE
      po_headers_all pha, --PURCHASE ORDER HEADER TABLE
      po_lines_all pla,--PURCHASE ORDER LINE TABLE
      po_distributions_all pda,--PURCHASE ORDER DISTRIBUTIONS TABLE
      rcv_shipment_headers rsh,--RECEIPT HEADER TABLE
      rcv_shipment_lines rsl--RECEIPT LINE TABLE
WHERE prha.requisition_header_id = prla.requisition_header_id
and  prla.requisition_line_id = prda.requisition_line_id
and  pha.po_header_id = pla.po_header_id
and pla.po_line_id = pda.po_line_id
and prda.distribution_id = pda.req_distribution_id
and rsh.shipment_header_id = rsl.shipment_header_id
and pha.po_header_id = rsl.po_header_id
--and   prha.segment1 = '336276';
and rsh.shipment_header_id = '23163104';

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

Relationship Between Requisition and Purchase Order in Oracle Apps R12 – Explained with Tables

SELECT prha.segment1 "REQ NUM", pha.segment1 "PO NUM"
  FROM po_headers_all pha,
       po_distributions_all pda,
       po_req_distributions_all prda,
       po_requisition_lines_all prla,
       po_requisition_headers_all prha
 WHERE     pha.po_header_id = pda.po_header_id
       AND pda.req_distribution_id = prda.distribution_id
       AND prda.requisition_line_id = prla.requisition_line_id
       AND prla.requisition_header_id = prha.requisition_header_id
       order by prha.segment1 desc;

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

Types of Purchase Orders in Oracle Apps R12

There are 4 types of Purchase Orders:

1. Standard PO: A Standard PO is created for one–time purchase of various items.
2. Planned PO: A Planned PO is a long–term agreement committing to buy items or services from a single source. You must specify tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities, and estimated cost.
3. Blanket agreement: A Blanket PO is created when you know the detail of the goods or services you plan to buy from a specific supplier in a period, but you do not know the detail of your delivery schedules.
4. Contract agreement: Contract purchase agreements are created with your suppliers to agree on specific terms and conditions without indicating the goods and services that you will be purchasing.

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

Query to Find out the Requisition and PO number from Order Number in B2B Order

 SELECT    OOHA.ORDER_NUMBER,
            OOHL.LINE_NUMBER,
            PRHA.SEGMENT1 REQUISITION_NUMBER,
            PRLA.LINE_NUM REQUISITION_LINE_NUMBER,
            PHA.SEGMENT1 PO_NUMBER,
            PLA.LINE_NUM PO_LINE_NUMBER
  FROM   OE_ORDER_HEADERS_ALL OOHA,
         OE_ORDER_LINES_ALL OOHL,
         PO_REQUISITION_HEADERS_ALL PRHA,
         PO_REQUISITION_LINES_ALL PRLA,
         PO_REQ_DISTRIBUTIONS_ALL PRDA,
         PO_DISTRIBUTIONS_ALL PDA,
         PO_HEADERS_ALL PHA,
         PO_LINES_ALL PLA
 WHERE   OOHA.HEADER_ID = OOHL.HEADER_ID
         AND PRHA.INTERFACE_SOURCE_LINE_ID = OOHL.LINE_ID
         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 PHA.PO_HEADER_ID = PLA.PO_HEADER_ID;

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

Query to Find Purchase Order in Oracle Apps R12

SELECT
  O.NAME "OPERATING_UNIT_NAME",
  PH.SEGMENT1 "PO_REV_NUM",
  PH.PO_HEADER_ID,
  PH.TYPE_LOOKUP_CODE "TYPE",
  PH.CURRENCY_CODE,
  PH.AUTHORIZATION_STATUS "STATUS",
  PV.VENDOR_SITE_CODE,
  V.VENDOR_NAME "SUPPLIER_NAME",
  LOC1.LOCATION_CODE "SHIP_TO_LOC",
  LOC2.LOCATION_CODE "BILL_TO_LOC",
  (PL1.QUANTITY*PL1.UNIT_PRICE) "TOTAL",
--LINES INFORMATION
  PL1.LINE_NUM,
  PL1.PURCHASE_BASIS "TYPE",
  I.SEGMENT1 "ITEM",
  PL1.ITEM_REVISION,
  C.SEGMENT1||C.SEGMENT1 "CATEGORY",
  PL1.ITEM_DESCRIPTION,
  PL1.QUANTITY,
  PL.UNIT_MEAS_LOOKUP_CODE,
  PL1.UNIT_PRICE,
  PL.NEED_BY_DATE,
  PL.SHIPMENT_TYPE,
  PL.SHIPMENT_NUM,
  M.ORGANIZATION_CODE "SHP_ORG_CODE",
  LOC1.LOCATION_CODE "SHIPMENT_LOC",
  PL.UNIT_MEAS_LOOKUP_CODE "SHP_UOM",
  PL.QUANTITY "SHP_QUANTITY",
  (PL.QUANTITY*PL.PRICE_OVERRIDE) "SHP_AMOUNT",
  D.DISTRIBUTION_NUM,
  D.DESTINATION_TYPE_CODE,
  GL.CONCATENATED_SEGMENTS,
  D.QUANTITY_ORDERED "DIS_QUANTITY",
  P.FULL_NAME "REQUESTER NAME",
  I.INVENTORY_ITEM_ID,
  C.CATEGORY_ID
FROM
  --PO_HEADERS
  HR_ORGANIZATION_UNITS O,
  po_headers_all PH,
  PO_VENDORS V,
  PO_VENDOR_SITES_ALL PV,
  HR_LOCATIONS_ALL_TL LOC1,
  HR_LOCATIONS_ALL_TL LOC2,
  --PO_LINES
  po_line_locations_all PL,
  PO_LINES_ALL PL1,
  MTL_SYSTEM_ITEMS_B I,
  MTL_CATEGORIES_B C,
  MTL_PARAMETERS M,
  PO_DISTRIBUTIONS_ALL D,
  GL_CODE_COMBINATIONS_KFV GL,
  PER_ALL_PEOPLE_F P
WHERE PH.VENDOR_SITE_ID=PV.VENDOR_SITE_ID
  AND PH.SHIP_TO_LOCATION_ID=PV.SHIP_TO_LOCATION_ID
  AND PH.VENDOR_ID=V.VENDOR_ID
  AND LOC1.LOCATION_ID=PH.SHIP_TO_LOCATION_ID
  AND LOC2.LOCATION_ID=PV.BILL_TO_LOCATION_ID
  AND PH.PO_HEADER_ID=PL1.PO_HEADER_ID
    --LINES
  AND PL1.ITEM_ID=I.INVENTORY_ITEM_ID
  AND PL.PO_HEADER_ID=PL1.PO_HEADER_ID
  AND PL.PO_LINE_ID=PL1.PO_LINE_ID
  AND PL1.CATEGORY_ID=C.CATEGORY_ID
  --ORG_CODE
  AND M.ORGANIZATION_ID=PL.SHIP_TO_ORGANIZATION_ID
  --AND O.ORGANIZATION_ID=M.ORGANIZATION_ID
  --AND C.ORGANIZATION_ID=M.ORGANIZATION_ID
  AND O.ORGANIZATION_ID=PH.ORG_ID
  --DISTRIBUTION
  AND D.PO_HEADER_ID=PL.PO_HEADER_ID
  AND D.PO_LINE_ID=PL.PO_LINE_ID
  AND D.LINE_LOCATION_ID=PL.LINE_LOCATION_ID
  AND GL.CODE_COMBINATION_ID=D.CODE_COMBINATION_ID
  AND D.DELIVER_TO_PERSON_ID=P.PERSON_ID;

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.