Tuesday, 15 April 2025

Oracle APPS R12 APIs

 

Oracle APPS R12 APIs


  1. API To Find Sales Order's Subtotal,discount,charges and Tax (OE_OE_TOTALS_SUMMARY.ORDER_TOTALS)
  2. API for Cancelling the Purchase Order (PO) Document (PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT)
  3. API for Deleting the category assignment to an item (INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY_ASSIGNMENT)
  4. API for Updating Category Assignment of an item (INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_ASSIGNMENT)
  5. API to Book a sales order (OE_ORDER_PUB.PROCESS_ORDER )
  6. API to Cancel a Sales Order (OE_ORDER_PUB.PROCESS_ORDER)
  7. API to Cancel an Order Line (OE_ORDER_PUB.PROCESS_ORDER)
  8. API to Check Existence of an Internal Bank R12 (CE_BANK_PUB.CHECK_BANK_EXIST)
  9. API to Create Item Specific UOM Conversion (INV_CONVERT.CREATE_UOM_CONVERSION)
  10. API to Create a Customer Account for an existing Party TCA R12 (HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT)
  11. API to Create a Customer Profile TCA R12 (HZ_CUSTOMER_PROFILE_V2PUB.CREATE_CUSTOMER_PROFILE)
  12. API to Create a Customer Site TCA R12 (HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE)
  13. API to Create a Customer Site Use TCA R12 (HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE)
  14. API to Create a Party Site TCA R12 (HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE)
  15. API to Create a Party Site Use TCA R12 (HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE_USE)
  16. API to Create a Phone Number (Contacts) TCA R12 (HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT)
  17. API to Create an Internal Bank Account in R12 CE_BANK_PUB.CREATE_BANK_ACCT
  18. API to Create an Internal Bank Branch R12 (CE_BANK_PUB.CREATE_BANK_BRANCH)
  19. API to Delete a Sales Order (OE_ORDER_PUB.PROCESS_ORDER )
  20. API to Delete an Order Line (OE_ORDER_PUB.PROCESS_ORDER)
  21. API to End Date an Internal Bank Branch in R12 - CE_BANK_PUB.SET_BANK_BRANCH_END_DATE
  22. API to End Date an Internal Bank in R12 - CE_BANK_PUB.SET_BANK_END_DATE
  23. API to Update Customer Address in Oracle TCA R12 (HZ_LOCATION_V2PUB.UPDATE_LOCATION)
  24. API to Update Oracle Applications Password (fnd_user_pkg.updateuser)
  25. API to Update Purchase Order Document (PO) (PO_CHANGE_API1_S.UPDATE_PO)
  26. API to Update a Customer Account TCA R12 (HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCOUNT)
  27. API to Update an Internal Bank Branch R12 (CE_BANK_PUB.UPDATE_BANK_BRANCH)
  28. API to Update an Internal Bank in R12 (CE_BANK_PUB.UPDATE_BANK)
  29. API to apply hold on AP invoice in R12 (AP_HOLDS_PKG.INSERT_SINGLE_HOLD)
  30. API to cancel single AP invoice (AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE)
  31. API to confirm on Order Header Status (OE_HEADER_STATUS_PUB)
  32. API to confirm on Order Line Status (OE_LINE_STATUS_PUB)
  33. API to create Group in TCA R12 (HZ_PARTY_V2PUB.CREATE_GROUP)
  34. API to create Party and Customer Account in R12 (HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT)
  35. API to create a Person Type Party and Customer Account TCA R12 (HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT)
  36. API to create customer Addresses in TCA R12 (HZ_LOCATION_V2PUB.CREATE_LOCATION)
  37. API to find AP INVOICE Status (AP_INVOICES_PKG.GET_APPROVAL_STATUS)
  38. API to get combination id based on segment info (FND_FLEX_EXT.GET_COMBINATION_ID)
  39. API to get open sales order quantity in oracle apps (OE_LINE_UTIL.GET_OPEN_QUANTITY)
  40. API to get the concatenated segment values for a code combination id (FND_FLEX_EXT.GET_SEGS)
  41. API to get the formatted contact details of a Party in oracle apps R12 (HZ_FORMAT_PHONE_V2PUB.PHONE_DISPLAY)
  42. API to get the segment delimiter for the specified key flex field structure (FND_FLEX_EXT.GET_DELIMITER)
  43. API to populate loc_id HZ_LOCATIONS R12 (HZ_TAX_ASSIGNMENT_V2PUB. CREATE_LOC_ASSIGNMENT )
  44. API to populate the Descriptive element Value of an item ( inv_item_catalog_elem_pub.process_item_descr_elements)
  45. API to release hold on AP invoice in R12 (AP_HOLDS_PKG.RELEASE_SINGLE_HOLD)
  46. API to update AR Invoice Printing Details in R12 AR_INVOICE_SQL_FUNC_PUB
  47. API to update AR Receipt in oracle apps R12 - AR_RECEIPT_UPDATE_API_PUB (UNIDENTIFIED to UNAPPLIED)
  48. API to update a Customer Account Relationship TCA R12 (HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCT_RELATE)
  49. API to update a Customer Profile TCA R12 (HZ_CUSTOMER_PROFILE_V2PUB.UPDATE_CUSTOMER_PROFILE)
  50. API to update an Internal Bank Account in Oracle Apps R12 (CE_BANK_PUB.UPDATE_BANK_ACCT)
  51. AR: Reversal of Receipt through API (ar_receipt_api_pub.REVERSE)
  52. AR: Unapplication of a Credit Memo through API (ar_cm_api_pub.unapply_on_account)
  53. AR_INVOICE_API_PUB.create_single_invoice
  54. AR_RECEIPT_API_PUB - Script to Create and Apply on account a AR Receipt
  55. AR_RECEIPT_API_PUB.Apply_on_account ( Script to apply a receipt on account )
  56. AR_RECEIPT_API_PUB.CREATE_MISC - R12 - Create Miscellaneous Cash Receipt in Oracle Apps
  57. AR_RECEIPT_API_PUB.Unapply_on_account ( Script to unapply on account a Receipt in R12)
  58. Add New Line to Existing Order Using the API (OE_ORDER_PUB.PROCESS_ORDER)
  59. Ar_receipt_api_pub.Apply
  60. Ar_receipt_api_pub.Create_and_apply
  61. Ar_receipt_api_pub.Create_cash
  62. Ar_receipt_api_pub.Unapply
  63. Assign Delivery Details to a Delivery through API ( WSH_DELIVERY_DETAILS_PUB.DETAIL_TO_DELIVERY )
  64. Assigning Category set to Category via API in Oracle Apps (INV_ITEM_CATEGORY_PUB.CREATE_VALID_CATEGORY)
  65. Assigning category to an Item using API (INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY_ASSIGNMENT)
  66. Autocreate Deliveries Through API WSH_DELIVERY_DETAILS_PUB.AUTOCREATE_DELIVERIES
  67. CASH MANAGEMENT API'S (CE_BANK_PUB) IN ORACLE APPS R12
  68. CE_BANK_PUB.CHECK_BRANCH_EXISTS - API to check existence of an Internal Bank Branch in R12
  69. CE_BANK_PUB.CREATE_BANK API to Create an Internal Bank in R12
  70. CE_BANK_PUB.UPDATE_BANK_BRANCH API to Update an Internal Bank Branch R12
  71. Create Credit Card in Oracle Payments using API (IBY_FNDCPT_SETUP_PUB.CREATE_CARD)
  72. Create Party of type Organization in Oracle TCA using API hz_party_v2pub.create_organization
  73. Create a Customer Account Relationship API HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE
  74. Create and Release Pciking Batch via API (wsh_picking_batches_pub.create_batch and wsh_picking_batches_pub.Release_batch)
  75. Credit Memo Creation via API ( ar_credit_memo_api_pub.create_request )
  76. FND_PROGRAM : Add Concurrent Program to Request Group via API
  77. FND_PROGRAM : Delete Concurrent Program Definition via API
  78. FND_PROGRAM : Delete Concurrent Program Executable via API
  79. FND_PROGRAM : Delete Concurrent Program Parameter via API
  80. FND_PROGRAM : Register Concurrent Program parameters via API
  81. FND_PROGRAM : Register Concurrent Program via API
  82. FND_PROGRAM : Remove Concurrent Program from Request Group via API
  83. FND_PROGRAM : Create Concurrent Executable via API
  84. FND_REQUEST.SUBMIT_REQUEST in R12
  85. FND_USER_PKG.CREATEUSER ( Create Applications User via PLSQL)
  86. GET ONHAND QUANTITIES THROUGH API in Oracle Apps R12 (INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES)
  87. Generation of a LOT number for an Inventory item via API in R12
  88. How to apply invoice in detail against the receipt through api (AR_RECEIPT_API_PUB.Apply_In_Detail)
  89. How to attach documents from backend? ( fnd_webattch.add_attachment )
  90. How to delete a attachment from backend (fnd_attached_documents2_pkg.delete_attachments)
  91. How to do UOM Conversions through api? ( inv_convert.inv_um_convert_new )
  92. How to get the Description of the Item based on Item catalog group using API (invicgds.inv_get_icg_desc)
  93. How to get timezone based on ZIP code? (HZ_TIMEZONE_PUB.GET_TIMEZONE_ID)
  94. How to set context and profile values from backend in R12 Oracle apps
  95. IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK – R12 – API to Create External Bank
  96. IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_ACCT – R12 – API to Create External Bank Account
  97. IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_BRANCH – R12 – API to Create External Bank Branch
  98. IMPORT EXTERNAL BANK ACCOUNTS R12 ORACLE APPS
  99. Item Categories Deletion through API in Oracle Apps (INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY)
  100. Item Categories Updation through API in Oracle Apps (INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY)
  101. Item categories creation through API in Oracle Apps (INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY)
  102. Item categories in oracle apps R12
  103. Item import based on Item template in R12 (insert script)
  104. OE_HOLDS_PUB.APPLY_HOLDS -- Apply Hold Script
  105. OE_HOLDS_PUB.RELEASE_HOLDS -- Release Holds Script
  106. OE_ORDER_PUB.GET_ORDER -- API to collect existing sales order data in R12
  107. OE_ORDER_PUB.PROCESS_ORDER ( Sample Script for R12)
  108. OE_ORDER_PUB.PROCESS_ORDER in Oracle Apps R12
  109. OE_ORDER_PUB.PROCESS_ORDER to Apply hold on a sales order
  110. OE_ORDER_PUB.PROCESS_ORDER to Release a hold on sales order in R12
  111. ORA-01403: no data found in Package AR_RECEIPT_API_PUB Procedure Apply
  112. Picking Batch Creation Through API ( wsh_picking_batches_pub.create_batch)
  113. Price List Import via API (QP_PRICE_LIST_PUB.PROCESS_PRICE_LIST) in R12
  114. Script to Submit Item Import (INCOIN) using FND_REQUEST in Oracle Apps R12
  115. Script to get the Quantity reserved against an sales order line in oracle apps R12 (INV_RESERVATION_PUB.QUERY_RESERVATION_OM_HDR_LINE)
  116. TRADING COMMUNITY ARCHITECTURE (TCA) API’S IN R12 ORACLE APPS
  117. Unassign Delivery Details from Delivery through API ( WSH_DELIVERY_DETAILS_PUB.DETAIL_TO_DELIVERY )
  118. Update Order Header Details Using the API (OE_ORDER_PUB.PROCESS_ORDER)
  119. hz_party_contact_v2pub.create_org_contact - API to create a Contact person for an organization in Oracle TCA

