[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUM(DECODE(trx_type,'R',-1*accounted_amount,accounted_amount)) amount
INTO ln_amount
FROM (SELECT 'T' trx_type
,SUM(NVL(rctld.acctd_amount,0)) accounted_amount
FROM ra_customer_trx rct
,ra_cust_trx_line_gl_dist_all rctld
,ra_cust_trx_types_all rctt
WHERE rct.customer_trx_id = rctld.customer_trx_id
AND rct.cust_trx_type_id = rctt.cust_trx_type_id
AND rct.org_id = rctt.org_id
AND rctld.latest_rec_flag = 'Y'
AND rctld.account_class = 'REC'
AND rctt.post_to_gl = 'Y'
AND rctt.type IN ('CB','INV','DM','CM','BR','DEP')
AND rctld.gl_date < TO_DATE(P_FROM_GL_DATE,'RRRR/MM/DD HH24:MI:SS')
AND rct.bill_to_customer_id = p_in_cust_account_id
AND rct.bill_to_site_use_id = p_in_site_use_id
AND rct.org_id = p_in_org_id
AND rct.invoice_currency_code = NVL2(P_CURRENCY,DECODE(P_CURRENCY,'ANY',rct.invoice_currency_code,P_CURRENCY),rct.invoice_currency_code)
AND rct.complete_flag = DECODE(P_INCOMPLETE_TRX,'N','Y', rct.complete_flag)
AND ( (P_ACCOUNTED = 'ACCOUNTED' AND rctld.posting_control_id <> -3 )
OR (P_ACCOUNTED = 'UNACCOUNTED' AND rctld.posting_control_id = -3 )
OR (P_ACCOUNTED = 'BOTH'))
UNION ALL
SELECT 'R' trx_type
,SUM(NVL(acr.amount * NVL(acr.exchange_rate,1),0)) accounted_amount
FROM ar_cash_receipts acr
,ar_cash_receipt_history_all acrh
WHERE acr.cash_receipt_id = acrh.cash_receipt_id
AND acr.org_id = acrh.org_id
AND acrh.first_posted_record_flag = 'Y'
AND acrh.gl_date < TO_DATE(P_FROM_GL_DATE,'RRRR/MM/DD HH24:MI:SS')
AND acr.pay_from_customer = p_in_cust_account_id
AND acr.customer_site_use_id = p_in_site_use_id
AND acr.org_id = p_in_org_id
AND acr.currency_code = NVL2(P_CURRENCY,DECODE(P_CURRENCY,'ANY',acr.currency_code,P_CURRENCY),acr.currency_code)
AND ( (P_ACCOUNTED = 'ACCOUNTED' AND acrh.posting_control_id <> -3 )
OR (P_ACCOUNTED = 'UNACCOUNTED' AND acrh.posting_control_id = -3 )
OR (P_ACCOUNTED = 'BOTH'))
UNION ALL
SELECT 'RE' trx_type
,SUM(NVL(acr.amount * NVL(acr.exchange_rate,1),0)) accounted_amount
FROM ar_cash_receipts acr
,ar_cash_receipt_history_all acrh
WHERE acr.cash_receipt_id = acrh.cash_receipt_id
AND acr.org_id = acrh.org_id
AND acr.reversal_date IS NOT NULL
AND acrh.current_record_flag = 'Y'
AND acrh.gl_date < TO_DATE(P_FROM_GL_DATE,'RRRR/MM/DD HH24:MI:SS')
AND acr.pay_from_customer = p_in_cust_account_id
AND acr.customer_site_use_id = p_in_site_use_id
AND acr.org_id = p_in_org_id
AND acr.currency_code = NVL2(P_CURRENCY,DECODE(P_CURRENCY,'ANY',acr.currency_code,P_CURRENCY),acr.currency_code)
AND ( (P_ACCOUNTED = 'ACCOUNTED' AND acrh.posting_control_id <> -3 )
OR (P_ACCOUNTED = 'UNACCOUNTED' AND acrh.posting_control_id = -3 )
OR (P_ACCOUNTED = 'BOTH'))
UNION ALL
SELECT 'A' trx_type
,SUM(NVL(aa.acctd_amount,0)) accounted_amount
FROM ar_adjustments aa
,ra_customer_trx_all rct
,ra_cust_trx_types_all rctt
WHERE rct.customer_trx_id = aa.customer_trx_id
AND rct.org_id = aa.org_id
AND rct.cust_trx_type_id = rctt.cust_trx_type_id
AND rct.org_id = rctt.org_id
AND aa.status = 'A' -- For approved Adjustments
AND aa.gl_date < TO_DATE(P_FROM_GL_DATE,'RRRR/MM/DD HH24:MI:SS')
AND rct.bill_to_customer_id = p_in_cust_account_id
AND rct.bill_to_site_use_id = p_in_site_use_id
AND rct.org_id = p_in_org_id
AND rctt.post_to_gl = 'Y' -- Only Postable to GL are picked
AND rctt.type IN ('CB','INV','DM','CM','BR','DEP') -- Guarantees are not picked
AND rct.invoice_currency_code = NVL2(P_CURRENCY,DECODE(P_CURRENCY,'ANY',rct.invoice_currency_code,P_CURRENCY),rct.invoice_currency_code)
AND rct.complete_flag = DECODE(P_INCOMPLETE_TRX,'N','Y', rct.complete_flag)
AND ( (P_ACCOUNTED = 'ACCOUNTED' AND aa.posting_control_id <> -3)
OR (P_ACCOUNTED = 'UNACCOUNTED' AND aa.posting_control_id = -3)
OR (P_ACCOUNTED = 'BOTH'))
UNION ALL
SELECT 'RE' trx_type
,SUM(ara.acctd_amount_applied_from) accounted_amount
FROM ar_cash_receipts acr
,ar_receivable_applications_all ara
,ar_receivables_trx_all art
WHERE acr.cash_receipt_id = ara.cash_receipt_id
AND acr.org_id = ara.org_id
AND ara.receivables_trx_id = art.receivables_trx_id
AND ara.org_id = art.org_id
AND ara.gl_date < TO_DATE(P_FROM_GL_DATE,'RRRR/MM/DD HH24:MI:SS')
AND acr.pay_from_customer = p_in_cust_account_id
AND acr.customer_site_use_id = p_in_site_use_id
AND acr.org_id = p_in_org_id
AND art.type = 'WRITEOFF'
AND acr.currency_code = NVL2(P_CURRENCY,DECODE(P_CURRENCY,'ANY',acr.currency_code,P_CURRENCY),acr.currency_code)
AND ( (P_ACCOUNTED = 'ACCOUNTED' AND ara.posting_control_id <> -3)
OR (P_ACCOUNTED = 'UNACCOUNTED' AND ara.posting_control_id = -3)
OR (P_ACCOUNTED = 'BOTH')));
SELECT REPLACE(LTRIM(hcp.phone_area_code||'-'||
hcp.phone_country_code||'-'||
hcp.phone_number,'-'),'--','-')
,hcp.primary_flag
INTO lc_cust_phone_number
,lc_primary_flag
FROM hz_contact_points hcp
WHERE hcp.status = 'A'
AND hcp.owner_table_id = p_owner_table_id
AND hcp.contact_point_type = 'PHONE'
AND hcp.phone_line_type IN ('GEN','PHONE','MOBILE')
AND hcp.primary_flag = 'Y';
SELECT REPLACE(LTRIM(hcp.phone_area_code||'-'||
hcp.phone_country_code||'-'||
hcp.phone_number,'-'),'--','-')
,hcp.primary_flag
INTO lc_cust_phone_number
,lc_primary_flag
FROM hz_contact_points hcp
WHERE hcp.contact_point_id = (SELECT MIN(hcp1.contact_point_id)
FROM hz_contact_points hcp1
WHERE hcp1.status = 'A'
AND hcp1.owner_table_id = p_owner_table_id
AND hcp1.contact_point_type = 'PHONE'
AND hcp1.phone_line_type IN ('GEN','PHONE','MOBILE'));
SELECT REPLACE(LTRIM(hcp.phone_area_code||'-'||
hcp.phone_country_code||'-'||
hcp.phone_number,'-'),'--','-')
,hcp.primary_flag
INTO lc_cust_phone_number
,lc_primary_flag
FROM hz_contact_points hcp
WHERE hcp.status = 'A'
AND hcp.owner_table_id = p_owner_table_id
AND hcp.contact_point_type = 'PHONE'
AND hcp.phone_line_type = 'FAX'
AND hcp.primary_flag = 'Y';
SELECT REPLACE(LTRIM(hcp.phone_area_code||'-'||
hcp.phone_country_code||'-'||
hcp.phone_number,'-'),'--','-')
,hcp.primary_flag
INTO lc_cust_phone_number
,lc_primary_flag
FROM hz_contact_points hcp
WHERE hcp.contact_point_id = (SELECT MIN(hcp1.contact_point_id)
FROM hz_contact_points hcp1
WHERE hcp1.status = 'A'
AND hcp1.owner_table_id = p_owner_table_id
AND hcp1.contact_point_type = 'PHONE'
AND hcp1.phone_line_type = 'FAX');