Oracle Advanced Pricing Modifier Qualifiers Setup extract script

--OM Advanced Pricing Modifier Qualifiers Setup

SELECT qsh.name,

       qsh.description,

       qsh.comments,

       qsh.active_flag,

       qsh.automatic_flag,

       qsh.start_date_active,

       qsh.end_date_active,

       qms.operand,

       qms.arithmetic_operator,

       qms.start_date_active,

       qms.modifier_level,

       qms.list_line_type,

       qms.product_attr_value item_code

  FROM qp_secu_list_headers_vl qsh,

       qp_qualifiers_v qqv,

       ar_customers arc,

       qp_modifier_summary_v qms

 WHERE     qqv.list_header_id = qsh.list_header_id

       AND qualifier_attr_value = TO_CHAR (arc.customer_id)

       AND arc.customer_name = NVL (:p_customer, arc.customer_name)

       AND qms.product_attr_value = NVL (:p_item_code, qms.product_attr_value)

       AND qqv.list_line_id = qms.list_line_id

       AND qqv.list_header_id = qms.list_header_id

       --AND qsh.end_date_active is NULL

       AND qsh.active_flag = 'Y';

OM Item Price list find Query

SELECT qlh.name, qlh.description, qlh.version_no,qll.product_id, product_attr_val_disp,operand, list_line_no

,product_uom_code, start_date_active

  FROM qp_list_lines_v qll

      ,qp_secu_list_headers_v qlh

 WHERE qll.list_header_id   = qlh.list_header_id  

   AND qll.product_attribute_context = 'ITEM'

   AND EXISTS

              (SELECT '1'

                 FROM MTL_SYSTEM_ITEMS mtl

                WHERE     product_attr_value = mtl.inventory_item_id

                      AND mtl.organization_id =

                             (SELECT qp_util.get_item_validation_org

                                FROM DUAL)

                      AND product_attribute = 'PRICING_ATTRIBUTE1'

               UNION

               SELECT '1'

                 FROM DUAL

                WHERE product_attribute != 'PRICING_ATTRIBUTE1')

       AND qll.product_attr_val_disp = NVL(:P_ITEM_CODE, qll.product_attr_val_disp) 

---------------Item Code and Description wise

SELECT qsh.name Price_list,

       PRODUCT_ID,

       PRODUCT_ATTR_VAL_DISP ITEM_CODE,REPLACE(TRIM(msi.DESCRIPTION),Chr(9),'') ITEM_DESCRIPTION, 

       qsl.PRODUCT_UOM_CODE UOM,

       qsl.operand Price,

       qsl.start_date_active

  FROM qp_secu_list_headers_v qsh, qp_list_lines_v qsl

     ,mtl_system_items_b  msi

 WHERE     qsh.list_header_id = qsl.list_header_id

       AND msi.INVENTORY_ITEM_ID = qsl.PRODUCT_ID

       AND qsh.END_DATE_ACTIVE IS NULL

       AND qsl.END_DATE_ACTIVE IS NULL

       AND msi.organization_id= 122 ---Master Org

--       AND PRODUCT_ATTR_VAL_DISP= 'FGBK0053'

       AND INVENTORY_ITEM_STATUS_CODE ='Active'

       AND qsh.name <> 'Default price list'

       AND qsl.start_date_active > '01-MAY-1951'

 Order by 1

Oracle Sales Order not going Material Transactions Script

--OM: Sales Order not going Material Transactions

SELECT hdr.order_number,line.line_id, line.org_id, line.shippable_flag,hdr.order_type_id

      ,hdr.ordered_date,hdr.booked_date,    hdr.cancelled_flag,hdr.booked_flag,hdr.open_flag,hdr.flow_status_code

        FROM oe_workflow_assignments wf_assign,

             oe_order_lines_all line,

             oe_order_headers_all hdr

       WHERE process_name = 'R_BILL_ONLY_INV_INTERFACE'

         AND line.line_type_id = wf_assign.line_type_id

         AND line.header_id = hdr.header_id

         AND hdr.order_type_id = wf_assign.order_type_id

         AND line.open_flag = 'N'

         AND line.flow_status_code <> 'CANCELLED'

         AND NVL (line.cancelled_flag, 'N') <> 'Y'

         AND NOT EXISTS (SELECT 1

                           FROM mtl_material_transactions

                          WHERE trx_source_line_id = line.line_id AND transaction_source_type_id = 2)

         AND NOT EXISTS (SELECT 1

                           FROM mtl_transactions_interface

                          WHERE trx_source_line_id = line.line_id AND transaction_source_type_id = 2)

Order by ORDERED_DATE                           

OM: sales order against lot number Oracle R12

---sales order against lot number 