Friday, 11 April 2025

API to repopulate new Tax categories in India GST shipping form of Oracle APPS R12

 /*API to repopulate new Tax categories in India GST shipping form*/

DECLARE
    lv_process_status  VARCHAR2(30);
    lv_process_message VARCHAR2(30);
BEGIN
    FOR c1 IN (SELECT a.*,
                      17305 new_tax_cat_id
               FROM   jai_tax_det_factors a
               WHERE  1 = 1
                      AND a.trx_id = 15562920
                      AND a.application_id = 707) LOOP
        jai_tax_determination_pkg.Override_tax_category_rule(
        pn_tax_rule_id => c1.tax_rule_id,
        pn_tax_category_id => c1.new_tax_cat_id,
        pn_det_factor_id => c1.det_factor_id,
        pv_process_status => lv_process_status,
        pv_process_message => lv_process_message);

        COMMIT;
    END LOOP;

    dbms_output.Put_line('lv_process_status : '||lv_process_status);
EXCEPTION
    WHEN OTHERS THEN
      dbms_output.Put_line('Error : '||SQLERRM);
END; 

Thursday, 10 April 2025

Query to find Total Receive Quantity Against PO In Oracle Apps R12

 /*Query to find Total Receive Quantity Against PO In Oracle Apps R12*/

