Oracle Fusion + Sales Order Audit Report Query + Sales Order Fulfillment Lines EFF Query

 SELECT * FROM 

(SELECT --dla.line_id,

       --dha.header_id,

   dha.order_number "Order Number",

       hp.party_name "customer",

       hp.country "country",

   (NVL(dla.ordered_qty,0) * NVL(dla.unit_selling_price,0)) amount,

  (SELECT attribute_char3

   FROM doo_headers_eff_b dheb

   WHERE dheb.header_id = dha.header_id

     AND CONTEXT_CODE='Subscription')  region,

       Revenue_Management.attribute_char8 "workday contract name",

       Subscription.attribute_char1 "billing_offset",

       ACCOUNTS_RECEIVABLES.attribute_char1 "invoice group by number",

       ACCOUNTS_RECEIVABLES.attribute_char2 "invoice group by description",

       ACCOUNTS_RECEIVABLES.attribute_char3 "management group",

        SF_SUBSCRIPTION.ATTRIBUTE_CHAR10 "align billing from",

            SF_SUBSCRIPTION.attribute_char11 "custom billing",

                 Conversion.ATTRIBUTE_CHAR4 "Misc info",

                 Conversion.attribute_char2 "PS billed amount",

                 Conversion.attribute_char3 "PS unbilled amount",

                 Conversion.attribute_char5 "supplier purchase order",

                 TO_CHAR(SNOW.attribute_timestamp1,'MM/DD/YY HH:MI') "cloud provision date",

                 TO_CHAR(SNOW.attribute_timestamp2,'MM/DD/YY HH:MI') "on-perm provision date",

                 IB.attribute_char2 "asset line identifer",

                 IB.attribute_char3 "life cycle pid ",

                 SFDC.attribute_char1 "PO requied",

 SFDC.attribute_char2 "PO Number",

 SFDC.attribute_char3 "PO Line Number",

                 SFDC.attribute_char4 "invoice trigger event",

                 SFDC.attribute_char5 "site id",

                 SFDC.attribute_char6 "bundle id",

                 SFDC.attribute_char7 "advanced billing flag",

                 SFDC.attribute_char10 "used inventory flag",

                 SFDC.attribute_char11 "GSS validation flag",

                 SFDC.attribute_char16 "suppress fulfillment",

                 Procurement.attribute_char1 "cabinet position",

                 Procurement.attribute_char2 "PO vendor cost/transfer price",

                 Procurement.attribute_char3 "related manual order",

                 Tax.attribute_char2 "0% tax  rate flag",

                 BRAZIL.attribute_char1 "CFOP-line order",

                 CSP.attribute_char1 "CSP flag",

                 CSP.attribute_char3 "private order flag",

     esib.item_number,

 esib.description item_description,

 bill_to_party.party_name bill_to_customer,

 ship_to_party.party_name ship_to_customer,

 SF_SUBSCRIPTION.ATTRIBUTE_CHAR5 sf_subscription_number,

 SF_SUBSCRIPTION.ATTRIBUTE_CHAR2  "SF Line Identifier (Model)",

 revenue_management.attribute_char6 "Rev Contract Grouping" ,

 (SELECT header_curr_duration_ext_amt

 FROM doo_order_pricing_details_v dopdv

 WHERE dopdv.fulfill_line_id = dfla.fulfill_line_id

                 AND dopdv.PRICE_ELEMENT_CODE='QP_NET_PRICE'

                 AND dopdv.ROLLUP_FLAG ='Y' ) Total_Amount_for_Duration

FROM doo_headers_all dha,

     doo_lines_all dla,

     doo_fulfill_lines_all dfla,

     doo_fulfill_lines_eff_b ACCOUNTS_RECEIVABLES,

     doo_fulfill_lines_eff_b Revenue_Management,

     doo_fulfill_lines_eff_b Subscription,

     doo_fulfill_lines_eff_b CONVERSION,

     doo_fulfill_lines_eff_b SFDC,

 doo_fulfill_lines_eff_b SNOW,

     doo_fulfill_lines_eff_b IB,

     doo_fulfill_lines_eff_b Procurement,

     doo_fulfill_lines_eff_b Tax,

     doo_fulfill_lines_eff_b BRAZIL,

     doo_fulfill_lines_eff_b CSP,

 doo_fulfill_lines_eff_b SF_SUBSCRIPTION,

     hz_parties hp,

 egp_system_items esib,

     doo_order_addresses bill_to,

 doo_order_addresses ship_to,

 hz_cust_accounts bill_to_cust,

 hz_parties bill_to_party,

 hz_parties ship_to_party

WHERE 1=1

  ---AND dha.order_number='00026264'

  AND dha.header_id=dla.header_id

  AND dla.line_id= dfla.line_id

  AND dfla.fulfill_line_id = accounts_receivables.fulfill_line_id (+)

  AND accounts_receivables.context_code (+)='ACCOUNTS RECEIVABLES'

  AND dfla.fulfill_line_id = Revenue_Management.fulfill_line_id (+)

  AND Revenue_Management.context_code (+)='Revenue_Management_Information_Line'

  AND dfla.fulfill_line_id = Subscription.fulfill_line_id (+)

  AND Subscription.context_code (+)='Subscription'

  AND dfla.fulfill_line_id = Conversion.fulfill_line_id (+)

  AND Conversion.context_code (+)='Conversion'

  AND dfla.fulfill_line_id = SFDC.fulfill_line_id (+)

  AND SFDC.context_code (+)='SFDC'

  AND dfla.fulfill_line_id = SNOW.fulfill_line_id (+)

  AND SNOW.context_code (+)='SNOW'

  AND dfla.fulfill_line_id = IB.fulfill_line_id (+)

  AND IB.context_code (+)='IB - Asset Line Identifier Model'

  AND dfla.fulfill_line_id = Procurement.fulfill_line_id (+)

  AND Procurement.context_code (+)='Procurement'

  AND dfla.fulfill_line_id = Tax.fulfill_line_id (+)

  AND Tax.context_code (+)='Tax'

  AND dfla.fulfill_line_id = BRAZIL.fulfill_line_id (+)

  AND BRAZIL.context_code (+)='BRAZIL-Localization'

  AND dfla.fulfill_line_id = CSP.fulfill_line_id (+)

  AND CSP.context_code (+)='CSP Details'

  AND dfla.fulfill_line_id = SF_SUBSCRIPTION.fulfill_line_id (+)

  AND SF_SUBSCRIPTION.context_code (+)='SF Subscription - SF Line Identifier Model'

  AND dha.sold_to_party_id = hp.party_id 

  AND dla.inventory_organization_id =esib.organization_id

  and dla.inventory_item_id = esib.inventory_item_id

  AND dha.header_id = bill_to.header_id (+)

  AND bill_to.address_use_type (+)= 'BILL_TO'

  AND bill_to.cust_acct_id = bill_to_cust.cust_account_id (+)

  AND bill_to_cust.party_id = bill_to_party.party_id

  AND dha.header_id = ship_to.header_id (+)

  AND ship_to.address_use_type (+) = 'SHIP_TO'

  AND ship_to.party_id = ship_to_party.party_id (+)

 ) 

  WHERE region = NVL(:p_region,region);