SELECT oola.ordered_quantity,

       oola.order_quantity_uom,

       oola.ordered_item,

       ooha.order_number,

       MTLN.LOT_NUMBER

  FROM apps.mtl_material_transactions mmt

       ,apps.oe_order_lines_all oola

       ,apps.oe_order_headers_all ooha

       ,apps.mtl_transaction_lot_numbers mtln

 WHERE   mmt.trx_source_line_id = oola.line_id

 AND     mmt.organization_id= oola.ship_from_org_id

 AND     oola.header_id = ooha.header_id

 AND     mmt.transaction_id = mtln.transaction_id

 -- and mmt.organization_id=3054

 AND    ooha.order_number = NVL(P_ORDER_NUMBER,ooha.order_number)

 Order by ooha.order_number;

OM: Order Header and Order Line base Advance Query

BEGIN

   mo_global.init('ONT');

END;

BEGIN

   mo_global.init('ONT');

END;

SELECT   ac.customer_name, ac.customer_number, ooh.order_number,

         ooh.cust_po_number, ooh.ordered_date,

         ooh.flow_status_code status_code, ooh.order_firmed_date,

         ood.organization_code warehouse, ooh.fob_point_code fob,

         ooh.freight_terms_code, ooh.shipping_method_code,

         ooh.shipment_priority_code, ooh.transactional_curr_code curr_code,

         ot.NAME order_type,

         (SELECT s.NAME

            FROM jtf.jtf_rs_salesreps s

           WHERE s.org_id = ood.operating_unit

             AND s.salesrep_id = ooh.salesrep_id) sales_parson,

         (SELECT qpl.NAME

            FROM qp_list_headers_vl qpl

           WHERE list_header_id = ooh.price_list_id) price_list,

         (SELECT rat.NAME

            FROM ra_terms rat

           WHERE rat.term_id = ooh.payment_term_id) payment_term_name, 

         ooh.orig_sys_document_ref,

         ooh.attribute1 header_ddf1, ooh.attribute2 header_ddf2,

         ooh.attribute3 header_ddf3, ooh.attribute4 header_ddf4,

         ooh.attribute5 header_ddf5, ooh.attribute6 header_ddf6,

----------------Line Info below-----------

         ool.line_number, ool.ordered_item, ool.ordered_quantity,

         ool.shipped_quantity, ool.schedule_ship_date,

         ool.order_quantity_uom uom, ool.request_date,

         ool.flow_status_code line_status_code,

         ool.tax_value,

        (SELECT transaction_type_code FROM ont.oe_transaction_types_all where transaction_type_id =ool.LINE_TYPE_ID)LINE_TYPE,

        (SELECT process_name FROM oe_workflow_assignments WHERE order_type_id = ooh.order_type_id and line_type_id= ool.line_type_id) WF_PROCESS_NAME,

         (SELECT NAME

            FROM pa_projects_all

           WHERE project_id = ool.project_id) project_name,

         (SELECT task_name

            FROM pa_tasks

           WHERE task_id = ool.task_id) task_name, ool.return_reason_code,

         ool.reference_type, ool.unit_selling_price unit_price, ool.tax_code,

         ool.tax_rate, ool.tax_value, ship_to.address1 ship_to_address1,

         ship_to.address2 ship_to_address2, ship_to.address3 ship_to_address3,

         ship_to.city ship_to_city, ship_to.state ship_to_state,

         ship_to.nls_territory bill_to_territory,

         ship_to.postal_code ship_to_postal_code,

         ship_to.province ship_to_pin, bill_to.address1 bill_to_address1,

         bill_to.address2 bill_to_address2, bill_to.address3 bill_to_address3,

         bill_to.city bill_to_city, bill_to.state bill_to_state,

         bill_to.nls_territory bill_to_territory,

         bill_to.postal_code bill_to_postal_code,

         bill_to.province bill_to_pin, ool.attribute1 line_ddf1,

         ool.attribute2 line_ddf2, ool.attribute3 line_ddf3,

         ool.attribute4 line_ddf4,ool.attribute5 line_ddf5,

         ool.attribute6 line_ddf6,

(select lookup_code from apps.oe_lookups where upper (lookup_type) = 'FOB' and enabled_flag = 'Y' and upper (lookup_code) = upper (ooh.fob_point_code) )FOB,

(select lookup_code from apps.oe_lookups where upper (lookup_type) = 'FREIGHT_TERMS' and enabled_flag = 'Y' and upper (lookup_code) = upper (ooh.freight_terms_code) )Freight_Terms,

(Select CHARGE_AMOUNT from OE_CHARGE_LINES_V  ocl where ocl.HEADER_ID=ool.HEADER_ID and ocl.LINE_ID=ool.LINE_ID) FREIGHT_CHARGE,

    ool.line_category_code,

    (SELECT order_number FROM oe_order_headers_all WHERE header_id = ool.return_attribute1) RMA_SALES_ORDER,

    (SELECT line_number  FROM oe_order_lines_all WHERE header_id = ool.return_attribute1 AND line_id = ool.return_attribute2) RMA_SO_Line_No

    FROM oe_order_headers_all ooh,

         oe_order_lines_all ool,

         oe_transaction_types_tl ot,

         ar_customers ac

       , org_organization_definitions ood