SELECT ( (SELECT SUM (Nvl (quantity, 0))
          FROM   rcv_transactions rt
          WHERE  rt.po_header_id = p_po_header_id
          AND rt.po_line_id = p_po_line_id
          AND rt.wip_entity_id = p_wip_entity_id
          AND rt.transaction_type = 'RECEIVE') 

         - Nvl ((SELECT SUM (Nvl (quantity, 0))
                 FROM   rcv_transactions rt
                 WHERE  rt.po_header_id p_po_header_id
                 AND rt.po_line_id p_po_line_id
                 AND rt.wip_entity_id = p_wip_entity_id
                 AND rt.transaction_type = 'RETURN TO VENDOR'),0) 

         + Nvl ((SELECT SUM (Nvl (quantity, 0))
                 FROM   rcv_transactions rt
                 WHERE  rt.po_header_id = p_po_header_id
                 AND rt.po_line_id = p_po_line_id
                 AND rt.wip_entity_id = p_wip_entity_id
                 AND rt.transaction_type = 'CORRECT'
                 AND EXISTS (SELECT 1
                             FROM   rcv_transactions rt2
                             WHERE  rt2.po_header_id p_po_header_id
                             AND rt2.po_line_id p_po_line_id
                             AND rt.wip_entity_id p_wip_entity_id
                             AND rt2.transaction_type 'RECEIVE'
                             AND rt2.transaction_id rt.parent_transaction_id)),0) )
       qty