Regards,

Amar Alam

Oracle Apps + Query to get customer contact information

 -- Retrieve Party Contact Points

SELECT cp.*
FROM ar.hz_cust_accounts c,
AR.HZ_PARTIES p,
ar.hz_contact_points cp
WHERE
c.account_number = :P_CUST_ACCT_NUM
AND p.party_id = c.party_id
AND cp.owner_table_name = 'HZ_PARTIES'
AND cp.owner_table_id = p.party_id;

-- Site contact point (this is different from site contact)

SELECT p.party_number,
c.cust_account_id,
c.account_number,
cp.*
FROM ar.hz_cust_accounts c,
AR.HZ_PARTIES p,
ar.hz_party_sites s,
ar.hz_contact_points cp
WHERE c.account_number = :P_CUST_ACCT_NUM
AND p.party_id = c.party_id
AND p.party_id = s.party_id
AND cp.owner_table_name = 'HZ_PARTY_SITES'
AND cp.owner_table_id = s.party_site_id;

-- Site Contacts with contact points

  SELECT p.party_name,

p.party_id,
p.party_number,
p.party_type,
c.cust_account_id,
c.account_number,
pp.party_name contact_name,
pp.PARTY_ID contact_party_id,
pp.party_type,
r.role_type,
co.phone_country_code,
co.phone_area_code,
co.phone_number,
co.phone_line_type,
co.raw_phone_number,
co.email_address,
co.contact_point_purpose,
co.primary_flag,
co.last_update_date
FROM ar.hz_cust_accounts c,
AR.HZ_PARTIES p,
ar.hz_cust_account_roles r,
AR.HZ_PARTIES cp,
ar.hz_contact_points co,
ar.hz_cust_acct_sites_all s, --needed only if you require org_id
ar.hz_parties pp,
AR.hz_relationships rel
WHERE s.cust_account_id = c.cust_account_id
AND p.party_id = c.party_id
AND r.cust_account_id = c.cust_account_id
AND cp.PARTY_ID = r.party_id
AND co.owner_table_name = 'HZ_PARTIES'
AND co.OWNER_TABLE_ID = cp.party_id
AND r.cust_acct_site_id IS NOT NULL
AND r.cust_acct_site_id = s.cust_acct_site_id
AND pp.party_id = rel.subject_id
AND rel.party_id = cp.party_id
AND rel.relationship_code = 'CONTACT_OF'
AND rel.directional_flag = 'F'
AND rel.subject_table_name = 'HZ_PARTIES'
AND c.account_number = :P_CUST_ACCT_NUM
ORDER BY c.cust_account_id DESC;

