Total Pageviews

Monday, December 10, 2012


Query to Extract AR Invoice Details ...............

SELECT
     hca.cust_account_id,
     oha.HEADER_ID,
     ola.line_id,
     oha.org_id "Organization ID",
     hca.account_number "Account Number",
     hzps.party_site_number "Customer Site Number",
oha.attribute1 "Invoice Date",
     oha.attribute1 "GL Date",
     oha.order_number "Invoice Key",
( SELECT name FROM apps.ra_terms_tl
     WHERE term_id =oha.payment_term_id
     AND LANGUAGE = 'US' ) "Payment Term",
     oha.transactional_curr_code "Invoice Currency",
     'LINE ' "Line Type" ,
1 "Transaction Type",
    (SELECT name FROM apps.oe_transaction_types_tl
     WHERE transaction_type_id=oha.order_type_id
     AND LANGUAGE = 'US')  "Transaction Type",
    (ola.ordered_quantity*ola.unit_selling_price) "Total_Chg",
    ola.tax_code "Line Tax Code", --Still Open Point
    ola.tax_value "Line Tax Amount",
    msi.description "Memo Line Name",
 ola.line_id,
    ola.orig_sys_line_ref
FROM
   oe_order_headers_all oha,
   oe_order_lines_all ola,
   hz_cust_accounts_all hca,
   hz_cust_acct_sites_all hcas,
   hz_cust_site_uses_all hcsu,
   hz_parties  hzp,
   hz_party_sites hzps,
   mtl_system_items_b msi
WHERE
    oha.header_id          =  ola.header_id
AND hca.cust_account_id    =  oha.sold_to_org_id
AND hca.cust_account_id    =  hcas.cust_account_id
AND hcas.cust_acct_site_id =  hcsu.cust_acct_site_id
AND hcsu.site_use_id       =  oha.invoice_to_org_id
AND ola.inventory_item_id  =  msi.inventory_item_id
AND hcsu.org_id            =  hcas.org_id
AND hcsu.site_use_code     =  'BILL_TO'
AND hzp.party_id           =  hca.party_id
AND hzp.party_id           =  hzps.party_id
AND hzps.party_site_id     =  hcas.party_site_id
AND ola.ship_from_org_id   =  msi.organization_id
--AND 1= (SELECT 1 FROM ra_interface_lines_all WHERE interface_line_attribute1 = TO_CHAR(oha.order_number) AND ROWNUM = 1) ;
AND oha.header_id =10014
And oha.flow_status_code = 'CLOSED'

Regards
Avinash & Rahul

No comments:

Post a Comment