FROM   dual; 

Overview of Purchasing in Oracle Apps

Overview of Purchasing in Oracle Apps

Create requisition to procure goods and service with supplier information, delivery instructions, multiple accounting distributions, and notes to buyers, approvers, and receivers. A request for quotation (RFQ) is sent to a supplier to request pricing and other information for an item. A quotation is the supplier’s response to that RFQ. Identify requisitions that require supplier quotations and automatically create a RFQ Or create manually and send it thru’ Fax or iSupplier portal. Record supplier quotations from a catalog, telephone conversation, or response from your RFQ. You can also receive quotations electronically and import as Quotations (catalog). Review, analyze, evaluate and approve supplier quotations. Create standard purchase order, BPA and blanket releases. Inform your suppliers of your shipment schedule requirements. Record supplier acceptances of your purchase order’s terms and conditions. Provide a quantity and price for each item you are ordering. Alternatively, you should also be able to create your purchase order simply by providing an amount if you are ordering a service that you cannot break down by price and quantity. Enter goods and service receipt information against the PO using routing controls viz: Direct delivery, standard receipt or standard receipt with inspection. Transfer and deliver goods using the Receiving Transactions window. If you want to perform an inspection transaction, you can open the Inspections window to specify accepted and rejected quantities.
This cycle involves following steps from creating a requisition to transfer the details to GL.
1. Create Requisition
Approve requisition
2. Create Purchase Order
Approve Purchase Order
3. Create Receipt after receiving the goods
4. Create an Invoice in AP
5. Pay the invoice
6. Transfer, Import and Post Journal to GL

Basic Components of Procurement

Requisition

Requisition is nothing but a formal request to buy something (like Inventory material, office supplies etc) needed for the enterprise. Only an employee can create one.
With on-line requisitions, you can centralize your purchasing department, source your requisitions with the best suppliers, and ensure that you obtain the appropriate management approval before creating purchase orders from requisitions.

Purchasing provides you with the features you need to satisfy the following basic requisition needs. You should be able to:

· Create, edit, and review requisition information on-line. You should also be able to enter suggested supplier information, delivery instructions, multiple accounting distributions, and notes to buyers, approvers, and receivers.
· Review the current status and action history of your requisitions. You should always know who approves requisitions and whether they are in the approval, purchasing, receiving, or delivery stage.
· Route requisitions according to your approval structure. You should also be able to set authorization limits by amount, charge account, item category, and location.
· Review and approve requisitions that need your approval. You should also be able to see the full requisition detail and review the action history before you approve a requisition.
· Print requisitions (with status Approved, Cancelled, Rejected, In Process, Pre-Approved, and Returned) for off-line review and approval. You should always be able to track the status of requisitions through the approval process.
· Import requisitions from other systems such as material or distributions requirement planning applications
· Perform on-line funds checking before creating requisitions. You should always know how your planned expenses compare to your budget.
· Automatically source requisitions from outstanding blanket purchase agreements or quotations you have received from suppliers
· Create requisitions quickly and easily for commonly purchased items
· Provide attachments as notes on requisition headers and lines
· Assign requisition lines to buyers and review buyer assignments for requisition lines
· Forward all requisitions awaiting approval from one approver to an alternate approver. Within your security and approval constraints, you should be able to reroute requisitions from one approver to another whenever you want.
· Record suggested foreign currency information for each requisition line

Requisition Types:

1. Purchasing Requisition: Purchase requisitions are used for requesting material from suppliers.
2. Internal Requisition: Internal requisitions provide the mechanism for requesting and transferring material from one inventory to other inventory

RFQ

A request for quotation (RFQ) is sent to a supplier to request pricing and other information for an item. A quotation is the supplier’s response to that RFQ. You send an RFQ to a supplier by fax, making a phone call, or using Oracle iSupplier Portal. A supplier can send a quotation, whether or not in response to an RFQ, is through the Purchasing Documents Open Interface.If you don’t receive quotations electronically from your supplier, you can create the quotation manually using the Quotations window, or copy the quotation from an RFQ.

Using Quotation for Purchase Order

When you create a purchase order (manually or from requisitions), you can use the Supplier Item Catalog window to retrieve quotation information. (The Supplier Item Catalog window can include quotations sent to you by your supplier through the Purchasing Documents Open Interface.) Purchasing provides all your approved quotation shipment information for a specific item or manufacturing category. You can copy this quotation shipment to an existing blanket purchase agreement or standard purchase order when you add this item or purchasing category to a purchase order line. You can sort this quotation information according to your needs, using criteria such as price or quantity. You can easily evaluate the source that is best for an item.
After you select the quotation shipment you want to use, Purchasing copies the item unit price, quantity, unit of measure, supplier product number, inspection required status, receipt required status, quotation number, quotation type, and supplier quotation number on your purchase order. Purchasing also copies the quotation item description on your purchase order if you define your items to do so. Purchasing automatically warns you when the terms and conditions of the quotation are different from the terms and conditions of your purchase order. The original purchase order terms and conditions remain unchanged.

Types of Quotations

There are three types of quotations and RFQs that come with Purchasing by default:
Bid: Used for a specific, fixed quantity, location, and date. For example, a Bid would be used for a large or expensive piece of equipment that you’ve never ordered before, or for an item that incurs transportation or other special costs. You cannot specify price breaks for a Bid quotation or RFQ.
Standard: Used for items you’ll need only once or not very often, but not necessarily for a specific, fixed quantity, location, and date. For example, you could use a Catalog quotation or RFQ for office supplies, but use a Standard quotation or RFQ for a special type of pen you don’t order very often. A Standard quotation or RFQ also includes price breaks at different quantity levels.
Catalog: Used for high–volume items or items for which your supplier sends you information regularly. A Catalog quotation or RFQ also includes price breaks at different quantity levels.
For all three types, you can define effectivity dates at the header level.
For Catalog and Standard quotations, you can also specify effectivity dates for individual price breaks. (For a Bid, you cannot specify effectivity dates at the shipment level.) You can also define your own RFQ or quotation types using the
Document Types window.

Purchase Order

Purchasing provides the Purchase Orders window that you can use to enter Standard and planned purchase orders as well as Blanket and Contract purchase agreements. You must be defined as a buyer to use this window.
Purchasing provides you the features you need to satisfy the following purchasing needs. You should be able to:
· Review all of your purchases with your suppliers to negotiate better discounts
· Create purchase orders simply by entering a supplier and item details
· Create standard purchase orders and blanket releases from both on-line and paper requisitions
· Create accurate and detailed accounting information so that you charge purchases to the appropriate departments
· Check your funds availability while creating purchase orders.
· Review the status and history of your purchase orders at any time for all the information you need
· Print purchase orders flexibly by using a number of print options
· Inform your suppliers of your shipment schedule requirements
· Record supplier acceptances of your purchase orders. You always know whether your suppliers have received and accepted your purchase order terms and conditions
· Create your purchase orders by providing a quantity and price for each item you are ordering. Alternatively, you should also be able to create your purchase order simply by providing an amount if you are ordering a service that you cannot break down by price and quantity