----------------------Bill To Customer-------------------------------

         ,(SELECT hp.party_name, hp.party_number, hca.account_number,

                 hca.cust_account_id, hp.party_id, hps.party_site_id,

                 hcsu.cust_acct_site_id, hps.location_id, hl.address1,

                 hl.address2, hl.address3, hl.city, hl.state,

                 ter.nls_territory, hl.postal_code, hl.province,

                 hcsu.site_use_code, hcsu.site_use_id, hcsa.bill_to_flag

            FROM hz_parties hp,

                 hz_party_sites hps,

                 hz_locations hl,

                 hz_cust_accounts_all hca,

                 hz_cust_acct_sites_all hcsa,

                 hz_cust_site_uses_all hcsu,

                 fnd_territories ter

           WHERE hp.party_id = hps.party_id

             AND hps.location_id = hl.location_id

             AND hp.party_id = hca.party_id

             AND hcsa.party_site_id = hps.party_site_id

             AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id

             AND hca.cust_account_id = hcsa.cust_account_id

             AND hl.country = ter.territory_code

             AND hcsu.site_use_code = 'BILL_TO') bill_to

----------------------Ship To Customer-------------------------------

         ,(SELECT hp.party_name, hp.party_number, hca.account_number,

                 hca.cust_account_id, hp.party_id, hps.party_site_id,

                 hcsu.cust_acct_site_id, hps.location_id, hl.address1,

                 hl.address2, hl.address3, hl.city, hl.state,

                 ter.nls_territory, hl.postal_code, hl.province,

                 hcsu.site_use_code, hcsu.site_use_id, hcsa.bill_to_flag

            FROM hz_parties hp,

                 hz_party_sites hps,

                 hz_locations hl,

                 hz_cust_accounts_all hca,

                 hz_cust_acct_sites_all hcsa,

                 hz_cust_site_uses_all hcsu,

                 fnd_territories ter

           WHERE hp.party_id = hps.party_id

             AND hps.location_id = hl.location_id

             AND hp.party_id = hca.party_id

             AND hcsa.party_site_id = hps.party_site_id

             AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id

             AND hca.cust_account_id = hcsa.cust_account_id

             AND hl.country = ter.territory_code

             AND hcsu.site_use_code = 'SHIP_TO') ship_to

   WHERE ooh.header_id = ool.header_id

     AND ooh.order_type_id = ot.transaction_type_id

     AND ac.customer_id = ooh.sold_to_org_id

--     AND ooh.flow_status_code = 'BOOKED'

     AND ood.organization_id = ooh.ship_from_org_id

     AND ood.organization_code = NVL (:p_inv_org_code, ood.organization_code)

     AND ooh.order_number = NVL (:p_sales_order_no, ooh.order_number)

     AND ship_to.cust_account_id = ooh.sold_to_org_id

     AND ship_to.site_use_id = ooh.ship_to_org_id

     AND bill_to.cust_account_id = ooh.sold_to_org_id

     AND bill_to.site_use_id = ooh.invoice_to_org_id

ORDER BY order_number

/

OM: Order header and line base query

BEGIN

mo_global.init('ONT');

END;

