Hi Team,
I have build two query report.
Q1 qury:
SELECT hp.party_name "CUSTOMER_NAME",
hl.address1,
hl.address2,
hl.address3,
hl.city,
hl.state,
hl.postal_code,
hca.account_number "CUSTOMER_CODE",
aps.cash_receipt_id,
hcsu.LOCATION "LOCATION", -- Added by Kanchan
rct.trx_number "INVOICE_NUMBER",
--ar.receipt_number,
HOU.Name "Company Name",
rbs.NAME "SOURCE_NAME",
aps.GL_DATE "GL DATE",
rct.trx_date "INVOICE_DATE",
--SUBSTR(TRUNC(rct.trx_date),4) "INVOICE_PERIOD",
rct.term_due_date "DUE_DATE",
arc.NAME "COLLECTOR_NAME",
rctl.line_number "SR_NO",
DECODE (rctl.memo_line_id, NULL, rctt.description, decode( rctt.description, arml.description, rctt.description , rctt.description || ' ' || CHR (10) || arml.description)) "DESCRIPTION",
rctl.uom_code "UNIT_OF_MEASURE",
rctl.quantity_invoiced "QUANTITY",
rctl.unit_selling_price "UNIT_RATE",
ROUND (rctl.extended_amount) "AMOUNT",
arn.text "COMMENTS",
rct.customer_trx_id "TRX_ID",
rct.org_id "ORG_ID",
rct.INVOICE_CURRENCY_CODE,
hca.cust_account_id "CUSTOMER_ID"
FROM ra_customer_trx_all rct,
ra_cust_trx_types_all rctt,
ar_payment_schedules_all aps,
-- ar_receivable_applications_all acra,
-- ar_cash_receipts_all ar,
ra_batch_sources_all rbs,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
hz_party_sites hps,
hz_cust_accounts hca,
hr_all_organization_units HOU,
hz_parties hp,
hz_locations hl,
ra_customer_trx_lines_all rctl,
ar_memo_lines_all_tl arml,
hz_customer_profiles hcp,
ar_collectors arc,
ar_notes arn
WHERE rct.cust_trx_type_id = rctt.cust_trx_type_id
AND rctt.TYPE = 'INV'
AND rct.complete_flag = 'Y'
AND rct.batch_source_id = rbs.batch_source_id
-- AND acra.cash_receipt_id = ar.cash_receipt_id
-- and acra.applied_customer_trx_id = rct.customer_trx_id
AND rct.org_id = rbs.org_id
and hou.organization_id=rct.LEGAL_ENTITY_ID
AND rct.bill_to_customer_id = hca.cust_account_id
AND aps.customer_trx_id = rct.customer_trx_id
AND hca.party_id = hp.party_id
AND rct.bill_to_site_use_id = hcsu.site_use_id
AND rct.org_id = hcsu.org_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsu.org_id = hcas.org_id
AND hcas.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND rct.customer_trx_id = rctl.customer_trx_id
AND rct.org_id = rctt.org_id
AND rctt.org_id = rctl.org_id
AND rctl.memo_line_id = arml.memo_line_id(+)
AND rctl.line_type = 'LINE'
AND rct.bill_to_site_use_id = hcp.site_use_id(+)
AND hcp.collector_id = arc.collector_id(+)
AND rct.customer_trx_id = arn.customer_trx_id(+)
AND Nvl(arn.note_id,0) = Nvl((SELECT MAX (note_id)
FROM ar_notes arnt
WHERE arnt.customer_trx_id = rct.customer_trx_id),0)
AND rct.org_id = :p_org_id
--AND rct.batch_source_id = :p_batch_src_id
AND TRUNC(aps.gl_date) BETWEEN TRUNC(:p_trx_date_low) AND TRUNC(:p_trx_date_high)
--AND ar.receipt_number='660754'
--and hca.cust_account_id=937288
and hca.cust_account_id=5436 --291784 ----5637 --5436
-- and rct.trx_number= 15762455 --15195934 --15044822
-- AND hca.account_number ='NDDGSP035'
&LP_TRX_NO
&LP_CUST_NO
ORDER BY hca.account_number,
rct.trx_date,
rctl.line_number
and Q2 query :
select customer_id,
NVL(receipt_amount+receipt_amount2,0)Payment
from
(
SELECT distinct hca.cust_account_id "CUSTOMER_ID" ,
(SELECT DISTINCT
--e.customer_id CUST_ID,
SUM((a.amount) * NVL (a.exchange_rate, 1)) Receipt_Amount
FROM ar_cash_receipts_all a,
ar_cash_receipt_history_all b,
ar_receipt_classes d,
ar_customers e,
hz_party_sites hzps,
xx_ar_addresses_v g,
hz_cust_acct_sites_all h,
hz_cust_site_uses_all f,
hr_organization_units_v i
WHERE b.cash_receipt_id = a.cash_receipt_id
AND a.org_id = i.organization_id
AND a.receipt_method_id = d.receipt_class_id
AND e.customer_id=g.customer_id
AND hzps.party_site_id = h.party_site_id
AND h.party_site_id = g.party_site_id
AND f.site_use_code = 'BILL_TO'
AND f.cust_acct_site_id = h.cust_acct_site_id
AND g.language_description = 'American English'
AND b.first_posted_record_flag = ('Y')
--and e.customer_id=937288
and e.customer_id= hca.cust_account_id --5436 --291784 --5436
AND f.site_use_id = a.customer_site_use_id
AND E.CUSTOMER_ID = A.PAY_FROM_CUSTOMER ---- ADDED
AND TRUNC (b.gl_date) BETWEEN TRUNC(:p_trx_date_low) AND TRUNC(:p_trx_date_high)
AND a.org_id = :p_org_id) Receipt_amount,
( SELECT
distinct --e.customer_id CUST_ID, --SMAN_10OCT2013_Added distinct_Requested by Biju
SUM((-1 * a.amount) * NVL (a.exchange_rate, 1)) Receipt_AMOUNT
FROM ar_cash_receipts_all a,
ar_cash_receipt_history_all b,
ar_receipt_classes d,
xx_ar_addresses_v g,
ar_customers e,
hz_party_sites hzps,
hz_cust_acct_sites_all h,
hz_cust_site_uses_all f,
hr_organization_units_v i
WHERE b.cash_receipt_id = a.cash_receipt_id
AND a.org_id = i.organization_id
AND a.receipt_method_id = d.receipt_class_id
AND e.customer_id=g.customer_id
AND hzps.party_site_id = h.party_site_id
AND h.party_site_id = g.party_site_id
AND f.site_use_code = 'BILL_TO'
AND f.cust_acct_site_id = h.cust_acct_site_id
AND g.language_description = 'American English'
AND a.status IN ('NSF', 'REV', 'STOP')
AND a.reversal_date IS NOT NULL
AND b.status = 'REVERSED'
AND E.CUSTOMER_ID = A.PAY_FROM_CUSTOMER ---- ADDED
AND f.site_use_id = a.customer_site_use_id
--and e.customer_id=937288
AND e.customer_id= hca.cust_account_id --5436 --291784 --
AND TRUNC (b.gl_date) BETWEEN TRUNC(:p_trx_date_low) AND TRUNC(:p_trx_date_high)
AND a.org_id = :p_org_id
) receipt_amount2
FROM ra_customer_trx_all rct,
ra_cust_trx_types_all rctt,
ar_payment_schedules_all aps,
ra_batch_sources_all rbs,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
hz_party_sites hps,
hz_cust_accounts hca,
hr_all_organization_units HOU,
hz_parties hp,
hz_locations hl,
ra_customer_trx_lines_all rctl,
ar_memo_lines_all_tl arml,
hz_customer_profiles hcp,
ar_collectors arc,
ar_notes arn
WHERE rct.cust_trx_type_id = rctt.cust_trx_type_id
AND rctt.TYPE = 'INV'
AND rct.complete_flag = 'Y'
AND rct.batch_source_id = rbs.batch_source_id
AND rct.org_id = rbs.org_id
and hou.organization_id=rct.LEGAL_ENTITY_ID
AND rct.bill_to_customer_id = hca.cust_account_id
AND aps.customer_trx_id = rct.customer_trx_id
AND hca.party_id = hp.party_id
AND rct.bill_to_site_use_id = hcsu.site_use_id
AND rct.org_id = hcsu.org_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsu.org_id = hcas.org_id
AND hcas.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND rct.customer_trx_id = rctl.customer_trx_id
AND rct.org_id = rctt.org_id
AND rctt.org_id = rctl.org_id
AND rctl.memo_line_id = arml.memo_line_id(+)
AND rctl.line_type = 'LINE'
AND rct.bill_to_site_use_id = hcp.site_use_id(+)
AND hcp.collector_id = arc.collector_id(+)
--and hca.cust_account_id=5436 --291741--5436
AND rct.customer_trx_id = arn.customer_trx_id(+)
AND Nvl(arn.note_id,0) = Nvl((SELECT MAX (note_id)
FROM ar_notes arnt
WHERE arnt.customer_trx_id = rct.customer_trx_id),0)
AND rct.org_id = :p_org_id
AND TRUNC(aps.gl_date) BETWEEN TRUNC(:p_trx_date_low) AND TRUNC(:p_trx_date_high)
)
So here i am linking Q1 query with Q2 by Customer_ID data link. But after doing the same , i am getting message. Moreover, when i am running my report , my q2 query runs for all customer_id , which i already hardcoded in Q1 query.
Please help on this issue.
Regards
Sachin