Purchase Order Types

There are mainly 4 types of Purchase Orders
· Standard Purchase Order
· Blanket Purchase Agreements
· Contract Purchase Agreements
· Planned Purchase Orders

Standard Purchase Order

You generally create standard purchase orders for one-time purchase of various items. You create standard purchase orders when you know the details of the goods or services you require, estimated costs, quantities, delivery schedules, and accounting distributions.

Blanket Purchase Agreements (BPA)

You create blanket purchase agreements 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 yet know the detail of your delivery schedules. You can use blanket purchase agreements to specify negotiated prices for your items before actually purchasing them.
You can issue a Blanket release against a BPA to place the actual order (as long as the release is within the blanket agreement effectively dates).

Contract Purchase Agreements

You create contract purchase agreements with your suppliers to agree on specific terms and conditions without indicating the goods and services that you will be purchasing. You can later issue standard purchase orders referencing your contracts.

Planned Purchase Orders

A planned purchase order 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.
You can issue scheduled releases against a planned purchase order to place the actual orders. If you use encumbrance accounting, you can use the planned purchase order to reserve funds for long term agreements.

Wednesday, 9 April 2025

Purchase Order Master Query In Oracle Apps R12

 

/*Purchase Order Master Query In Oracle Apps R12*/

SELECT xx.org_id,
       (SELECT organization_name
        FROM   apps.org_organization_definitions
        WHERE  organization_id = xx.org_id)    ou_name,
       xx.po_type,
       xx.po_num,
       Trunc (xx.po_date)                      po_date,
       (SELECT Listagg(release_num, ', ')
                 within GROUP (ORDER BY release_num)
        FROM   po_releases_all
        WHERE  po_release_id = xx.released_id) release_num,
       (SELECT Listagg(To_char (release_date), ', ')
                 within GROUP (ORDER BY To_char (release_date))
        FROM   po_releases_all
        WHERE  po_release_id = xx.released_id) release_date,
       xx.shipment_num,
       xx.line_num,
       xx.item,
       ( CASE
           WHEN xx.line_type_id = 3
                AND xx.osp_description IS NOT NULL THEN xx.item_description
                                                        || ' FOR-> '
                                                        || xx.osp_description
           ELSE xx.item_description
         END )                                 item_description,
       xx.need_by,
       xx.unit_price,
       xx.pending,
       xx.pend_qty_val,
       Nvl (xx.quantity, 0)                    item_qty,
       Nvl (xx.quantity, 0) * xx.unit_price    VALUE,
       xx.currency_code,
       xx.rate,
       xx.vendor_code,
       name,
       xx.wip_entity_name,
       xx.status_code