-- CUSTOMER ACCOUNT CONTACT INFO

select hp.party_name
,hp1.party_name Contact_name
,hoc.contact_number
,hcar.cust_acct_site_id
,hca.cust_account_id
from apps.hz_parties hp
,apps.hz_cust_accounts hca
,apps.hz_org_contacts hoc
,apps.hz_cust_account_roles hcar
,apps.hz_parties hp1
,apps.hz_relationships hr
where 1 = 1
and hca.party_id = hp.party_id
AND hr.subject_id = hp1.party_id
AND hr.object_id = hp.party_id
AND hcar.party_id = hr.party_id
AND hoc.party_relationship_id = hr.relationship_id
AND hcar.cust_acct_site_id is null
AND hcar.STATUS like 'A';

--CUSTOMER ACCOUNT SITE CONTACT INFO

select hp.party_name
,hp1.party_name Contact_name
,hoc.contact_number
,hps.party_site_id
,hps.PARTY_SITE_NUMBER
from apps.hz_parties hp
,apps.hz_party_sites hps
,apps.hz_cust_accounts hca
,apps.hz_cust_acct_sites_all hcas
,apps.hz_org_contacts hoc
,apps.hz_cust_account_roles hcar
,apps.hz_parties hp1
,apps.hz_relationships hr
where 1 = 1
and hp.party_id = hps.party_id
and hca.party_id = hp.party_id
and hca.cust_account_id = hcas.cust_account_id
and hcas.party_site_id = hps.party_site_id
and hcar.cust_acct_site_id(+) = hcas.cust_acct_site_id
AND hr.subject_id = hp1.party_id
AND hr.object_id = hp.party_id
AND hcar.party_id = hr.party_id
AND hoc.party_relationship_id = hr.relationship_id
AND hcar.STATUS like 'A';

Regards,

Amar Alam

Oracle apps + Query to get Customer Contact details

select account_number "Account Number"
     , obj.party_name "Customer Name"
     , sub.party_name "Contact Name"
     , hcp.contact_point_type || ': ' ||
       DECODE(hcp.contact_point_type, 'EMAIL', hcp.email_address
                                    , 'PHONE', hcp.phone_area_code || ' ' || hcp.phone_number
                                    , 'WEB'  , hcp.url
                                    , 'Unknow contact Point Type ' || hcp.contact_point_type
             ) "How to Contact"
  from apps.hz_cust_accounts  hca
     , apps.hz_parties        obj
     , apps.hz_relationships  rel
     , apps.hz_contact_points hcp
     , apps.hz_parties        sub
 where hca.party_id           = rel.object_id
   and hca.party_id           = obj.party_id
   and rel.subject_id         = sub.party_id
   and rel.relationship_type  = 'CONTACT'
   and rel.directional_flag   = 'F'
   and rel.party_id           = hcp.owner_table_id
   and hcp.owner_table_name   = 'HZ_PARTIES'
   and hca.account_number=1258;

Thanks
Amar Alam

O2C Technical Flow in Oracle Apps

SELECT DISTINCT
                OOHA.ORDER_NUMBER,
                OTTT.NAME "ORDER TYPE",
                OOHA.FLOW_STATUS_CODE HEADER_STATUS,
                OOLA.CUST_PO_NUMBER,
                OOLA.ORDERED_ITEM,
                OOLA.UNIT_SELLING_PRICE,
                ORDERED_QUANTITY,
                QLP.NAME  PRICE_LIST,
                (SELECT LAST_NAME||', '||FIRST_NAME FROM PER_ALL_PEOPLE_F WHERE PERSON_ID=RSA.PERSON_ID) SALESREP,
                OOD.ORGANIZATION_NAME,
                HCA.ACCOUNT_NAME "CUSTOMER NAME",
                HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
                HCSUA.LOCATION SHIP_TO_LOCATION,
                HL.ADDRESS1||','||HL.ADDRESS2||','||HL.ADDRESS3||','||HL.ADDRESS4||','||HL.CITY||','||HL.STATE||','||HL.POSTAL_CODE||','||HL.COUNTRY SHIP_TO_ADDRESS,
                HCSUA1.LOCATION BILL_TO_LOCATION,
                HL1.ADDRESS1||','||HL1.ADDRESS2||','||HL1.ADDRESS3||','||HL1.ADDRESS4||','||HL1.CITY||','||HL1.STATE||','||HL1.POSTAL_CODE||','||HL1.COUNTRY BILL_TO_ADDRESS,
                --WDD.DELIVERY_DETAIL_ID,
                WND.NAME "DELIVERY NUMBER"
                ,RCTA.TRX_NUMBER "INVOICE NUMBER"
                ,XTE.TRANSACTION_NUMBER,
                APSA.PAYMENT_SCHEDULE_ID,
                XLAE.ENTITY_ID,
                XLAH.AE_HEADER_ID,
                XLAL.GL_SL_LINK_ID,
                GIR.JE_BATCH_ID,
                L.JE_HEADER_ID,
                L.JE_LINE_NUM,
                B.NAME BATCH_NAME,
                B.DESCRIPTION BATCH_DESCRIPTION,
                H.NAME JOURNAL_NAME,
                H.STATUS JOURNAL_STATUS,
                H.DESCRIPTION JE_DESCRIPTION
FROM OE_ORDER_HEADERS_ALL OOHA,
                OE_ORDER_LINES_ALL OOLA,
                WSH_DELIVERY_DETAILS WDD,
                WSH_DELIVERY_ASSIGNMENTS WDA,
                WSH_NEW_DELIVERIES WND,
                OE_TRANSACTION_TYPES_TL OTTT,
                QP_LIST_HEADERS QLP,
                RA_SALESREPS_ALL RSA,
                RA_CUSTOMER_TRX_LINES_ALL RCTLA,
                RA_CUSTOMER_TRX_ALL RCTA,
                HZ_CUST_ACCOUNTS HCA,
                HZ_CUST_SITE_USES_ALL HCSUA,
                HZ_CUST_ACCT_SITES_ALL HCASA,
                HZ_PARTY_SITES  HPS,
                HZ_LOCATIONS HL,
                HZ_CUST_SITE_USES_ALL HCSUA1,
                HZ_CUST_ACCT_SITES_ALL HCASA1,
                HZ_PARTY_SITES  HPS1,
                HZ_LOCATIONS HL1,
                ORG_ORGANIZATION_DEFINITIONS OOD
                --AR_RECEIVABLE_APPLICATIONS_ALL ARAA,
                --AR_CASH_RECEIPTS_ALL ACRA
                ,AR_PAYMENT_SCHEDULES_ALL APSA
                ,XLA.XLA_TRANSACTION_ENTITIES XTE
                ,XLA_EVENTS XLAE
                ,XLA_AE_HEADERS XLAH
                ,XLA_AE_LINES XLAL
                ,GL_IMPORT_REFERENCES GIR
                ,GL_JE_LINES L
                ,GL_JE_HEADERS H
                ,GL_JE_BATCHES B