SELECT

     AC.CUSTOMER_NAME, AC.CUSTOMER_NUMBER

    ,OOH.ORDER_NUMBER,OOH.CUST_PO_NUMBER

    ,OOH.ORDERED_DATE, OOH.FLOW_STATUS_CODE STATUS_CODE

    ,OOH.ORDER_FIRMED_DATE

    ,OOD.ORGANIZATION_CODE WAREHOUSE

    ,OOH.FOB_POINT_CODE FOB

    ,OOH.FREIGHT_TERMS_CODE

    ,OOH.SHIPPING_METHOD_CODE

    ,OOH.SHIPMENT_PRIORITY_CODE

    ,OOH.TRANSACTIONAL_CURR_CODE CURR_CODE

    ,OOH.ATTRIBUTE1 HEADER_DDF1,OOH.ATTRIBUTE2 HEADER_DDF2,OOH.ATTRIBUTE3 HEADER_DDF3,OOH.ATTRIBUTE4 HEADER_DDF4

    ,OT.NAME ORDER_TYPE

    ,( select s.name  from JTF.jtf_rs_salesreps s where S.ORG_ID=OOD.OPERATING_UNIT AND s.salesrep_id = ooh.salesrep_id) SALES_PARSON

    ,(SELECT NAME FROM qp_list_HEADERS_VL qpl WHERE LIST_HEADER_ID = OOH.PRICE_LIST_ID) PRICE_LIST

    ,(SELECT RAT.NAME FROM RA_TERMS RAT WHERE RAT.TERM_ID=OOH.PAYMENT_TERM_ID) PAYMENT_TERM_NAME

    ,OOL.LINE_NUMBER

    ,OOL.ORDERED_ITEM

    ,OOL.ORDERED_QUANTITY

    ,OOL.SHIPPED_QUANTITY

    ,OOL.SCHEDULE_SHIP_DATE

    ,OOL.ORDER_QUANTITY_UOM UOM

    ,OOL.REQUEST_DATE

    ,OOl.FLOW_STATUS_CODE LINE_STATUS_CODE

    ,(SELECT NAME  FROM pa_projects_all WHERE project_id = OOL.PROJECT_ID) PROJECT_NAME

    ,(SELECT TASK_NAME FROM pa_tasks WHERE task_id = OOL.TASK_ID)TASK_NAME

    ,OOL.RETURN_REASON_CODE

    ,OOL.REFERENCE_TYPE

    ,OOL.UNIT_SELLING_PRICE UNIT_PRICE

    ,OOL.TAX_CODE,OOL.TAX_RATE,OOL.TAX_VALUE

    ,SHIP_TO.address1 SHIP_TO_ADDRESS1,SHIP_TO.address2 SHIP_TO_ADDRESS2,SHIP_TO.address3 SHIP_TO_ADDRESS3,SHIP_TO.city SHIP_TO_CITY

    ,SHIP_TO.state SHIP_TO_STATE,SHIP_TO.nls_territory BILL_TO_TERRITORY,SHIP_TO.postal_code SHIP_TO_POSTAL_CODE,SHIP_TO.province SHIP_TO_PIN

    ,BILL_TO.address1 BILL_TO_ADDRESS1,BILL_TO.address2 BILL_TO_ADDRESS2,BILL_TO.address3 BILL_TO_ADDRESS3,BILL_TO.city BILL_TO_CITY

    ,BILL_TO.state BILL_TO_STATE,BILL_TO.nls_territory BILL_TO_TERRITORY,BILL_TO.postal_code BILL_TO_POSTAL_CODE,BILL_TO.province BILL_TO_PIN

    ,OOL.ATTRIBUTE1 LINE_DDF1,OOH.ATTRIBUTE2 LINE_DDF2,OOH.ATTRIBUTE3 LINE_DDF3,OOH.ATTRIBUTE4 LINE_DDF4

FROM

     OE_ORDER_HEADERS_ALL OOH

    ,OE_ORDER_LINES_ALL OOL

    ,OE_TRANSACTION_TYPES_TL OT

    ,AR_CUSTOMERS AC

--    ,MTL_PARAMETERS MP

   ,ORG_ORGANIZATION_DEFINITIONS OOD

        ----------------------Bill To Customer-------------------------------

,(SELECT hp.party_name,

       hp.party_number,

       hca.account_number,

       hca.cust_account_id,

       hp.party_id,

       hps.party_site_id,

       hcsu.cust_acct_site_id,

       hps.location_id,

       hl.address1,

       hl.address2,

       hl.address3,

       hl.city,

       hl.state,

       ter.nls_territory,

       hl.postal_code,

       hl.province,

       hcsu.site_use_code,

       hcsu.site_use_id,

       hcsa.bill_to_flag

FROM hz_parties hp,

     hz_party_sites hps,

     hz_locations hl,

     hz_cust_accounts_all hca,

     hz_cust_acct_sites_all hcsa,

     hz_cust_site_uses_all hcsu,

     fnd_territories ter

WHERE     hp.party_id = hps.party_id

      AND hps.location_id = hl.location_id

      AND hp.party_id = hca.party_id

      AND hcsa.party_site_id = hps.party_site_id

      AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id

      AND hca.cust_account_id = hcsa.cust_account_id

      AND hl.country = ter.territory_code

      AND hcsu.site_use_code = 'BILL_TO')BILL_TO

----------------------Ship To Customer-------------------------------

,(SELECT hp.party_name,

       hp.party_number,

       hca.account_number,

       hca.cust_account_id,

       hp.party_id,

       hps.party_site_id,

       hcsu.cust_acct_site_id,

       hps.location_id,

       hl.address1,

       hl.address2,

       hl.address3,

       hl.city,

       hl.state,

       ter.nls_territory,

       hl.postal_code,

       hl.province,

       hcsu.site_use_code,

       hcsu.site_use_id,

       hcsa.bill_to_flag

FROM hz_parties hp,

     hz_party_sites hps,

     hz_locations hl,

     hz_cust_accounts_all hca,

     hz_cust_acct_sites_all hcsa,

     hz_cust_site_uses_all hcsu,

     fnd_territories ter

WHERE     hp.party_id = hps.party_id

      AND hps.location_id = hl.location_id

      AND hp.party_id = hca.party_id

      AND hcsa.party_site_id = hps.party_site_id

      AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id

      AND hca.cust_account_id = hcsa.cust_account_id

      AND hl.country = ter.territory_code

      AND hcsu.site_use_code = 'SHIP_TO')SHIP_TO

