1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

O2c queries

Discussion in 'Oracle Financials' started by sambhaji, Nov 4, 2013.

  1. sambhaji

    sambhaji Active Member

    Messages:
    62
    Likes Received:
    2
    Trophy Points:
    160
    Hi,

    If anybody has sql queries/report query on O2C(ORDER MANAGEMENT)
    Please do senfd me .


    Regards
    Sambhaji
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi check this query, It may help you.

    Code (SQL):
    SELECT ooha.order_number,ooha.org_id,
           hca.account_name,
           hp.party_name "Customer Name",
           hcasab.orig_system_reference      BILL_TO_ORIG_REF,
           hpsb.STATUS                       BILL_TO_STATUS,
           'ADDRESS1 - '||bill_loc.address1||','||CHR(10)|| 'ADDRESS2 - '||bill_loc.address2||','||CHR(10)||
           'ADDRESS3 - '||bill_loc.address3||','||CHR(10)|| 'CITY     - '||bill_loc.city||','||CHR(10)||
           'POSTAL CD- '||bill_loc.postal_code||','||CHR(10)|| 'COUNTRY  - '|| bill_loc.country  BILL_TO_ADDRESS,
           hcasas.orig_system_reference      SHIP_TO_ORIG_REF,
           hpss.STATUS SHIP_TO_STATUS,
           'ADDRESS1 - '||ship_loc.address1||','||CHR(10)|| 'ADDRESS2 - '||ship_loc.address2||','||CHR(10)||
           'ADDRESS3 - '||ship_loc.address3||','||CHR(10)|| 'CITY     - '||ship_loc.city||','||CHR(10)||
           'POSTAL CD- '||ship_loc.postal_code||','||CHR(10)|| 'COUNTRY  - '|| ship_loc.country  SHIP_TO_ADDRESS,
           oola.inventory_item_id,oola.ordered_item,
           msib.description item_description,
           wnd.name delivery_number,
           rct.trx_number "AR Invoice Number",
           acr.receipt_number "AR Receipt Number",
           gjh.ledger_id,
           gjh.name
      FROM oe_order_headers_all ooha,
           oe_order_lines_all oola,
           hz_parties hp,
           hz_cust_accounts hca,
           hz_party_sites hpss,
           hz_party_sites hpsb,
           hz_locations bill_loc,
           hz_locations ship_loc,
           hz_cust_acct_sites_all hcasab,
           hz_cust_acct_sites_all hcasas,
           hz_cust_site_uses_all hzsuab,
           hz_cust_site_uses_all hzsuas,
           mtl_system_items_b msib,
           wsh_delivery_details wdd,
           wsh_new_deliveries wnd,
           wsh_delivery_assignments wda,
           ra_customer_trx_all rct,
           ra_customer_trx_lines_all rctl,
           ra_cust_trx_line_gl_dist_all rctld,        
           ar_cash_receipts_all acr,
           xla.xla_transaction_entities xte,
           xla_events xe,
           xla_ae_headers xah,
           xla_ae_lines xal,
           xla_distribution_links xdl,
           gl_import_references gir,
           gl_je_batches gjb,
           gl_je_headers gjh,
           gl_je_lines gjl
     WHERE ooha.order_number = :SalesOrderNumber --Right click :RequitionNumber from Toad Enable Prompt For Substitution Variables
       AND ooha.org_id = 204
       AND hca.cust_account_id    = ooha.sold_to_org_id
       AND hp.party_id            = hca.party_id
       AND hpss.party_id            = hca.party_id
       AND hpsb.party_id            = hca.party_id
       AND bill_loc.location_id = hpss.location_id
       AND ship_loc.location_id = hpsb.location_id
       AND hcasas.cust_account_id  = hca.cust_account_id
       AND hcasab.cust_account_id  = hca.cust_account_id
       AND hcasas.party_site_id    = hpss.party_site_id
       AND hcasab.party_site_id    = hpsb.party_site_id
       AND hzsuas.cust_acct_site_id = hcasas.cust_acct_site_id
       AND hzsuab.cust_acct_site_id = hcasab.cust_acct_site_id
       AND hzsuas.site_use_id = ooha.ship_to_org_id
       AND hzsuab.site_use_id = ooha.invoice_to_org_id            
       AND wda.delivery_id        = wnd.delivery_id(+)
       AND wdd.delivery_detail_id = wda.delivery_detail_id
       AND wdd.source_header_id   = ooha.header_id
       AND wdd.source_line_id     = oola.line_id
       AND wdd.organization_id    = msib.organization_id(+)
       AND wdd.inventory_item_id  =msib.inventory_item_id(+)
       AND rct.interface_header_attribute1 = to_char(ooha.order_number)
       AND rct.org_id = ooha.org_id
       AND rctl.customer_trx_id = rct.customer_trx_id
       AND rctl.sales_order = to_char(ooha.order_number)
       AND rctld.customer_trx_id = rct.customer_trx_id
       AND rctld.customer_trx_line_id = rctl.customer_trx_line_id
       AND acr.receipt_number = 'G-1001'
       AND acr.pay_from_customer = rct.sold_to_customer_id
       AND acr.org_id = ooha.org_id
       AND acr.customer_site_use_id = rct.bill_to_site_use_id
       AND xte.transaction_number = acr.receipt_number
       AND xte.entity_code = 'RECEIPTS'
       AND xe.entity_id = xte.entity_id  
       AND xah.event_id = xe.event_id
       AND xal.ae_header_id = xah.ae_header_id
       AND xal.accounting_class_code = 'CASH'
       AND xdl.ae_header_id = xah.ae_header_id
       AND xdl.ae_line_num = xal.ae_line_num
       --and xdl.source_distribution_id_num_1
       AND gir.reference_5 = xte.entity_id  -- Entity Id
       AND gir.reference_6 = to_char(xe.event_id) --Event Id
       AND gir.reference_7 = to_char (xah.ae_header_id) -- AE Header Id
       AND gir.gl_sl_link_id = xal.gl_sl_link_id
       AND gir.created_by = 1318
       AND gjb.je_batch_id = gir.je_batch_id
       AND gjh.je_batch_id=gjb.je_batch_id
       AND gjh.je_header_id = gir.je_header_id
       AND gjl.je_header_id=gjh.je_header_id
       AND gjl.je_line_num= gir.je_line_num
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula