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