WHERE OOHA.HEADER_ID = OOLA.HEADER_ID
                AND OOHA.ORDER_NUMBER = :P_ORDER_NUMBER
                AND OOHA.HEADER_ID = WDD.SOURCE_HEADER_ID
                AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
                AND WDA.DELIVERY_ID = WND.DELIVERY_ID
                AND OOHA.PRICE_LIST_ID=QLP.LIST_HEADER_ID
                AND OOHA.ORDER_TYPE_ID = OTTT.TRANSACTION_TYPE_ID
                AND OOHA.SALESREP_ID=RSA.SALESREP_ID
                AND OOHA.SOLD_TO_ORG_ID = HCA.CUST_ACCOUNT_ID
                AND HCA.CUST_ACCOUNT_ID=OOHA.SOLD_TO_ORG_ID
                AND HCSUA.SITE_USE_ID=OOHA.SHIP_TO_ORG_ID
                AND HCSUA.CUST_ACCT_SITE_ID=HCASA.CUST_ACCT_SITE_ID
                AND HCSUA.SITE_USE_CODE='SHIP_TO'
                AND HCASA.PARTY_SITE_ID=HPS.PARTY_SITE_ID
                AND HL.LOCATION_ID=HPS.LOCATION_ID
                AND HCSUA1.SITE_USE_ID=OOHA.INVOICE_TO_ORG_ID
                AND HCSUA1.CUST_ACCT_SITE_ID=HCASA1.CUST_ACCT_SITE_ID
                AND HCSUA1.SITE_USE_CODE='BILL_TO'
                AND HCASA1.PARTY_SITE_ID=HPS1.PARTY_SITE_ID
                AND HL1.LOCATION_ID=HPS1.LOCATION_ID
                AND OOHA.SHIP_FROM_ORG_ID = OOD.ORGANIZATION_ID
                AND TO_CHAR (OOHA.ORDER_NUMBER) = RCTLA.INTERFACE_LINE_ATTRIBUTE1
                AND TO_CHAR(WND.NAME)=RCTLA.INTERFACE_LINE_ATTRIBUTE3
                AND RCTLA.INVENTORY_ITEM_ID=OOLA.INVENTORY_ITEM_ID
                AND RCTLA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
                --AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
                --AND ACRA.CASH_RECEIPT_ID = ARAA.CASH_RECEIPT_ID
                AND RCTA.CUSTOMER_TRX_ID=APSA.CUSTOMER_TRX_ID
                AND RCTA.TRX_NUMBER = XTE.TRANSACTION_NUMBER
                AND XLAE.ENTITY_ID = XTE.ENTITY_ID
                AND XLAE.APPLICATION_ID = XTE.APPLICATION_ID
                AND XLAH.EVENT_ID = XLAE.EVENT_ID
                AND XLAL.AE_HEADER_ID = XLAH.AE_HEADER_ID
                AND GIR.GL_SL_LINK_TABLE = XLAL.GL_SL_LINK_TABLE
                AND GIR.GL_SL_LINK_ID = XLAL.GL_SL_LINK_ID
                AND L.JE_HEADER_ID = GIR.JE_HEADER_ID
                AND L.JE_LINE_NUM = GIR.JE_LINE_NUM
                AND H.JE_HEADER_ID = L.JE_HEADER_ID
                AND B.JE_BATCH_ID = H.JE_BATCH_ID
                AND H.JE_SOURCE = 'Receivables'
                AND H.STATUS = 'P';

Thanks
Amar Alam

Order Hold Information Query

SELECT
      ooh.order_number "Order Number",
       ooh.ordered_date "Ordered Date",
       ooh.flow_status_code "Order Status",
       ohd.name "Hold Name",
       ohs.released_flag "Hold Release Flag",
       ohr.release_reason_code "Hold Release Reason",
       ohr.creation_date "Hold Release Date",
       ooh.payment_type_code "Payment Type Code"
FROM apps.oe_order_headers_all ooh,
     apps.oe_order_holds_all hld,
     apps.oe_hold_sources_all ohs,
     apps.oe_hold_definitions ohd,
     apps.oe_hold_releases ohr
WHERE ooh.ordered_date BETWEEN :P_DATE_FROM AND :P_DATE_TO
      AND ooh.order_category_code = 'ORDER'
      AND ooh.header_id=hld.header_id(+)
      AND hld.hold_source_id=ohs.hold_source_id(+)
      AND ohs.hold_id=ohd.hold_id(+)
      AND ohs.hold_release_id=ohr.hold_release_id(+)
ORDER BY ooh.order_number

Ur's
Amar Alam

Order 2 Cash Cycle Technical Flow in Oracle Apps

SELECT *
  FROM oe_order_headers_all
 WHERE order_number = 66161;

SELECT *
  FROM oe_order_lines_all
 WHERE header_id = (SELECT header_id
                      FROM oe_order_headers_all
                     WHERE order_number = 66161);

SELECT *
  FROM wsh_delivery_details
 WHERE source_header_id = (SELECT header_id
                             FROM oe_order_headers_all
                            WHERE order_number = 66161);

SELECT *
  FROM wsh_delivery_assignments
 WHERE delivery_detail_id IN (
                         SELECT delivery_detail_id
                           FROM wsh_delivery_details
                          WHERE source_header_id =
                                                 (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66161));

