[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_records ;
v_last_updated_by NUMBER(15);
v_last_update_date DATE;
v_last_update_login NUMBER(15);
CURSOR trx_select
IS
SELECT rct.customer_trx_id,
hzca.cust_account_id customer_id,
--hzp.party_name customer_name,
rct.trx_number,
rct.trx_date,
rct.purchase_order,
rtt.TYPE,
arem.address_lines_phonetic,
ffc.eliminations_id,
rct.receipt_method_id,
rct.initial_customer_trx_id,
DECODE(hzp.PARTY_TYPE,'ORGANIZATION', hzp.DUNS_NUMBER_C,NULL) duns_number_c,
rsu.Cust_Acct_site_ID bill_to_address_id,
rct.invoice_currency_code
FROM hz_parties hzp,
hz_cust_accounts hzca,
ra_customer_trx rct,
ra_cust_trx_types rtt,
HZ_CUST_SITE_USES rsu ,
ar_remit_to_addresses_v arem,
fv_facts_customers_v ffc
WHERE hzp.party_id = hzca.party_id
AND rct.bill_to_customer_id = hzca.cust_account_id
AND rct.set_of_books_id = v_set_of_books_id
AND rct.complete_flag = 'Y'
AND rtt.cust_trx_type_id = rct.cust_trx_type_id
AND rsu.site_use_id = rct.bill_to_site_use_id
AND rct.remit_to_address_id = arem.address_id
AND ffc.customer_id = hzca.cust_account_id
AND UPPER(hzca.customer_class_code) = 'FEDERAL'
AND rtt.TYPE = 'INV'
AND rct.cust_trx_type_id IN
(SELECT cust_trx_type_id
FROM ra_cust_trx_types
WHERE cust_trx_type_id = DECODE(parm_transaction_type,NULL, cust_trx_type_id,parm_transaction_type)
)
AND rct.bill_to_customer_id IN
(SELECT DISTINCT cust_account_id
FROM hz_customer_profiles
WHERE profile_class_id = DECODE(parm_profile_class_id,NULL, profile_class_id, parm_profile_class_id)
)
AND hzca.cust_account_id IN
(
(SELECT hzca.cust_account_id
FROM hz_parties hp,
hz_cust_accounts hca
WHERE hp.party_id = hca.party_id
AND NVL(category_code,'XXX') LIKE DECODE(parm_customer_category,NULL, NVL(category_code,'XXX'), parm_customer_category)
)
INTERSECT
(SELECT cust_account_id
FROM hz_cust_accounts
WHERE cust_account_id LIKE DECODE(parm_customer_id,NULL, '%',parm_customer_id)
)
)
AND rct.trx_date BETWEEN DECODE(parm_trx_date_low,NULL, TO_DATE('1990/1/1', 'yyyy/mm/dd'), parm_trx_date_low) AND DECODE(parm_trx_date_high,NULL,TRUNC(SYSDATE), parm_trx_date_high)
AND rct.invoice_currency_code = DECODE(parm_currency, NULL, rct.invoice_currency_code, parm_currency);
CURSOR det_select(p_customer_trx_id VARCHAR2)
IS
SELECT rctl.line_number,
arp.amount_due_remaining,
rctl.quantity_invoiced,
rctl.description,
rgld.code_combination_id,
rctl.uom_code,
rctl.unit_selling_price,
fu.user_name,
rgld.percent,
rgld.account_class,
rctl.customer_trx_line_id
FROM ra_customer_trx_lines rctl,
ra_cust_trx_line_gl_dist rgld,
fnd_user fu,
ar_payment_schedules arp
WHERE rgld.customer_trx_id = p_customer_trx_id
AND rgld.customer_trx_id = rctl.customer_trx_id(+)
AND rctl.customer_trx_line_id(+) = rgld.customer_trx_line_id
AND rctl.created_by = fu.user_id(+)
AND rgld.set_of_books_id = v_set_of_books_id
AND arp.org_id = v_org_id
AND arp.customer_trx_id = rgld.customer_trx_id
AND NOT EXISTS
(SELECT 'X'
FROM fv_ipac_trx_all
WHERE set_of_books_id = v_set_of_books_id
AND org_id = v_org_id
AND customer_trx_id = p_customer_trx_id
AND trx_line_no = rctl.line_number
)
;
SELECT SUM(amount) amount,
fit.customer_trx_id,
fit.trx_number,
fit.trx_date,
fit.customer_id,
fit.cash_receipt_id ,
fit.accounted_flag,
fit.cnt_nm,
fit.trn_set_id
FROM fv_ipac_trx_all fit
WHERE fit.exclude_flag = 'N'
AND set_of_books_id = v_set_of_books_id
AND org_id = v_org_id
AND fit.report_flag = 'Y'
AND fit.processed_flag = 'N'
AND fit.account_class <>'REC'
AND fit.unt_iss <> '~RA'
AND ( fit.cash_receipt_id IS NULL
OR ( fit.cash_receipt_id IS NOT NULL
AND NVL(fit.accounted_flag, 'N') <> 'Y' ))
GROUP BY fit.customer_trx_id ,
fit.trx_number,
fit.trx_date,
fit.customer_id,
fit.cash_receipt_id,
fit.accounted_flag,
fit.cnt_nm,
fit.trn_set_id;
SELECT ae_header_id,
ae_line_num,
accounted_cr,
accounted_dr,
accounting_class_code,
code_combination_id
FROM xla_ae_lines
WHERE ae_header_id = p_ae_header_id;
SELECT customer_trx_id,
snd_app_sym,
sgl_acct_num
FROM fv_ipac_trx_all
WHERE unt_iss = '~RA'
AND set_of_books_id = v_set_of_books_id
AND org_id = v_org_id
AND report_flag = 'Y'
AND exclude_flag = 'N'
AND processed_flag = 'N'
AND accounted_flag = 'Y'
GROUP BY customer_trx_id,
snd_app_sym;
SELECT DISTINCT customer_trx_id
FROM fv_ipac_trx_all trx
WHERE set_of_books_id = p_set_of_books_id
AND org_id = p_org_id
AND processed_flag = 'N'
AND exclude_flag = 'N'
AND report_flag = 'Y'
AND cash_receipt_id IS NOT NULL
AND accounted_flag = 'Y'
AND account_class <> 'REC'
AND unt_iss <> '~RA'
ORDER BY customer_trx_id;
SELECT SUM(fit.amount) amount,
fit.cnt_nm,
fit.cnt_phn_nr,
fit.contract_no,
fit.dpr_cd,
fit.dsc,
fit.trx_number,
fit.trx_date,
fit.trn_set_id,
fit.obl_dcm_nr,
fit.pay_flg,
fit.po_number,
SUM(fit.qty) qty,
fit.cust_duns_num,
fit.snd_app_sym,
fit.unt_iss,
fit.unt_prc,
fit.customer_trx_id,
fit.customer_id,
fit.taxpayer_number,
fit.trx_line_no trx_line_no,
fit.cash_receipt_id,
fit.sender_do_sym,
fit.sender_alc,
rct.comments comments
FROM fv_ipac_trx_all fit,
ra_customer_trx rct
WHERE fit.org_id = v_org_id
AND fit.set_of_books_id = v_set_of_books_id
AND fit.customer_trx_id = p_cust_trx_id
AND fit.customer_trx_id = rct.customer_trx_id
AND fit.processed_flag = 'N'
AND fit.exclude_flag = 'N'
AND fit.report_flag = 'Y'
AND fit.account_class <>'REC'
AND fit.unt_iss <> '~RA'
GROUP BY fit.customer_trx_id,
fit.customer_id,
fit.taxpayer_number,
fit.trx_line_no,
fit.cash_receipt_id,
fit.snd_app_sym,
fit.cnt_nm,
fit.cnt_phn_nr,
fit.contract_no,
fit.dpr_cd,
fit.dsc,
fit.trx_number,
fit.trx_date,
fit.trn_set_id,
fit.obl_dcm_nr,
fit.pay_flg,
fit.po_number,
fit.cust_duns_num,
fit.unt_iss,
fit.unt_prc,
fit.sender_do_sym,
fit.sender_alc,
rct.comments
ORDER BY fit.customer_trx_id,
fit.trx_line_no;
DELETE
FROM fv_ipac_trx_all trx
WHERE set_of_books_id = v_set_of_books_id
AND NVL(org_id,-99) = NVL(v_org_id,-99)
AND (report_flag = 'N'
OR ( report_flag = 'Y'
AND bulk_exception IS NOT NULL));
PROCEDURE delete_records
IS
l_module_name VARCHAR2(200) ;
l_module_name := g_module_name || 'delete_records';
DELETE
FROM fv_ipac_trx_all
WHERE set_of_books_id = v_set_of_books_id
AND NVL(org_id,-99) = NVL(v_org_id,-99)
AND ipac_billing_id BETWEEN g_start_billing_id AND g_end_billing_id ;
errmsg := SQLERRM || ' -- Error IN deleleting the records' || ' form IPAC TABLE PROCEDURE ' || ':- delete_records' ;
END delete_records;
SELECT chart_of_accounts_id
INTO flex_num
FROM gl_sets_of_books
WHERE set_of_books_id = v_set_of_books_id;
SELECT flex_value_set_id
INTO gbl_gl_acc_value_set_id
FROM fnd_id_flex_segments
WHERE application_column_name = gbl_gl_segment_name
AND id_flex_code = FLEX_CODE
AND id_flex_num = FLEX_NUM;
SELECT fts.treasury_symbol
INTO v_treasury_symbol
FROM fv_fund_parameters ffp,
fv_treasury_symbols fts,
gl_code_combinations glc
WHERE DECODE(lv_bal_seg_name, 'SEGMENT1', glc.segment1, 'SEGMENT2', glc.segment2, 'SEGMENT3', glc.segment3, 'SEGMENT4',
glc.segment4, 'SEGMENT5', glc.segment5, 'SEGMENT6', glc.segment6, 'SEGMENT7', glc.segment7, 'SEGMENT8', glc.segment8, 'SEGMENT9',
glc.segment9, 'SEGMENT10',glc.segment10, 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12, 'SEGMENT13',glc.segment13,'SEGMENT14',
glc.segment14, 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16, 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18, 'SEGMENT19',
glc.segment19,'SEGMENT20',glc.segment20, 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22, 'SEGMENT23',glc.segment23,'SEGMENT24',
glc.segment24, 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26, 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28, 'SEGMENT29',
glc.segment29, 'SEGMENT30',glc.segment30) = ffp.fund_value
AND glc.code_combination_id = v_ccid
AND ffp.treasury_symbol_id = fts.treasury_symbol_id
AND ffp.set_of_books_id = v_set_of_books_id;
SELECT aba.agency_location_code
INTO v_sender_alc
FROM ar_receipt_method_accounts_all arma,
ap_bank_accounts aba
WHERE aba.bank_account_id = arma.bank_account_id
AND aba.currency_code = nvl(parm_currency,v_invoice_currency)
AND arma.primary_flag = 'Y'
AND arma.receipt_method_id = v_receipt_method_id;
--without end dates, this select will bring in multiple rows.
--Restricting to fetch one row.
BEGIN
FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
'Customer ID = ' || transaction_rec.customer_id ||
' p_bill_to_address_id = ' || p_bill_to_address_id);
SELECT eb.agency_location_code
INTO v_customer_alc
FROM hz_cust_acct_sites_all hzcas,
hz_cust_site_uses_all hzcsu,
iby_external_payers_all payer,
iby_pmt_instr_uses_all iby_ins,
iby_ext_bank_accounts_v eb
WHERE hzcas.cust_account_id = transaction_rec.customer_id
AND hzcas.cust_acct_site_id = p_bill_to_address_id
AND hzcsu.cust_acct_site_id =hzcas.cust_acct_site_id
AND hzcsu.site_use_code = 'BILL_TO'
AND hzcsu.site_use_id = payer.acct_site_use_id
AND payer.ext_payer_id = iby_ins.ext_pmt_party_id
AND iby_ins.instrument_type = 'BANKACCOUNT'
AND transaction_rec.trx_date BETWEEN
iby_ins.start_date AND
NVL(iby_ins.end_date, TO_DATE('12/31/9999', 'MM/DD/YYYY'))
AND iby_ins.instrument_id = eb.ext_bank_account_id
AND rownum = 1;
PROCEDURE insert_trx_rec(ins_trx IN fv_ipac_trx_all%ROWTYPE)
IS
v_trx_billing_id NUMBER;
l_module_name := g_module_name || 'insert_trx_rec';
SELECT fv_ipac_billing_id_s.NEXTVAL INTO v_trx_billing_id FROM dual;
INSERT
INTO fv_ipac_trx_all
(
set_of_books_id,
org_id,
run_date,
ipac_billing_id,
taxpayer_number,
sender_do_sym,
trn_set_id,
amount,
cnt_nm,
cnt_phn_nr,
dpr_cd,
dsc,
trx_number,
trx_date,
obl_dcm_nr,
pay_flg,
po_number,
qty,
snd_app_sym,
unt_iss,
unt_prc,
exception_category,
customer_trx_id,
customer_id,
report_flag,
trx_line_no,
exclude_flag,
processed_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
contract_no,
cust_duns_num,
sgl_acct_num,
cr_dr_flag,
account_class
)
VALUES
(
v_set_of_books_id,
v_org_id,
TRUNC(SYSDATE),
v_trx_billing_id,
ins_trx.taxpayer_number,
ins_trx.sender_do_sym,
ins_trx.trn_set_id,
ins_trx.amount,
NVL(ins_trx.cnt_nm,-99),
parm_contact_ph_no,
ins_trx.dpr_cd,
ins_trx.dsc,
ins_trx.trx_number,
ins_trx.trx_date,
ins_trx.obl_dcm_nr,
ins_trx.pay_flg,
ins_trx.po_number,
ins_trx.qty,
ins_trx.snd_app_sym,
ins_trx.unt_iss,
ins_trx.unt_prc,
ins_trx.exception_category,
ins_trx.customer_trx_id,
ins_trx.customer_id,
ins_trx.report_flag,
ins_trx.trx_line_no,
v_trx_excl_flag,
v_trx_proc_flag,
v_created_by,
v_creation_date,
v_last_updated_by,
v_last_update_date,
v_last_update_login,
ins_trx.contract_no,
ins_trx.cust_duns_num,
ins_trx.sgl_acct_num,
ins_trx.cr_dr_flag,
ins_trx.account_class
)
;
errmsg := SQLERRM || ' -- Error in inserting the data into' ||
' FV_IPAC_TRX_ALL table : Procedure :- insert_trx_rec';
END; -- insert_trx_rec
SELECT DECODE (gbl_gl_segment_name,
'SEGMENT1',glc.segment1,
'SEGMENT2', glc.segment2, 'SEGMENT3',
glc.segment3, 'SEGMENT4',
glc.segment4, 'SEGMENT5',
glc.segment5, 'SEGMENT6',
glc.segment6, 'SEGMENT7',
glc.segment7, 'SEGMENT8',
glc.segment8, 'SEGMENT9',
glc.segment9, 'SEGMENT10',
glc.segment10, 'SEGMENT11',
glc.segment11,'SEGMENT12',
glc.segment12, 'SEGMENT13',
glc.segment13,'SEGMENT14',
glc.segment14, 'SEGMENT15',
glc.segment15,'SEGMENT16',
glc.segment16, 'SEGMENT17',
glc.segment17,'SEGMENT18',
glc.segment18, 'SEGMENT19',
glc.segment19,'SEGMENT20',
glc.segment20, 'SEGMENT21',
glc.segment21,'SEGMENT22',
glc.segment22, 'SEGMENT23',
glc.segment23,'SEGMENT24',
glc.segment24, 'SEGMENT25',
glc.segment25,'SEGMENT26',
glc.segment26, 'SEGMENT27',
glc.segment27,'SEGMENT28',
glc.segment28, 'SEGMENT29',
glc.segment29,'SEGMENT30',
glc.segment30)
INTO l_gl_account_num
FROM gl_code_combinations glc
WHERE code_combination_id = p_ccid
AND glc.chart_of_accounts_id = flex_num;
SELECT SUBSTR(compiled_value_attributes, 5, 1)
INTO l_account_type
FROM fnd_flex_values
WHERE flex_value = p_gl_account
AND flex_value_set_id = gbl_gl_acc_value_set_id;
SELECT ussgl_enabled_flag
INTO l_enabled_flag
FROM fv_facts_ussgl_accounts
WHERE ussgl_account = p_gl_account_num;
SELECT parent_flex_value
INTO l_parent_gl_account_num
FROM fnd_flex_value_hierarchies
WHERE (p_gl_account_num BETWEEN child_flex_value_low AND child_flex_value_high)
AND flex_value_set_id = gbl_gl_acc_value_set_id
AND parent_flex_value <> 'T'
AND parent_flex_value IN
(SELECT ussgl_account
FROM fv_facts_ussgl_accounts
WHERE ussgl_account = parent_flex_value
AND ussgl_enabled_flag ='Y'
)
;
SELECT customer_trx_id ,
snd_app_sym
FROM fv_ipac_trx_all
WHERE processed_flag ='N'
AND set_of_books_id = v_set_of_books_id
AND org_id = v_org_id
AND exclude_flag ='N'
AND unt_iss <> '~RA'
AND ipac_billing_id BETWEEN
g_start_billing_id AND
g_end_billing_id
GROUP BY customer_trx_id,
snd_app_sym;
IS SELECT trx_line_no,
SUM(amount) amount
FROM fv_ipac_trx_all
WHERE org_id = v_org_id
AND set_of_books_id = v_set_of_books_id
AND processed_flag ='N'
AND customer_trx_id = p_customer_trx_id
AND snd_app_sym = p_snd_app_sym
AND account_class <> 'REC'
AND unt_iss <> '~RA'
AND set_of_books_id = v_set_of_books_id
AND exclude_flag ='N'
GROUP BY trx_line_no ;
SELECT * INTO l_trx_rec
FROM fv_ipac_trx_all
WHERE org_id = v_org_id
AND set_of_books_id = v_set_of_books_id
AND customer_trx_id = trx_rec.customer_trx_id
AND account_class = 'REC';
insert_trx_rec(l_trx_rec);
SELECT customer_trx_id ,
snd_app_sym
FROM fv_ipac_trx_all
WHERE processed_flag ='N'
AND org_id = v_org_id
AND set_of_books_id = v_set_of_books_id
AND exclude_flag ='N'
AND unt_iss <> '~RA'
AND accounted_flag ='Y'
AND report_flag ='Y'
GROUP BY customer_trx_id,
snd_app_sym;
SELECT trx_line_no,
SUM(amount) amount
FROM fv_ipac_trx_all
WHERE org_id = v_org_id
AND set_of_books_id = v_set_of_books_id
AND processed_flag ='N'
AND customer_trx_id = p_customer_trx_id
AND snd_app_sym = p_snd_app_sym
AND account_class <> 'REC'
AND unt_iss <> '~RA'
AND exclude_flag ='N'
GROUP BY trx_line_no ;
SELECT COUNT(1) trx_count
FROM fv_ipac_trx_all
WHERE org_id = v_org_id
AND set_of_books_id = v_set_of_books_id
AND customer_trx_id = p_customer_trx_id
AND snd_app_sym = p_snd_app_sym
AND unt_iss = '~RA'
AND processed_flag ='N'
AND exclude_flag ='N'
GROUP BY trx_line_no ;
SELECT SUM(DECODE(cr_dr_flag,'D',ABS(amount),0)) - SUM(DECODE(cr_dr_flag,'C',ABS(amount),0)),
COUNT(sgl_acct_num)
INTO l_amount,
l_count_sgl_acct
FROM fv_ipac_trx_all
WHERE set_of_books_id = v_set_of_books_id
AND org_id = v_org_id
AND customer_trx_id = trx_rec.customer_trx_id
AND (bulk_exception <> 'BUDGETARY'
AND bulk_exception IS NULL)
AND unt_iss = '~RA'
AND snd_app_sym = trx_rec.snd_app_sym;
UPDATE fv_ipac_trx_all
SET bulk_exception = 'SGL_SUM_MISMATCH',
report_flag ='N' ,
amount =
(SELECT SUM(amount)
FROM fv_ipac_trx_all
WHERE set_of_books_id = v_set_of_books_id
AND org_id = v_org_id
AND customer_trx_id =trx_rec.customer_trx_id
AND ACCOUNT_CLASS <> 'REC'
AND unt_iss <> '~RA'
)
WHERE set_of_books_id = v_set_of_books_id
AND org_id = v_org_id
AND customer_trx_id = trx_rec.customer_trx_id
AND snd_app_sym =trx_rec.snd_app_sym
AND (bulk_exception IS NULL
AND bulk_exception <> 'BUDGETARY')
AND unt_iss = '~RA';
UPDATE fv_ipac_trx_all
SET bulk_exception = 'EXCEED_DR_CR',
report_flag ='N' ,
amount =
(SELECT SUM(amount)
FROM fv_ipac_trx_all
WHERE set_of_books_id = v_set_of_books_id
AND org_id = v_org_id
AND customer_trx_id =trx_rec.customer_trx_id
AND ACCOUNT_CLASS <> 'REC'
AND unt_iss <> '~RA'
)
WHERE set_of_books_id = v_set_of_books_id
AND org_id = v_org_id
AND customer_trx_id =trx_rec.customer_trx_id
AND snd_app_sym =trx_rec.snd_app_sym
AND (bulk_exception IS NULL
AND bulk_exception <> 'BUDGETARY')
AND unt_iss = '~RA';
v_last_updated_by := fnd_global.user_id;
v_last_update_date := SYSDATE;
v_last_update_login := fnd_global.login_id;
SELECT fv_ipac_billing_id_s.NEXTVAL+1 INTO g_start_billing_id FROM dual ;
FOR trx_select_rec IN trx_select
LOOP -- trx_select
init_vars;
v_receipt_method_id := trx_select_rec.receipt_method_id;
trx_rec.sender_do_sym := trx_select_rec.address_lines_phonetic;
trx_rec.dpr_cd := trx_select_rec.eliminations_id;
trx_rec.trx_number := trx_select_rec.trx_number;
trx_rec.trx_date := trx_select_rec.trx_date;
trx_rec.obl_dcm_nr := trx_select_rec.trx_number;
trx_rec.po_number := trx_select_rec.purchase_order;
trx_rec.customer_trx_id := trx_select_rec.customer_trx_id;
trx_rec.customer_id := trx_select_rec.customer_id;
l_bill_to_address_id := trx_select_rec.bill_to_address_id;
v_commitment_id := trx_select_rec.initial_customer_trx_id;
trx_rec.cust_duns_num := trx_select_rec.duns_number_c;
v_invoice_currency := trx_select_rec.invoice_currency_code;
SELECT trx_number
INTO trx_rec.contract_no
FROM Ra_Customer_Trx
WHERE customer_trx_id = v_commitment_id;
SELECT SUM(amount_due_original),
SUM(NVL(amount_adjusted,0)+ NVL(amount_credited,0)+ NVL(amount_due_remaining,0) + NVL(amount_applied,0))
INTO v_original_amount,
v_paid_amount
FROM ar_payment_schedules
WHERE customer_trx_id = trx_select_rec.customer_trx_id
AND org_id = v_org_id;
DELETE
FROM fv_ipac_trx_all
WHERE customer_trx_id = trx_select_rec.customer_trx_id
AND processed_flag = 'N'
AND exclude_flag = 'N'
AND cash_receipt_id IS NULL
AND set_of_books_id = v_set_of_books_id
AND NVL(org_id,-99) = NVL(v_org_id,-99);
FOR det_select_rec IN det_select(trx_select_rec.customer_trx_id)
LOOP -- detail_select
IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, '---- DETAILS -------');
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Trx Number: '||trx_select_rec.trx_number);
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Trx Date: '||trx_select_rec.trx_date);
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Amount: '||det_select_rec.amount_due_remaining);
get_treasury_symbol(bl_seg_name, det_select_rec.code_combination_id);
trx_rec.amount := det_select_rec.amount_due_remaining;
trx_rec.cnt_nm := det_select_rec.user_name;
trx_rec.dsc := det_select_rec.description;
trx_rec.qty := ROUND(det_select_rec.quantity_invoiced * (det_select_rec.percent/100),2);
trx_rec.unt_iss := det_select_rec.uom_code;
trx_rec.unt_prc := det_select_rec.unit_selling_price;
trx_rec.trx_line_no := det_select_rec.line_number;
trx_rec.account_class := det_select_rec.account_class;
trx_rec.sgl_acct_num := gl_account_num(det_select_rec.code_combination_id);
trx_rec.exception_category := get_trx_exception(trx_rec, det_select_rec.customer_trx_line_id, l_bill_to_address_id);
IF (det_select_rec.account_class = 'REV' AND SIGN(det_select_rec .amount_due_remaining) =1) OR (det_select_rec.account_class = 'REC' AND SIGN(det_select_rec .amount_due_remaining) =-1 ) THEN
trx_rec.cr_dr_flag := 'C';
insert_trx_rec(trx_rec);
END LOOP; -- detail_select
END LOOP; -- trx_SELECT
SELECT fv_ipac_billing_id_s.CURRVAL INTO g_end_billing_id FROM dual;
UPDATE fv_ipac_trx_all trx
SET report_flag ='N'
WHERE org_id = v_org_id
AND set_of_books_id = v_set_of_books_id
AND report_flag <> 'N'
AND EXISTS
(SELECT 'X'
FROM fv_ipac_trx_all
WHERE org_id = v_org_id
AND set_of_books_id = v_set_of_books_id
AND customer_trx_id = trx.customer_trx_id
AND report_flag = 'N'
)
;
delete_records;
delete_records ;
delete_records ;
errmsg := SQLERRM || ' -- Error IN IPAC selection' || ' process : Procedure :- main';
v_statement := 'SELECT ''PCA '' FROM dual';
SELECT customer_trx_id
FROM fv_ipac_trx_all trx
WHERE set_of_books_id = lv_set_of_books_id
AND org_id = v_org_id
AND processed_flag = 'N'
AND exclude_flag = 'N'
AND report_flag = 'Y'
GROUP BY customer_trx_id,
trn_set_id;
SELECT COUNT(DISTINCT(customer_trx_id||trn_set_id))
INTO v_header_count
FROM fv_ipac_trx_all trx
WHERE set_of_books_id = lv_set_of_books_id
AND org_id = v_org_id
AND processed_flag = 'N'
AND exclude_flag = 'N'
AND report_flag = 'Y'
AND account_class <> 'REC'
AND unt_iss <> '~RA'
AND Bulk_Exception IS NULL
AND cash_receipt_id IS NOT NULL
AND accounted_flag='Y';
SELECT COUNT(1)
INTO l_total_ussgl_count
FROM fv_ipac_trx_all trx
WHERE set_of_books_id = lv_set_of_books_id
AND org_id = v_org_id
AND trx_line_no is NOT NULL
AND unt_iss = '~RA'
AND bulk_exception is NULL
AND customer_trx_id IN
( SELECT customer_trx_id
FROM fv_ipac_trx_all trx
WHERE set_of_books_id = lv_set_of_books_id
AND org_id = v_org_id
AND processed_flag = 'N'
AND exclude_flag = 'N'
AND report_flag = 'Y'
AND account_class <> 'REC'
AND unt_iss <> '~RA'
AND bulk_exception IS NULL
AND cash_receipt_id IS NOT NULL
AND accounted_flag='Y'
)
;
v_statement := 'SELECT ''No transactions found to report for Bulk File!'' FROM dual' ;
SELECT COUNT(1)
INTO v_detail_count
FROM
(SELECT customer_trx_id
FROM fv_ipac_trx_all
WHERE processed_flag = 'N'
AND set_of_books_id = lv_set_of_books_id
AND org_id = v_org_id
AND exclude_flag = 'N'
AND report_flag = 'Y'
AND account_class <> 'REC'
AND unt_iss <> '~RA'
GROUP BY customer_trx_id,
trx_line_no,
snd_app_sym
);
SELECT lpad(lv_sender_alc,8,'0')||
to_char(sysdate,'YYYYMMDD')||
lpad(FV_IPAC_AR_BATCH_HDR_S.nextval,3,'0')
INTO l_file_id
FROM DUAL;
v_statement := 'SELECT ''B''||''IPAC'' || LPAD('
|| v_total_count||',8,''0'') ||
'''||l_file_id||''' FROM dual' ;
'SELECT ''H''||
LPAD(SUBSTR(fit.sender_alc,1,8),8,''0'')||
REPLACE(TO_CHAR(SUM(fit.amount),''FM099999999999D00''),
''.'','''')||
LPAD(SUBSTR(fit.taxpayer_number,1,8),8,'' '')||
RPAD(SUBSTR(fit.sender_do_sym,1,5),5,'' '') ||
fit.trn_set_id ||
RPAD(NVL(SUBSTR(rct.ct_reference, 1, 8), '' ''), 8, '' '')||
RPAD('' '',2)
FROM fv_ipac_trx fit,
ra_customer_trx rct
WHERE fit.set_of_books_id = :b_set_of_books_id
AND rct.customer_trx_id = fit.customer_trx_id
AND fit.processed_flag = ''N''
AND fit.exclude_flag = ''N''
AND fit.report_flag = ''Y''
AND fit.account_class <> ''REC''
AND fit.unt_iss <> ''~RA''
AND fit.customer_trx_id = :b_customer_trx_id
GROUP BY fit.customer_trx_id,fit.sender_alc,
fit.trn_set_id,
fit.taxpayer_number,
fit.sender_do_sym,rct.ct_reference
ORDER BY fit.customer_trx_id, fit.trn_set_id'
;
SELECT 'D' || RPAD(' ',16)||RPAD(' ',12)|| REPLACE(TO_CHAR(trx_details_rec.amount, 'FM099999999999D00'),'.','')||
RPAD(SUBSTR(trx_details_rec.cnt_nm,1,60),60,' ') || RPAD(nvl(trx_details_rec.cnt_phn_nr,' '),17,' ')||RPAD(' ',6)||
DECODE(trx_details_rec.contract_no,NULL,RPAD(' ',17), RPAD(SUBSTR(trx_details_rec.contract_no,1,17),17,' '))||
DECODE(trx_details_rec.dpr_cd,NULL,' ', RPAD(SUBSTR(trx_details_rec.dpr_cd,1,2),2,' '))||
DECODE(trx_details_rec.dsc,NULL,RPAD(' ',320), RPAD(SUBSTR(trx_details_rec.dsc,1,320),320,' '))|| RPAD('0',8,'0')
|| DECODE(trx_details_rec.trx_number,NULL,RPAD(' ',22), RPAD(SUBSTR(trx_details_rec.trx_number,1,22),22,' '))||
RPAD(' ',30)||RPAD(' ',20)|| RPAD(NVL(SUBSTR(trx_details_rec.comments, 1, 320), ' '), 320, ' ')||
DECODE(trx_details_rec.obl_dcm_nr,NULL,RPAD(' ',17), RPAD(SUBSTR(trx_details_rec.obl_dcm_nr,1,17),17,' '))||
DECODE(trx_details_rec.pay_flg,NULL,' ', trx_details_rec.pay_flg)|| DECODE(trx_details_rec.po_number,NULL,RPAD(' ',22),
RPAD(SUBSTR(trx_details_rec.po_number,1,22),22,' '))|| LPAD(trx_details_rec.qty*100,14,0) || RPAD(' ',1) || RPAD(' ',27)||
RPAD(' ',8)|| RPAD(NVL(trx_details_rec.cust_duns_num,' '),9)|| RPAD(' ',4)|| RPAD(' ',15)||
DECODE(trx_details_rec.snd_app_sym,NULL,RPAD(' ',27), RPAD(SUBSTR(REPLACE(trx_details_rec.snd_app_sym, ' ', ''),1,27),27,' '))
|| RPAD(' ',8) || RPAD(' ',9) || RPAD(' ',4) || RPAD(' ',15) || DECODE(trx_details_rec.unt_iss,NULL,' ',
RPAD(SUBSTR(trx_details_rec.unt_iss,1,2),2,' '))|| DECODE(trx_details_rec.unt_prc,NULL,RPAD('0',14,'0'),
REPLACE(TO_CHAR(trx_details_rec.unt_prc,'FM099999999999D00') ,'.',''))|| RPAD(' ',15) trx_line_rec
INTO l_trx_detail_rec
FROM dual ;
SELECT sgl_acct_num,
amount,
cr_dr_flag
FROM fv_ipac_trx_all trx
WHERE org_id = v_org_id
AND set_of_books_id = v_set_of_books_id
AND customer_trx_id = p_cust_trx_id
AND unt_iss = '~RA'
AND report_flag = 'Y'
AND processed_flag = 'N'
AND accounted_flag = 'Y'
AND exclude_flag = 'N'
AND bulk_exception is NULL
AND cash_receipt_id IS NOT NULL
AND snd_app_sym = p_snd_app_sym
AND trx_line_no = l_trx_line_no;
SELECT event_id,
event_type_code
FROM xla_events
WHERE application_id = 222
AND entity_id = p_entity_id
AND
( (p_proc = 1 AND event_status_code <> 'P' )
OR
(p_proc = 2 AND event_status_code = 'P' )
);
SELECT entity_id,
legal_entity_id
INTO l_entity_id,
l_legal_entity_id
FROM xla_transaction_entities
WHERE source_id_int_1 = NVL(p_cash_receipt_id , trx_receipt_rec.cash_receipt_id )
AND application_id=222
AND entity_code ='RECEIPTS';
SELECT event_id,
event_type_code
INTO l_event_id,
l_event_type_code
FROM xla_events
WHERE application_id = 222
AND entity_id = l_entity_id
and event_status_code <> 'P';
UPDATE fv_ipac_trx_all
SET accounted_flag = 'Y',
bulk_exception = NULL
WHERE set_of_books_id = v_set_of_books_id
AND org_id = v_org_id
AND customer_trx_id = trx_receipt_rec.customer_trx_id
AND exclude_flag = 'N'
AND report_flag = 'Y'
AND processed_flag = 'N';
SELECT ae_header_id
INTO l_ae_header_id
FROM xla_ae_headers
WHERE event_id = get_evnt_rec.event_id;
then Debit Account is updated with cr_dr_flag='C'
and Credit Account cr_dr_flag='D',
Amount = for both the Accounts.
This is required for the Bulk File reporting..*/
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
'xla_acnt_rec.accounted_cr= '||xla_acnt_rec.accounted_cr);
SELECT racust.line_number
INTO l_trx_line_no
FROM ra_customer_trx_lines racust,
ar_distributions ardist,
xla_distribution_links xladist
WHERE
xladist.ae_header_id = xla_acnt_rec.ae_header_id
AND
xladist.ae_line_num = xla_acnt_rec.ae_line_num
AND
xladist.application_id = 222
AND
xladist.SOURCE_DISTRIBUTION_ID_NUM_1 = ardist.line_id
AND
ardist.REF_CUSTOMER_TRX_LINE_ID = racust.CUSTOMER_TRX_LINE_ID;
INSERT
INTO fv_ipac_trx_all
(
set_of_books_id,
org_id,
ipac_billing_id,
amount,
cnt_nm,
trx_number,
trx_date,
snd_app_sym,
unt_iss,
customer_trx_id,
customer_id,
trx_line_no,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
sgl_acct_num,
bulk_exception,
cr_dr_flag,
PROCESSED_FLAG,
REPORT_FLAG,
ACCOUNTED_FLAG,
RECEIPT_FLAG,
cash_receipt_id,
exclude_flag,
trn_set_id
)
VALUES
(
v_set_of_books_id,
v_org_id,
fv_ipac_billing_id_s.NEXTVAL,
l_amount,
nvl(trx_receipt_rec.cnt_nm,-99),
trx_receipt_rec.trx_number,
trx_receipt_rec.trx_date,
v_treasury_symbol,
'~RA',
trx_receipt_rec.customer_trx_id,
trx_receipt_rec.customer_id,
l_trx_line_no,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
l_gl_account_num,
v_bulk_exception,
l_cr_dr_flag,
'N',
'Y',
'Y',
'Y',
NVL(p_cash_receipt_id,trx_receipt_rec.cash_receipt_id),
'N',
trx_receipt_rec.trn_set_id
)
;
INSERT
INTO fv_ipac_trx_all
(
set_of_books_id,
org_id,
ipac_billing_id,
amount,
cnt_nm,
trx_number,
trx_date,
unt_iss,
customer_trx_id,
customer_id,
report_flag,
exclude_flag,
processed_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
receipt_flag,
accounted_flag,
bulk_exception,
cash_receipt_id,
trn_set_id
)
VALUES
(
v_set_of_books_id,
v_org_id,
fv_ipac_billing_id_s.NEXTVAL,
trx_receipt_rec.amount,
'-99',
trx_receipt_rec.trx_number,
trx_receipt_rec.trx_date,
'~RA',
trx_receipt_rec.customer_trx_id,
trx_receipt_rec.customer_id,
'Y',
'N',
'N',
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
'Y',
'N',
'ACCOUNTING_NOT_CREATED',
trx_receipt_rec.cash_receipt_id,
trx_receipt_rec.trn_set_id
)
;
UPDATE fv_ipac_trx_all
SET accounted_flag = 'N'
WHERE set_of_books_id = v_set_of_books_id
AND org_id = v_org_id
AND customer_trx_id= trx_receipt_rec.customer_trx_id
AND exclude_flag = 'N'
AND report_flag = 'Y'
AND processed_flag = 'N';
'Create_Receipt_Accounting - Unexpected Error, Calling Update');
UPDATE fv_ipac_trx_all
SET accounted_flag = '',
bulk_exception = NULL
WHERE set_of_books_id = v_set_of_books_id
AND org_id = v_org_id
AND customer_trx_id = trx_receipt_rec.customer_trx_id
AND exclude_flag = 'N'
AND report_flag = 'Y'
AND processed_flag = 'N';
SELECT cba.agency_location_code
INTO v_sender_alc
FROM ar_receipt_method_accounts arma,
Ce_bank_accounts cba,
CE_BANK_ACCT_USES_ALL cbal
WHERE cbal.bank_account_id =cba.bank_account_id
AND cbal.bank_acct_use_id = arma.remit_bank_acct_use_id
AND cba.currency_code = (p_currency_code)
AND arma.primary_flag = 'Y'
AND arma.receipt_method_id =p_receipt_method_id
AND arma.org_id = v_org_id;
select PAYMENT_TRXN_EXTENSION_ID
into
l_payment_trxn_extension_id
from ra_customer_trx_all
where CUSTOMER_TRX_ID = trx_receipt_rec.customer_trx_id;
/* SELECT cba.agency_location_code
INTO v_sender_alc
FROM ar_receipt_method_accounts_all arma,
Ce_bank_accounts cba
WHERE cba.bank_account_id = arma.remit_bank_acct_use_id
AND cba.currency_code = (p_currency_code)
AND arma.primary_flag = 'Y'
AND arma.receipt_method_id = p_receipt_method_id; */
UPDATE fv_ipac_trx_all
SET sender_alc = v_sender_alc,
cash_receipt_id = x_cash_receipt_id,
receipt_flag = 'Y'
WHERE set_of_books_id = v_set_of_books_id
AND org_id = v_org_id
AND customer_trx_id = trx_receipt_rec.customer_trx_id
AND exclude_flag = 'N'
AND report_flag = 'Y'
AND processed_flag = 'N';
INSERT
INTO fv_interagency_funds_all
(
INTERAGENCY_FUND_ID,
SET_OF_BOOKS_ID,
ORG_ID,
PROCESSED_FLAG,
CHARGEBACK_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
CUSTOMER_ID,
CASH_RECEIPT_ID,
RECEIPT_NUMBER
)
VALUES
(
fv_interagency_funds_s.NEXTVAL,
v_set_of_books_id,
v_org_id,
'N',
'N',
SYSDATE,
fnd_global.user_id,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
trx_receipt_rec.customer_id,
x_cash_receipt_id,
trx_receipt_rec.trx_number
)
;
INSERT
INTO fv_ipac_trx_all
(
set_of_books_id,
org_id,
ipac_billing_id,
amount,
cnt_nm,
trx_number,
trx_date,
unt_iss,
customer_trx_id,
customer_id,
report_flag,
exclude_flag,
processed_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
receipt_flag,
accounted_flag,
bulk_exception,
trn_set_id
)
VALUES
(
v_set_of_books_id,
v_org_id,
fv_ipac_billing_id_s.NEXTVAL,
trx_receipt_rec.amount,
'-99',
trx_receipt_rec.trx_number,
trx_receipt_rec.trx_date,
'~RA',
trx_receipt_rec.customer_trx_id,
trx_receipt_rec.customer_id,
'Y',
'N',
'N',
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
'N',
'N',
'RECEIPT_NOT_CREATED',
trx_receipt_rec.trn_set_id
)
;
UPDATE fv_ipac_trx_all
SET receipt_flag = 'N'
WHERE set_of_books_id = v_set_of_books_id
AND org_id = v_org_id
AND customer_trx_id = trx_receipt_rec.customer_trx_id
AND exclude_flag = 'N'
AND report_flag = 'Y'
AND processed_flag = 'N';
SELECT count(1)
INTO l_rec_count
FROM fv_ipac_trx_all
WHERE set_of_books_id = v_set_of_books_id
AND org_id = v_org_id
AND accounted_flag ='Y'
AND report_flag ='Y'
AND exclude_flag ='N'
AND processed_flag = 'N'
AND bulk_exception is null;
UPDATE fv_ipac_trx_all
SET processed_flag = 'Y'
WHERE set_of_books_id = v_set_of_books_id
AND org_id = v_org_id
AND bulk_exception IS NULL
AND cash_receipt_id IS NOT NULL
AND accounted_flag = 'Y'
AND exclude_flag = 'N'
AND report_flag = 'Y';