Query to retrieve Invoice List for a specific Customer
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
SELECT hp.party_name customer ,hca.account_number ,rcta.trx_date ,rcta.trx_number Invoice_number ,rcta.status_trx ,rcta.invoice_currency_code Invoice_currency ,rctla.line_number ,msi.segment1 Item_number ,rctla.description ,rctla.extended_amount line_amt ,rcta.org_id ,rctt.cust_trx_type_id FROM RA_CUSTOMER_TRX_ALL RCTA, RA_CUSTOMER_TRX_LINES_ALL RCTLA, RA_CUST_TRX_TYPES_ALL rctt, HZ_CUST_ACCOUNTS_ALL HCA, HZ_PARTIES HP, MTL_SYSTEM_ITEMS_B msi WHERE rcta.customer_trx_id = rctla.customer_trx_id AND msi.inventory_item_id = rctla.inventory_item_id AND msi.organization_id = 116 AND rcta.cust_trx_type_id = rctt.cust_trx_type_id AND rctt.TYPE = ‘INV’ AND rctla.line_type = ‘LINE’ AND rcta.org_id = rctla.org_id AND rcta.bill_to_customer_id = hca.cust_account_id AND hca.party_id = hp.party_id AND hp.party_name = ‘A. C. Networks’ — AND hca.account_number = ‘1581’ — AND rcta.status_trx = ‘OP’ ORDER BY hp.party_name,rcta.trx_number,rctla.line_number; |