SELECT *
  FROM wsh_new_deliveries
 WHERE delivery_id IN (
          SELECT delivery_id
            FROM wsh_delivery_assignments
           WHERE delivery_detail_id IN (
                         SELECT delivery_detail_id
                           FROM wsh_delivery_details
                          WHERE source_header_id =
                                                 (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66161)));

SELECT *
  FROM ra_customer_trx_all
 WHERE (interface_header_attribute1) =
                             (SELECT TO_CHAR (order_number)
                                FROM oe_order_headers_all
                               WHERE header_id = (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66162));

SELECT *
  FROM ra_customer_trx_lines_all
 WHERE (interface_line_attribute1) =
                             (SELECT TO_CHAR (order_number)
                                FROM oe_order_headers_all
                               WHERE header_id = (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66162));

SELECT *
  FROM ra_customer_trx_lines_all
 WHERE (interface_line_attribute3) IN (
          SELECT TO_CHAR (delivery_id)
            FROM wsh_delivery_assignments
           WHERE delivery_detail_id IN (
                         SELECT delivery_detail_id
                           FROM wsh_delivery_details
                          WHERE source_header_id =
                                                 (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66161)))

SELECT *
  FROM ar_cash_receipts_all
 WHERE cash_receipt_id IN (
          SELECT cash_receipt_id
            FROM ar_payment_schedules_all
           WHERE customer_trx_id IN (
                    SELECT DISTINCT customer_trx_id
                               FROM ra_customer_trx_all
                              WHERE (interface_header_attribute1 =
                                        (SELECT TO_CHAR (order_number)
                                           FROM oe_order_headers_all
                                          WHERE header_id =
                                                   (SELECT header_id
                                                      FROM oe_order_headers_all
                                                     WHERE order_number =
                                                                         66162))
                                    )));

SELECT *
  FROM ar_receivable_applications_all
 WHERE customer_trx_id =
          (SELECT customer_trx_id
             FROM ra_customer_trx_all
            WHERE (interface_header_attribute1) =
                             (SELECT TO_CHAR (order_number)
                                FROM oe_order_headers_all
                               WHERE header_id = (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66162)));

SELECT *
  FROM ar_cash_receipts_all
 WHERE cash_receipt_id =
          (SELECT cash_receipt_id
             FROM ar_receivable_applications_all
            WHERE customer_trx_id =
                     (SELECT customer_trx_id
                        FROM ra_customer_trx_all
                       WHERE (interface_header_attribute1) =
                                (SELECT TO_CHAR (order_number)
                                   FROM oe_order_headers_all
                                  WHERE header_id =
                                                 (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66162))));

SELECT *
  FROM gl_code_combinations
 WHERE code_combination_id =
          (SELECT code_combination_id
             FROM ar_receivable_applications_all
            WHERE customer_trx_id =
                     (SELECT customer_trx_id
                        FROM ra_customer_trx_all
                       WHERE (interface_header_attribute1) =
                                (SELECT TO_CHAR (order_number)
                                   FROM oe_order_headers_all
                                  WHERE header_id =
                                                 (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66162))));


Ur's
AmarAlam

Order Sales Returns Closed Report Query