FROM   (SELECT abc.org_id,
               abc.po_type,
               abc.currency_code,
               abc.po_release_id                  released_id,
               abc.po_line_id,
               CASE
                 WHEN abc.wip_entity_id IS NULL THEN
                 Item_code (abc.inventory_item_id)
                 WHEN abc.wip_entity_id IS NOT NULL THEN abc.segment1
               END                                item,
               CASE
                 WHEN abc.wip_entity_id IS NULL THEN abc.inventory_item_id
                 WHEN abc.wip_entity_id IS NOT NULL THEN abc.inventory_item_id
               END                                item_id,
               CASE
                 WHEN abc.wip_entity_id IS NULL THEN
                 Xxfilix_item_desc_po(abc.po_line_id)
                 WHEN abc.wip_entity_id IS NOT NULL THEN abc.des
               END                                item_description,
               (SELECT description
                FROM   mtl_system_items_b
                WHERE  inventory_item_id = abc.primary_item_id
                       AND organization_id = 112) osp_description,
               abc.remark,
               abc.po_num,
               abc.po_date,
               abc.quantity,
               abc.quantity_received,
               abc.quantity_cancelled,
               abc.pending,
               abc.pend_qty_val,
               abc.unit_price,
               abc.vendor_code,
               abc.name,
               abc.need_by,
               abc.source,
               abc.wip_entity_name,
               abc.status_code,
               abc.primary_item_id,
               abc.shipment_num,
               abc.line_num,
               abc.rate,
               abc.line_type_id
        FROM   (SELECT
xyz.po_type,
        xyz.currency_code,
Decode(XYZ.destination_context, 'SHOP FLOOR', xyz.primary_item_id)primary_item_id,
xyz.wip_entity_id,
xyz.segment1,
xyz.org_id,
xyz.inventory_item_id,
xyz.item_revision,
xyz.vendor_id,
xyz.des,
xyz.remark,
xyz.po_num,
xyz.po_date,
xyz.quantity,
xyz.quantity_received,
xyz.quantity_cancelled,
xyz.pending,
xyz.pend_qty_val,
xyz.unit_price,
xyz.vendor_code,
xyz.name,
xyz.need_by,
xyz.po_release_id,
xyz.po_line_id,
xyz.po_header_id,
xyz.source,
xyz.wip_entity_name,
xyz.status_code,
xyz.shipment_num,
xyz.line_num,
xyz.rate,
xyz.line_type_id
 FROM   (SELECT DISTINCT abc1.po_type,
                         abc1.currency_code,
                         abc1.po_release_id,
                         abc1.po_line_id,
                         abc1.po_header_id,
                         abc1.line_location_id,
                         abc1.vendor_id,
                         abc1.segment1,
                         abc1.org_id,
                         abc1.inventory_item_id,
                         abc1.item_revision,
                         abc1.des,
                         abc1.remark,
                         abc1.po_num,
                         abc1.po_date,
                         abc1.quantity,
                         abc1.quantity_received,
                         abc1.quantity_cancelled,
                         abc1.pending,
                         abc1.pend_qty_val,
                         abc1.unit_price,
                         abc1.vendor_code,
                         abc1.name,
                         abc1.need_by,
                         jih.source,
                         pda.wip_entity_id,
                         we.wip_entity_name,
                         abc1.closed_code status_code,
                         abc1.shipment_num,
                         abc1.line_num,
                         abc1.rate,
                         abc1.line_type_id,
                         pda.destination_context,
                         we.primary_item_id
         FROM   (SELECT pha.type_lookup_code
                                po_type,
                        pha.currency_code,
                        pllv.po_release_id,
                        pllv.po_line_id,
                        pllv.po_header_id,
                        pllv.line_location_id,
                        pv.vendor_id,
                        msi.segment1,
                        msi.organization_id
                                 org_id,
                        msi.inventory_item_id,
                        pla.item_revision,
                        pla.item_description
                        des
                                ,
                        pllv.shipment_num,
                        pla.line_num,
                        pla.line_type_id,
                        Decode(pla.attribute1, '0', NULL,
                                               pla.attribute1)
                                 remark,
                        pha.segment1
                                 po_num,
                        pha.creation_date
                                 po_date,
                        pllv.quantity,
                        pllv.quantity_received,
                        pllv.quantity_cancelled,
                        ( pllv.quantity - pllv.quantity_received -
                          pllv.quantity_cancelled )
                                 pending,
                        Round(( ( pllv.quantity - pllv.quantity_received -
                                      pllv.quantity_cancelled ) *
                                ( pllv.price_override ) ), 2)
                                 pend_qty_val,
                        pllv.price_override
                                 unit_price,
                        pv.segment1
                                 vendor_code,
                        pv.vendor_name
                                NAME
                                 ,
                        Nvl(pllv.promised_date, pllv.need_by_date)
                                 need_by,
                        pha.rate,
                        pllv.closed_code
                 FROM   mtl_system_items msi,
                        po_lines_all pla,
                        po_line_locations_all pllv,
                        po_headers_all pha,
                        po_vendors pv
                 WHERE  msi.inventory_item_id = pla.item_id
                        AND pla.po_line_id = pllv.po_line_id
                        AND pllv.po_header_id = pha.po_header_id
                        AND pha.vendor_id = pv.vendor_id
                        AND pllv.po_release_id IS NOT NULL
                        --------------AND (pllv.quantity- pllv.quantity_received) > 0
                        AND pllv.ship_to_organization_id = msi.organization_id
                        AND ( pllv.cancel_flag <> 'Y'
                               OR pllv.cancel_flag IS NULL )
                 --AND NVL (pllv.closed_code, 'X') NOT IN('CLOSED','FINALLY CLOSED')
                 --AND NVL (pha.closed_code, 'X') NOT IN('CLOSED','FINALLY CLOSED')
                 --AND NVL (pllv.closed_code, 'X') NOT IN('CLOSED','FINALLY CLOSED')
                 --AND NVL (pla.closed_code, 'X') NOT IN('CLOSED','FINALLY CLOSED')
                 UNION ALL
                 SELECT pha.type_lookup_code
                        po_type,
                        pha.currency_code,
                        pllv.po_release_id,
                        pllv.po_line_id,
                        pllv.po_header_id,
                        pllv.line_location_id,
                        pv.vendor_id,
                        msi.segment1,
                        msi.organization_id
                        org_id,
                        msi.inventory_item_id,
                        pla.item_revision,
                        pla.item_description
                        des
                        ,
                        pllv.shipment_num,
                        pla.line_num,
                        pla.line_type_id,
                        Decode(pla.attribute1, '0', NULL,
                                               pla.attribute1)
                        remark,
                        pha.segment1
                        po_num,
                        pha.creation_date
                        po_date,
                        pllv.quantity,
                        pllv.quantity_received,
                        pllv.quantity_cancelled,
                        ( pllv.quantity - pllv.quantity_received -
                          pllv.quantity_cancelled )
                        pending,
                        Round(( ( pllv.quantity - pllv.quantity_received -
                                  pllv.quantity_cancelled ) *
                                      ( pllv.price_override ) ), 2)
                        pend_qty_val,
                        pllv.price_override
                        unit_price,
                        pv.segment1
                        vendor_code,
                        pv.vendor_name
                        NAME
                        ,
                        Nvl(pllv.promised_date, pllv.need_by_date)
                        need_by,
                        pha.rate,
                        pllv.closed_code
                 FROM   mtl_system_items msi,
                        po_lines_all pla,
                        po_line_locations_all pllv,
                        po_headers_all pha,
                        po_vendors pv
                 WHERE  msi.inventory_item_id = pla.item_id
                        AND pla.po_line_id = pllv.po_line_id
                        AND pllv.po_header_id = pha.po_header_id
                        AND pha.vendor_id = pv.vendor_id
                        AND pllv.po_release_id IS NULL
                        -------------AND (pllv.quantity- pllv.quantity_received) > 0
                        AND pllv.ship_to_organization_id = msi.organization_id
                        AND ( pllv.cancel_flag <> 'Y'
                               OR pllv.cancel_flag IS NULL )
                        --AND NVL (pllv.closed_code, 'X') NOT IN('CLOSED','FINALLY CLOSED')
                        --AND NVL (pha.closed_code, 'X') NOT IN('CLOSED','FINALLY CLOSED')
                        --AND NVL (pllv.closed_code, 'X') NOT IN('CLOSED','FINALLY CLOSED')
                        --AND NVL (pla.closed_code, 'X') NOT IN('CLOSED','FINALLY CLOSED')
                        AND shipment_type NOT IN( 'PLANNED', 'BLANKET' )) abc1,
                po_distributions_all pda,
                jai_po_osp_lines jil,
                jai_po_osp_hdrs jih,
                wip_entities we
         WHERE  1 = 1
                AND pda.po_header_id = abc1.po_header_id
                AND pda.po_line_id = abc1.po_line_id
                AND pda.line_location_id = abc1.line_location_id
                AND jil.po_distribution_id(+) = pda.po_distribution_id
                AND jih.po_header_id(+) = abc1.po_header_id
                AND we.wip_entity_id(+) = pda.wip_entity_id
                AND jih.oth_doc_id(+) = abc1.po_release_id
                AND Nvl (jih.cancel_flag, 0) <> 'Y') xyz) abc) xx
WHERE  1 = 1
       AND Nvl (xx.source, 'XXXX') NOT IN ( 'RETURN TO VENDOR' )
       AND XX.po_num = '23677501237'
       AND xx.released_id = (SELECT pra.po_release_id
                             FROM   po_releases_all pra
                             WHERE  pra.po_release_id = xx.released_id
                                    AND pra.release_num = 5)
ORDER  BY xx.name;