WHERE

     OOH.HEADER_ID=OOL.HEADER_ID

AND  OOH.ORDER_TYPE_ID=OT.TRANSACTION_TYPE_ID

AND  AC.CUSTOMER_ID=OOH.SOLD_TO_ORG_ID

AND OOH.FLOW_STATUS_CODE ='BOOKED'

AND OOD.ORGANIZATION_ID = OOH.SHIP_FROM_ORG_ID

AND OOD.ORGANIZATION_CODE = NVL(:P_INV_ORG_CODE,OOD.ORGANIZATION_CODE) 

AND OOH.ORDER_NUMBER = NVL(:P_SALES_ORDER_NO, OOH.ORDER_NUMBER)

AND SHIP_TO.cust_account_id = OOH.SOLD_TO_ORG_ID 

AND SHIP_TO.site_use_id = OOH.SHIP_TO_ORG_ID  

AND BILL_TO.cust_account_id = OOH.SOLD_TO_ORG_ID 

AND BILL_TO.site_use_id = OOH.INVOICE_TO_ORG_ID 

Order By ORDER_NUMBER

PO and OM Joining

Select * 
FROM rcv_transactions_interface rti,
          rcv_shipment_lines rsl,
          rcv_shipment_headers rsh,
          po_requisition_lines prl,
          mtl_system_items msi,
          mtl_units_of_measure mum1,
          mtl_units_of_measure mum2,
          mtl_units_of_measure mum3,
          mtl_units_of_measure mum4,
          mtl_units_of_measure mum5,
          po_lookup_codes plc1,
          po_lookup_codes plc2,
          po_lookup_codes plc3,
          po_lookup_codes plc4,
          po_lookup_codes plc5,
          po_lookup_codes plc6,
          oe_sold_to_orgs_v oec,
          oe_order_lines_all oel,
          oe_order_headers_all oeh,
          oe_transaction_types_tl oetl,
          oe_transaction_types_all oet,
          po_headers_trx_v ph,
          po_lines_trx_v pl,
          po_line_locations_trx_v pll,
          po_releases_all pr,
          po_vendors pov,
          hr_locations_all_tl hrl,
          mtl_transaction_reasons mtr,
          rcv_routing_headers rrh,
          per_all_people_f hre,
          org_organization_definitions ood,
          fnd_currencies_vl fcv,
          per_jobs pj,
          po_job_associations pja
    WHERE (    rti.transaction_type = plc1.lookup_code
           AND plc1.lookup_type = 'RCV TRANSACTION TYPE'
          )
      AND (rti.shipment_line_id = rsl.shipment_line_id(+))
      AND (rti.requisition_line_id = prl.requisition_line_id(+))
      AND (rti.shipment_header_id = rsh.shipment_header_id(+))
      AND (    msi.inventory_item_id(+) = rti.item_id
           AND NVL (msi.organization_id, rti.to_organization_id) =
                                                        rti.to_organization_id
           AND msi.primary_unit_of_measure = mum1.unit_of_measure(+)
          )
      AND (rti.unit_of_measure = mum2.unit_of_measure(+))
      AND (plc2.lookup_type(+) = 'INSPECTION STATUS'
           AND plc2.lookup_code(+) = rti.inspection_status_code)
      AND oel.line_id(+) = rti.oe_order_line_id
      AND oeh.header_id(+) = rti.oe_order_header_id
      AND oec.customer_id(+) = rsh.customer_id
      AND oet.transaction_type_id(+) = oeh.order_type_id
      AND oet.transaction_type_id = oetl.transaction_type_id(+)
      AND oetl.LANGUAGE(+) = USERENV ('LANG')
      AND oet.transaction_type_code(+) = 'ORDER'
      AND ph.po_header_id(+) = rti.po_header_id
      AND (pl.po_line_id(+) = rti.po_line_id)
      AND (pll.line_location_id(+) = rti.po_line_location_id)
      AND (pr.po_release_id(+) = rti.po_release_id)
      AND pov.vendor_id(+) = rsh.vendor_id
      AND plc3.lookup_type(+) = 'RCV DESTINATION TYPE'
      AND plc3.lookup_code(+) = rti.destination_type_code
      AND hrl.location_id(+) =
                             NVL (rti.deliver_to_location_id, rti.location_id)
      AND hrl.LANGUAGE(+) = USERENV ('LANG')
      AND rrh.routing_header_id(+) = rti.routing_header_id
      AND mum3.unit_of_measure(+) = pl.unit_meas_lookup_code
      AND mum4.unit_of_measure(+) = pll.secondary_unit_of_measure
      AND mum5.uom_code(+) = oel.ordered_quantity_uom2
      AND mtr.reason_id(+) = rti.reason_id
      AND hre.person_id(+) = rti.deliver_to_person_id
      AND TRUNC (SYSDATE) BETWEEN hre.effective_start_date(+) AND hre.effective_end_date(+)
      AND DECODE (hr_security.view_all,
                  'Y', 'TRUE',
                  hr_security.show_person (hre.person_id(+), hre.current_applicant_flag(+),
                                           hre.current_employee_flag(+), hre.current_npw_flag(+),
                                           hre.employee_number(+), hre.applicant_number(+),
                                           hre.npw_number(+))
                 ) = 'TRUE'
      AND DECODE (hr_general.get_xbg_profile,
                  'Y', hre.business_group_id(+),
                  hr_general.get_business_group_id
                 ) = hre.business_group_id(+)
      AND ood.organization_id(+) = rti.from_organization_id
      AND DECODE (rti.source_document_code,
                  'PO', 'PO TYPE',
                  'SHIPMENT SOURCE TYPE'
                 ) = plc4.lookup_type
      AND DECODE (rti.transaction_type,
                  'UNORDERED', 'STANDARD',
                  DECODE (rti.source_document_code,
                          'PO', ph.type_lookup_code,
                          rsh.receipt_source_code
                         )
                 ) = plc4.lookup_code
      AND plc5.lookup_type = 'TRANSACTION STATUS'
      AND plc5.lookup_code = rti.transaction_status_code
      AND plc6.lookup_type = 'RCV PROCESSING MODE'
      AND plc6.lookup_code = rti.processing_mode_code
      AND fcv.currency_code(+) = rti.currency_code
      AND pj.job_id(+) = rti.job_id
      AND pja.job_id(+) = rti.job_id
      AND NVL (ph.org_id, -99) = NVL (pll.org_id, -99);

