/*Sample script to fetch the Receipt Register along with its TDS and WCT details */
SELECT apps.gl_flexfields_pkg.get_description_sql
(cc.chart_of_accounts_id,
1,
cc.segment1
) company,
apps.gl_flexfields_pkg.get_description_sql
(cc.chart_of_accounts_id,
2,
cc.segment2
) business_unit,
apps.gl_flexfields_pkg.get_description_sql
(cc.chart_of_accounts_id,
4,
cc.segment4
) region,
apps.gl_flexfields_pkg.get_description_sql
(cc.chart_of_accounts_id,
5,
cc.segment5
) LOCATION,
NVL (acr.doc_sequence_value, '0') document_no,
acr.receipt_date, acr.receipt_number receipt_no,
NVL (acr.customer_receipt_reference, ' ') chq_num,
acr.postmark_date chq_date,
rat.TRX_NUMBER invoice_no,
rat.TRX_DATE invoice_date,
rc.customer_number customer_code,
rc.customer_name customer_name,
remit_bank_branch.bank_name remittance_bank_name,
acr.amount receipt_amount, acr.cash_receipt_id,
acr.currency_code,
DECODE
(acr.TYPE,
'MISC', NULL,
NVL (SUM (DECODE (arr.status,
'APP', NVL (arr.amount_applied, 0),
0
)
),
0
)
) applied_amount,
DECODE
(acr.TYPE,
'MISC', NULL,
NVL (SUM (DECODE (arr.status,
'UNAPP', NVL (arr.amount_applied, 0),
0
)
),
0
)
) unnapplied_amount,
DECODE
(acr.TYPE,
'MISC', NULL,
NVL (SUM (DECODE (arr.status,
'ACC', NVL (arr.amount_applied, 0),
0
)
),
0
)
) on_account_amount,
DECODE
(acr.TYPE,
'MISC', NULL,
NVL (SUM (DECODE (arr.status,
'UNID', NVL (arr.amount_applied, 0),
0
)
),
0
)
) unidentified_amount,
SUM (arr.attribute1) "TDS",
SUM (arr.attribute2) "WCT",
arr.attribute3 "OTHER",
rc.customer_id, rat.customer_trx_id
FROM apps.hz_cust_accounts_all cust_acct,
apps.ar_payment_schedules_all ps,
apps.ar_receivable_applications_all arr,
apps.hz_cust_acct_sites_all acct_site,
apps.hz_party_sites party_site,
apps.hz_locations loc,
apps.hz_cust_site_uses_all site_uses,
apps.ar_cash_receipts_all acr,
apps.ar_cash_receipt_history_all crh,
apps.gl_code_combinations cc,
apps.ra_customer_trx_all rat,
apps.ra_customers rc,
apps.ap_bank_accounts_all remit_bank,
apps.ap_bank_branches remit_bank_branch
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 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 ps.customer_id = acct_site.cust_account_id
AND ps.customer_site_use_id = site_uses.site_use_id
--AND arr.applied_customer_trx_id = rat.customer_trx_id(+)
AND rat.customer_trx_id(+) = ps.customer_trx_id
AND acr.pay_from_customer = rc.customer_id
AND remit_bank.bank_account_id(+) =
acr.remittance_bank_account_id
AND remit_bank.bank_branch_id = remit_bank_branch.bank_branch_id(+)
AND crh.cash_receipt_history_id = arr.cash_receipt_history_id
-- AND crh.first_posted_record_flag = 'Y'
AND NVL (acr.confirmed_flag, 'Y') = 'Y'
AND NVL (arr.confirmed_flag, 'Y') = 'Y'
AND TRUNC (acr.receipt_date)
BETWEEN TO_DATE (NVL (p_from_date,
TO_CHAR (SYSDATE,
'RRRR/MM/DD hh24:mi:ss'
)
),
'RRRR/MM/DD hh24:mi:ss'
)
AND TO_DATE (NVL (p_to_date,
TO_CHAR (SYSDATE,
'RRRR/MM/DD hh24:mi:ss'
)
),
'RRRR/MM/DD hh24:mi:ss'
)
AND rc.customer_id = NVL (p_customer, rc.customer_id)
GROUP BY acr.TYPE,
apps.gl_flexfields_pkg.get_description_sql
(cc.chart_of_accounts_id,
1,
cc.segment1
),
apps.gl_flexfields_pkg.get_description_sql
(cc.chart_of_accounts_id,
2,
cc.segment2
),
apps.gl_flexfields_pkg.get_description_sql
(cc.chart_of_accounts_id,
4,
cc.segment4
),
apps.gl_flexfields_pkg.get_description_sql
(cc.chart_of_accounts_id,
5,
cc.segment5
),
NVL (acr.doc_sequence_value, '0'),
acr.receipt_date,
acr.receipt_number,
NVL (acr.customer_receipt_reference, ' '),
acr.postmark_date,
acr.cash_receipt_id,
rat.trx_date,
rat.ct_reference,
acr.amount, --vat.tax_code,
acr.cash_receipt_id,
acr.currency_code,
rc.customer_number,
rc.customer_name,
remit_bank_branch.bank_name,
rat.created_by,
rc.customer_id,
rat.customer_trx_id,
arr.attribute3
ORDER BY acr.receipt_number;
By
Deepak J
No comments:
Post a Comment