[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select dra.disb_rule_id,
dra.rule_name,
dra.fee_option,
dra.fee_basis,
dra.fee_amount,
dra.fee_percent,
nvl(dra.consolidate_by_due_date, 'N') consolidate_by_due_date,
dra.frequency,
dra.day_of_month,
dra.scheduled_month,
nvl(dra.consolidate_strm_type, 'N') consolidate_strm_type,
drs.stream_type_purpose,
drv.invoice_seq_start,
drv.invoice_seq_end,
drv.next_inv_seq,
drv.disb_rule_vendor_site_id
from okl_disb_rules_all_b dra,
okl_disb_rule_vendor_sites drv,
okl_disb_rule_sty_types drs
where drv.disb_rule_id = dra.disb_rule_id
and drs.disb_rule_id = dra.disb_rule_id
and drv.vendor_id = p_vendor_id
and drv.vendor_site_id = p_vendor_site_id
and drs.stream_type_purpose = p_stream_type_purpose
and TRUNC(sysdate) between TRUNC(NVL(drv.start_date, dra.start_date)) and
TRUNC(NVL(NVL(drv.end_date, dra.end_date),TRUNC(sysdate)));
l_update_flag boolean := TRUE; -- bug: 6662247
l_update_flag := FALSE;
l_update_flag := FALSE;
IF l_update_flag = TRUE THEN
-- Update okl_disb_rule_vendor_sites
UPDATE okl_disb_rule_vendor_sites
SET next_inv_seq = l_new_next_inv_seq
WHERE disb_rule_vendor_site_id = l_disb_rules.drv_rec.disb_rule_vendor_site_id
AND disb_rule_id = l_disb_rules.dra_rec.disb_rule_id;
SELECT
tap.id tap_id
,NVL(tap.set_of_books_id, OKL_ACCOUNTING_UTIL.get_set_of_books_id) sob_id
,tap.org_id
,tap.invoice_category_code
,tap.invoice_number
--,tap.vendor_invoice_number
,nvl(tap.vendor_invoice_number, tap.invoice_number) vendor_invoice_number
,tap.object_version_number
,tap.code_combination_id tap_ccid
,tap.date_invoiced date_invoiced
,tap.pay_group_lookup_code
,tap.ipvs_id
,tap.ippt_id
,tap.IPT_FREQUENCY
,tap.IPT_ID
,tap.payment_method_code
,tap.currency_code
,tap.currency_conversion_type
,tap.currency_conversion_rate
,tap.currency_conversion_date
,tap.workflow_yn
,tap.date_gl gl_date
,tap.nettable_yn
--start:| 02-May-2007 cklee Disbursement changes for R12B |
-- ,tap.khr_id
,tpl.khr_id
--end:| 02-May-2007 cklee Disbursement changes for R12B |
,tap.wait_vendor_invoice_yn
,tap.try_id
,tpl.id tpl_id
,tpl.code_combination_id tpl_ccid
,tpl.sty_id
,acc_db.code_combination_id db_ccid
,acc_cr.code_combination_id cr_ccid
,tpl.amount invoice_amount
,acc_db.amount line_amount
,povs.vendor_id
,tap.trx_status_code
,tpl.line_number
,tap.INVOICE_TYPE
,tap.SET_OF_BOOKS_ID
,tap.DATE_GL
,tap.LEGAL_ENTITY_ID
,tap.VPA_ID
,tpl.INV_DISTR_LINE_CODE
,tpl.DISBURSEMENT_BASIS_CODE
,tpl.TPL_ID_REVERSES
,tpl.CODE_COMBINATION_ID
,tpl.LSM_ID
,tpl.KLE_ID
,tpl.ITC_ID
,tpl.DATE_ACCOUNTING
,tpl.PAYABLES_INVOICE_ID
,tpl.REQUEST_ID
,tpl.FUNDING_REFERENCE_NUMBER
,tpl.FUNDING_REFERENCE_TYPE_CODE
,tpl.SEL_ID
,tpl.TAXABLE_YN
--start:| 02-May-2007 cklee Disbursement changes for R12B |
,tpl.REF_LINE_NUMBER
,tpl.CNSLD_LINE_NUMBER
--end:| 02-May-2007 cklee Disbursement changes for R12B |
FROM
okl_trns_acc_dstrs acc_db
,okl_trns_acc_dstrs acc_cr
,po_vendor_sites_all povs
,okl_txl_ap_inv_lns_b tpl
,okl_trx_ap_invoices_b tap
WHERE
NVL(tap.trx_status_code, 'ENTERED') in ( 'ENTERED', 'APPROVED' ) AND
trunc(tap.date_invoiced) BETWEEN
NVL(p_from_date, SYSDATE-10000) AND NVL(p_to_date, SYSDATE+10000)
AND nvl(acc_db.cr_dr_flag(+), 'D') = 'D' --ssiruvol 05May2007
AND nvl(acc_cr.cr_dr_flag(+), 'C') = 'C' --ssiruvol 05May2007
AND acc_db.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
AND acc_cr.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
AND tpl.id = acc_db.source_id (+)
AND tpl.id = acc_cr.source_id (+)
AND povs.vendor_site_id = tap.ipvs_id
AND tap.id = tpl.tap_id
AND tap.FUNDING_TYPE_CODE IS NULL
AND nvl(tap.khr_id,-1) = nvl(p_contract_id, nvl(tap.khr_id,-1))
AND nvl(tap.vendor_id,-1) = nvl(p_vendor_id, nvl(tap.vendor_id,-1) )
AND nvl(tap.ipvs_id,-1) = nvl(p_vendor_site_id, nvl(tap.ipvs_id,-1))
--start:| 24-APR-2007 cklee Disbursement changes for R12B |
-- AND nvl(tpl.sty_id,-1) = nvl(p_sty_id, nvl(tpl.sty_id,-1))
AND nvl(tpl.sty_id,-1) = NVL((select id from OKL_STRM_TYPE_B where STREAM_TYPE_PURPOSE = nvl(p_stream_type_purpose, 'XXX')),nvl(tpl.sty_id,-1))
--end:| 24-APR-2007 cklee Disbursement changes for R12B |
AND nvl(tap.vpa_id,-1) = nvl(p_vpa_id, nvl(tap.vpa_id,-1))
UNION ALL
SELECT
tap.id tap_id
,NVL(tap.set_of_books_id, OKL_ACCOUNTING_UTIL.get_set_of_books_id) sob_id
,tap.org_id
,tap.invoice_category_code
,tap.invoice_number
--,tap.vendor_invoice_number
,nvl(tap.vendor_invoice_number, tap.invoice_number) vendor_invoice_number
,tap.object_version_number
,tap.code_combination_id tap_ccid
,tap.date_invoiced date_invoiced
,tap.pay_group_lookup_code
,tap.ipvs_id
,tap.ippt_id
,tap.IPT_FREQUENCY
,tap.IPT_ID
,tap.payment_method_code
,tap.currency_code
,tap.currency_conversion_type
,tap.currency_conversion_rate
,tap.currency_conversion_date
,tap.workflow_yn
,tap.date_gl gl_date
,tap.nettable_yn
--start:| 02-May-2007 cklee Disbursement changes for R12B |
-- ,tap.khr_id
,tpl.khr_id
--end:| 02-May-2007 cklee Disbursement changes for R12B |
,tap.wait_vendor_invoice_yn
,tap.try_id
,tpl.id tpl_id
,tpl.code_combination_id tpl_ccid
,tpl.sty_id
,acc_db.code_combination_id db_ccid
,acc_cr.code_combination_id cr_ccid
,tpl.amount invoice_amount
,acc_db.amount line_amount
,povs.vendor_id
,tap.trx_status_code
,tpl.line_number
,tap.INVOICE_TYPE
,tap.SET_OF_BOOKS_ID
,tap.DATE_GL
,tap.LEGAL_ENTITY_ID
,tap.VPA_ID
,tpl.INV_DISTR_LINE_CODE
,tpl.DISBURSEMENT_BASIS_CODE
,tpl.TPL_ID_REVERSES
,tpl.CODE_COMBINATION_ID
,tpl.LSM_ID
,tpl.KLE_ID
,tpl.ITC_ID
,tpl.DATE_ACCOUNTING
,tpl.PAYABLES_INVOICE_ID
,tpl.REQUEST_ID
,tpl.FUNDING_REFERENCE_NUMBER
,tpl.FUNDING_REFERENCE_TYPE_CODE
,tpl.SEL_ID
,tpl.TAXABLE_YN
--start:| 02-May-2007 cklee Disbursement changes for R12B |
,tpl.REF_LINE_NUMBER
,tpl.CNSLD_LINE_NUMBER
--end:| 02-May-2007 cklee Disbursement changes for R12B |
FROM
okl_trns_acc_dstrs acc_db
,okl_trns_acc_dstrs acc_cr
,po_vendor_sites_all povs
,okl_txl_ap_inv_lns_b tpl
,okl_trx_ap_invoices_b tap
WHERE
NVL(tap.trx_status_code, 'APPROVED') in ( 'APPROVED') AND
trunc(tap.date_invoiced) BETWEEN
NVL(p_from_date, SYSDATE-10000) AND NVL(p_to_date, SYSDATE+10000)
AND nvl(acc_db.cr_dr_flag(+), 'D') = 'D' --ssiruvol 05May2007
AND nvl(acc_cr.cr_dr_flag(+), 'C') = 'C' --ssiruvol 05May2007
AND acc_db.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
AND acc_cr.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
AND tpl.id = acc_db.source_id (+)
AND tpl.id = acc_cr.source_id (+)
AND povs.vendor_site_id = tap.ipvs_id
AND tap.id = tpl.tap_id
AND tap.FUNDING_TYPE_CODE IS NOT NULL
AND nvl(tap.khr_id,-1) = nvl(p_contract_id, nvl(tap.khr_id,-1))
AND nvl(tap.vendor_id,-1) = nvl(p_vendor_id, nvl(tap.vendor_id,-1) )
AND nvl(tap.ipvs_id,-1) = nvl(p_vendor_site_id, nvl(tap.ipvs_id,-1))
--start:| 24-APR-2007 cklee Disbursement changes for R12B |
-- AND nvl(tpl.sty_id,-1) = nvl(p_sty_id, nvl(tpl.sty_id,-1))
AND nvl(tpl.sty_id,-1) = NVL((select id from OKL_STRM_TYPE_B where STREAM_TYPE_PURPOSE = nvl(p_stream_type_purpose, 'XXX')), nvl(tpl.sty_id,-1))
--end:| 24-APR-2007 cklee Disbursement changes for R12B |
AND nvl(tap.vpa_id,-1) = nvl(p_vpa_id, nvl(tap.vpa_id,-1))
ORDER BY vendor_id,
ipvs_id,
pay_group_lookup_code,
payment_method_code,
ippt_id,
set_of_books_id,
code_combination_id,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
legal_entity_id,
vpa_id,
invoice_type,
ipt_id,
ipt_frequency,
sty_id,
date_invoiced;
select id
from OKL_STRM_TYPE_B
where STREAM_TYPE_PURPOSE = p_stream_type_purpose;
select dra.rule_name,
dra.fee_option,
dra.fee_basis,
dra.fee_amount,
dra.fee_percent,
nvl(dra.consolidate_by_due_date, 'N') consolidate_by_due_date,
dra.frequency,
dra.day_of_month,
dra.scheduled_month,
nvl(dra.consolidate_strm_type, 'N') consolidate_strm_type,
drs.stream_type_purpose,
drv.invoice_seq_start,
drv.invoice_seq_end,
drv.next_inv_seq,
drv.disb_rule_vendor_site_id,
drv.disb_rule_id
from okl_disb_rules_all_b dra,
okl_disb_rule_vendor_sites drv,
okl_disb_rule_sty_types drs
where drv.DISB_RULE_ID = dra.disb_rule_id
and drs.disb_rule_id = dra.disb_rule_id
and drv.vendor_id = vId
and drv.vendor_site_id = vSiteid
and drs.stream_type_purpose = nvl((select stream_type_purpose from OKL_STRM_TYPE_B where id = nvl(styId, -1)),nvl(drs.stream_type_purpose,'XXX'))
and TRUNC(sysdate) between TRUNC(NVL(drv.start_date, dra.start_date)) and TRUNC(NVL(NVL(drv.end_date, dra.end_date),TRUNC(sysdate)));
l_cnsld_invs(i).cin_rec.LAST_UPDATED_BY := fnd_global.user_id ;
l_cnsld_invs(i).cin_rec.LAST_UPDATE_DATE := sysdate ;
update okl_disb_rule_vendor_sites
set next_inv_seq = l_new_next_inv_seq
where disb_rule_vendor_site_id = r_disb_rules.disb_rule_vendor_site_id
and disb_rule_id = r_disb_rules.disb_rule_id;
l_cnsld_invs(i).tplv_tbl(j).LAST_UPDATED_BY := fnd_global.user_id;
l_cnsld_invs(i).tplv_tbl(j).LAST_UPDATE_DATE := sysdate;
SELECT
TRUNC(okl_pay_invoices_cons_pvt.get_ap_invoice_date(
tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y')) cin_date_invoiced -- | 12-Dec-2007 cklee -- Fixed bug: 6682348 added trunc
,tap.pay_group_lookup_code
,tap.ipvs_id
,tap.ippt_id
,tap.payment_method_code
,tap.currency_code
,tap.currency_conversion_type
,tap.currency_conversion_rate
,TRUNC(tap.currency_conversion_date) currency_conversion_date -- cklee 09/13/07
--futrue release ,okl_pay_invoices_cons_pvt.get_contract_group(
-- tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,chr.contract_number, nvl(tpl.adv_grouping_flag,'Y')) contract_group
,okl_pay_invoices_cons_pvt.get_Disbursement_rule(
tap.date_invoiced,tpl.id,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y') disbursement_rule
,okl_pay_invoices_cons_pvt.get_Disbursement_group(
tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y') disbursement_group
,SUM(tpl.amount) cin_invoice_amount
,povs.vendor_id
,tap.INVOICE_TYPE
,NVL(tap.set_of_books_id, OKL_ACCOUNTING_UTIL.get_set_of_books_id) sob_id
,tap.LEGAL_ENTITY_ID
,tap.VPA_ID
,acc_cr.code_combination_id
,COUNT(1) line_count
FROM
okl_trns_acc_dstrs acc_cr
,okl_txl_ap_inv_lns_b tpl
,okl_trx_ap_invoices_b tap
,po_vendor_sites_all povs
-- ,okc_k_headers_all_b chr -- cklee 09/13/07
,OKL_STRM_TYPE_B sty -- cklee 09/13/07
WHERE
NVL(tap.trx_status_code, 'ENTERED') in ( 'ENTERED', 'APPROVED' ) AND
trunc(tap.date_invoiced) BETWEEN
NVL(p_from_date, trunc(tap.date_invoiced)) AND NVL(p_to_date, trunc(tap.date_invoiced))
AND nvl(acc_cr.cr_dr_flag(+), 'C') = 'C'
AND acc_cr.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
AND tpl.id = acc_cr.source_id (+)
AND tap.id = tpl.tap_id
AND povs.vendor_site_id = tap.ipvs_id
AND tap.FUNDING_TYPE_CODE IS NULL
AND nvl(tpl.khr_id,-1) = nvl(p_contract_id, nvl(tpl.khr_id,-1)) -- cklee 09/13/07
AND nvl(povs.vendor_id,-1) = nvl(p_vendor_id, nvl(povs.vendor_id,-1) )
AND nvl(tap.ipvs_id,-1) = nvl(p_vendor_site_id, nvl(tap.ipvs_id,-1))
-- AND tpl.khr_id = chr.id
AND tpl.sty_id = sty.id
AND nvl(sty.stream_type_purpose, 'xxx') = nvl(p_stream_type_purpose,nvl(sty.stream_type_purpose, 'xxx'))
AND nvl(tap.vpa_id,-1) = nvl(p_vpa_id, nvl(tap.vpa_id,-1))
GROUP BY
povs.vendor_id,
tap.ipvs_id,
tap.pay_group_lookup_code,
tap.payment_method_code,
tap.ippt_id,
NVL(tap.set_of_books_id, OKL_ACCOUNTING_UTIL.get_set_of_books_id),
acc_cr.code_combination_id,
tap.currency_code,
tap.currency_conversion_type,
tap.currency_conversion_rate,
TRUNC(tap.currency_conversion_date), -- cklee 09/13/07
tap.legal_entity_id,
tap.vpa_id,
tap.invoice_type,
okl_pay_invoices_cons_pvt.get_Disbursement_rule(
tap.date_invoiced,tpl.id,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y'),
okl_pay_invoices_cons_pvt.get_Disbursement_group(
tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y'),
TRUNC(okl_pay_invoices_cons_pvt.get_ap_invoice_date(
tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y')) -- | 12-Dec-2007 cklee -- Fixed bug: 6682348 added trunc
UNION ALL
SELECT
TRUNC(okl_pay_invoices_cons_pvt.get_ap_invoice_date(
tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y')) cin_date_invoiced -- | 12-Dec-2007 cklee -- Fixed bug: 6682348 added trunc
,tap.pay_group_lookup_code
,tap.ipvs_id
,tap.ippt_id
,tap.payment_method_code
,tap.currency_code
,tap.currency_conversion_type
,tap.currency_conversion_rate
,TRUNC(tap.currency_conversion_date) currency_conversion_date -- cklee 09/13/07
--futrue release ,okl_pay_invoices_cons_pvt.get_contract_group(
-- tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,chr.contract_number, nvl(tpl.adv_grouping_flag,'Y')) contract_group
,okl_pay_invoices_cons_pvt.get_Disbursement_rule(
tap.date_invoiced,tpl.id,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y') disbursement_rule
,okl_pay_invoices_cons_pvt.get_Disbursement_group(
tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y') disbursement_group
,SUM(tpl.amount) cin_invoice_amount
,povs.vendor_id
,tap.INVOICE_TYPE
,NVL(tap.set_of_books_id, OKL_ACCOUNTING_UTIL.get_set_of_books_id) sob_id
,tap.LEGAL_ENTITY_ID
,tap.VPA_ID
,acc_cr.code_combination_id
,COUNT(1) line_count
FROM
okl_trns_acc_dstrs acc_cr
,okl_txl_ap_inv_lns_b tpl
,okl_trx_ap_invoices_b tap
,po_vendor_sites_all povs
-- ,okc_k_headers_all_b chr -- cklee 09/13/07
,OKL_STRM_TYPE_B sty -- cklee 09/13/07
WHERE
NVL(tap.trx_status_code, 'APPROVED') in ( 'APPROVED') AND
trunc(tap.date_invoiced) BETWEEN
NVL(p_from_date, trunc(tap.date_invoiced)) AND NVL(p_to_date, trunc(tap.date_invoiced))
AND nvl(acc_cr.cr_dr_flag(+), 'C') = 'C'
AND acc_cr.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
AND tpl.id = acc_cr.source_id (+)
AND tap.id = tpl.tap_id
AND povs.vendor_site_id = tap.ipvs_id
AND tap.FUNDING_TYPE_CODE IS NOT NULL
AND nvl(tpl.khr_id,-1) = nvl(p_contract_id, nvl(tpl.khr_id,-1)) -- cklee 09/13/07
AND nvl(tap.vendor_id,-1) = nvl(p_vendor_id, nvl(tap.vendor_id,-1) )
AND nvl(tap.ipvs_id,-1) = nvl(p_vendor_site_id, nvl(tap.ipvs_id,-1))
-- AND tpl.khr_id = chr.id
AND tpl.sty_id = sty.id
AND nvl(sty.stream_type_purpose, 'xxx') = nvl(p_stream_type_purpose,nvl(sty.stream_type_purpose, 'xxx'))
AND nvl(tap.vpa_id,-1) = nvl(p_vpa_id, nvl(tap.vpa_id,-1))
GROUP BY
povs.vendor_id,
tap.ipvs_id,
tap.pay_group_lookup_code,
tap.payment_method_code,
tap.ippt_id,
NVL(tap.set_of_books_id, OKL_ACCOUNTING_UTIL.get_set_of_books_id),
acc_cr.code_combination_id,
tap.currency_code,
tap.currency_conversion_type,
tap.currency_conversion_rate,
TRUNC(tap.currency_conversion_date), -- cklee 09/13/07
tap.legal_entity_id,
tap.vpa_id,
tap.invoice_type,
okl_pay_invoices_cons_pvt.get_Disbursement_rule(
tap.date_invoiced,tpl.id,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y'),
okl_pay_invoices_cons_pvt.get_Disbursement_group(
tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y'),
TRUNC(okl_pay_invoices_cons_pvt.get_ap_invoice_date(
tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y')); -- | 12-Dec-2007 cklee -- Fixed bug: 6682348 added trunc
SELECT
tap.id tap_id
,NVL(tap.set_of_books_id, OKL_ACCOUNTING_UTIL.get_set_of_books_id) sob_id
,tap.org_id
,tap.invoice_number
,nvl(tap.vendor_invoice_number, tap.invoice_number) vendor_invoice_number
,tap.code_combination_id tap_ccid
,tap.date_invoiced date_invoiced
,tap.pay_group_lookup_code
,tap.ipvs_id
,tap.ippt_id
,tap.payment_method_code
,tap.currency_code
,tap.currency_conversion_type
,tap.currency_conversion_rate
,TRUNC(tap.currency_conversion_date) currency_conversion_date -- cklee 09/13/07
,tpl.khr_id
,tap.try_id
,tpl.id tpl_id
,tpl.sty_id
,acc_db.code_combination_id db_ccid
,acc_cr.code_combination_id cr_ccid
,povs.vendor_id
,tap.INVOICE_TYPE
,tap.DATE_GL
,tap.LEGAL_ENTITY_ID
,tap.VPA_ID
-- ,nvl(tpl.adv_grouping_flag, 'Y') adv_grouping_flag -- cklee 09/14/07
,sty.stream_type_purpose -- cklee 09/14/07
FROM
okl_trns_acc_dstrs acc_db
,okl_trns_acc_dstrs acc_cr
,po_vendor_sites_all povs
,okl_txl_ap_inv_lns_b tpl
,okl_trx_ap_invoices_b tap
-- ,okc_k_headers_all_b chr -- cklee 09/13/07
,OKL_STRM_TYPE_B sty -- cklee 09/13/07
WHERE
NVL(tap.trx_status_code, 'ENTERED') in ( 'ENTERED', 'APPROVED' )
AND trunc(tap.date_invoiced) BETWEEN
NVL(p_from_date, trunc(tap.date_invoiced)) AND NVL(p_to_date, trunc(tap.date_invoiced))
AND nvl(acc_db.cr_dr_flag(+), 'D') = 'D' --ssiruvol 05May2007
AND nvl(acc_cr.cr_dr_flag(+), 'C') = 'C' --ssiruvol 05May2007
AND acc_db.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
AND acc_cr.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
AND tpl.id = acc_db.source_id (+)
AND tpl.id = acc_cr.source_id (+)
AND povs.vendor_site_id = tap.ipvs_id
AND tap.id = tpl.tap_id
AND tap.FUNDING_TYPE_CODE IS NULL
-- AND tpl.khr_id = chr.id
AND tpl.sty_id = sty.id
and tpl.khr_id = nvl(p_contract_id, tpl.khr_id) -- cklee 09/13/07
and sty.stream_type_purpose = nvl(p_stream_type_purpose, sty.stream_type_purpose)
and povs.vendor_id = p_vendor_id
and tap.ipvs_id = p_ipvs_id
and NVL(tap.pay_group_lookup_code,'x') = NVL(p_pay_group_lookup_code,'x')
and tap.payment_method_code = p_payment_method_code
and NVL(tap.ippt_id,-123) = NVL(p_ippt_id,-123)
and NVL(tap.set_of_books_id, OKL_ACCOUNTING_UTIL.get_set_of_books_id) = p_set_of_books_id
and nvl(acc_cr.code_combination_id, -1) = nvl(p_code_combination_id, nvl(acc_cr.code_combination_id, -1))
and tap.currency_code = p_currency_code
and nvl(tap.currency_conversion_type, 'x') = nvl(p_currency_conversion_type,nvl(tap.currency_conversion_type, 'x'))
and nvl(tap.currency_conversion_rate, -1) = nvl(p_currency_conversion_rate,nvl(tap.currency_conversion_rate, -1))
and nvl(TRUNC(tap.currency_conversion_date), trunc(sysdate)) = nvl(TRUNC(p_currency_conversion_date),nvl(TRUNC(tap.currency_conversion_date), trunc(sysdate))) -- | 12-Dec-2007 cklee -- Fixed bug: 6682348 added trunc
and tap.legal_entity_id = p_legal_entity_id
and nvl(tap.vpa_id,-1) = nvl(p_vpa_id, nvl(tap.vpa_id,-1))
and nvl(tap.invoice_type, 'x') = nvl(p_invoice_type,nvl(tap.invoice_type, 'x'))
and okl_pay_invoices_cons_pvt.get_Disbursement_rule(
tap.date_invoiced,tpl.id, povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y')
= p_disbursement_rule
and okl_pay_invoices_cons_pvt.get_Disbursement_group(
tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y')
= p_disbursement_group
-- and nvl(okl_pay_invoices_cons_pvt.get_contract_group(
-- tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,chr.contract_number, nvl(tpl.adv_grouping_flag,'Y')), 'x')
-- = nvl(p_contract_group, 'x')
and TRUNC(okl_pay_invoices_cons_pvt.get_ap_invoice_date(
tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y')) -- | 12-Dec-2007 cklee -- Fixed bug: 6682348 added trunc
= p_cin_date_invoiced
UNION ALL
SELECT
tap.id tap_id
,NVL(tap.set_of_books_id, OKL_ACCOUNTING_UTIL.get_set_of_books_id) sob_id
,tap.org_id
,tap.invoice_number
,nvl(tap.vendor_invoice_number, tap.invoice_number) vendor_invoice_number
,tap.code_combination_id tap_ccid
,tap.date_invoiced date_invoiced
,tap.pay_group_lookup_code
,tap.ipvs_id
,tap.ippt_id
,tap.payment_method_code
,tap.currency_code
,tap.currency_conversion_type
,tap.currency_conversion_rate
,TRUNC(tap.currency_conversion_date) currency_conversion_date -- cklee 09/13/07
,tpl.khr_id
,tap.try_id
,tpl.id tpl_id
,tpl.sty_id
,acc_db.code_combination_id db_ccid
,acc_cr.code_combination_id cr_ccid
,povs.vendor_id
,tap.INVOICE_TYPE
,tap.DATE_GL
,tap.LEGAL_ENTITY_ID
,tap.VPA_ID
-- ,nvl(tpl.adv_grouping_flag, 'Y') adv_grouping_flag -- cklee 09/14/07
,sty.stream_type_purpose -- cklee 09/14/07
FROM
okl_trns_acc_dstrs acc_db
,okl_trns_acc_dstrs acc_cr
,po_vendor_sites_all povs
,okl_txl_ap_inv_lns_b tpl
,okl_trx_ap_invoices_b tap
-- ,okc_k_headers_all_b chr -- cklee 09/13/07
,OKL_STRM_TYPE_B sty -- cklee 09/13/07
WHERE
NVL(tap.trx_status_code, 'ENTERED') in ( 'ENTERED', 'APPROVED' )
AND trunc(tap.date_invoiced) BETWEEN
NVL(p_from_date, trunc(tap.date_invoiced)) AND NVL(p_to_date, trunc(tap.date_invoiced))
AND nvl(acc_db.cr_dr_flag(+), 'D') = 'D' --ssiruvol 05May2007
AND nvl(acc_cr.cr_dr_flag(+), 'C') = 'C' --ssiruvol 05May2007
AND acc_db.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
AND acc_cr.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
AND tpl.id = acc_db.source_id (+)
AND tpl.id = acc_cr.source_id (+)
AND povs.vendor_site_id = tap.ipvs_id
AND tap.id = tpl.tap_id
AND tap.FUNDING_TYPE_CODE IS NOT NULL
-- AND tpl.khr_id = chr.id
AND tpl.sty_id = sty.id
and tpl.khr_id = nvl(p_contract_id, tpl.khr_id) -- cklee 09/13/07
and sty.stream_type_purpose = nvl(p_stream_type_purpose, sty.stream_type_purpose)
and povs.vendor_id = p_vendor_id
and tap.ipvs_id = p_ipvs_id
and NVL(tap.pay_group_lookup_code,'x') = NVL(p_pay_group_lookup_code,'x')
and tap.payment_method_code = p_payment_method_code
and NVL(tap.ippt_id,-123) = NVL(p_ippt_id,-123)
and NVL(tap.set_of_books_id, OKL_ACCOUNTING_UTIL.get_set_of_books_id) = p_set_of_books_id
and nvl(acc_cr.code_combination_id, -1) = nvl(p_code_combination_id, nvl(acc_cr.code_combination_id, -1))
and tap.currency_code = p_currency_code
and nvl(tap.currency_conversion_type, 'x') = nvl(p_currency_conversion_type,nvl(tap.currency_conversion_type, 'x'))
and nvl(tap.currency_conversion_rate, -1) = nvl(p_currency_conversion_rate,nvl(tap.currency_conversion_rate, -1))
and nvl(TRUNC(tap.currency_conversion_date), trunc(sysdate)) = nvl(TRUNC(p_currency_conversion_date),nvl(TRUNC(tap.currency_conversion_date), trunc(sysdate))) -- -- | 12-Dec-2007 cklee -- Fixed bug: 6682348 added trunc
and tap.legal_entity_id = p_legal_entity_id
and nvl(tap.vpa_id,-1) = nvl(p_vpa_id, nvl(tap.vpa_id,-1))
and nvl(tap.invoice_type, 'x') = nvl(p_invoice_type,nvl(tap.invoice_type, 'x'))
and okl_pay_invoices_cons_pvt.get_Disbursement_rule(
tap.date_invoiced,tpl.id,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y')
= p_disbursement_rule
and okl_pay_invoices_cons_pvt.get_Disbursement_group(
tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y')
= p_disbursement_group
-- and nvl(okl_pay_invoices_cons_pvt.get_contract_group(
-- tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,chr.contract_number, nvl(tpl.adv_grouping_flag,'Y')), 'x')
-- = nvl(p_contract_group, 'x')
and TRUNC(okl_pay_invoices_cons_pvt.get_ap_invoice_date(
tap.date_invoiced,povs.vendor_id,tap.ipvs_id,sty.stream_type_purpose,'Y'))
= p_cin_date_invoiced; -- | 12-Dec-2007 cklee -- Fixed bug: 6682348 added trunc
BEGIN -- block to handel trx status update
cin_ln_cnt := 0; -- initial to 0
l_cin_rec.LAST_UPDATED_BY := fnd_global.user_id ;
l_cin_rec.LAST_UPDATE_DATE := sysdate ;
l_cin_rec.REQUEST_ID := Fnd_Global.CONC_REQUEST_ID ; -- 11-Dec-2007 cklee -- Fixed bug: 6682348 -- stamped request_id when insert
OKL_CIN_PVT.insert_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cin_rec => l_cin_rec,
x_cin_rec => lx_cin_rec);
UPDATE okl_txl_ap_inv_lns_all_b txl
SET txl.cnsld_ap_inv_id = lx_cin_rec.cnsld_ap_inv_id,
txl.cnsld_line_number = cin_ln_cnt,
txl.LAST_UPDATED_BY = fnd_global.user_id,
txl.LAST_UPDATE_DATE = sysdate,
txl.REQUEST_ID = Fnd_Global.CONC_REQUEST_ID -- 11-Dec-2007 cklee -- Fixed bug: 6682348 -- stamped request_id when insert/UPDATE
WHERE txl.id = r_invoice.tpl_id;
FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' update okl_txl_ap_inv_lns_all_b with FK: cnsld_ap_inv_id and cnsld_line_number. cin_ln_cnt:<'
|| cin_ln_cnt || '>r_invoice.tpl_id:' ||r_invoice.tpl_id || 'lx_cin_rec.cnsld_ap_inv_id:'|| lx_cin_rec.cnsld_ap_inv_id);
UPDATE OKL_TRX_AP_INVS_ALL_B trx
SET trx.TRX_STATUS_CODE = 'PROCESSED',
trx.REQUEST_ID = Fnd_Global.CONC_REQUEST_ID -- 11-Dec-2007 cklee -- Fixed bug: 6682348 -- stamped request_id when insert/UPDATE
WHERE trx.ID = l_tplv_tbl(k).tap_id
AND trx.TRX_STATUS_CODE IN ('ENTERED', 'APPROVED');
FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' UPDATE OKL_TRX_AP_INVS_ALL_B with Processed status.l_tplv_tbl('
||k||').tap_id'|| l_tplv_tbl(k).tap_id);
UPDATE OKL_TRX_AP_INVS_ALL_B trx
SET trx.TRX_STATUS_CODE = 'ERROR',
trx.REQUEST_ID = Fnd_Global.CONC_REQUEST_ID -- 11-Dec-2007 cklee -- Fixed bug: 6682348 -- stamped request_id when insert/UPDATE
WHERE trx.ID = l_tplv_tbl(j).tap_id
AND trx.TRX_STATUS_CODE IN ('ENTERED', 'APPROVED');
FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' UPDATE OKL_TRX_AP_INVS_ALL_B with Error status.l_tplv_tbl(' ||j||').tap_id'|| l_tplv_tbl(j).tap_id);
SELECT 1
FROM OKL_TRX_AP_INVS_ALL_B TAP
where NVL(TAP.ADV_GROUPING_FLAG, 'Y') = 'N'
and tap.id in
(select txl.tap_id
from okl_txl_ap_invs_all_b txl,
okl_cnsld_ap_invs_all cin
where txl.cnsld_ap_inv_id = tap.cnsld_ap_inv_id
and txl.tap_id = tap.id);
SELECT *
FROM OKL_CNSLD_AP_INVS_ALL
WHERE trx_status_code = 'ENTERED';
2. Update # 1 line FK with #1 consoldiate invoice
3. Assume processing fee is only applicable for ""Standard"" invoice.
4. Mark the OKL internal invoice header status to 'Processed"" once complete the transaction of consoldation."
elsif Fee Option = Per Invoice line then
--The fee is charged as multiple separate lines, one corresponding to each invoice line on the AP invoice with a negative amounts.
-- The amount of fee corresponding to an invoice line = Invoice line amount * %
"1. Create a new OKL internal invoice line with negative amount and associated with the corresponding OKL internal invoice header.
(Assume the processing fee amount < invoice amount.)
2. Update # 1 line FK with #1 consoldiate invoice.
3. Assume processing fee is only applicable for ""Standard"" invoice.
end if;
"1. Find out the propotion amount for each line and update each line.
formula:
new line amount := old line amount - (fixed fee * (line amount / header amount))
2. Update the OKL internal invoice header amount accordingly
formula:
new OKL internal header amount := old header amount - (fixed fee * (line amount / header amount))
3. Update the consolidation invoice header amount acordingly.
formula:
3. new header amount := sum of all line amount (rounding issue may happen)
(Assume the processing fee amount < invoice amount.)
4. Assume processing fee is only applicable for ""Standard"" invoice.
elsif Fee Option = Per Invoice line then
--The fee amount per line is calculated as % of line amount and subtracted from each line
"1. Find out the propotion amount for each line and update each line.
formula:
new line amount := old line amount - (old line amount * fee %)
2. Update the OKL internal invoice header amount accordingly
formula:
new OKL internal header amount := old header amount - (old line amount * fee %)
3. Update the consolidation invoice header amount acordingly.
formula:
3. new header amount := sum of all line amount (rounding issue may happen)
(Assume the processing fee amount < invoice amount.)
4. Assume processing fee is only applicable for ""Standard"" invoice.
end if;
SELECT *
FROM OKL_CNSLD_AP_INVS_ALL
WHERE trx_status_code = 'ENTERED';
SELECT TPL.ID,
TPL.cnsld_line_number,
TPL.inv_distr_line_code,
TPL.org_id,
TPL.ATTRIBUTE_CATEGORY,
TPL.ATTRIBUTE1,
TPL.ATTRIBUTE2,
TPL.ATTRIBUTE3,
TPL.ATTRIBUTE4,
TPL.ATTRIBUTE5,
TPL.ATTRIBUTE6,
TPL.ATTRIBUTE7,
TPL.ATTRIBUTE8,
TPL.ATTRIBUTE9,
TPL.ATTRIBUTE10,
TPL.ATTRIBUTE11,
TPL.ATTRIBUTE12,
TPL.ATTRIBUTE13,
TPL.ATTRIBUTE14,
TPL.ATTRIBUTE15,
DSTRS.AMOUNT,
DSTRS.CODE_COMBINATION_ID,
DSTRS.CR_DR_FLAG
FROM OKL_TXL_AP_INV_LNS_B TPL,
OKL_TRNS_ACC_DSTRS DSTRS
WHERE TPL.CNSLD_AP_INV_ID = p_cnsld_inv_id
AND TPL.ID = DSTRS.SOURCE_ID
AND DSTRS.SOURCE_TABLE = 'OKL_TXL_AP_INV_LNS_B';
SELECT ap_invoices_interface_s.nextval
FROM dual;
l_xpiv_rec.REQUEST_ID := Fnd_Global.CONC_REQUEST_ID ; -- 11-Dec-2007 cklee -- Fixed bug: 6682348 -- stamped request_id when insert
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPICB.pls call OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs ');
OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_xpiv_rec => l_xpiv_rec
,x_xpiv_rec => lx_xpiv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPICB.pls call OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs '|| x_return_status);
l_xlpv_rec.REQUEST_ID := Fnd_Global.CONC_REQUEST_ID ; -- 11-Dec-2007 cklee -- Fixed bug: 6682348 -- stamped request_id when insert
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPICB.pls call okl_xtl_pay_invs_pub.insert_xtl_pay_invs ');
OKL_XTL_PAY_INVS_PUB.insert_xtl_pay_invs(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_xlpv_rec => l_xlpv_rec
,x_xlpv_rec => lx_xlpv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPICB.pls call okl_xtl_pay_invs_pub.insert_xtl_pay_invs '||x_return_status);
-- Update internal AP table for Success
-----------------------------------------------------------------
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Successfully Prepared Invoice Number : ' || r_cnsld_hdr.invoice_number);
UPDATE OKL_CNSLD_AP_INVS
SET TRX_STATUS_CODE = 'PROCESSED'
WHERE CNSLD_AP_INV_ID = r_cnsld_hdr.cnsld_ap_inv_id;
-- Update internal AP table for Error
-----------------------------------------------------------------
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'*=>ERROR: Processing Invoice Number : ' || r_cnsld_hdr.invoice_number);
UPDATE OKL_CNSLD_AP_INVS
SET TRX_STATUS_CODE = 'ERROR'
WHERE CNSLD_AP_INV_ID = r_cnsld_hdr.cnsld_ap_inv_id;
UPDATE OKL_EXT_PAY_INVS_ALL_B
SET TRX_STATUS_CODE = 'ERROR'
WHERE CNSLD_AP_INV_ID = r_cnsld_hdr.cnsld_ap_inv_id;
SELECT
tap.id tap_id
,NVL(tap.set_of_books_id, OKL_ACCOUNTING_UTIL.get_set_of_books_id) sob_id
,tap.org_id
,tap.invoice_category_code
,tap.invoice_number
--,tap.vendor_invoice_number
,nvl(tap.vendor_invoice_number, tap.invoice_number) vendor_invoice_number
,tap.object_version_number
,tap.code_combination_id tap_ccid
,tap.date_invoiced date_invoiced
,tap.pay_group_lookup_code
,tap.ipvs_id
,tap.ippt_id
,tap.payment_method_code
,tap.currency_code
,tap.currency_conversion_type
,tap.currency_conversion_rate
,tap.currency_conversion_date
,tap.workflow_yn
,tap.date_gl gl_date
,tap.nettable_yn
,tap.khr_id
,tap.wait_vendor_invoice_yn
,tap.try_id
,tpl.id tpl_id
,tpl.code_combination_id tpl_ccid
,tpl.sty_id
,acc_db.code_combination_id db_ccid
,acc_cr.code_combination_id cr_ccid
,tpl.amount invoice_amount
,acc_db.amount line_amount
,povs.vendor_id
FROM
okl_trns_acc_dstrs acc_db
,okl_trns_acc_dstrs acc_cr
,po_vendor_sites_all povs
,okl_txl_ap_inv_lns_b tpl
,okl_trx_ap_invoices_b tap
WHERE
NVL(tap.trx_status_code, 'ENTERED') in ( 'ENTERED', 'APPROVED' ) AND
trunc(tap.date_invoiced) BETWEEN
NVL(p_from_date,SYSDATE-10000) AND NVL(p_to_date, SYSDATE+10000)
AND acc_db.cr_dr_flag (+) = 'D'
AND acc_cr.cr_dr_flag (+) = 'C'
AND acc_db.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
AND acc_cr.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
AND tpl.id = acc_db.source_id (+)
AND tpl.id = acc_cr.source_id (+)
AND povs.vendor_site_id = tap.ipvs_id
AND tap.id = tpl.tap_id
AND tap.org_id = NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
AND tap.FUNDING_TYPE_CODE IS NULL
ORDER BY tap.id;
SELECT * FROM (
SELECT
-- NULL tpl_id -- cklee comment out
tap.id tap_id
,NVL(tap.set_of_books_id,
OKL_ACCOUNTING_UTIL.get_set_of_books_id) sob_id
,tap.org_id
,tap.invoice_category_code
,tap.invoice_number
--,tap.vendor_invoice_number
,nvl(tap.vendor_invoice_number, tap.invoice_number) vendor_invoice_number
,tap.object_version_number
,tap.code_combination_id tap_ccid
,tap.date_invoiced date_invoiced
,tap.pay_group_lookup_code
,tap.ipvs_id
,tap.ippt_id
,tap.payment_method_code
,tap.currency_code
,tap.currency_conversion_type
,tap.currency_conversion_rate
,tap.currency_conversion_date
,tap.workflow_yn
,nvl(tap.date_gl,tap.date_invoiced) gl_date
,tap.nettable_yn
,tap.khr_id
,tap.wait_vendor_invoice_yn
,tap.try_id
,acc_db.code_combination_id db_ccid
,acc_cr.code_combination_id cr_ccid
,tap.amount invoice_amount
,acc_db.amount line_amount
,povs.vendor_id
-- ,tap.FUNDING_TYPE_CODE -cklee
FROM
okl_trns_acc_dstrs acc_db
,okl_trns_acc_dstrs acc_cr
,po_vendor_sites_all povs
,okl_trx_ap_invoices_b tap
WHERE
NVL(tap.trx_status_code, 'ENTERED') in ( 'APPROVED' ) AND
trunc(tap.date_invoiced) BETWEEN
NVL(p_from_date,SYSDATE-10000) AND NVL(p_to_date,SYSDATE+10000)
AND acc_db.cr_dr_flag (+) = 'D'
AND acc_cr.cr_dr_flag (+) = 'C'
AND acc_db.source_table (+)= 'OKL_TRX_AP_INVOICES_B'
AND acc_cr.source_table (+)= 'OKL_TRX_AP_INVOICES_B'
AND tap.id = acc_db.source_id (+)
AND tap.id = acc_cr.source_id (+)
AND povs.vendor_site_id = tap.ipvs_id
AND tap.org_id = NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
AND tap.FUNDING_TYPE_CODE IS NOT NULL
--AND tap.FUNDING_TYPE_CODE <> 'ASSET_SUBSIDY'
AND tap.FUNDING_TYPE_CODE not in ('ASSET_SUBSIDY', 'MANUAL_DISB')
UNION
SELECT
-- tpl.id tpl_id -- cklee comment out
tap.id tap_id
,NVL(tap.set_of_books_id,OKL_ACCOUNTING_UTIL.get_set_of_books_id) sob_id
,tap.org_id
,tap.invoice_category_code
,tap.invoice_number
--,tap.vendor_invoice_number
,nvl(tap.vendor_invoice_number, tap.invoice_number) vendor_invoice_number
,tap.object_version_number
,tap.code_combination_id tap_ccid
,tap.date_invoiced date_invoiced
,tap.pay_group_lookup_code
,tap.ipvs_id
,tap.ippt_id
,tap.payment_method_code
,tap.currency_code
,tap.currency_conversion_type
,tap.currency_conversion_rate
,tap.currency_conversion_date
,tap.workflow_yn
,nvl(tap.date_gl,tap.date_invoiced) gl_date
,tap.nettable_yn
,tap.khr_id
,tap.wait_vendor_invoice_yn
,tap.try_id
,acc_db.code_combination_id db_ccid
,acc_cr.code_combination_id cr_ccid
,tap.amount invoice_amount
,acc_db.amount line_amount
,povs.vendor_id
-- ,tap.FUNDING_TYPE_CODE -- cklee
FROM
okl_trns_acc_dstrs acc_db
,okl_trns_acc_dstrs acc_cr
,po_vendor_sites_all povs
,okl_trx_ap_invoices_b tap
,okl_txl_ap_inv_lns_b tpl
WHERE
NVL(tap.trx_status_code, 'ENTERED') in ( 'APPROVED' )
AND trunc(tap.date_invoiced) BETWEEN
NVL(p_from_date,SYSDATE-10000) AND NVL(p_to_date, SYSDATE+10000)
AND acc_db.cr_dr_flag (+) = 'D'
AND acc_cr.cr_dr_flag (+) = 'C'
AND acc_db.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
AND acc_cr.source_table (+)= 'OKL_TXL_AP_INV_LNS_B'
AND tpl.id = acc_db.source_id (+)
AND tpl.id = acc_cr.source_id (+)
AND povs.vendor_site_id = tap.ipvs_id
AND tap.id = tpl.tap_id
AND tap.org_id = NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
--AND tap.FUNDING_TYPE_CODE = 'ASSET_SUBSIDY' ;
SELECT ap_invoices_interface_s.nextval
FROM dual;
SELECT currency_code
,currency_conversion_type
,currency_conversion_rate
,currency_conversion_date
FROM okl_k_headers_full_v
WHERE id = cp_khr_id;
-- Populate and insert external AP invoice header
-----------------------------------------------------------------
l_xpiv_rec.invoice_id := NULL;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPICB.pls call OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs ');
OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_xpiv_rec => l_xpiv_rec
,x_xpiv_rec => lx_xpiv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPICB.pls call OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs ');
-- Populate and Insert external AP invoice Lines
-----------------------------------------------------------------
--l_xlpv_rec.xpi_id_details := lx_xpiv_rec.id;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPICB.pls call okl_xtl_pay_invs_pub.insert_xtl_pay_invs ');
okl_xtl_pay_invs_pub.insert_xtl_pay_invs(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_xlpv_rec => l_xlpv_rec
,x_xlpv_rec => lx_xlpv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPICB.pls call okl_xtl_pay_invs_pub.insert_xtl_pay_invs ');
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPICB.pls call okl_trx_ar_invoices_pub.insert_trx_ar_invoices ');
okl_trx_ar_invoices_pub.insert_trx_ar_invoices (
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_taiv_rec => l_taiv_rec
,x_taiv_rec => lx_taiv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPICB.pls call okl_trx_ar_invoices_pub.insert_trx_ar_invoices ');
-- Insert external AR invoice line
-----------------------------------------------------------------
l_tilv_rec.tai_id := lx_taiv_rec.id;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPICB.pls call okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns ');
okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns (
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_tilv_rec => l_tilv_rec
,x_tilv_rec => lx_tilv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPICB.pls call okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns ');
-- Update internal AP table for Success
-----------------------------------------------------------------
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Successfully Prepared Invoice Number : ' || r_invoice.invoice_number);
UPDATE okl_trx_ap_invoices_b
SET trx_status_code = 'PROCESSED'
WHERE id = r_invoice.tap_id;
-- Update internal AP table for Error
-----------------------------------------------------------------
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'*=>ERROR: Processing Invoice Number : ' || r_invoice.invoice_number);
UPDATE okl_trx_ap_invoices_b
SET trx_status_code = 'ERROR'
WHERE id = r_invoice.tap_id;
UPDATE OKL_EXT_PAY_INVS_b
SET trx_status_code = 'ERROR'
WHERE id = lx_xpiv_rec.id;
UPDATE okl_trx_ar_invoices_b
SET trx_status_code = 'ERROR'
WHERE id = lx_taiv_rec.id;
-- Populate and insert external AP invoice header
-----------------------------------------------------------------
l_xpiv_rec.invoice_id := NULL;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPICB.pls call OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs ');
OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_xpiv_rec => l_xpiv_rec
,x_xpiv_rec => lx_xpiv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPICB.pls call OKL_EXT_PAY_INVS_PUB.insert_ext_pay_invs ');
-- Populate and Insert external AP invoice Lines
-----------------------------------------------------------------
--l_xlpv_rec.xpi_id_details := lx_xpiv_rec.id;
okl_xtl_pay_invs_pub.insert_xtl_pay_invs(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_xlpv_rec => l_xlpv_rec
,x_xlpv_rec => lx_xlpv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPICB.pls call okl_trx_ar_invoices_pub.insert_trx_ar_invoices ');
okl_trx_ar_invoices_pub.insert_trx_ar_invoices (
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_taiv_rec => l_taiv_rec
,x_taiv_rec => lx_taiv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPICB.pls call okl_trx_ar_invoices_pub.insert_trx_ar_invoices ');
-- Insert external AR invoice line
-----------------------------------------------------------------
l_tilv_rec.tai_id := lx_taiv_rec.id;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPICB.pls call okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns ');
okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns (
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_tilv_rec => l_tilv_rec
,x_tilv_rec => lx_tilv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPICB.pls call okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns ');
-- Update internal AP table for Success
-----------------------------------------------------------------
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Successfully Prepared Invoice Number : ' || fund_rec.invoice_number);
UPDATE okl_trx_ap_invoices_b
SET trx_status_code = 'PROCESSED'
WHERE id = fund_rec.tap_id;
-- Update internal AP table for Error
-----------------------------------------------------------------
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'*=>ERROR: Processing Invoice Number : ' || fund_rec.invoice_number);
UPDATE okl_trx_ap_invoices_b
SET trx_status_code = 'ERROR'
WHERE id = fund_rec.tap_id;
UPDATE OKL_EXT_PAY_INVS_b
SET trx_status_code = 'ERROR'
WHERE id = lx_xpiv_rec.id;
UPDATE okl_trx_ar_invoices_b
SET trx_status_code = 'ERROR'
WHERE id = lx_taiv_rec.id;
SELECT ap_invoices_interface_s.nextval
FROM dual;
SELECT currency_code
,currency_conversion_type
,currency_conversion_rate
,currency_conversion_date
FROM okl_k_headers_full_v
WHERE id = cp_khr_id;
select id
from okc_K_headers_b
where contract_number = cNum;
select vendor_id
from po_vendors
where vendor_name = nvl(vendorN, 'XX');
select vendor_site_id
from po_vendor_sites_all
where vendor_id = nvl(vId, -1)
and vendor_site_code = nvl(VSite, 'XX');
select account_derivation
from okl_sys_acct_opts;
OKL_CIN_PVT.insert_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cin_rec => l_cin_rec,
x_cin_rec => lx_cin_rec);
OKL_TPL_PVT.update_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tplv_tbl => l_tplv_tbl,
x_tplv_tbl => lx_tplv_tbl);
UPDATE OKL_TRX_AP_INVS_ALL_B
SET TRX_STATUS_CODE = 'PROCESSED'
WHERE ID = l_tplv_tbl(j).tap_id;
UPDATE OKL_TRX_AP_INVS_ALL_B
SET TRX_STATUS_CODE = 'ERROR'
WHERE ID = l_tplv_tbl(j).tap_id;
select id
from okc_k_headers_all_b
where contract_number = cNum;
select pv.vendor_id
from po_vendors pv
where pv.vendor_name = vendorN;
select vendor_site_id
from po_vendor_sites_all povs
where povs.vendor_site_code = VSite;