APP-ONT-251584 retroactive billing is not enabled in Order Management

Order Management(OM) Common Issue:

Set the OM Parameters. Navigate to the OM System Parameters values window. Order Management > Setup > System Parameters > Values.

Order To Bill_to and Ship_to Customer Address

----------------------Bill To Customer-------------------------------
SELECT hp.party_name,
       hp.party_number,
       hca.account_number,
       hca.cust_account_id,
       hp.party_id,
       hps.party_site_id,
       hcsu.cust_acct_site_id,
       hps.location_id,
       hl.address1,
       hl.address2,
       hl.address3,
       hl.city,
       hl.state,
       ter.nls_territory,
       hl.postal_code,
       hl.province,
       hcsu.site_use_code,
       hcsu.site_use_id,
       hcsa.bill_to_flag
FROM hz_parties hp,
     hz_party_sites hps,
     hz_locations hl,
     hz_cust_accounts_all hca,
     hz_cust_acct_sites_all hcsa,
     hz_cust_site_uses_all hcsu,
     fnd_territories ter
WHERE     hp.party_id = hps.party_id
      AND hps.location_id = hl.location_id
      AND hp.party_id = hca.party_id
      AND hcsa.party_site_id = hps.party_site_id
      AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
      AND hca.cust_account_id = hcsa.cust_account_id
      AND hl.country = ter.territory_code
      AND hcsu.site_use_code = 'BILL_TO'
      AND hca.cust_account_id=:SOLD_TO_ORG_ID --Select SOLD_TO_ORG_ID  From oe_order_headers_all
      and hcsu.site_use_id=:INVOICE_TO_ORG_ID --225009  Select INVOICE_TO_ORG_ID From oe_order_headers_all

----------------------Ship To Customer-------------------------------
/* Formatted on 7/17/2017 3:05:46 PM (QP5 v5.115.810.9015) */
SELECT hp.party_name,
       hp.party_number,
       hca.account_number,
       hca.cust_account_id,
       hp.party_id,
       hps.party_site_id,
       hcsu.cust_acct_site_id,
       hps.location_id,
       hl.address1,
       hl.address2,
       hl.address3,
       hl.city,
       hl.state,
       ter.nls_territory,
       hl.postal_code,
       hl.province,
       hcsu.site_use_code,
       hcsu.site_use_id,
       hcsa.bill_to_flag
FROM hz_parties hp,
     hz_party_sites hps,
     hz_locations hl,
     hz_cust_accounts_all hca,
     hz_cust_acct_sites_all hcsa,
     hz_cust_site_uses_all hcsu,
     fnd_territories ter
