[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MIN(start_date) , MAX(end_date)
INTO vl_fy_begin_date , vl_fy_end_date
FROM gl_period_statuses
WHERE period_year = p_fiscal_year
AND set_of_books_id = P_SET_OF_BOOKS_ID
AND application_id = '101';
SELECT MAX (end_date)
INTO vg_end_date
FROM gl_period_statuses
WHERE period_year = P_FISCAL_YEAR
AND set_of_books_id = vp_sob_id
AND application_id = '101'
AND quarter_num = P_QUARTER;
DELETE FROM fv_receivables_activity_temp;
SELECT rct.customer_trx_id,
rct.related_customer_trx_id,
rct.trx_date,
aps.amount_due_original,
aps.amount_due_remaining,
aps.actual_date_closed,
aps.due_date,
aps.class,
aps.payment_schedule_id,
interface_header_attribute3 created_from,
hzp1.category_code customer_category_code
--fvis.status
FROM RA_CUSTOMER_TRX_ALL rct,
RA_CUST_TRX_LINE_GL_DIST_ALL rctlgd,
AR_PAYMENT_SCHEDULES_ALL aps,
--RA_CUSTOMERS rc, Bug#4476059 Quick Change
hz_cust_accounts hzca1,
hz_parties hzp1,
FV_RECEIVABLE_TYPES_ALL frt,
FV_REC_CUST_TRX_TYPES_ALL fctt,
GL_CODE_COMBINATIONS glc
--FV_INVOICE_STATUSES_ALL fvis
WHERE rct.customer_trx_id = rctlgd.customer_trx_id
AND rct.trx_date <= vg_end_date
AND rctlgd.account_class = 'REC'
AND rctlgd.set_of_books_id = vp_sob_id
AND aps.customer_trx_id = rct.customer_trx_id
AND rct.bill_to_customer_id = hzca1.cust_account_id
AND hzca1.party_id = hzp1.party_id
AND rctlgd.code_combination_id = glc.code_combination_id
AND hzca1.customer_class_code = vp_nonfed_customer_class
AND aps.class = 'INV'
AND frt.receivable_type_id = fctt.receivable_type_id
AND frt.receivable_type = vp_type_of_receivable
AND fctt.cust_trx_type_id = rct.cust_trx_type_id
AND frt.SET_OF_BOOKS_ID = vp_sob_id -- Bug 4655467
--AND frt.org_id = vp_org_id -- Bug 4655467
AND rct.SET_OF_BOOKS_ID = vp_sob_id -- Bug 4655467
--AND rct.org_id = vp_org_id -- Bug 4655467
--added to main query for delinquent debt 180 days or less
--AND aps.customer_trx_id = fvis.customer_trx_id (+)
---AND vg_end_date BETWEEN NVL(fvis.start_date, vg_end_date)
--added to main query for delinquent debt 180 daysorless
--AND NVL(fvis.end_date, vg_end_date)
UNION
SELECT rct2.customer_trx_id,
rct2.related_customer_trx_id,
rct2.trx_date,
aps2.amount_due_original,
aps2.amount_due_remaining,
aps2.actual_date_closed,
aps2.due_date,
aps2.class,
aps2.payment_schedule_id,
rct2.interface_header_attribute3 created_from,
hzp2.category_code customer_category_code
-- fvis.status
FROM RA_CUSTOMER_TRX_ALL rct2,
AR_PAYMENT_SCHEDULES_ALL aps2,
--RA_CUSTOMERS rc2 Bug#4476059 Quick Change
hz_cust_accounts hzca2,
hz_parties hzp2
-- FV_INVOICE_STATUSES_ALL fvis
where aps2.class in ('DM','CM')
and hzca2.cust_account_id = rct2.bill_to_customer_id
and hzca2.party_id = hzp2.party_id
and rct2.customer_trx_id = aps2.customer_trx_id
and rct2.SET_OF_BOOKS_ID = vp_sob_id -- Bug 4655467
--and rct2.org_id = vp_org_id -- Bug 4655467
--added to main query for delinquent debt 180 days or less
--and aps2.customer_trx_id = fvis.customer_trx_id (+)
and rct2.related_customer_trx_id in
(Select rct3.customer_trx_id
from RA_CUSTOMER_TRX_ALL rct3,
RA_CUST_TRX_LINE_GL_DIST_ALL rctlgd3,
--RA_CUSTOMERS rc3,-- Bug#4476059 Quick Change
hz_cust_accounts hzca3,
hz_parties hzp3,
AR_PAYMENT_SCHEDULES_ALL aps3,
FV_RECEIVABLE_TYPES_ALL frt3,
FV_REC_CUST_TRX_TYPES_ALL fctt3,
-- FV_FUND_PARAMETERS fp3, -- Bug 4655467
-- FV_TREASURY_SYMBOLS fts3, -- Bug 4655467
GL_CODE_COMBINATIONS glc3
where rct3.customer_trx_id = rctlgd3.customer_trx_id
and rct3.trx_date <= vg_end_date
and rctlgd3.account_class ='REC'
and rctlgd3.set_of_books_id = vp_sob_id
and frt3.SET_OF_BOOKS_ID = vp_sob_id -- Bug 4655467
-- and frt3.org_id = vp_org_id -- Bug 4655467
and rct3.SET_OF_BOOKS_ID = vp_sob_id -- Bug 4655467
-- and rct3.org_id = vp_org_id -- Bug 4655467
and aps3.customer_trx_id = rct3.customer_trx_id
and rct3.bill_to_customer_id = hzca3.cust_account_id
and hzca3.party_id = hzp3.party_id
and rctlgd3.code_combination_id = glc3.code_combination_id
and hzca3.customer_class_code = vp_nonfed_customer_class
and aps3.class = 'INV'
and frt3.receivable_type = vp_type_of_receivable
and frt3.receivable_type_id = fctt3.receivable_type_id
and fctt3.cust_trx_type_id = rct3.cust_trx_type_id);
SELECT status
FROM ra_customer_trx_all rct , fv_invoice_statuses_all fvis
WHERE rct.customer_trx_id = P_customer_trx_id
AND fvis.customer_trx_id = rct.customer_trx_id
AND vg_end_date BETWEEN NVL(fvis.start_date,vg_end_date)
AND NVL(fvis.end_date,vg_end_date);
SELECT nvl(min(due_date),recs.due_date)
INTO l_dm_due_date
FROM ar_payment_schedules_all a
WHERE a.customer_trx_id = recs.related_customer_trx_id
AND amount_due_remaining > 0;
SELECT MIN(payment_schedule_id)
INTO l_pay_schedule_id
FROM ar_payment_schedules_all a
WHERE a.customer_trx_id = recs.customer_trx_id;
SELECT related_customer_trx_id
INTO l_customer_trx_id
FROM ra_customer_trx_all rct
WHERE rct.customer_trx_id = recs.customer_trx_id
AND rct.SET_OF_BOOKS_ID = vp_sob_id; -- Bug 4655467
SELECT count(*)
INTO l_count
FROM fv_invoice_statuses_all fvis
WHERE fvis.customer_trx_id = recs.customer_trx_id
AND fvis.status = 'CROSS';
SELECT min(payment_schedule_id)
INTO l_pay_schedule_id
FROM ar_payment_schedules_all
WHERE customer_trx_id = recs.customer_trx_id
AND amount_due_remaining > 0;
insert_row('1B01', 'Section B' ,NULL ,NULL) ;
insert_row('1B02', 'Delinquent Debt by Age' ,NULL ,NULL) ;
insert_row('1B03','LINE' ,NULL ,NULL) ;
insert_row('1B1', '(1) Total Delinquencies',NULL ,NULL);
insert_row('1B1A', ' (A) 1-90 Days', num_delinq_1A , amt_delinq_1A);
insert_row('1B1B', ' (B) 91-180 Days', num_delinq_1B , amt_delinq_1B);
insert_row('1B1C', ' (C) 181-365 Days', num_delinq_1C , amt_delinq_1C);
insert_row('1B1D', ' (D) 1-2 Years', num_delinq_1D , amt_delinq_1D);
insert_row('1B1E', ' (E) 2-6 Years', num_delinq_1E , amt_delinq_1E);
insert_row('1B1F', ' (F) 6-10 Years', num_delinq_1F , amt_delinq_1F);
insert_row('1B1G', ' (G) Over 10 Years',num_delinq_1G , amt_delinq_1G);
insert_row('1B2', '(2) Commercial', num_delinq_commercial,
amt_delinq_commercial);
insert_row('1B3', '(3) Consumer', num_delinq_consumer,
amt_delinq_consumer);
insert_row('1B4', '(4) Foreign/Sovereign Debt' , num_delinq_forgn_sovrn ,
amt_delinq_forgn_sovrn);
insert_row('1B411', '' ,NULL ,NULL);
insert_row('1B412', '' ,NULL ,NULL);
insert_row('1B413', '' ,NULL ,NULL);
insert_row('1B414', '' ,NULL ,NULL);
insert_row('1B415', '' ,NULL ,NULL);
insert_row('1B416', '' ,NULL ,NULL);
insert_row('1B417', '' ,NULL ,NULL);
insert_row('1B418', '' ,NULL ,NULL);
insert_row('1B419', '' ,NULL ,NULL);
insert_row('1B420', '' ,NULL ,NULL);
insert_row('1B421', '' ,NULL ,NULL);
insert_row('2011','LINE' ,NULL ,NULL) ;
insert_row('2012','Part II - Debt Management Tool and Technique',NULL,NULL);
insert_row('2013',' Performance Data ' ,NULL ,NULL) ;
insert_row('2014','LINE' ,NULL ,NULL) ;
insert_row('2A01','Section A' ,NULL ,NULL) ;
insert_row('2A02','Delinquent Debt 180 Days or Less',NULL ,NULL);
insert_row('2A03','LINE' ,NULL ,NULL) ;
insert_row('2A1', '(1) Total Delinquencies 1 - 180 Days',num_delinq_2A_tot ,
amt_delinq_2A_tot ) ;
insert_row('2A1A',' (A) In Bankruptcy', num_tot_delinq_bankruptcy ,
amt_tot_delinq_bankruptcy);
insert_row('2A1B',' (B) In Forbearance or In Formal Appeals Process',
num_tot_delinq_forbearance ,amt_tot_delinq_forbearance);
insert_row('2A1C',' (C) In Foreclosure',
num_tot_delinq_foreclosure ,amt_tot_delinq_foreclosure);
insert_row('2A1D',' (D) At Private Collection Agencies',
num_tot_delinq_collection ,amt_tot_delinq_collection);
insert_row('2A1E',' (E) In Litigation',
num_tot_delinq_litigation ,amt_tot_delinq_litigation);
insert_row('2A1F',' (F) Eligible for Internal Offset',
num_tot_delinq_internal_offset ,amt_tot_delinq_internal_offset);
insert_row('2A1G',' (G) In Wage Garnishment',
num_tot_delinq_garnishment ,amt_tot_delinq_garnishment);
insert_row('2A1H',' (H) At Treasury for Cross Servicing',
num_tot_delinq_cross ,amt_tot_delinq_cross);
insert_row('2A1I',' (I) At Treasury for Offset',
num_tot_delinq_treasury_offset ,amt_tot_delinq_treasury_offset);
insert_row('2A1J',' (J) At Agency',
num_tot_delinq_agency ,amt_tot_delinq_agency);
insert_row('2A1K',' (K) Other - must footnote',
num_tot_delinq_other ,amt_tot_delinq_other);
insert_row('2B01','LINE' ,NULL ,NULL) ;
insert_row('2B02','Section B' ,NULL ,NULL) ;
insert_row('2B03','Debt Eligible for Referral to Treasury for' ,NULL ,NULL) ;
insert_row('2B04','Offset and Cross-Servicing' ,NULL ,NULL) ;
insert_row('2B05','LINE' ,NULL ,NULL) ;
insert_row('2B11', '(1) Debt Eligible for Referral to Treasury' ,NULL ,NULL) ;
insert_row('2B12', ' for Offset' ,NULL ,NULL) ;
insert_row('2B1A', ' (A) Delinquent Debt Over 180 Days to 10 Years' ,
num_debt_eligible_180_10 ,amt_debt_eligible_180_10) ;
insert_row('2B1B', ' (B) In Bankruptcy (-)' ,
num_debt_eligible_bankruptcy ,amt_debt_eligible_bankruptcy) ;
insert_row('2B1C', ' (C) Foreign/Sovereign Debt (-)' ,
num_debt_eligible_foreign ,amt_debt_eligible_foreign) ;
insert_row('2B1D', ' (D) In Forbearance or Formal Appeals Process(-)' ,
num_debt_eligible_forbearance ,amt_debt_eligible_forbearance) ;
insert_row('2B1E', ' (E) In Foreclosure (-)' ,
num_debt_eligible_foreclosure ,amt_debt_eligible_foreclosure) ;
insert_row('2B1F', ' (F) Other - must footnote (+ or -)' ,
num_debt_eligible_other ,amt_debt_eligible_other) ;
insert_row('2B1G1',' (G) Debt Eligible for Referral to Treasury' ,
num_debt_eligible_offset ,amt_debt_eligible_offset) ;
insert_row('2B1G2',' for Offset' ,NULL, NULL) ;
insert_row('2B21', '(2) Debt Eligible for Referral to Treasury or a ' ,
NULL ,NULL) ;
insert_row('2B22', ' Designated Debt Collection Center for' ,NULL ,NULL) ;
insert_row('2B23', ' Cross-Servicing' ,NULL ,NULL) ;
insert_row('2B2A1',' (A) Debt Eligible for Referral to Treasury' ,
num_debt_eligible_offset ,amt_debt_eligible_offset) ;
insert_row('2B2A2',' for Offset' ,NULL,NULL) ;
insert_row('2B2B', ' (B) At PCAs (-)' ,num_debt_eligible_collection ,
amt_debt_eligible_collection) ;
insert_row('2B2C', ' (C) In Litigation(-)' ,num_debt_eligible_litigation ,
amt_debt_eligible_litigation) ;
insert_row('2B2D', ' (D) Eligible for Internal Offset (-)' ,
num_debt_eligible_int_offset ,amt_debt_eligible_int_offset) ;
insert_row('2B2E', ' (E) Other - must footnote (+ or -)' ,
num_debt_eligible_other ,amt_debt_eligible_other) ;
insert_row('2B2F1',' (F) Debt Eligible for Referral to Treasury or a ' ,
num_debt_eligible_X_servicing ,amt_debt_eligible_X_servicing) ;
insert_row('2B2F2',' Designated Debt Collection Center for' ,NULL ,NULL) ;
insert_row('2B2F3',' Cross-Servicing' ,NULL ,NULL) ;
SELECT rct.customer_trx_id,
rct.related_customer_trx_id,
rct.trx_date,
aps.amount_due_original,
aps.amount_due_remaining,
aps.actual_date_closed,
aps.due_date,
aps.class,
aps.payment_schedule_id,
rct.interface_header_attribute3 created_from,
hzp1.category_code customer_category_code
FROM RA_CUSTOMER_TRX_ALL rct,
RA_CUST_TRX_LINE_GL_DIST_ALL rctlgd,
AR_PAYMENT_SCHEDULES_ALL aps,
--RA_CUSTOMERS rc, --Bug#4476059 Quick Change
hz_cust_accounts hzca1,
hz_parties hzp1,
FV_RECEIVABLE_TYPES_ALL frt,
FV_REC_CUST_TRX_TYPES_ALL fctt,
-- FV_FUND_PARAMETERS fp, -- Bug 4655467
GL_CODE_COMBINATIONS glc
-- FV_TREASURY_SYMBOLS fts -- Bug 4655467
WHERE rct.customer_trx_id = rctlgd.customer_trx_id
AND rct.trx_date <= vg_end_date
AND rctlgd.account_class ='REC'
AND rctlgd.set_of_books_id = vp_sob_id
AND aps.customer_trx_id = rct.customer_trx_id
AND rct.bill_to_customer_id = hzca1.cust_account_id
AND hzca1.party_id = hzp1.party_id
AND rctlgd.code_combination_id = glc.code_combination_id
AND hzca1.customer_class_code = vp_nonfed_customer_class
AND aps.class = 'INV'
AND frt.receivable_type_id = fctt.receivable_type_id
AND frt.receivable_type = vp_type_of_receivable
AND fctt.cust_trx_type_id = rct.cust_trx_type_id
AND frt.SET_OF_BOOKS_ID = vp_sob_id -- Bug 4655467
--AND frt.org_id = vp_org_id -- Bug 4655467
AND rct.SET_OF_BOOKS_ID = vp_sob_id -- Bug 4655467
--AND rct.org_id = vp_org_id -- Bug 4655467
UNION
SELECT rct2.customer_trx_id,
rct2.related_customer_trx_id,
rct2.trx_date,
aps2.amount_due_original,
aps2.amount_due_remaining,
aps2.actual_date_closed,
aps2.due_date,
aps2.class,
aps2.payment_schedule_id,
rct2.interface_header_attribute3 created_from,
hzp2.category_code customer_category_code
from RA_CUSTOMER_TRX_ALL rct2,
ar_payment_schedules_all aps2,
--ra_customers rc2 --Bug#4476059 Quick Change
hz_cust_accounts hzca2,
hz_parties hzp2
where aps2.class in ('DM','CM')
and hzca2.cust_account_id = rct2.bill_to_customer_id
AND hzca2.party_id = hzp2.party_id
and rct2.customer_trx_id = aps2.customer_trx_id
and rct2.SET_OF_BOOKS_ID = vp_sob_id -- Bug 4655467
-- and rct2.org_id = vp_org_id -- Bug 4655467
and rct2.related_customer_trx_id
IN
(Select rct3.customer_trx_id
from RA_CUSTOMER_TRX_ALL rct3,
RA_CUST_TRX_LINE_GL_DIST_ALL rctlgd3,
--RA_CUSTOMERS rc3, --Bug#4476059 Quick Change
hz_cust_accounts hzca3,
hz_parties hzp3,
AR_PAYMENT_SCHEDULES_ALL aps3,
FV_RECEIVABLE_TYPES_ALL frt3,
FV_REC_CUST_TRX_TYPES_ALL fctt3,
-- FV_FUND_PARAMETERS fp3, -- Bug 4655467
-- FV_TREASURY_SYMBOLS fts3, -- Bug 4655467
GL_CODE_COMBINATIONS glc3
where rct3.customer_trx_id = rctlgd3.customer_trx_id
and rct3.trx_date <= vg_end_date
and rctlgd3.account_class ='REC'
and rctlgd3.set_of_books_id = vp_sob_id
and frt3.SET_OF_BOOKS_ID = vp_sob_id -- Bug 4655467
-- and frt3.org_id = vp_org_id -- Bug 4655467
and rct3.SET_OF_BOOKS_ID = vp_sob_id -- Bug 4655467
-- and rct3.org_id = vp_org_id -- Bug 4655467
and aps3.customer_trx_id = rct3.customer_trx_id
and rct3.bill_to_customer_id = hzca3.cust_account_id
and hzca3.party_id = hzp3.party_id
and rctlgd3.code_combination_id = glc3.code_combination_id
and hzca3.customer_class_code = vp_nonfed_customer_class
and aps3.class = 'INV'
and frt3.receivable_type = vp_type_of_receivable
and frt3.receivable_type_id = fctt3.receivable_type_id
and fctt3.cust_trx_type_id = rct3.cust_trx_type_id);
SELECT decode(fvs.factsi_customer_attribute, 'ATTRIBUTE1', ara.attribute1,
'ATTRIBUTE2', ara.attribute2, 'ATTRIBUTE3', ara.attribute3,
'ATTRIBUTE4', ara.attribute4, 'ATTRIBUTE5', ara.attribute5,
'ATTRIBUTE6', ara.attribute6, 'ATTRIBUTE7', ara.attribute7,
'ATTRIBUTE8', ara.attribute8, 'ATTRIBUTE8', ara.attribute8,
'ATTRIBUTE9', ara.attribute9, 'ATTRIBUTE10', ara.attribute10,
'ATTRIBUTE11', ara.attribute11, 'ATTRIBUTE12', ara.attribute12,
'ATTRIBUTE13', ara.attribute13, 'ATTRIBUTE14', ara.attribute14,
'ATTRIBUTE15', ara.attribute15) attribute15,
ara.amount,
ara.receivables_trx_id,
ara.payment_schedule_id,
rct.interface_header_attribute3 created_from,
ara.customer_trx_id,
hzp1.category_code customer_category_code,
ara.type,
rctt.type trx_type
FROM ar_adjustments_all ara,
ra_customer_trx_all rct,
--ra_customers rc, --Bug#4476059 Quick Change
hz_cust_accounts hzca1,
hz_parties hzp1,
ra_cust_trx_types_all rctt,
fv_system_parameters fvs
WHERE rct.customer_trx_id = ara.customer_trx_id
AND hzca1.cust_account_id = rct.bill_to_customer_id
AND hzca1.party_id = hzp1.party_id
AND rct.cust_trx_type_id = rctt.cust_trx_type_id
AND ara.apply_date >= vl_fy_begin_date
AND ara.apply_date < vg_end_date+1
AND ara.gl_date >= vl_fy_begin_date
AND ara.gl_date < vg_end_date+1
AND ara.payment_schedule_id = p_payment_schedule_id
AND ara.customer_trx_id = p_customer_trx_id
AND rct.SET_OF_BOOKS_ID = vp_sob_id; -- Bug 4655467
SELECT ara.amount_applied,
ara.APPLIED_CUSTOMER_TRX_ID ,
ara.receivables_trx_id,
hzp1.category_code customer_category_code,
ara.applied_payment_schedule_id,
acr.receipt_number,
acr.cash_receipt_id,
acr.amount
from ar_receivable_applications_all ara,
ar_cash_receipts_all acr,
--ra_customers rc, ----Bug#4476059 Quick Change
hz_cust_accounts hzca1,
hz_parties hzp1,
ra_customer_trx_all rct
where ara.cash_receipt_id = acr.cash_receipt_id
and ara.status = 'APP'
and ara.apply_date between vl_fy_begin_date and vg_end_date+1
and rct.customer_trx_id = p_customer_trx_id
and hzca1.cust_account_id = rct.bill_to_customer_id
AND hzca1.party_id = hzp1.party_id
AND ara.APPLIED_CUSTOMER_TRX_ID = p_customer_trx_id
AND ara.applied_payment_schedule_id = p_payment_schedule_id ;
SELECT receipt_desc_type ,NVL(ficr.amount,0) amount
FROM fv_interim_cash_receipts_all ficr,
ar_cash_receipts_all acr,
ar_cash_receipt_history_all acrh
WHERE ficr.receipt_number = p_receipt_number
AND ficr.customer_trx_id = p_customer_trx_id
AND ficr.set_of_books_id = vp_sob_id
AND ficr.batch_id = acrh.batch_id
AND acrh.cash_receipt_id = acr.cash_receipt_id
AND acrh.current_record_flag = 'Y'
AND acr.cash_receipt_id = p_cash_receipt_id ;
select ara.amount_applied,
ara.applied_customer_trx_id,
ara.applied_payment_schedule_id,
acr.receipt_number,
ara.cash_receipt_id,
aps.class
from ar_receivable_applications_all ara,
ar_cash_receipts_all acr,
ar_payment_schedules_all aps
where ara.cash_receipt_id = acr.cash_receipt_id
and acr.set_of_books_id = vp_sob_id
and nvl(ara.days_late,0) >= 0
and trunc(ara.apply_date) > trunc(aps.due_date)
and aps.customer_trx_id = ara.applied_customer_trx_id
and aps.payment_schedule_id = ara.applied_payment_schedule_id
and ara.applied_customer_trx_id = p_customer_trx_id
and ara.applied_payment_schedule_id = p_payment_schedule_id ;
select nvl(sum(amount),0)
into l_adj_amount
from ar_adjustments_all
where customer_trx_id = recs.customer_trx_id
and apply_date < vl_fy_begin_date + 1;
select nvl(sum(amount_applied),0) * -1
into l_appld_amount
from ar_receivable_applications_all
where applied_customer_trx_id = recs.customer_trx_id
and apply_date < vl_fy_begin_date + 1;
SELECT MIN(payment_schedule_id)
INTO l_pay_schedule_id
FROM ar_payment_schedules_all a
WHERE a.customer_trx_id = recs.customer_trx_id
AND actual_date_closed > vl_fy_begin_date;
SELECT min(payment_schedule_id)
INTO l_pay_schedule_id
FROM ar_payment_schedules_all a
WHERE a.customer_trx_id = recs.customer_trx_id;
SELECT DISTINCT 'x' INTO dummy
FROM fv_finance_charge_controls_all
WHERE set_of_books_id = vp_sob_id
AND charge_type = recs.created_from ;
SELECT DISTINCT 'x' INTO dummy
FROM fv_finance_charge_controls_all
WHERE set_of_books_id = vp_sob_id
AND charge_type = recs.created_from
AND category<> 'A';
SELECT distinct 'Y' into fc_flag
from fv_finance_charge_controls_all
where charge_type = adj_recs.created_from;
SELECT status_trx
INTO l_status
FROM ra_customer_trx_all
WHERE customer_trx_id = adj_recs.customer_trx_id;
select distinct 'Y' into fc_flag
from fv_finance_charge_controls_all
where set_of_books_id = vp_sob_id
AND charge_type = recs.created_from
AND category <> 'A';
select min(applied_payment_schedule_id)
into schedule_id
FROM ar_receivable_applications_all
where applied_customer_trx_id =IIC_col_recs.applied_customer_trx_id
and cash_receipt_id = IIC_col_recs.cash_receipt_id;
insert_row('101', 'LINE' ,NULL ,NULL) ;
insert_row('102', 'Part I - Status of Receivables' ,NULL ,NULL) ;
insert_row('103', 'LINE' ,NULL ,NULL) ;
insert_row('1A01','Section A' ,NULL ,NULL) ;
insert_row('1A02','Receivables and Collections' ,NULL ,NULL) ;
insert_row('1A03','LINE' ,NULL ,NULL) ;
insert_row('1A1', '(1) Beginning FY Balance',
num_fy_begin_bal ,amt_fy_begin_bal ) ;
insert_row('1A2', '(2) New Receivables (+)',
num_fy_new_rec ,amt_fy_new_rec ) ;
insert_row('1A3', '(3) Accruals (+)', '', amt_adj_accrual + amt_accruals) ;
insert_row('1A4', '(4) Collections on Receivables (-)', '' ,
amt_col_at_agency +
amt_col_third_party +
amt_col_asset_sales +
amt_col_others ) ;
insert_row('1A4A',' (A) At Agency', '' ,amt_col_at_agency ) ;
insert_row('1A4B',' (B) At Third Party', '' ,amt_col_third_party ) ;
insert_row('1A4C',' (C) Asset Sales', '' ,amt_col_asset_sales ) ;
insert_row('1A4D',' (D) Other - must footnote', '' ,amt_col_others ) ;
insert_row('1A5', '(5) Adjustments',
num_adj ,amt_adj_reclassified + amt_adj_sales_assets + amt_adj_consolidation) ;
insert_row('1A5A',' (A) Reclassified/Adjusted Amounts (+ or -)',
'', amt_adj_reclassified) ;
insert_row('1A5B',' (B) Adjustments Due to Sale of Assets (+ or -)',
'', amt_adj_sales_assets) ;
insert_row('1A5C',' (C) Consolidations (+ or -)',
'', amt_adj_consolidation) ;
insert_row('1A6', '(6) Amounts Written-Off (-)',
num_write_off_A + num_write_off_B , amt_write_off_A + amt_write_off_B ) ;
insert_row('1A6A',' (A) Currently Not Collectible',
num_write_off_A , amt_write_off_A ) ;
insert_row('1A6B',' (B) Written-Off and Closed Out',
num_write_off_B , amt_write_off_B ) ;
insert_row('1A70', '(7) Ending Balance',
num_fy_begin_bal + num_fy_new_rec - num_write_off_A - num_write_off_B,
amt_fy_begin_bal + amt_fy_new_rec + amt_accruals
+amt_col_at_agency + amt_col_third_party + amt_col_asset_sales + amt_col_others
+ amt_adj_reclassified + amt_adj_sales_assets + amt_adj_consolidation
+ amt_write_off_A + amt_write_off_A ) ;
insert_row('1A71','LINE' ,NULL ,NULL) ;
insert_row('1A7A',' (A) Foreign/Sovereign', '' , '' ) ;
insert_row('1A7B',' (B) State and Local Government', '' , '' ) ;
insert_row('1A7C','LINE' ,NULL ,NULL) ;
insert_row('1A8', '(8) Rescheduled Debt','' ,'' ) ;
insert_row('1A8A',' (A) Delinquent','' ,'') ;
insert_row('1A8B',' (B) Non-Delinquent','' ,'') ;
insert_row('1A91', '(9) Interest' || ' & ' || 'Late Charges',
'', amt_fin_accruals_int + amt_fin_adj_accruals_int ) ;
insert_row('1A92','LINE' ,NULL ,NULL) ;
insert_row('2C01', 'LINE' ,NULL ,NULL) ;
insert_row('2C02', 'Section C' ,NULL ,NULL) ;
insert_row('2C03', 'Collections' ,NULL ,NULL) ;
insert_row('2C04', 'LINE' ,NULL ,NULL) ;
insert_row('2C1', '(1) Collections on Delinquent Debt',
num_SECC_1A + num_SECC_1B + num_SECC_1C + num_SECC_1D +
num_SECC_1E + num_SECC_1F + num_SECC_1G + num_SECC_1H +
num_SECC_1I + num_SECC_1J ,
amt_SECC_1A + amt_SECC_1B + amt_SECC_1C + amt_SECC_1D +
amt_SECC_1E + amt_SECC_1F + amt_SECC_1G + amt_SECC_1H +
amt_SECC_1I + amt_SECC_1J ) ;
insert_row('2C1A', ' (A) By Private Collection Agencies',
num_SECC_1A ,amt_SECC_1A ) ;
insert_row('2C1B', ' (B) By Litigation',
num_SECC_1B ,amt_SECC_1B ) ;
insert_row('2C1C', ' (C) By Internal Offset',
num_SECC_1C , amt_SECC_1C) ;
insert_row('2C1D', ' (D) By Third Party',
num_SECC_1D ,amt_SECC_1D ) ;
insert_row('2C1E', ' (E) By Asset Sales',
num_SECC_1E ,amt_SECC_1E ) ;
insert_row('2C1F', ' (F) By Wage Garnishment',
num_SECC_1F ,amt_SECC_1F);
insert_row('2C1G1',' (G) By Treasury or a Designated Debt Collection',
num_SECC_1G ,amt_SECC_1G ) ;
insert_row('2C1G2',' Center Cross Servicing', NULL ,NULL ) ;
insert_row('2C1H', ' (H) By Treasury Offset',
num_SECC_1H , amt_SECC_1H);
insert_row('2C1I', ' (I) By Agency',
num_SECC_1I , amt_SECC_1I);
insert_row('2C1J', ' (J) Other - must footnote',
num_SECC_1J ,amt_SECC_1J);
insert_row('2D01','LINE' ,NULL ,NULL) ;
insert_row('2D02','Section D' ,NULL ,NULL) ;
insert_row('2D03','Debt Disposition' ,NULL ,NULL) ;
insert_row('2D04','LINE' ,NULL ,NULL) ;
insert_row('2D1', '(1) Written Off and Not Closed Out' ,NULL ,NULL) ;
insert_row('2D1A', ' (A) At Private Collection Agencies' ,NULL ,NULL) ;
insert_row('2D1B1',' (B) At Treasury or a Designated Debt Collection' ,
NULL ,NULL) ;
insert_row('2D1B2',' Center for Cross Servicing' ,NULL ,NULL) ;
insert_row('2D1C', ' (C) At Treasury for Offset' ,NULL ,NULL) ;
insert_row('2D1D4',' (D) Other - must footnote' ,NULL ,NULL) ;
insert_row('2D2', '(2) Reported to IRS on Form 1099-C' ,NULL ,NULL) ;
insert_row('2D21','LINE' ,NULL ,NULL) ;
PROCEDURE insert_row
( p_line_num VARCHAR2,
p_descpription VARCHAR2,
p_count NUMBER,
p_amount NUMBER
) IS
l_module_name VARCHAR2(200);
l_module_name := g_module_name || 'Insert_Row.';
INSERT INTO fv_receivables_activity_temp (
LINE_NUM,
DESCRIPTION,
COUNT ,
AMOUNT)
VALUES (
p_line_num,
p_descpription,
p_count,
p_amount );
'Error in Insert_Row procedure while inserting value for line:' ||
p_line_num ;
END insert_row;
SELECT COUNT(*)
INTO vl_count
FROM fv_receivables_activity_temp ;