SELECT (SELECT NAME
          FROM apps.hr_operating_units
         WHERE organization_id = ooh.org_id) sales_org,
       (SELECT ood1.organization_name
          FROM apps.org_organization_definitions ood1
         WHERE ood1.organization_id =
                                     ool.ship_from_org_id)
                                                          ship_from_warehouse,
       hcs_ship.attribute1 sales_channel,
       NVL
          (jrs.NAME,
           (SELECT c.full_name
              FROM apps.per_all_people_f c
             WHERE 1 = 1
               AND c.employee_number = jrs.salesrep_number
               AND TRUNC (SYSDATE) BETWEEN c.effective_start_date
                                       AND c.effective_end_date)
          ) sales_person,
       ooh.flow_status_code header_status, ool.flow_status_code line_status,
       ota.order_category_code order_category, ott.NAME order_type_code,
       NVL (ott.description, ott.NAME) description, ooh.order_number,
       NVL ((SELECT full_name
               FROM apps.per_all_people_f a, apps.fnd_user b
              WHERE a.person_id = b.employee_id
                AND b.user_id = ooh.created_by
                AND ROWNUM < 2),
            (SELECT user_name
               FROM apps.fnd_user
              WHERE user_id = ooh.created_by)
           ) created_by,
       ooh.transactional_curr_code currency_code, ooh.fob_point_code,
       ooh.freight_terms_code, ooh.cust_po_number, msi.segment1 item_code,
       msi.description item_description, msi.weight_uom_code, msi.unit_weight,
       CASE
          WHEN xxfcc.channel_level1 = 'CONSUMER'
          AND xxfcc.channel_level2 IN ('LOC_TT', 'EXP_TT')
             THEN 'GT'
          WHEN xxfcc.channel_level1 = 'CONSUMER'
          AND xxfcc.channel_level2 IN ('LOC_MT', 'EXP_MT')
             THEN 'MT'
          ELSE xxfcc.channel_level1
       END channel_group,
       ool.unit_selling_price, ool.line_number, ool.order_quantity_uom,
       CASE
          WHEN ota.order_category_code = 'RETURN'
             THEN ool.ordered_quantity
       END return_ordered_quantity,
       CASE
          WHEN ota.order_category_code = 'RETURN'
             THEN ((ool.ordered_quantity * msi.unit_weight) / 1000
                  )
       END return_ordered_wt_mt,
       CASE
          WHEN ota.order_category_code = 'RETURN'
             THEN ool.shipped_quantity
       END return_shipped_quantity
  FROM ont.oe_order_headers_all ooh,
       ont.oe_transaction_types_tl ott,
       ont.oe_transaction_types_all ota,
       ont.oe_order_lines_all ool,
       apps.org_organization_definitions ood,
       apps.hr_operating_units hou,
       inv.mtl_system_items_b msi,
       ar.hz_cust_site_uses_all hcs_ship,
       jtf.jtf_rs_salesreps jrs,
       ar.hz_cust_acct_sites_all hca_ship,
       ar.hz_party_sites hps_ship,
       ar.hz_parties hp_ship,
       ar.hz_locations hl_ship,
       ar.hz_cust_site_uses_all hcs_bill,
       ar.hz_cust_acct_sites_all hca_bill,
       ar.hz_party_sites hps_bill,
       ar.hz_parties hp_bill,
       ar.hz_locations hl_bill,
       inv.mtl_parameters mp,
       apps.xxiff_fin_customer_channel xxfcc
 WHERE 1 = 1
   AND ooh.ship_to_org_id = hcs_ship.site_use_id
   AND ott.transaction_type_id = ota.transaction_type_id
   AND ott.transaction_type_id = ooh.order_type_id
   AND ott.LANGUAGE = 'US'
   AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
   AND hca_ship.party_site_id = hps_ship.party_site_id
   AND hps_ship.party_id = hp_ship.party_id
   AND hps_ship.location_id = hl_ship.location_id
   AND ooh.invoice_to_org_id = hcs_bill.site_use_id
   AND hcs_bill.cust_acct_site_id = hca_bill.cust_acct_site_id
   AND hca_bill.party_site_id = hps_bill.party_site_id
   AND hcs_bill.site_use_id = xxfcc.site_use_id(+)
   AND msi.inventory_item_id = ool.inventory_item_id
   AND msi.organization_id = ood.organization_id
   AND ood.organization_id = ooh.org_id
   AND hou.organization_id = ood.operating_unit
   AND ooh.salesrep_id = jrs.salesrep_id
   AND ooh.org_id = jrs.org_id
   AND ooh.header_id = ool.header_id
   AND hps_bill.party_id = hp_bill.party_id
   AND ooh.org_id = fnd_profile.VALUE ('ORG_ID')
   --AND trunc(ooh.creation_date)='31-JUL-2013'
   --AND ooh.order_number='8880101739'
   AND ota.order_category_code = 'RETURN'
   AND ool.flow_status_code = 'CLOSED'
   AND hps_bill.location_id = hl_bill.location_id
   AND UPPER (ott.NAME) NOT LIKE '%IDS%ISO%'
   AND mp.organization_id(+) = ooh.ship_from_org_id


Ur's
AmarAlam

Overview of Oracle Shipping

PICK RELEASE:  Pick release is an activity to confirm picking of goods from stock and keeping aside for shipping to a sales order. Normally pick release happens based on scheduled ship date on sales order. Pick release process can be done manually or automatically scheduled. Once pick release is activated it normally prints Pick ticket or Pick Slip for warehouse personnel to pick the material. Pickslip displays the information like Item, quantity, location from where it should be picked and destination place where it should be moved (eg. docking station). Pick release doesn’t decrements the onhand inventory but it decrements the available inventory for next picking.

DELIVERY : Delivery is a batch of sales order lines grouped together based on some criteria (criteria could be shipto location, sales order etc). Delivery can be automatically created by system during pick release or it can be manually created.

