Query to exact AR Invoice Data.
SELECT distinct hou.NAME OU_NAME,--HOU.organization_id,
(select DISTINCT NAME from ra_batch_sources_all
where BATCH_SOURCE_ID=ra.BATCH_SOURCE_ID
AND org_id=ra.ORG_ID)SOURCE,
(select TYPE from ra_cust_trx_types_all where cust_trx_type_id=ra.cust_trx_type_id and rownum=1)CLASS,
(select name from ra_cust_trx_types_all where cust_trx_type_id=ra.cust_trx_type_id and rownum=1)INVOICE_TYPE,
hp.party_name BILL_TO_CUSTOMER_NAME,
hc.ACCOUNT_NUMBER BILL_TO_ACCOUNT_NUMBER,
hps_bill.PARTY_SITE_NAME BILL_TO_PARTY_SITE_NAME,
hps_bill.PARTY_SITE_NUMBER BILL_TO_PARTY_SITE_NUMBER,
hp_ship.party_name SHIP_TO_CUSTOMER_NAME,
hc_ship.ACCOUNT_NUMBER SHIP_TO_ACCOUNT_NUMBER,
hps_ship.PARTY_SITE_NAME SHIP_TO_PARTY_SITE_NAME,
hps_ship.PARTY_SITE_NUMBER SHIP_TO_PARTY_SITE_NUMBER,
ra.TRX_NUMBER,
ra.TRX_DATE,
rct.GL_DATE,
ra.INVOICE_CURRENCY_CODE,
ra.EXCHANGE_RATE_TYPE,
ra.EXCHANGE_DATE,
ra.EXCHANGE_RATE,
(SELECT UPPER(NAME)
FROM ra_terms_tl
WHERE TERM_ID = ra.TERM_ID)PAYMENT_TERM,
'OPEN INVOICE DATA' DESCRIPTION,
'1' QUANTITY_INVOICED,
-- rl.UNIT_SELLING_PRICE,
-- rl.DESCRIPTION,
-- rl.QUANTITY_INVOICED,
-- rl.UNIT_SELLING_PRICE,
aps.AMOUNT_DUE_REMAINING AMOUNT,
(select concatenated_segments from gl_code_combinations_kfv where code_combination_id=rct.code_combination_id)REVENUE_ACCOUNT_CODE,
(select gcc.concatenated_segments
from gl_code_combinations_kfv gcc,ra_cust_trx_line_gl_dist_all rctg
where gcc.code_combination_id=rctg.code_combination_id
AND rctg.CUSTOMER_TRX_ID =ra.CUSTOMER_TRX_ID AND ACCOUNT_CLASS='REC')RECEIVABLE_ACCOUNT_CODE
FROM ra_customer_trx_all ra,
ra_customer_trx_lines_all rl,
ar_payment_schedules_all aps,
ra_cust_trx_types_all rt,
hz_cust_accounts hc,
hz_parties hp,
hz_cust_acct_sites_all hcasa_bill,
hz_cust_site_uses_all hcsua_bill,
hz_party_sites hps_bill,
hz_cust_accounts hc_ship,
hz_parties hp_ship,
hz_cust_acct_sites_all hcasa_ship,
hz_cust_site_uses_all hcsua_ship,
hz_party_sites hps_ship,
ra_cust_trx_line_gl_dist_all rct,
hr_operating_units hou
WHERE ra.customer_trx_id = rl.customer_trx_id
AND ra.customer_trx_id = aps.customer_trx_id
AND ra.org_id = aps.org_id
AND ra.org_id = hou.ORGANIZATION_ID
AND hou.ORGANIZATION_ID in ('6845','6846','6843','6844','7463','8047')
AND rct.customer_trx_id = aps.customer_trx_id
AND rct.customer_trx_id = ra.customer_trx_id
AND rct.customer_trx_id = rl.customer_trx_id
AND rct.customer_trx_line_id = rl.customer_trx_line_id
AND ra.complete_flag = 'Y'
AND rl.line_type IN ('FREIGHT', 'LINE')
AND ra.cust_trx_type_id = rt.cust_trx_type_id
---------BILL_TO---------
AND ra.bill_to_customer_id = hc.cust_account_id
AND hc.status = 'A'
AND hcasa_bill.org_id in ('6845','6846','6843','6844','7463','8047')
AND hp.party_id = hc.party_id
AND hcasa_bill.cust_account_id = ra.bill_to_customer_id
AND hcasa_bill.cust_acct_site_id = hcsua_bill.cust_acct_site_id
AND hcsua_bill.site_use_code = 'BILL_TO'
AND hcsua_bill.site_use_id = ra.bill_to_site_use_id
AND hps_bill.party_site_id = hcasa_bill.party_site_id
AND hcasa_bill.status = 'A'
AND hcsua_bill.status = 'A'
---------SHIP_TO
AND ra.SHIP_TO_CUSTOMER_ID = hc_ship.cust_account_id
AND hc_ship.status = 'A'
AND hcasa_ship.org_id in ('6845','6846','6843','6844','7463','8047')
AND hp_ship.party_id = hc_ship.party_id
AND hcasa_ship.cust_account_id = ra.SHIP_TO_CUSTOMER_ID
AND hcasa_ship.cust_acct_site_id = hcsua_ship.cust_acct_site_id
AND hcsua_ship.site_use_code = 'SHIP_TO'
AND hcsua_ship.site_use_id = ra.SHIP_TO_SITE_USE_ID
AND hps_ship.party_site_id = hcasa_ship.party_site_id
AND hcasa_ship.status = 'A'
AND hcsua_ship.status = 'A'
AND aps.amount_due_remaining <> 0
AND aps.status = 'OP'
AND rct.CREATION_DATE between '01-JAN-18' and '30-SEP-2021'
AND aps.AMOUNT_DUE_REMAINING<>0
-- AND ra.trx_number='F64TX17180000360'
=======================================================================================================================
Query to exact the Open AR Receipt Query
SELECT distinct
cust_acct.account_number,
hp.PARTY_NAME account_name,
party_site.PARTY_SITE_NUMBER,
acr.RECEIPT_NUMBER,
acr.RECEIPT_DATE,
ABS(ps.AMOUNT_DUE_REMAINING)AMOUNT_DUE_REMAINING ,
acr.AMOUNT,
null Bank_Charges,
ps.GL_DATE,
(select distinct NAME from ar_receipt_methods where RECEIPT_METHOD_ID=acr.RECEIPT_METHOD_ID)RECEIPT_METHOD,
(select distinct NAME from hr_operating_units where ORGANIZATION_ID=acr.ORG_ID)OPERATING_UNIT,
acr.CURRENCY_CODE,
DECODE (acr.TYPE,
'CASH', 'Standard',
'MISC', 'Miscellaneous',
acr.TYPE
)Type,
NULL ACTIVITY,
acr.EXCHANGE_RATE_TYPE,
acr.EXCHANGE_RATE,
acr.EXCHANGE_DATE
FROM ar_payment_schedules_all ps
, ar_receivable_applications_all arr
, hz_cust_accounts_all cust_acct
, hz_cust_acct_sites_all acct_site
, hz_party_sites party_site
, hz_parties hp
, hz_locations loc
, hz_cust_site_uses_all site_uses
, ar_cash_receipts_all acr
, ar_cash_receipt_history_all crh
, gl_code_combinations cc
WHERE ps.customer_id = cust_acct.cust_account_id
AND ps.customer_id = cust_acct.cust_account_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND ps.cash_receipt_id = acr.cash_receipt_id
AND acr.cash_receipt_id = crh.cash_receipt_id
AND crh.account_code_combination_id = cc.code_combination_id
AND ps.CLASS = 'PMT'
AND ps.cash_receipt_id = arr.cash_receipt_id
AND arr.STATUS IN ('UNAPP','UNID')
AND ps.status = 'OP'
AND site_uses.site_use_code = 'BILL_TO'
AND site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
AND NVL (site_uses.status, 'A') = 'A'
AND cust_acct.cust_account_id = acct_site.cust_account_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND party_site.party_id=hp.party_id
AND ps.customer_id = acct_site.cust_account_id
AND ps.customer_site_use_id = site_uses.site_use_id
AND ps.ORG_ID in ('6845','6846','6843','6844','7463','8047')
Query to Exact the Customer Data
SELECT DISTINCT hcsu.PRIMARY_SALESREP_ID,hp.party_name,hca.ACCOUNT_NUMBER,hps.PARTY_SITE_NUMBER,
hcas.org_id Customer_org_id,jrs.SALESREP_ID,jrs.org_id--INTO V_SALESPERSON
FROM hz_parties hp
, hz_party_sites hps
, hz_cust_accounts hca
, hz_cust_acct_sites_all hcas
, hz_cust_site_uses_all hcsu
, hz_locations hl
, JTF_RS_SALESREPS jrs
WHERE hca.party_id = hp.party_id
AND hp.party_id = hps.party_id
AND hca.cust_account_id = hcas.cust_account_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hps.party_site_id = hcas.party_site_id
AND hps.location_id = hl.location_id
AND jrs.SALESREP_ID=hcsu.PRIMARY_SALESREP_ID
-- AND hps.party_site_number ='777034'--i.bill_to_party_site_number
AND hcsu.site_use_code = 'BILL_TO'
-- AND hca.account_number ='417543'--i.bill_to_account_number
AND hcas.org_id in ('8248','8249','8250','8251')
AND hca.STATUS='A'
AND hcas.org_id<>jrs.org_id
AND hcsu.PRIMARY_SALESREP_ID>0
----------------------------------------------------------------------------------------
Other Query to exact Customer Details
SELECT hp.party_name, hca.cust_account_id, hcas.org_id, hp.party_id,
hcas.cust_acct_site_id, hps.location_id, hl.address1, hl.address2,
hl.address3, hl.address4, hl.city, hl.postal_code, hl.state, hp.PARTY_NUMBER, hps.PARTY_SITE_NUMBER, hca.ACCOUNT_NUMBER, hcsu.LOCATION
FROM hz_parties hp,
hz_party_sites hps,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu,
hz_locations hl
WHERE hp.party_id = hca.party_id
AND hca.party_id = hps.party_id
AND hps.party_site_id = hcas.party_site_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_code = 'BILL_TO'
AND hl.location_id = hps.location_id
AND hcas.status = 'A'
AND hcsu.status = 'A'
AND hp.party_name='ABC TANNERS'
and hcas.org_id='121'
--- AND hl.location_id = 89091
---AND hca.cust_account_id = 965678
Customer Master fetch Query
SELECT hp.party_name, hca.cust_account_id, hcas.org_id,
(Select NAME from HR_operating_units where ORGANIZATION_ID=hcas.org_id) OPERATINGUNIT,
hp.party_id,
hcas.cust_acct_site_id, hps.location_id, hl.address1, hl.address2,
hl.address3, hl.address4, hl.city, hl.postal_code, hl.state, hp.PARTY_NUMBER, hps.PARTY_SITE_NUMBER, hca.ACCOUNT_NUMBER, hcsu.LOCATION
FROM hz_parties hp,
hz_party_sites hps,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu,
hz_locations hl
WHERE hp.party_id = hca.party_id
AND hca.party_id = hps.party_id
AND hps.party_site_id = hcas.party_site_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_code = 'BILL_TO'
AND hl.location_id = hps.location_id
AND hcas.status = 'A'
AND hcsu.status = 'A'
-- AND hp.party_name='ABC TANNERS'
and hcas.org_id in ('8248','8249','8250','8251')
--- AND hl.location_id = 89091
---AND hca.cust_account_id = 965678
No comments:
Post a Comment