WHERE     hp.party_id = hps.party_id
      AND hps.location_id = hl.location_id
      AND hp.party_id = hca.party_id
      AND hcsa.party_site_id = hps.party_site_id
      AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
      AND hca.cust_account_id = hcsa.cust_account_id
      AND hl.country = ter.territory_code
      AND hcsu.site_use_code = 'SHIP_TO'
      AND hca.cust_account_id=:SOLD_TO_ORG_ID --Select SOLD_TO_ORG_ID From oe_order_headers_all
      and hcsu.site_use_id=:SHIP_TO_ORG_ID --Select SHIP_TO_ORG_ID  From oe_order_headers_all 

--- Upto INR 40000 off on Desktop CPUs & All in One Computers; No Cost EMI available

Order to Cash cycle (O2C) Query

SELECT
     AC.CUSTOMER_NAME
    ,OOH.ORDER_NUMBER
    ,OOH.ORDERED_DATE
    ,(Select  ORGANIZATION_CODE From mtl_parameters where ORGANIZATION_ID=OOH.SHIP_FROM_ORG_ID) ORG_CODE
    ,OT.NAME ORDER_TYPE
    ,OOL.ORDERED_ITEM
    ,OOL.ORDERED_QUANTITY
    ,OOL.SHIPPED_QUANTITY
    ,OOL.UNIT_SELLING_PRICE UNIT_PRICE
    ,WND.NAME DELIVERY_NO
    ,(SELECT RAT.NAME FROM RA_TERMS RAT WHERE RAT.TERM_ID=RCT.TERM_ID) TERM_NAME
    ,RCT.TRX_NUMBER
    ,RCT.TRX_DATE
    ,ARPS.AMOUNT_LINE_ITEMS_ORIGINAL TOTAL_INVOICE_AMOUNT
    ,ARPS.TAX_ORIGINAL      ORIGINAL_TAX_AMT
    ,ARPS.TAX_REMAINING     REMAINING_TAX_AMT
    ,ARPS.AMOUNT_DUE_REMAINING DUE_AMOUNT
FROM
    OE_ORDER_HEADERS_ALL OOH
    ,OE_ORDER_LINES_ALL OOL
    ,OE_TRANSACTION_TYPES_TL OT
    ,AR_CUSTOMERS AC
    ,WSH_DELIVERY_DETAILS WDD
    ,WSH_NEW_DELIVERIES   WND
    ,WSH_DELIVERY_ASSIGNMENTS WDA
    ,RA_CUSTOMER_TRX_ALL RCT
    --,RA_CUSTOMER_TRX_LINES_ALL RCTL
    ,AR_PAYMENT_SCHEDULES_ALL ARPS
WHERE
 OOH.HEADER_ID=OOL.HEADER_ID
AND  OOH.ORDER_TYPE_ID=OT.TRANSACTION_TYPE_ID
AND  AC.CUSTOMER_ID=OOH.SOLD_TO_ORG_ID
--AND  WDD.SOURCE_HEADER_ID=OOH.HEADER_ID
AND  WDD.SOURCE_LINE_ID(+)=OOL.LINE_ID
AND  WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID(+)
AND  WDA.DELIVERY_ID=WND.DELIVERY_ID(+)
--AND  WND.CUSTOMER_ID=AC.CUSTOMER_ID
--AND  OOH.ORG_ID=RCT.ORG_ID
--AND  OOH.ORDER_NUMBER=RCT.INTERFACE_HEADER_ATTRIBUTE1(+)
AND  WDA.DELIVERY_ID=RCT.INTERFACE_HEADER_ATTRIBUTE3(+)
AND ARPS.CUSTOMER_TRX_ID(+)=RCT.CUSTOMER_TRX_ID
--AND  RCT.ORG_ID=RCTL.ORG_ID
--AND  RCT.CUSTOMER_TRX_ID=RCTL.CUSTOMER_TRX_ID(+)
--AND RCTL.INTERFACE_LINE_ATTRIBUTE6(+)=TO_CHAR(OOL.LINE_ID)
--AND RCTL.interface_line_context = 'ORDER ENTRY'
--AND RCTL.LINE_TYPE='LINE'
--AND OOH.ORDER_NUMBER=RCTL.INTERFACE_LINE_ATTRIBUTE1
Order By ORDER_NUMBER

How to find price discounts and surcharges on order lines in Order Management

SELECT   h.order_number, l.line_number, pa.list_line_type_code,
         pa.arithmetic_operator, pa.operand,
         DECODE (pa.modifier_level_code,
                 'ORDER', l.unit_list_price
                  * l.ordered_quantity
                  * pa.operand
                  * SIGN (pa.adjusted_amount)
                  / 100,
                 (pa.adjusted_amount * NVL (l.ordered_quantity, 0))
                ) discount_amt
    FROM qp_list_headers_vl lh,
         oe_price_adjustments pa,
         oe_order_lines_all l,
         oe_order_headers_all h
   WHERE h.order_number = 151266
     AND h.header_id = l.header_id
     AND h.org_id = l.org_id
     AND h.header_id = pa.header_id
     AND l.line_id = pa.line_id(+)
     AND pa.list_header_id = lh.list_header_id