TRIP:  Trip is primarily the shipment routing plan, it has pick up location and drop off location. Most of the cases multiple deliveries can be grouped under a trip.

SHIP CONFIRM:   It is an activity where it is confirmed systematically that goods are shipped from the warehouse that finally reduces the on hand quantity and updates the install base with customer & sales order details.


Thanks
Amar Alam

SHIP SET in Oracle Order Management

Ship Set , Order Lines and Delivery Details in OM

In Oracle Order Management When we ever add Order line in a SHIP_SET , I will add the data into OE_SETS tables as well as update OE_ORDER_LINES_ALL table for ship_set_ID.After Booking event ship_set_id got populated in WSH_DELIVERY_DETAILS Table.

Now if we TRY to remove the line from the SHIP SET , system will update the WSH_DELIVERY_DETAILS table and OE_ORDER_LINES_ALL table and set the ship_set_id column in both the tables to NULL.

BUT please note that once delivery is CLOSED/SHIPPED , system will not let you remove the line from SHIP_SET ( And why we need that , suppose we have say 5 lines in the SHIP SET and these lines assocaited with say 2 deliveries and if 1 of the of the delivery is closed and we want to remove the lines from the ship set the system will not let yoy do that). Only Option possible is DELETE from OE_SETS and UPDATE on WSH_DELIVERY_DETAILS and OE_ORDER_LINES_ALL ( Off course best practices from the Oracle will not allow us to do any DML on base Oracle Apps table).


Ur's
AmarAlam

Order to Cash Cycle - Tables get Affected @ Each Step

1) Order Entry
------------------------------------
oe_order_headers_all.flow_status_code = 'ENTERED'
oe_order_headers_all.booked_flag = 'N'

oe_order_lines_all.flow_status_code = 'ENTERED'
oe_order_lines_all.booked_flag = 'N'
oe_order_lines_all.open_flag = 'Y'

2) Order Booking
------------------------------------
oe_order_headers_all.flow_status_code = 'BOOKED'
oe_order_headers_all.booked_flag = 'Y'

oe_order_lines_all.flow_status_code = 'AWAITING SHIPPING'
oe_order_lines_all.booked_flag = 'Y'

wsh_delivery_details.released_status = 'R' (ready to release)

wsh_delivery_assignments.delivery_id = BLANK

3) Reservation
------------------------------------
mtl_demand
mtl_reservations

4) Pick Release
------------------------------------
wsh_new_deliveries.status_code = 'OP'
wsh_delivery_assignments.delivery_id gets assigned
wsh_delivery_details.released_status = 'S' (submitted for release)
mtl_txn_request_headers
mtl_txn_request_lines
mtl_material_transactions_temp

5) Pick Confirm
------------------------------------
oe_order_lines_all.flow_status_code = 'PICKED'
mtl_material_transactions
wsh_delivery_details.released_status = 'Y' (Released)
mtl_onhand_quantities

6) Ship Confirm
------------------------------------
Data will be removed from wsh_new_deliveries
oe_order_lines_all.flow_status_code = 'SHIPPED'
wsh_delivery_details.released_status = 'C' (Shipped)
wsh_serial_numbers
data will be deleted from mtl_demand and mtl_reservations
item qty gets deducted from mtl_onhand_quantities

7) Enter Invoice
------------------------------------
After shipping the order, workflow backgroud engine picks up records and post it to
ra_interface_lines_all
Auto invoice program picks up records from interface table and insert them into
ra_customer_trx_all (trx_number is invoice number)
ra_customer_trx_lines_all (line_attribute_1 = Order number and line_attribute_6 = Order line id)

8) Complete Line
------------------------------------
oe_order_lines_all.flow_status_code = 'SHIPPED'
oe_order_lines_all.open_flag = 'N'

9) Close Order
------------------------------------
oe_order_headers_all.flow_status_code = 'CLOSED'
oe_order_lines_all.flow_status_code = 'CLOSED'
oe_order_lines_all.open_flag = 'N'

10) Create Receipt
------------------------------------
AR_CASH_RECEIPTS_ALL

11) Transfer to General Ledger
------------------------------------
GL_INTERFACE

12) Journal Import
------------------------------------
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES

13) Posting
------------------------------------
GL_BALANCES


Ur's
AmarAlam

Navigation to Add/Edit OM Cancel Reason Codes

Using the below navigation in OM, We can add or end date the cancel reasons
Setup -> Quick Codes->Order Management-> CANCEL_CODE


Ur's
AmarAlam