--     AND (   pa.list_line_type_code = 'DIS'  OR pa.list_line_type_code = 'SUR'  OR pa.list_line_type_code = 'TAX'        )
     AND pa.applied_flag = 'Y'
     AND NOT EXISTS (
            SELECT 'X'
              FROM oe_price_adj_assocs pas, oe_price_adjustments pa1
             WHERE pas.rltd_price_adj_id = pa.price_adjustment_id
               AND pa1.price_adjustment_id = pas.price_adjustment_id
               AND pa1.list_line_type_code = 'PBH')
ORDER BY l.line_id
/

Note: QP_LIST_HEADERS_VL is view based on QP_LIST_HEADERS_B and QP_LIST_HEADERS_TL tables.

How to find freight charges on order lines in Order Management

SELECT   header_id, line_id, charge_id, charge_name, charge_amount,
         currency_code, invoiced_flag, interco_invoiced_flag, org_id,
         source_system_code, estimated_flag, invoiced_amount
    FROM oe_charge_lines_v
   WHERE header_id = (SELECT header_id
                        FROM oe_order_headers_all
                       WHERE order_number = 151266)
ORDER BY line_id
/

Note: The OE_CHARGE_LINES_V view is based on OE_PRICE_ADJUSTMENTS,  OE_ORDER_HEADERS_ALL  AND OE_ORDER_LINES_ALL for FREIGHT CHARGES.

Example: Submission of Standard Order Import Program

Example: Submission of Standard Order Import Program

SET SERVEROUTPUT ON;

DECLARE

     v_request_id                        NUMBER           DEFAULT 0;

    

    --Order Import Parameters

    p_operating_unit                     VARCHAR2(20)    := NULL;

    p_order_source                       VARCHAR2(20)    := 'XYZ';

    p_orig_sys_document_ref              VARCHAR2(20)    := NULL;

    p_operation_code                     VARCHAR2(20)    := NULL;

    p_validate_only                      VARCHAR2(20)    := 'N';

    p_debug_level                        VARCHAR2(20)    := '1';

    p_num_instances                      VARCHAR2(20)    := '4';

    p_sold_to_org_id                     VARCHAR2(20)    := NULL;

    p_sold_to_org                        VARCHAR2(20)    := NULL;

    p_change_sequence                    VARCHAR2(20)    := NULL;

    p_perf_param                         VARCHAR2(20)    := 'Y';

    p_rtrim_data                         VARCHAR2(20)    := 'N';

    p_pro_ord_with_null_flag             VARCHAR2(20)    := 'Y';

    p_default_org_id                     VARCHAR2(20)    := '83';

    p_validate_desc_flex                 VARCHAR2(20)    := 'N';

    -- End of Parameters -----

    v_context varchar2(100);

    FUNCTION set_context( i_user_name    IN  VARCHAR2

                         ,i_resp_name    IN  VARCHAR2

                         ,i_org_id       IN  NUMBER)

    RETURN VARCHAR2

    IS

        /* Inorder to reduce the content of the post I moved the implementation part of this function to another post and it is available here */

    END set_context;

BEGIN

      -- Setting the context ----

      v_context := set_context('&V_USER_NAME','&V_RESPONSIBILITY',82);

      IF v_context = 'F'

      THEN

        DBMS_OUTPUT.PUT_LINE('Error while setting the context');       

      END IF;

      DBMS_OUTPUT.PUT_LINE('Submit Order Import Concurrent Program');

      v_request_id:=  FND_REQUEST.SUBMIT_REQUEST (

               application  =>  'ONT'

              ,program      =>  'OEOIMP'

              ,description  =>  'Order Import'

              ,start_time   =>  SYSDATE

              ,sub_request  =>  NULL

              ,argument1    =>  p_operating_unit

              ,argument2    =>  p_order_source

              ,argument3    =>  p_orig_sys_document_ref

              ,argument4    =>  p_operation_code

              ,argument5    =>  p_validate_only

              ,argument6    =>  p_debug_level

              ,argument7    =>  p_num_instances

              ,argument8    =>  p_sold_to_org_id

              ,argument9    =>  p_sold_to_org

              ,argument10   =>  p_change_sequence

              ,argument11   =>  p_perf_param

              ,argument12   =>  p_rtrim_data

              ,argument13   =>  p_pro_ord_with_null_flag

              ,argument14   =>  p_default_org_id

              ,argument15   =>  p_validate_desc_flex

             );

       COMMIT;

       DBMS_OUTPUT.PUT_LINE('Request_id: '||v_request_id);

EXCEPTION WHEN OTHERS THEN        

       DBMS_OUTPUT.PUT_LINE(SQLCODE||' Error :'||SQLERRM);

END;