[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MIN(ste.stream_element_date)
FROM OKL_STRM_ELEMENTS ste,
OKL_STREAMS stm,
okl_strm_type_v sty,
okc_k_headers_b khr,
OKL_K_HEADERS khl,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls
WHERE ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND khr.id = stm.khr_id
-- changed by zrehman for Bug#6788005 start
AND ((khr.scs_code IN ('LEASE', 'LOAN') AND khl.deal_type IS NOT NULL) OR (khr.scs_code = 'INVESTOR'))
-- changed by zrehman for Bug#6788005 start
--AND khr.sts_code IN ( 'BOOKED','EVERGREEN','TERMINATED')
AND khr.id = p_khr_id
AND khl.id = stm.khr_id
--AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND kle.id (+) = stm.kle_id
AND kls.code (+) = kle.sts_code;
select nbd.khr_id
from okl_k_control nbd,
okl_k_headers_full_v khr
where nbd.khr_id = khr.id
and khr.id = p_khr_id;
l_last_updated_by okl_k_control.last_updated_by%TYPE := Fnd_Global.USER_ID;
l_last_update_login okl_k_control.last_update_login%TYPE := Fnd_Global.LOGIN_ID;
update okl_k_control
set EARLIEST_STRM_BILL_DATE = l_next_bill_date,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login,
REQUEST_ID = l_request_id,
PROGRAM_ID = l_program_id,
PROGRAM_UPDATE_DATE = sysdate
where khr_id = l_khr_id;
INSERT INTO okl_k_control
(
khr_id, EARLIEST_STRM_BILL_DATE,
CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,
REQUEST_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE
)
VALUES
( p_khr_id, l_next_bill_date,
l_last_updated_by,sysdate,sysdate,l_last_updated_by,l_last_update_login,
l_request_id,l_program_id,sysdate
);
select count(*)
from fnd_concurrent_requests req,
fnd_concurrent_programs_vl pgm
where pgm.USER_CONCURRENT_PROGRAM_NAME
IN ( p_conc_pgm1, p_conc_pgm2)
and req.concurrent_program_id = pgm.concurrent_program_id
and req.PHASE_CODE <> 'C';
l_last_updated_by okl_parallel_processes.last_updated_by%TYPE := Fnd_Global.USER_ID;
l_last_update_login okl_parallel_processes.last_update_login%TYPE := Fnd_Global.LOGIN_ID;
SELECT Name org_name, organization_id org_id
FROM hr_operating_units
WHERE mo_global.check_access(organization_id) = 'Y'; --MOAC
fnd_file.put_line(fnd_file.log,'** START: Delete all Stream Billing records from OKL_PARALLEL_PROCESSES. **');
fnd_file.put_line(fnd_file.log,' => Could not perform a delete all for Stream Billing '
||'records because not all requests have Completed.');
delete from okl_parallel_processes
where OBJECT_TYPE = 'CONTRACT';
fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
fnd_file.put_line(fnd_file.log,'** END: Delete all Stream Billing records from OKL_PARALLEL_PROCESSES. **');
fnd_file.put_line(fnd_file.log,'** START: Delete Org specific Stream Billing records from OKL_PARALLEL_PROCESSES. **');
delete from okl_parallel_processes
where OBJECT_TYPE = 'CONTRACT'
and org_id = l_org_id
and request_id in (
select req.request_id
from fnd_concurrent_requests req,
fnd_concurrent_programs_vl pgm
where pgm.USER_CONCURRENT_PROGRAM_NAME IN ( 'Master Program -- Process Billable Streams',
'Process Billable Streams')
and req.concurrent_program_id = pgm.concurrent_program_id
and req.PHASE_CODE = 'C');
fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
fnd_file.put_line(fnd_file.log,'** END: Delete Org specific Stream Billing records from OKL_PARALLEL_PROCESSES. **');
fnd_file.put_line(fnd_file.log,'** START: Delete all Consolidation records from OKL_PARALLEL_PROCESSES. **');
fnd_file.put_line(fnd_file.log,' => Could not perform a delete all for Consolidation '
||'records because not all requests have Completed.');
delete from okl_parallel_processes
where OBJECT_TYPE = 'CUSTOMER';
fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
fnd_file.put_line(fnd_file.log,'** END: Delete all Consolidation records from OKL_PARALLEL_PROCESSES. **');
fnd_file.put_line(fnd_file.log,'** START: Delete Org specific Consolidation records from OKL_PARALLEL_PROCESSES. **');
delete from okl_parallel_processes
where OBJECT_TYPE = 'CUSTOMER'
and org_id = l_org_id
and request_id in (
select req.request_id
from fnd_concurrent_requests req,
fnd_concurrent_programs_vl pgm
where pgm.USER_CONCURRENT_PROGRAM_NAME IN ( 'Master Program -- Receivables Bills Consolidation',
'Receivables Bills Consolidation')
and req.concurrent_program_id = pgm.concurrent_program_id
and req.PHASE_CODE = 'C');
fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
fnd_file.put_line(fnd_file.log,'** END: Delete Org specific Consolidation records from OKL_PARALLEL_PROCESSES. **');
fnd_file.put_line(fnd_file.log,'** START: Delete all Transfer records from OKL_PARALLEL_PROCESSES. **');
fnd_file.put_line(fnd_file.log,' => Could not perform a delete all for Transfer '
||'records because not all requests have Completed.');
delete from okl_parallel_processes
where OBJECT_TYPE = 'XTRX_CONTRACT';
fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
fnd_file.put_line(fnd_file.log,'** END: Delete all Transfer records from OKL_PARALLEL_PROCESSES. **');
fnd_file.put_line(fnd_file.log,'** START: Delete Org Specific Transfer records from OKL_PARALLEL_PROCESSES. **');
delete from okl_parallel_processes
where OBJECT_TYPE = 'XTRX_CONTRACT'
and org_id = l_org_id
and request_id in (
select req.request_id
from fnd_concurrent_requests req,
fnd_concurrent_programs_vl pgm
where pgm.USER_CONCURRENT_PROGRAM_NAME IN ( 'Master Program -- Receivables Invoice Transfer',
'Receivables Invoice Transfer to AR')
and req.concurrent_program_id = pgm.concurrent_program_id
and req.PHASE_CODE = 'C');
fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
fnd_file.put_line(fnd_file.log,'** END: Delete Org Specific Transfer records from OKL_PARALLEL_PROCESSES. **');
Fnd_File.put_line(Fnd_File.LOG,'** START: Delete all Prepare Receivables records from OKL_PARALLEL_PROCESSES. **');
Fnd_File.put_line(Fnd_File.LOG,' => Could not perform a delete all for Prepare Receivables '
||'records because not all requests have Completed.');
DELETE FROM okl_parallel_processes
WHERE OBJECT_TYPE = 'PREP_CONTRACT';
Fnd_File.put_line(Fnd_File.LOG,' => Deleted '||SQL%rowcount||' row(s).');
Fnd_File.put_line(Fnd_File.LOG,'** END: Delete all Prepare Receivables records from OKL_PARALLEL_PROCESSES. **');
Fnd_File.put_line(Fnd_File.LOG,'** START: Delete Org Specific Prepare Receivables records from OKL_PARALLEL_PROCESSES. **');
DELETE FROM okl_parallel_processes
WHERE OBJECT_TYPE = 'PREP_CONTRACT'
AND org_id = l_org_id
AND request_id IN (
SELECT req.request_id
FROM fnd_concurrent_requests req,
fnd_concurrent_programs_vl pgm
WHERE pgm.USER_CONCURRENT_PROGRAM_NAME IN ( 'Master Program -- Prepare Receivables',
'Prepare Receivables Bills')
AND req.concurrent_program_id = pgm.concurrent_program_id
AND req.PHASE_CODE = 'C');
Fnd_File.put_line(Fnd_File.LOG,' => Deleted '||SQL%rowcount||' row(s).');
Fnd_File.put_line(Fnd_File.LOG,'** END: Delete Org Specific Prepare Receivables records from OKL_PARALLEL_PROCESSES. **');
fnd_file.put_line(fnd_file.log,'** START: Delete all Stream Billing records from OKL_PARALLEL_PROCESSES. **');
fnd_file.put_line(fnd_file.log,' => Could not perform a delete all for Stream Billing '
||'records because not all requests have Completed.');
delete from okl_parallel_processes
where OBJECT_TYPE = 'CONTRACT';
fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
fnd_file.put_line(fnd_file.log,'** END: Delete all Stream Billing records from OKL_PARALLEL_PROCESSES. **');
fnd_file.put_line(fnd_file.log,'** START: Delete Org specific Stream Billing records from OKL_PARALLEL_PROCESSES. **');
delete from okl_parallel_processes
where OBJECT_TYPE = 'CONTRACT'
and org_id = l_org_id
and request_id in (
select req.request_id
from fnd_concurrent_requests req,
fnd_concurrent_programs_vl pgm
where pgm.USER_CONCURRENT_PROGRAM_NAME IN ( 'Master Program -- Process Billable Streams',
'Process Billable Streams')
and req.concurrent_program_id = pgm.concurrent_program_id
and req.PHASE_CODE = 'C');
fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
fnd_file.put_line(fnd_file.log,'** END: Delete Org specific Stream Billing records from OKL_PARALLEL_PROCESSES. **');
fnd_file.put_line(fnd_file.log,'** START: Delete all Consolidation records from OKL_PARALLEL_PROCESSES. **');
fnd_file.put_line(fnd_file.log,' => Could not perform a delete all for Consolidation '
||'records because not all requests have Completed.');
delete from okl_parallel_processes
where OBJECT_TYPE = 'CUSTOMER';
fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
fnd_file.put_line(fnd_file.log,'** END: Delete all Consolidation records from OKL_PARALLEL_PROCESSES. **');
fnd_file.put_line(fnd_file.log,'** START: Delete Org specific Consolidation records from OKL_PARALLEL_PROCESSES. **');
delete from okl_parallel_processes
where OBJECT_TYPE = 'CUSTOMER'
and org_id = l_org_id
and request_id in (
select req.request_id
from fnd_concurrent_requests req,
fnd_concurrent_programs_vl pgm
where pgm.USER_CONCURRENT_PROGRAM_NAME IN ( 'Master Program -- Receivables Bills Consolidation',
'Receivables Bills Consolidation')
and req.concurrent_program_id = pgm.concurrent_program_id
and req.PHASE_CODE = 'C');
fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
fnd_file.put_line(fnd_file.log,'** END: Delete Org specific Consolidation records from OKL_PARALLEL_PROCESSES. **');
fnd_file.put_line(fnd_file.log,'** START: Delete all Transfer records from OKL_PARALLEL_PROCESSES. **');
fnd_file.put_line(fnd_file.log,' => Could not perform a delete all for Transfer '
||'records because not all requests have Completed.');
delete from okl_parallel_processes
where OBJECT_TYPE = 'XTRX_CONTRACT';
fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
fnd_file.put_line(fnd_file.log,'** END: Delete all Transfer records from OKL_PARALLEL_PROCESSES. **');
fnd_file.put_line(fnd_file.log,'** START: Delete Org Specific Transfer records from OKL_PARALLEL_PROCESSES. **');
delete from okl_parallel_processes
where OBJECT_TYPE = 'XTRX_CONTRACT'
and org_id = l_org_id
and request_id in (
select req.request_id
from fnd_concurrent_requests req,
fnd_concurrent_programs_vl pgm
where pgm.USER_CONCURRENT_PROGRAM_NAME IN ( 'Master Program -- Receivables Invoice Transfer',
'Receivables Invoice Transfer to AR')
and req.concurrent_program_id = pgm.concurrent_program_id
and req.PHASE_CODE = 'C');
fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
fnd_file.put_line(fnd_file.log,'** END: Delete Org Specific Transfer records from OKL_PARALLEL_PROCESSES. **');
Fnd_File.put_line(Fnd_File.LOG,'** START: Delete all Prepare Receivables records from OKL_PARALLEL_PROCESSES. **');
Fnd_File.put_line(Fnd_File.LOG,' => Could not perform a delete all for Prepare Receivables '
||'records because not all requests have Completed.');
DELETE FROM okl_parallel_processes
WHERE OBJECT_TYPE = 'PREP_CONTRACT';
Fnd_File.put_line(Fnd_File.LOG,' => Deleted '||SQL%rowcount||' row(s).');
Fnd_File.put_line(Fnd_File.LOG,'** END: Delete all Prepare Receivables records from OKL_PARALLEL_PROCESSES. **');
Fnd_File.put_line(Fnd_File.LOG,'** START: Delete Org Specific Prepare Receivables records from OKL_PARALLEL_PROCESSES. **');
DELETE FROM okl_parallel_processes
WHERE OBJECT_TYPE = 'PREP_CONTRACT'
AND org_id = l_org_id
AND request_id IN (
SELECT req.request_id
FROM fnd_concurrent_requests req,
fnd_concurrent_programs_vl pgm
WHERE pgm.USER_CONCURRENT_PROGRAM_NAME IN ( 'Master Program -- Prepare Receivables',
'Prepare Receivables Bills')
AND req.concurrent_program_id = pgm.concurrent_program_id
AND req.PHASE_CODE = 'C');
Fnd_File.put_line(Fnd_File.LOG,' => Deleted '||SQL%rowcount||' row(s).');
Fnd_File.put_line(Fnd_File.LOG,'** END: Delete Org Specific Prepare Receivables records from OKL_PARALLEL_PROCESSES. **');
Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Number of Deleted Stream Billing Records: '||l_print_strm_cnt);
Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Number of Deleted AR Transfer Records : '||l_print_xfer_cnt);
cursor chk_update_header_csr ( p_date date, orgId VARCHAr2 ) is -- rmunjulu R12 Forward Port missing on R12 (oklh bug 4728636)
-- -- bug# 5872306 (ssiruvol)
SELECT khr.contract_number contract_number, khr.id khr_id
FROM okc_k_headers_b khr, -- rmunjulu R12 Forward Port missing on R12 (oklh bug 4728636)
okl_k_control nbd -- rmunjulu R12 Forward Port missing on R12 (oklh bug 4728636)
WHERE ((p_contract_number is not null and KHR.CONTRACT_NUMBER=p_contract_number)
or (p_contract_number is null and KHR.CONTRACT_NUMBER=KHR.CONTRACT_NUMBER))
AND ((p_cust_acct_id is not null and KHR.CUST_ACCT_ID=p_cust_acct_id)
or (p_cust_acct_id is null and KHR.CUST_ACCT_ID=KHR.CUST_ACCT_ID))
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN','TERMINATED', 'EXPIRED') -- Bug 6472228 added - Expired status
AND p_source = 'BILL_STREAMS'
AND khr.id = nbd.khr_id -- rmunjulu R12 Forward Port missing on R12 (oklh bug 4728636)
-- rmunjulu R12 Forward Port missing on R12 (oklh bug 4728636) + fixes for bug 5634652 logic to check for print lead days
AND nbd.earliest_strm_bill_date <= (NVL(p_date, SYSDATE) +
--Bug# 7701159 - Susbtitute SQL query for PL/SQL call
NVL( NVL(
(SELECT RULE.RULE_INFORMATION3 FROM OKC_RULES_B RULE, OKC_RULE_GROUPS_B RGP
WHERE RGP.ID = RULE.RGP_ID AND RGP.DNZ_CHR_ID = KHR.ID
AND RGP.RGD_CODE = 'LABILL' AND RULE.RULE_INFORMATION_CATEGORY = 'LAINVD'
),
(SELECT TERM.PRINTING_LEAD_DAYS FROM HZ_CUSTOMER_PROFILES CP,
RA_TERMS TERM where KHR.BILL_TO_SITE_USE_ID = CP.SITE_USE_ID AND CP.STANDARD_TERMS = TERM.TERM_ID)
),0)
)
-- OKL_STREAM_BILLING_PVT.get_printing_lead_days(khr.id)) -- Added for Bug#6794547
AND KHR.authoring_org_id = NVL(TO_NUMBER(orgId),-99)
AND nvl(p_ia_contract_type, L_CONTRACT_TYPE) = L_CONTRACT_TYPE
AND NOT EXISTS
(SELECT '1'
FROM OKL_PARALLEL_PROCESSES opp
WHERE khr.contract_number = opp.object_value
AND opp.object_type = 'CONTRACT'
AND opp.process_status in ('PENDING_ASSIGNMENT', 'ASSIGNED'))
--fmiao 5209209 change
-- rmunjulu R12 fixes - comment out Prepare Recvbles
/* UNION
--3 levels
SELECT khr.contract_number contract_number, khr.id khr_id
FROM okc_k_headers_b khr
WHERE id IN (
SELECT CHR.id
FROM okl_txd_ar_ln_dtls_b tld,
okl_txl_ar_inv_lns_b til,
okl_trx_ar_invoices_b tai,
okl_strm_type_v sty,
okc_k_headers_b CHR
WHERE tai.trx_status_code = 'SUBMITTED'
AND tai.khr_id = CHR.id
AND til.tai_id = tai.id
AND tld.til_id_details = til.id
AND sty.id = tld.sty_id
AND p_source = 'AR_PREPARE'
AND NOT EXISTS
(SELECT '1'
FROM OKL_PARALLEL_PROCESSES opp
WHERE CHR.contract_number = opp.object_value
AND opp.object_type = 'PREP_CONTRACT'
AND opp.process_status IN ('PENDING_ASSIGNMENT', 'ASSIGNED')) )
GROUP BY khr.contract_number, khr.id
UNION
-- 2 levels
SELECT khr.contract_number contract_number, khr.id khr_id
FROM okc_k_headers_b khr
WHERE id IN (
SELECT CHR.id
FROM okl_txl_ar_inv_lns_b til,
okl_trx_ar_invoices_b tai,
okl_strm_type_v sty,
okc_k_headers_b CHR
WHERE tai.trx_status_code = 'SUBMITTED'
AND tai.khr_id = CHR.id
AND til.tai_id = tai.id
AND til.sty_id = sty.id
AND p_source = 'AR_PREPARE'
AND NOT EXISTS
(SELECT *
FROM okl_txd_ar_ln_dtls_b tld
WHERE tld.til_id_details = til.id
)
AND NOT EXISTS
(SELECT '1'
FROM OKL_PARALLEL_PROCESSES opp
WHERE CHR.contract_number = opp.object_value
AND opp.object_type = 'PREP_CONTRACT'
AND opp.process_status IN ('PENDING_ASSIGNMENT', 'ASSIGNED')) )
GROUP BY khr.contract_number, khr.id
--fmiao 5209209 end
*/
-- rmunjulu R12 Fixes modify AR Transfer
UNION
SELECT khr.contract_number contract_number, khr.id khr_id
FROM okc_k_headers_b khr
WHERE id in (
SELECT TAI.khr_id -- rmunjulu R12 fixes - changed to TAI
FROM --okl_ext_sell_invs_b xsi, -- rmunjulu R12 fixes - commented
--okl_xtl_sell_invs_b xls, -- rmunjulu R12 fixes - commented
--okl_txd_ar_ln_dtls_b tld,-- rmunjulu R12 fixes - commented
--okl_txl_ar_inv_lns_b til,-- rmunjulu R12 fixes - commented
okl_trx_ar_invoices_b tai,
okc_k_headers_b chr-- rmunjulu R12 fixes - commented
WHERE tai.TRX_STATUS_CODE = 'SUBMITTED' -- rmunjulu R12 fixes - changed to TAI + SUBMITTED
--AND XSI.ID = XLS.XSI_ID_DETAILS -- rmunjulu R12 fixes - commented
--AND xls.tld_id = tld.id -- rmunjulu R12 fixes - commented
--and tld.til_id_details = til.id -- rmunjulu R12 fixes - commented
--and til.tai_id = tai.id -- rmunjulu R12 fixes - commented
and tai.khr_id = chr.id
AND p_source = 'AR_TRANSFER'
AND NOT EXISTS
(SELECT '1'
FROM OKL_PARALLEL_PROCESSES opp
WHERE chr.contract_number = opp.object_value
AND opp.object_type = 'XTRX_CONTRACT'
AND opp.process_status in ('PENDING_ASSIGNMENT', 'ASSIGNED')) )
group by khr.contract_number, khr.id
-- rmunjulu R12 Fixes -- below select not needed as will be same as above select
/*
UNION
SELECT khr.contract_number contract_number, khr.id khr_id
FROM okc_k_headers_b khr
WHERE id in (
SELECT chr.id
FROM okl_ext_sell_invs_b xsi,
okl_xtl_sell_invs_b xls,
okl_txl_ar_inv_lns_b til,
okl_trx_ar_invoices_b tai,
okc_k_headers_b chr
WHERE XSI.TRX_STATUS_CODE = 'WORKING'
AND XSI.ID = XLS.XSI_ID_DETAILS
AND xls.til_id = til.id
and til.tai_id = tai.id
and tai.khr_id = chr.id
AND p_source = 'AR_TRANSFER'
AND NOT EXISTS
(SELECT '1'
FROM OKL_PARALLEL_PROCESSES opp
WHERE chr.contract_number = opp.object_value
AND opp.object_type = 'XTRX_CONTRACT'
AND opp.process_status in ('PENDING_ASSIGNMENT', 'ASSIGNED')) )
group by khr.contract_number, khr.id
*/
-- rmunjulu R12 Fixes -- comment out Consolidation
/*
UNION
SELECT to_char(CUSTOMER_ID), null khr_id
FROM OKL_EXT_SELL_INVS_B ext
WHERE ext.TRX_STATUS_CODE = 'SUBMITTED'
AND p_source = 'CONSOLIDATION'
AND NOT EXISTS
(SELECT '1'
FROM OKL_PARALLEL_PROCESSES opp
WHERE ext.CUSTOMER_ID = to_number(opp.object_value)
AND opp.object_type = 'CUSTOMER'
AND opp.process_status in ('PENDING_ASSIGNMENT', 'ASSIGNED'));
SELECT khr.contract_number contract_number, khr.id khr_id
FROM okc_k_headers_b khr,
okl_k_control nbd,
okc_k_lines_b cle
WHERE ((p_contract_number is not null and KHR.CONTRACT_NUMBER=p_contract_number)
or (p_contract_number is null and KHR.CONTRACT_NUMBER=KHR.CONTRACT_NUMBER))
AND ((p_inv_cust_acct_id is not null and cle.CUST_ACCT_ID IS NOT NULL AND cle.CUST_ACCT_ID = p_inv_cust_acct_id )
or (p_inv_cust_acct_id is null and cle.CUST_ACCT_ID = cle.CUST_ACCT_ID))
AND cle.dnz_chr_id = khr.id
AND khr.scs_code ='INVESTOR'
AND khr.sts_code = 'ACTIVE'
AND p_source = 'BILL_STREAMS'
AND nvl(p_ia_contract_type, L_IA_TYPE) = L_IA_TYPE
AND khr.id = nbd.khr_id
AND nbd.earliest_strm_bill_date <= (NVL(p_date, SYSDATE) + OKL_STREAM_BILLING_PVT.get_printing_lead_days(khr.id))
AND KHR.authoring_org_id = NVL(TO_NUMBER(orgId),-99)
AND NOT EXISTS
(SELECT '1'
FROM OKL_PARALLEL_PROCESSES opp
WHERE khr.contract_number = opp.object_value
AND opp.object_type = 'CONTRACT'
AND opp.process_status in ('PENDING_ASSIGNMENT', 'ASSIGNED'))
-- modified by zrehman for Bug#6788005 on 01-Feb-2008 end
;
SELECT
opp.object_value CONTRACT_NUMBER,
COUNT(STE.ID) LINE_COUNT
FROM
OKL_STRM_ELEMENTS STE,
OKL_STREAMS STM,
OKL_STRM_TYPE_V STY,
OKL_PARALLEL_PROCESSES OPP
WHERE
OPP.ASSIGNED_PROCESS = p_seq_next AND
OPP.OBJECT_TYPE = 'CONTRACT' AND
(
(p_date_from is not null and STE.STREAM_ELEMENT_DATE >= p_date_from)
OR
(p_date_from is null)
) AND
STE.STREAM_ELEMENT_DATE <= (NVL(p_date_to, SYSDATE) +
NVL( NVL(
(SELECT RULE.RULE_INFORMATION3 FROM OKC_RULES_B RULE, OKC_RULE_GROUPS_B RGP, okc_k_headers_b khr
WHERE opp.khr_id = khr.id AND RGP.ID = RULE.RGP_ID AND RGP.DNZ_CHR_ID = KHR.ID
AND RGP.RGD_CODE = 'LABILL' AND RULE.RULE_INFORMATION_CATEGORY = 'LAINVD'
),
(SELECT TERM.PRINTING_LEAD_DAYS FROM HZ_CUSTOMER_PROFILES CP, RA_TERMS TERM, okc_k_headers_b khr
WHERE opp.khr_id = khr.id AND KHR.BILL_TO_SITE_USE_ID = CP.SITE_USE_ID AND CP.STANDARD_TERMS = TERM.TERM_ID)
),0)
--Bug# 7701159 - Susbtitute SQL query for PL//*/*SQL call
/*NVL((SELECT NVL(RULE.RULE_INFORMATION3,TERM.PRINTING_LEAD_DAYS)
FROM OKC_RULES_B RULE,
OKC_RULE_GROUPS_B RGP,
HZ_CUSTOMER_PROFILES CP,
RA_TERMS TERM
,okc_k_headers_b khr
WHERE opp.khr_id = khr.id and
RGP.ID = RULE.RGP_ID
AND RGP.DNZ_CHR_ID = KHR.ID
AND RGP.RGD_CODE = 'LABILL'
AND RULE.RULE_INFORMATION_CATEGORY = 'LAINVD'
AND KHR.BILL_TO_SITE_USE_ID = CP.SITE_USE_ID
AND CP.STANDARD_TERMS = TERM.TERM_ID),
0)*/
) and
-- STE.STREAM_ELEMENT_DATE <= (NVL(p_date_to, SYSDATE) + OKL_STREAM_BILLING_PVT.get_printing_lead_days(opp.khr_id)) AND -- Bug 6377127
STE.AMOUNT <> 0 AND
STM.ID = STE.STM_ID AND
STE.DATE_BILLED IS NULL AND
STM.ACTIVE_YN = 'Y' AND
STM.SAY_CODE = 'CURR' AND
STY.ID = STM.STY_ID AND
STY.BILLABLE_YN = 'Y' AND
opp.khr_id = STM.KHR_ID AND
p_source = 'BILL_STREAMS'
GROUP BY opp.object_value
--fmiao 5209209 change
-- rmunjulu R12 Fixes comment out Prepare Recevbles
/*
UNION
-- 3 levels
SELECT
CHR.CONTRACT_NUMBER CONTRACT_NUMBER,
COUNT(*) LINE_COUNT
FROM
OKL_TXD_AR_LN_DTLS_B TLD,
OKL_TXL_AR_INV_LNS_B TIL,
OKL_TRX_AR_INVOICES_B TAI,
OKC_K_HEADERS_B CHR,
OKL_STRM_TYPE_V STY,
OKL_PARALLEL_PROCESSES OPP
WHERE tai.trx_status_code = 'SUBMITTED'
AND tai.khr_id = CHR.id
AND til.tai_id = tai.id
AND tld.til_id_details = til.id
AND sty.id = tld.sty_id
AND OPP.OBJECT_VALUE = CHR.CONTRACT_NUMBER
AND OPP.ASSIGNED_PROCESS = p_seq_next
AND OPP.OBJECT_TYPE = 'PREP_CONTRACT'
AND p_source = 'AR_PREPARE'
GROUP BY CHR.CONTRACT_NUMBER
UNION
--2 levels
SELECT
CHR.CONTRACT_NUMBER CONTRACT_NUMBER,
COUNT(*) LINE_COUNT
FROM
OKL_TXL_AR_INV_LNS_B TIL,
OKL_TRX_AR_INVOICES_B TAI,
OKC_K_HEADERS_B CHR,
OKL_STRM_TYPE_V STY,
OKL_PARALLEL_PROCESSES OPP
WHERE tai.trx_status_code = 'SUBMITTED'
AND tai.khr_id = CHR.id
AND til.tai_id = tai.id
AND til.sty_id = sty.id
AND OPP.OBJECT_VALUE = CHR.CONTRACT_NUMBER
AND OPP.ASSIGNED_PROCESS = p_seq_next
AND OPP.OBJECT_TYPE = 'PREP_CONTRACT'
AND p_source = 'AR_PREPARE'
AND NOT EXISTS
(SELECT *
FROM okl_txd_ar_ln_dtls_b tld
WHERE tld.til_id_details = til.id
)
GROUP BY CHR.CONTRACT_NUMBER
--fmiao 5209209 end
*/
-- rmunjulu R12 Fixes Modify AR TRANSFER
UNION
-- transfer 3
SELECT
KHR.CONTRACT_NUMBER CONTRACT_NUMBER,
COUNT(*) LINE_COUNT
FROM
--OKL_EXT_SELL_INVS_B XSI, -- rmunjulu R12 fixes - commented
--OKL_XTL_SELL_INVS_B XLS, -- rmunjulu R12 fixes - commented
--OKL_TXD_AR_LN_DTLS_B TLD, -- rmunjulu R12 fixes - commented
--OKL_TXL_AR_INV_LNS_B TIL, -- rmunjulu R12 fixes - commented
OKL_TRX_AR_INVOICES_B TAI,
OKC_K_HEADERS_B KHR,
OKL_PARALLEL_PROCESSES OPP
WHERE
TAI.TRX_STATUS_CODE = 'SUBMITTED' AND -- rmunjulu R12 fixes - changed to TAI and SUBMITTED
--XSI.ID = XLS.XSI_ID_DETAILS AND -- rmunjulu R12 fixes - commented
--XLS.TLD_ID = TLD.ID AND -- rmunjulu R12 fixes - commented
--TLD.TIL_ID_DETAILS = TIL.ID AND -- rmunjulu R12 fixes - commented
--TIL.TAI_ID = TAI.ID AND -- rmunjulu R12 fixes - commented
TAI.KHR_ID = KHR.ID AND
OPP.OBJECT_VALUE = KHR.CONTRACT_NUMBER AND
OPP.ASSIGNED_PROCESS = p_seq_next AND
OPP.OBJECT_TYPE = 'XTRX_CONTRACT' AND
p_source = 'AR_TRANSFER'
GROUP BY KHR.CONTRACT_NUMBER
/* -- rmunjulu R12 Fixes -- below select not needed as will be same as above select
UNION
-- transfer 2
SELECT
KHR.CONTRACT_NUMBER CONTRACT_NUMBER,
COUNT(*) LINE_COUNT
FROM
OKL_EXT_SELL_INVS_V XSI,
OKL_XTL_SELL_INVS_V XLS,
OKL_TXL_AR_INV_LNS_V TIL,
OKL_TRX_AR_INVOICES_V TAI,
OKC_K_HEADERS_B KHR,
OKL_PARALLEL_PROCESSES OPP
WHERE
XSI.TRX_STATUS_CODE = 'WORKING' AND
XSI.ID = XLS.XSI_ID_DETAILS AND
XLS.TIL_ID = TIL.ID AND
TIL.TAI_ID = TAI.ID AND
TAI.KHR_ID = KHR.ID AND
OPP.OBJECT_VALUE = KHR.CONTRACT_NUMBER AND
OPP.ASSIGNED_PROCESS = p_seq_next AND
OPP.OBJECT_TYPE = 'XTRX_CONTRACT' AND
p_source = 'AR_TRANSFER'
GROUP BY KHR.CONTRACT_NUMBER
UNION
*/
-- consolidation
/* -- rmunjulu -- comment out consolidation
SELECT
TO_CHAR(CUSTOMER_ID) CONTRACT_NUMBER,
COUNT(*)
FROM
OKL_EXT_SELL_INVS_B EXT,
OKL_PARALLEL_PROCESSES OPP
WHERE
EXT.TRX_STATUS_CODE = 'SUBMITTED' AND
OPP.OBJECT_VALUE = EXT.CUSTOMER_ID AND
OPP.ASSIGNED_PROCESS = p_seq_next AND
OPP.OBJECT_TYPE = 'CUSTOMER' AND
p_source = 'CONSOLIDATION'
GROUP BY CUSTOMER_ID;
l_last_updated_by okl_parallel_processes.last_updated_by%TYPE := Fnd_Global.USER_ID;
l_last_update_login okl_parallel_processes.last_update_login%TYPE := Fnd_Global.LOGIN_ID;
select count(*)
from fnd_concurrent_requests req,
fnd_concurrent_programs pgm
where req.PRIORITY_REQUEST_ID = p_request_id
and req.concurrent_program_id = pgm.concurrent_program_id
and req.PHASE_CODE = 'C'
and request_id <> p_request_id
and STATUS_CODE = 'E'
--end modified abhsaxen for performance SQLID 20562749
;
select count(*)
from fnd_concurrent_requests req,
fnd_concurrent_programs pgm
where req.priority_request_id = p_request_id
and req.concurrent_program_id = pgm.concurrent_program_id
and req.phase_code = 'C'
and request_id <> p_request_id
and status_code = 'G'
--end modified abhsaxen for performance SQLID 20562754
;
select decode(chr.scs_code, 'INVESTOR', 'IA', 'LEASE', 'C', null)
from
okc_k_headers_all_b chr
,okl_k_headers khr
where chr.id = khr.id
and chr.scs_code in ('INVESTOR', 'LEASE')
and chr.contract_number = p_contract_number;
select okl_opp_seq.nextval
into l_seq_next
from dual ;
for chk_update_header_csr_rec in chk_update_header_csr (lp_date_to, orgId) loop -- rmunjulu R12 Forward Port missing on R12 (oklh bug 4728636)
INSERT INTO OKL_PARALLEL_PROCESSES
(
object_type, object_value, assigned_process, process_status, start_date, khr_id,
ORG_ID,CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,
REQUEST_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE
)
VALUES
(decode (p_source,'BILL_STREAMS','CONTRACT',
'AR_TRANSFER','XTRX_CONTRACT',
'CONSOLIDATION','CUSTOMER',
--fmiao 5209209 change
'AR_PREPARE','PREP_CONTRACT',NULL), -- fmiao 5209209 end
chk_update_header_csr_rec.contract_number, to_char(l_seq_next),'PENDING_ASSIGNMENT', sysdate, chk_update_header_csr_rec.khr_id,
l_org_id,l_last_updated_by,sysdate,sysdate,l_last_updated_by,l_last_update_login,
l_request_id,l_program_id,sysdate
);
update okl_parallel_processes opp
set volume = (select count(*)
from okc_k_lines_b chl
where chl.dnz_chr_id = opp.khr_id)
WHERE OPP.ASSIGNED_PROCESS = TO_CHAR(l_seq_next); -- bug# 5872306 (ssiruvol)
update okl_parallel_processes opp
-- set volume = volume* (select ceil((lp_date_to - nbd.earliest_strm_bill_date)/30) -- rmunjulu R12 Forward Port bug 5634652
SET volume = volume* (SELECT CEIL((lp_date_to
-- rmunjulu R12 Forward Port bug 5634652 logic to check print lead days
+
NVL( NVL(
(SELECT RULE.RULE_INFORMATION3 FROM OKC_RULES_B RULE, OKC_RULE_GROUPS_B RGP, okc_k_headers_b khr
WHERE opp.khr_id = khr.id AND RGP.ID = RULE.RGP_ID AND RGP.DNZ_CHR_ID = KHR.ID
AND RGP.RGD_CODE = 'LABILL' AND RULE.RULE_INFORMATION_CATEGORY = 'LAINVD'
),
(SELECT TERM.PRINTING_LEAD_DAYS FROM HZ_CUSTOMER_PROFILES CP, RA_TERMS TERM, okc_k_headers_b khr
WHERE opp.khr_id = khr.id AND KHR.BILL_TO_SITE_USE_ID = CP.SITE_USE_ID AND CP.STANDARD_TERMS = TERM.TERM_ID)
),0)
--Bug# 7701159 - Susbtitute SQL query for PL/SQL call
/*NVL((SELECT NVL(RULE.RULE_INFORMATION3,TERM.PRINTING_LEAD_DAYS)
FROM OKC_RULES_B RULE,
OKC_RULE_GROUPS_B RGP,
HZ_CUSTOMER_PROFILES CP,
RA_TERMS TERM
,okc_k_headers_b khr
WHERE opp.khr_id = khr.id and
RGP.ID = RULE.RGP_ID
AND RGP.DNZ_CHR_ID = KHR.ID
AND RGP.RGD_CODE = 'LABILL'
AND RULE.RULE_INFORMATION_CATEGORY = 'LAINVD'
AND KHR.BILL_TO_SITE_USE_ID = CP.SITE_USE_ID
AND CP.STANDARD_TERMS = TERM.TERM_ID),
0)*/
-- + OKL_STREAM_BILLING_PVT.get_printing_lead_days(opp.khr_id)
-- rmunjulu R12 Forward Port bug 5710903 Add ONE to account for scenario where these dates are same
- nbd.earliest_strm_bill_date + 1)/30)
from okl_k_control nbd
where nbd.khr_id = opp.khr_id)
WHERE OPP.ASSIGNED_PROCESS = TO_CHAR(l_seq_next); -- bug# 5872306 (ssiruvol)
l_contract_tab.DELETE(i);
l_contract_tab.DELETE(i);
UPDATE OKL_PARALLEL_PROCESSES
SET
assigned_process = l_seq_next||'-'||l_sort_tab1(i).worker_number,
volume = l_sort_tab1(i).line_count,
process_status = 'ASSIGNED'
WHERE object_Type = 'CONTRACT'
AND object_value = l_sort_tab1(i).contract_number
AND process_status = 'PENDING_ASSIGNMENT';
UPDATE OKL_PARALLEL_PROCESSES
SET
assigned_process = l_seq_next||'-'||l_sort_tab1(i).worker_number,
volume = l_sort_tab1(i).line_count,
process_status = 'ASSIGNED'
WHERE object_Type = 'PREP_CONTRACT'
AND object_value = l_sort_tab1(i).contract_number
AND process_status = 'PENDING_ASSIGNMENT';
UPDATE OKL_PARALLEL_PROCESSES
SET
assigned_process = l_seq_next||'-'||l_sort_tab1(i).worker_number,
volume = l_sort_tab1(i).line_count,
process_status = 'ASSIGNED'
WHERE object_Type = 'XTRX_CONTRACT'
AND object_value = l_sort_tab1(i).contract_number
AND process_status = 'PENDING_ASSIGNMENT';
UPDATE OKL_PARALLEL_PROCESSES
SET
assigned_process = l_seq_next||'-'||l_sort_tab1(i).worker_number,
volume = l_sort_tab1(i).line_count,
process_status = 'ASSIGNED'
WHERE object_Type = 'CUSTOMER'
AND object_value = l_sort_tab1(i).contract_number
AND process_status = 'PENDING_ASSIGNMENT';
l_sort_tab1.DELETE(i);
DELETE OKL_PARALLEL_PROCESSES
WHERE process_status = 'PENDING_ASSIGNMENT'
AND assigned_process = to_char(l_seq_next);
DELETE OKL_PARALLEL_PROCESSES
WHERE volume = 0
AND assigned_process like to_char(l_seq_next)||'%';
DELETE OKL_PARALLEL_PROCESSES
WHERE assigned_process = to_char(l_seq_next);
DELETE OKL_PARALLEL_PROCESSES
WHERE assigned_process = to_char(g_opp_seq_num);
DELETE OKL_PARALLEL_PROCESSES
WHERE assigned_process = to_char(g_opp_seq_num);
DELETE OKL_PARALLEL_PROCESSES
WHERE assigned_process = to_char(g_opp_seq_num);
DELETE OKL_PARALLEL_PROCESSES
WHERE assigned_process = TO_CHAR(g_opp_seq_num);
l_update_tbl IN OUT NOCOPY update_tbl_type)
IS
l_old_cnr_id NUMBER;
IF l_update_tbl.COUNT > 0 THEN
IF saved_bill_rec.l_overall_status IS NULL THEN
l_old_cnr_id := -9;
FOR m in l_update_tbl.FIRST..l_update_tbl.LAST LOOP
PRINT_TO_LOG( 'l_update_tbl.cnr_id '||l_update_tbl(m).cnr_id);
PRINT_TO_LOG( 'l_update_tbl.cons_inv_number '||l_update_tbl(m).cons_inv_number);
PRINT_TO_LOG( 'l_update_tbl.lln_id '||l_update_tbl(m).lln_id);
PRINT_TO_LOG( 'l_update_tbl.lsm_id '||l_update_tbl(m).lsm_id);
PRINT_TO_LOG( 'l_update_tbl.asset_number '||l_update_tbl(m).asset_number);
PRINT_TO_LOG( 'l_update_tbl.invoice_format '||l_update_tbl(m).invoice_format);
PRINT_TO_LOG( 'l_update_tbl.line_type '||l_update_tbl(m).line_type);
PRINT_TO_LOG( 'l_update_tbl.sty_name '||l_update_tbl(m).sty_name);
PRINT_TO_LOG( 'l_update_tbl.contract_number '||l_update_tbl(m).contract_number);
PRINT_TO_LOG( 'l_update_tbl.lsm_amount '||l_update_tbl(m).lsm_amount);
PRINT_TO_LOG( 'l_update_tbl.xsi_id '||l_update_tbl(m).xsi_id);
PRINT_TO_LOG( 'l_update_tbl.xls_id '||l_update_tbl(m).xls_id);
g_xsi_tbl(g_xsi_counter).id :=l_update_tbl(m).xsi_id;
g_xsi_tl_tbl(g_xsi_tl_counter).id :=l_update_tbl(m).xsi_id;
g_xsi_tl_tbl(g_xsi_tl_counter).xtrx_cons_invoice_number := l_update_tbl(m).cons_inv_number;
g_xsi_tl_tbl(g_xsi_tl_counter).xtrx_format_type := l_update_tbl(m).invoice_format;
g_xsi_tl_tbl(g_xsi_tl_counter).xtrx_private_label := l_update_tbl(m).private_label;
g_xls_tbl(g_xls_counter).id :=l_update_tbl(m).xls_id;
g_xls_tbl(g_xls_counter).lsm_id :=l_update_tbl(m).LSM_ID;
g_xls_tbl(g_xls_counter).xtrx_cons_stream_id :=l_update_tbl(m).lsm_id;
g_xls_tl_tbl(g_xls_tl_counter).id :=l_update_tbl(m).xls_id;
g_xls_tl_tbl(g_xls_tl_counter).xtrx_contract := l_update_tbl(m).contract_number;
g_xls_tl_tbl(g_xls_tl_counter).xtrx_asset := l_update_tbl(m).asset_number;
g_xls_tl_tbl(g_xls_tl_counter).xtrx_stream_type := l_update_tbl(m).sty_name;
g_xls_tl_tbl(g_xls_tl_counter).xtrx_stream_group := l_update_tbl(m).line_type;
l_update_tbl.DELETE;
select RULE_INFORMATION1
from okc_rule_groups_v rgp,
okc_rules_v rul
where rgp.dnz_chr_id = cp_khr_id AND
rgp.chr_id = rgp.dnz_chr_id AND
rgp.id = rul.rgp_id AND
rgp.cle_id IS NULL AND
rgp.rgd_code = 'LABILL' AND
rul.rule_information_category = 'LAINVD';
SELECT id,
priority,
pkg_name,
proc_name
FROM okl_invoice_mssgs_v;
g_cnr_tbl(g_header_counter).last_update_date := SYSDATE;
g_cnr_tbl(g_header_counter).last_updated_by := Fnd_Global.USER_ID;
g_cnr_tbl(g_header_counter).program_update_date := SYSDATE;
PRINT_TO_LOG('=***********> SELECT id FROM okl_cnsld_ar_lines_v');
PRINT_TO_LOG('=***********> SELECT id FROM okl_cnsld_ar_lines_v');
PRINT_TO_LOG('=***********> SELECT id FROM okl_cnsld_ar_lines_v');
PRINT_TO_LOG('=***********> SELECT id FROM okl_cnsld_ar_lines_v');
g_lln_tbl(g_line_counter).last_update_date := SYSDATE;
g_lln_tbl(g_line_counter).last_updated_by := Fnd_Global.USER_ID;
g_lln_tbl(g_line_counter).program_update_date := SYSDATE;
SELECT language_code
FROM FND_LANGUAGES
WHERE INSTALLED_FLAG IN ('I', 'B');
-- because it will be inserted next time around
g_cnr_tbl.DELETE(g_header_counter);
g_cnr_tl_tbl(tl_count).last_updated_by := fnd_global.user_id;
g_cnr_tl_tbl(tl_count).last_update_date := sysdate;
g_cnr_tl_tbl(tl_count).last_update_login := fnd_global.login_id;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT(
p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,p_imav_rec
,x_imav_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
PRINT_TO_LOG('Performing bulk insert for cnr, record count is '||g_cnr_tbl.count);
insert into okl_cnsld_ar_hdrs_b
values g_cnr_tbl(x);
insert into okl_cnsld_ar_hdrs_tl
values g_cnr_tl_tbl(d);
PRINT_TO_LOG('Error during Header Insertion, rollback to H1');
g_cnr_tbl.DELETE;
g_cnr_tl_tbl.DELETE;
g_lln_tbl.DELETE;
g_lln_tl_tbl.DELETE;
g_lsm_tbl.DELETE;
g_lsm_tl_tbl.DELETE;
g_xsi_tbl.DELETE;
g_xsi_tl_tbl.DELETE;
g_xls_tbl.DELETE;
g_xls_tl_tbl.DELETE;
g_cnr_tbl.delete;
g_cnr_tl_tbl.delete;
-- because it will be inserted next time around
g_lln_tbl.DELETE(g_line_counter);
g_lln_tl_tbl(tl_count).last_updated_by := fnd_global.user_id;
g_lln_tl_tbl(tl_count).last_update_date := sysdate;
g_lln_tl_tbl(tl_count).last_update_login := fnd_global.login_id;
PRINT_TO_LOG('Performing bulk insert for lln, record count is '||g_lln_tbl.count);
insert into okl_cnsld_ar_lines_b
values g_lln_tbl(x);
insert into okl_cnsld_ar_lines_tl
values g_lln_tl_tbl(e);
PRINT_TO_LOG('Error during Line Insertion, rollback to L1');
g_lln_tl_tbl.DELETE;
g_lsm_tbl.DELETE;
g_lsm_tl_tbl.DELETE;
g_xsi_tbl.DELETE;
g_xsi_tl_tbl.DELETE;
g_xls_tbl.DELETE;
g_xls_tl_tbl.DELETE;
delete from okl_cnsld_ar_hdrs_b
where id = g_lln_tbl(e).cnr_id;
g_lln_tbl.DELETE;
g_lln_tbl.delete;
g_lln_tl_tbl.delete;
g_lsm_tl_tbl(tl_count).last_updated_by := fnd_global.user_id;
g_lsm_tl_tbl(tl_count).last_update_date := sysdate;
g_lsm_tl_tbl(tl_count).last_update_login := fnd_global.login_id;
PRINT_TO_LOG('Performing bulk insert for lsm, record count is '||g_lsm_tbl.count);
insert into okl_cnsld_ar_strms_b
values g_lsm_tbl(x);
insert into okl_cnsld_ar_strms_tl
values g_lsm_tl_tbl(f);
PRINT_TO_LOG('Error during Stream Insertion, rollback to D1');
g_cnr_tl_tbl.delete;
g_lln_tl_tbl.DELETE;
g_lsm_tl_tbl.DELETE;
g_xsi_tbl.DELETE;
g_xsi_tl_tbl.DELETE;
g_xls_tbl.DELETE;
g_xls_tl_tbl.DELETE;
delete from okl_cnsld_ar_hdrs_b
where id = g_lln_tbl(f).cnr_id;
delete from okl_cnsld_ar_lines_b
where id = g_lsm_tbl(e).lln_id;
g_lsm_tbl.DELETE;
g_lln_tbl.DELETE;
g_cnr_tbl.DELETE;
g_lsm_tbl.delete;
g_lsm_tl_tbl.delete;
Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
Okl_Inv_Mssg_Att_Pub.INSERT_INV_MSSG_ATT(
p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,g_imav_tbl
,x_imav_tbl
);
Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
g_imav_tbl.DELETE;
g_lsm_tbl(g_stream_counter).last_update_date := (SYSDATE);
g_lsm_tbl(g_stream_counter).last_updated_by := Fnd_Global.USER_ID;
g_lsm_tbl(g_stream_counter).program_update_date := (SYSDATE);
PRINT_TO_LOG('Performing bulk update for xsi, record count is '||g_xsi_tbl.COUNT );
update okl_ext_sell_invs_b
set trx_status_code = g_xsi_tbl(indx).trx_status_code,
-- xtrx_invoice_pull_yn = g_xsi_tbl(indx).xtrx_invoice_pull_yn,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where id = g_xsi_tbl(indx).id;
PRINT_TO_LOG('Error during Update of okl_ext_sell_invs_b, rollback to U1');
g_xsi_tbl.delete;
PRINT_TO_LOG('Performing bulk update for xls, record count is '||g_xls_tbl.COUNT );
update okl_xtl_sell_invs_b
set lsm_id = g_xls_tbl(s).lsm_id,
xtrx_cons_stream_id = g_xls_tbl(s).lsm_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where id = g_xls_tbl(s).id;
PRINT_TO_LOG('Error during Update of okl_xtl_sell_invs_b, rollback to U2');
g_xls_tbl.delete;
PRINT_TO_LOG('Performing bulk update for xsi tl, record count is '||g_xsi_tl_tbl.COUNT );
update okl_ext_sell_invs_tl
set xtrx_cons_invoice_number = g_xsi_tl_tbl(u).xtrx_cons_invoice_number,
xtrx_format_type = g_xsi_tl_tbl(u).xtrx_format_type,
xtrx_private_label = g_xsi_tl_tbl(u).xtrx_private_label,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where id = g_xsi_tl_tbl(u).id;
PRINT_TO_LOG('Error during Update of okl_ext_sell_invs_tl, rollback to U3');
g_xsi_tl_tbl.delete;
PRINT_TO_LOG('Performing bulk update for xls tl, record count is '||g_xls_tl_tbl.COUNT );
update okl_xtl_sell_invs_tl
set xtrx_contract = g_xls_tl_tbl(t).xtrx_contract,
xtrx_asset = g_xls_tl_tbl(t).xtrx_asset,
xtrx_stream_type = g_xls_tl_tbl(t).xtrx_stream_type,
xtrx_stream_group = g_xls_tl_tbl(t).xtrx_stream_group,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where id = g_xls_tl_tbl(t).id;
PRINT_TO_LOG('Error during Update of okl_xtl_sell_invs_tl, rollback to U4');
g_xls_tl_tbl.delete;
p_update_tbl IN OUT NOCOPY update_tbl_type)
IS
l_api_name CONSTANT VARCHAR2(30) := 'process_cons_bill_tbl';
l_update_tbl update_tbl_type;
SELECT chr_id
FROM okc_k_lines_b
WHERE id = p_cle_id;
SELECT name
FROM okc_k_lines_v
WHERE id = p_cle_id;
SELECT FA.ID
FROM OKC_K_HEADERS_B CHR,
OKC_K_LINES_B TOP_CLE,
OKC_LINE_STYLES_b TOP_LSE,
OKC_K_LINES_B SUB_CLE,
OKC_LINE_STYLES_b SUB_LSE,
OKC_K_ITEMS CIM,
OKC_K_LINES_V FA,
OKC_LINE_STYLES_B AST_LSE,
OKL_CNSLD_AR_STRMS_B LSM
WHERE
CHR.ID = TOP_CLE.DNZ_CHR_ID AND
TOP_CLE.LSE_ID = TOP_LSE.ID AND
TOP_LSE.LTY_CODE IN('SOLD_SERVICE','FEE') AND
TOP_CLE.ID = SUB_CLE.CLE_ID AND
SUB_CLE.LSE_ID = SUB_LSE.ID AND
SUB_LSE.LTY_CODE IN ('LINK_SERV_ASSET', 'LINK_FEE_ASSET') AND
SUB_CLE.ID = LSM.KLE_ID AND
LSM.ID = p_lsm_id AND
CIM.CLE_ID = SUB_CLE.ID AND
CIM.JTOT_OBJECT1_CODE = 'OKX_COVASST' AND
CIM.OBJECT1_ID1 = FA.ID AND
FA.LSE_ID = AST_LSE.ID AND
AST_LSE.LTY_CODE = 'FREE_FORM1';
SELECT
inf.name inf_name,
inf.contract_level_yn,
ity.id ity_id,
ity.name ity_name,
ity.group_asset_yn,
ity.group_by_contract_yn,
ilt.id ilt_id,
ilt.sequence_number,
ilt.name ilt_name,
sty.name sty_name
FROM okl_invoice_formats_v inf,
okl_invoice_types_v ity,
okl_invc_line_types_v ilt,
okl_invc_frmt_strms_v frs,
okl_strm_type_v sty
WHERE inf.id = p_format_id
AND ity.inf_id = inf.id
AND ilt.ity_id = ity.id
AND frs.ilt_id = ilt.id
AND sty.id = frs.sty_id
AND frs.sty_id = p_stream_id;
SELECT
inf.name inf_name,
inf.contract_level_yn,
ity.id ity_id,
ity.name ity_name,
ity.group_asset_yn,
ity.group_by_contract_yn,
ilt.id ilt_id,
ilt.sequence_number,
ilt.name ilt_name
FROM okl_invoice_formats_v inf,
okl_invoice_types_v ity,
okl_invc_line_types_v ilt
WHERE inf.id = p_format_id
AND ity.inf_id = inf.id
AND ilt.ity_id = ity.id
AND inf.ilt_id = ilt.id;
SELECT id, name
FROM okl_strm_type_v;
SELECT
inf.id inf_id,
inf.name inf_name,
inf.contract_level_yn,
ity.id ity_id,
ity.name ity_name,
ity.group_asset_yn,
ity.group_by_contract_yn,
ilt.id ilt_id,
ilt.sequence_number,
ilt.name ilt_name,
sty.name sty_name,
frs.sty_id sty_id
FROM okl_invoice_formats_v inf,
okl_invoice_types_v ity,
okl_invc_line_types_v ilt,
okl_invc_frmt_strms_v frs,
okl_strm_type_v sty
WHERE ity.inf_id = inf.id
AND ilt.ity_id = ity.id
AND frs.ilt_id = ilt.id
AND sty.id = frs.sty_id
UNION -- default invlice format
SELECT
inf.id inf_id,
inf.name inf_name,
inf.contract_level_yn,
ity.id ity_id,
ity.name ity_name,
ity.group_asset_yn,
ity.group_by_contract_yn,
ilt.id ilt_id,
ilt.sequence_number,
ilt.name ilt_name,
'DEFAULT FORMAT' sty_name,
NULL
FROM okl_invoice_formats_v inf,
okl_invoice_types_v ity,
okl_invc_line_types_v ilt
WHERE ity.inf_id = inf.id
AND ilt.ity_id = ity.id
AND inf.ilt_id = ilt.id;
SELECT id, priority, pkg_name, proc_name
, start_date, end_date
FROM okl_invoice_mssgs_v;
p_update_tbl);
p_update_tbl := l_update_tbl;
p_update_tbl);
p_update_tbl := l_update_tbl;
l_asset_tbl.delete;
l_cnt := p_update_tbl.count;
PRINT_TO_LOG( 'DEL Updates (p_saved_bill_rec.l_cons_inv_num)'||p_saved_bill_rec.l_cons_inv_num);
PRINT_TO_LOG( 'DEL Updates (l_format_name)'||l_format_name);
p_update_tbl(l_cnt).cnr_id := p_saved_bill_rec.l_cnr_id;
p_update_tbl(l_cnt).cons_inv_number := p_saved_bill_rec.l_cons_inv_num;
p_update_tbl(l_cnt).lln_id := p_saved_bill_rec.l_lln_id;
p_update_tbl(l_cnt).lsm_id := l_lsm_id;
p_update_tbl(l_cnt).asset_number := l_asset_name;
p_update_tbl(l_cnt).invoice_format := l_format_name;
p_update_tbl(l_cnt).line_type := l_cons_line_name;
p_update_tbl(l_cnt).sty_name := l_sty_name;
p_update_tbl(l_cnt).contract_number := p_cons_bill_tbl(k).contract_number;
p_update_tbl(l_cnt).private_label := p_cons_bill_tbl(k).private_label;
p_update_tbl(l_cnt).lsm_amount := p_cons_bill_tbl(k).amount;
p_update_tbl(l_cnt).xsi_id := p_cons_bill_tbl(k).xsi_id;
p_update_tbl(l_cnt).xls_id := p_cons_bill_tbl(k).xls_id;
p_update_tbl(l_cnt).cnr_total := p_update_tbl(l_cnt).cnr_total + p_cons_bill_tbl(k).amount;
p_update_tbl(l_cnt).cnr_total := p_cons_bill_tbl(k).amount;
p_update_tbl(l_cnt).lln_total := p_update_tbl(l_cnt).lln_total + p_cons_bill_tbl(k).amount;
p_update_tbl(l_cnt).lln_total := p_cons_bill_tbl(k).amount;
SELECT
xsib.customer_id customer_id,
xsib.currency_code currency,
xsib.customer_address_id bill_to_site,
xsib.receipt_method_id payment_method,
xsit.xtrx_private_label private_label,
TRUNC(xsiB.TRX_DATE) date_consolidated,
NVL(
nvl(
(SELECT ity.id ity_id
FROM okl_invoice_types_b ity,
okl_invc_line_types_b ilt,
okl_invc_frmt_strms frs
WHERE ity.inf_id = inf.id
AND ilt.ity_id = ity.id
AND frs.ilt_id = ilt.id
AND tld.sty_id = frs.sty_id),
(select ity1.id
from okl_invoice_types_b ity1,
okl_invc_line_types_b ilt1
where ilt1.id = inf.ilt_id
and ilt1.ity_id = ity1.id)), -1
) inv_type,
xsib.inf_id inf_id,
tai.khr_id contract_id,
chr.contract_number contract_number,
'-1' prev_cons_invoice_num,
xsib.org_id org_id,
xsib.set_of_books_id set_of_books_id,
til.kle_id kle_id,
tld.sty_id stream_id,
xsib.id xsi_id,
xls.id xls_id,
xls.amount c_amount,
xls.sel_id sel_id,
xsib.legal_entity_id legal_entity_id -- for LE Uptake project 08-11-2006
FROM
okl_ext_sell_invs_b xsib,
okl_ext_sell_invs_tl xsit,
okl_xtl_sell_invs_b xls,
okl_txd_ar_ln_dtls_b tld,
okl_txl_ar_inv_lns_b til,
okl_trx_ar_invoices_b tai,
okc_k_headers_b chr,
okl_invoice_formats_b inf, -- 5138822
okl_parallel_processes pws
WHERE
xsiB.TRX_STATUS_CODE = 'SUBMITTED' AND
xls.xsi_id_details = xsiB.id AND
tld.id = xls.tld_id AND
til.id = tld.TIL_ID_DETAILS AND
tai.id = til.tai_id AND
tai.khr_id = chr.id
AND
chr.contract_number = NVL(p_contract_number,chr.contract_number) AND
tai.clg_id IS NULL AND
tai.cpy_id IS NULL AND
tai.qte_id IS NULL AND
xls.amount >=0 AND
xsiB.inf_id = inf.id (+) AND
pws.object_type = 'CUSTOMER' AND
XSIB.CUSTOMER_ID = to_number(pws.object_value) AND
pws.assigned_process = p_assigned_process AND
XSIB.ID = XSIT.ID
and XSIT.LANGUAGE = USERENV('LANG')
UNION
SELECT
xsib.customer_id customer_id,
xsib.currency_code currency,
xsib.customer_address_id bill_to_site,
xsib.receipt_method_id payment_method,
xsit.xtrx_private_label private_label,
TRUNC(xsib.TRX_DATE) date_consolidated,
NVL(
NVL((SELECT ity.id ity_id
FROM okl_invoice_types_b ity,
okl_invc_line_types_b ilt,
okl_invc_frmt_strms frs
WHERE ity.inf_id = inf.id
AND ilt.ity_id = ity.id
AND frs.ilt_id = ilt.id
AND tld.sty_id = frs.sty_id),
(select ity1.id from okl_invoice_types_b ity1,
okl_invc_line_types_b ilt1
where ilt1.id = inf.ilt_id
and ilt1.ity_id = ity1.id)), -1
) inv_type, -- bug 5138822
xsib.inf_id inf_id,
tai.khr_id contract_id, -- get contract Id
chr.contract_number contract_number,
'-1' prev_cons_invoice_num,
xsib.org_id org_id,
xsib.set_of_books_id set_of_books_id,
til.kle_id kle_id,
tld.sty_id stream_id, -- to get the line seq #
xsib.id xsi_id,
xls.id xls_id,
xls.amount c_amount,
xls.sel_id sel_id,
xsib.legal_entity_id legal_entity_id -- for LE Uptake project 08-11-2006
FROM
okl_ext_sell_invs_b xsib,
okl_ext_sell_invs_tl xsit,
okl_xtl_sell_invs_b xls,
okl_txd_ar_ln_dtls_b tld,
okl_txl_ar_inv_lns_b til,
okl_trx_ar_invoices_b tai,
okc_k_headers_b chr,
okl_invoice_formats_b inf -- 5138822
WHERE
xsib.TRX_STATUS_CODE = 'SUBMITTED' AND
xls.xsi_id_details = xsib.id AND
tld.id = xls.tld_id AND
til.id = tld.TIL_ID_DETAILS AND
tai.id = til.tai_id AND
tai.khr_id = chr.id
AND
chr.contract_number = p_contract_number AND
tai.clg_id IS NULL AND
tai.cpy_id IS NULL AND
tai.qte_id IS NULL AND
xls.amount >= 0 AND
xsib.inf_id = inf.id (+) and
XSIB.ID = XSIT.ID and
XSIT.LANGUAGE = USERENV('LANG')
ORDER BY 1,2,3,4,5,6,7,8,9,10
--end modified abhsaxen for performance SQLID 20563033
;
CURSOR c1 IS SELECT
xsi.customer_id customer_id,
xsi.currency_code currency,
xsi.customer_address_id bill_to_site,
xsi.receipt_method_id payment_method,
xsi.xtrx_private_label private_label,
TRUNC(xsi.TRX_DATE) date_consolidated,
NVL(
NVL((SELECT ity.id ity_id
FROM okl_invoice_types_b ity,
okl_invc_line_types_b ilt,
okl_invc_frmt_strms frs
WHERE ity.inf_id = inf.id
AND ilt.ity_id = ity.id
AND frs.ilt_id = ilt.id
AND til.sty_id = frs.sty_id),
(select ity1.id from okl_invoice_types_b ity1,
okl_invc_line_types_b ilt1
where ilt1.id = inf.ilt_id
and ilt1.ity_id = ity1.id)), -1
) inv_type, -- bug 5138822
xsi.inf_id inf_id,
tai.khr_id contract_id,
chr.contract_number contract_number,
'-1' prev_cons_invoice_num,
xsi.org_id org_id,
xsi.set_of_books_id set_of_books_id,
til.kle_id kle_id,
til.sty_id stream_id,
xsi.id xsi_id,
xls.id xls_id,
xls.amount c1_amount,
xls.sel_id sel_id,
xsi.legal_entity_id legal_entity_id -- for LE Uptake project 08-11-2006
FROM
okl_ext_sell_invs_v xsi,
okl_xtl_sell_invs_v xls,
okl_txl_ar_inv_lns_v til,
okl_trx_ar_invoices_v tai,
okc_k_headers_b chr,
okl_invoice_formats_b inf, -- 5138822
okl_parallel_processes pws
WHERE
xsi.TRX_STATUS_CODE = 'SUBMITTED' AND
xls.xsi_id_details = xsi.id AND
til.id = xls.til_id AND
tai.id = til.tai_id AND
tai.khr_id = chr.id AND
-- Contract Specific consolidation
chr.contract_number = NVL(p_contract_number,chr.contract_number) AND
-- Contract Specific consolidation
tai.clg_id IS NULL AND
tai.cpy_id IS NULL AND
tai.qte_id IS NULL AND
xls.amount >= 0 AND
xsi.inf_id = inf.id (+) AND -- 5138822
pws.object_type = 'CUSTOMER' AND
XSI.CUSTOMER_ID = to_number(pws.object_value) AND
pws.assigned_process = p_assigned_process
ORDER BY 1,2,3,4,5,6,7,8,9,10;
SELECT language_code
FROM FND_LANGUAGES
WHERE INSTALLED_FLAG IN ('I', 'B');
l_update_tbl update_tbl_type;
SELECT *
FROM okl_cnsld_ar_lines_v
WHERE cnr_id = p_cnr_id
ORDER BY sequence_number;
TYPE cnr_update_rec_type IS RECORD (
cnr_id NUMBER,
lln_id NUMBER,
lsm_id NUMBER,
xsi_id NUMBER,
xls_id NUMBER,
return_status VARCHAR2(1)
);
TYPE cnr_update_tbl_type IS TABLE OF cnr_update_rec_type
INDEX BY BINARY_INTEGER;
cnr_update_tbl cnr_update_tbl_type;
SELECT contract_number
FROM okc_k_headers_b
WHERE id = p_id;
SELECT name
FROM okl_strm_type_v
WHERE id = p_id;
SELECT khr_id
FROM okl_cnsld_ar_strms_b
WHERE id = p_lsm_id;
SELECT
DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID)
FROM dual;
SELECT NAME
FROM hr_operating_units
WHERE ORGANIZATION_ID=MO_GLOBAL.GET_CURRENT_ORG_ID;--MOAC- Concurrent request
cons_bill_tbl.delete;
l_update_tbl.delete;
cons_bill_tbl.delete;
p_update_tbl => l_update_tbl);
l_update_tbl);
g_cnr_tl_tbl(tl_count).last_updated_by := fnd_global.user_id;
g_cnr_tl_tbl(tl_count).last_update_date := sysdate;
g_cnr_tl_tbl(tl_count).last_update_login := fnd_global.login_id;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT(
p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,p_imav_rec
,x_imav_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
PRINT_TO_LOG('Performing bulk insert for cnr, record count is '||g_cnr_tbl.count);
insert into okl_cnsld_ar_hdrs_b
values g_cnr_tbl(x);
insert into okl_cnsld_ar_hdrs_tl
values g_cnr_tl_tbl(d);
PRINT_TO_LOG('Error during Header Insertion, rollback to H2');
g_cnr_tbl.DELETE;
g_cnr_tl_tbl.DELETE;
g_lln_tbl.DELETE;
g_lln_tl_tbl.DELETE;
g_lsm_tbl.DELETE;
g_lsm_tl_tbl.DELETE;
g_xsi_tbl.DELETE;
g_xsi_tl_tbl.DELETE;
g_xls_tbl.DELETE;
g_xls_tl_tbl.DELETE;
g_cnr_tbl.delete;
g_cnr_tl_tbl.delete;
g_lln_tl_tbl(tl_count).last_updated_by := fnd_global.user_id;
g_lln_tl_tbl(tl_count).last_update_date := sysdate;
g_lln_tl_tbl(tl_count).last_update_login := fnd_global.login_id;
PRINT_TO_LOG('Performing bulk insert for lln, record count is '||g_lln_tbl.count);
insert into okl_cnsld_ar_lines_b
values g_lln_tbl(x);
insert into okl_cnsld_ar_lines_tl
values g_lln_tl_tbl(e);
PRINT_TO_LOG('Error during Line Insertion, rollback to L2');
g_lln_tl_tbl.DELETE;
g_lsm_tbl.DELETE;
g_lsm_tl_tbl.DELETE;
g_xsi_tbl.DELETE;
g_xsi_tl_tbl.DELETE;
g_xls_tbl.DELETE;
g_xls_tl_tbl.DELETE;
delete from okl_cnsld_ar_hdrs_b
where id = g_lln_tbl(e).cnr_id;
g_lln_tbl.DELETE;
g_lln_tbl.delete;
g_lln_tl_tbl.delete;
g_lsm_tl_tbl(tl_count).last_updated_by := fnd_global.user_id;
g_lsm_tl_tbl(tl_count).last_update_date := sysdate;
g_lsm_tl_tbl(tl_count).last_update_login := fnd_global.login_id;
PRINT_TO_LOG('Performing bulk insert for lsm, record count is '||g_lsm_tbl.count);
insert into okl_cnsld_ar_strms_b
values g_lsm_tbl(x);
insert into okl_cnsld_ar_strms_tl
values g_lsm_tl_tbl(f);
PRINT_TO_LOG('Error during Stream Insertion, rollback to D2');
g_cnr_tl_tbl.delete;
g_lln_tl_tbl.DELETE;
g_lsm_tl_tbl.DELETE;
g_xsi_tbl.DELETE;
g_xsi_tl_tbl.DELETE;
g_xls_tbl.DELETE;
g_xls_tl_tbl.DELETE;
delete from okl_cnsld_ar_hdrs_b
where id = g_lln_tbl(f).cnr_id;
delete from okl_cnsld_ar_lines_b
where id = g_lsm_tbl(e).lln_id;
g_lsm_tbl.DELETE;
g_lln_tbl.DELETE;
g_cnr_tbl.DELETE;
g_lsm_tbl.delete;
g_lsm_tl_tbl.delete;
Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
Okl_Inv_Mssg_Att_Pub.INSERT_INV_MSSG_ATT(
p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,g_imav_tbl
,x_imav_tbl
);
Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
g_imav_tbl.DELETE;
PRINT_TO_LOG('Performing final bulk update for xsi, record count is '||g_xsi_tbl.COUNT );
update okl_ext_sell_invs_b
set trx_status_code = g_xsi_tbl(indx).trx_status_code,
-- xtrx_invoice_pull_yn = g_xsi_tbl(indx).xtrx_invoice_pull_yn,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where id = g_xsi_tbl(indx).id;
g_xsi_tbl.delete;
PRINT_TO_LOG('Error during Update of okl_ext_sell_invs_b, rollback to U5');
PRINT_TO_LOG('Performing final bulk update for xls, record count is '||g_xls_tbl.COUNT );
update okl_xtl_sell_invs_b
set lsm_id = g_xls_tbl(s).lsm_id,
xtrx_cons_stream_id = g_xls_tbl(s).lsm_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where id = g_xls_tbl(s).id;
g_xls_tbl.delete;
PRINT_TO_LOG('Error during Update of okl_xtl_sell_invs_b, rollback to U6');
PRINT_TO_LOG('Performing final bulk update for xsi tl, record count is '||g_xsi_tl_tbl.COUNT );
update okl_ext_sell_invs_tl
set xtrx_cons_invoice_number = g_xsi_tl_tbl(u).xtrx_cons_invoice_number,
xtrx_format_type = g_xsi_tl_tbl(u).xtrx_format_type,
xtrx_private_label = g_xsi_tl_tbl(u).xtrx_private_label,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where id = g_xsi_tl_tbl(u).id;
g_xsi_tl_tbl.delete;
PRINT_TO_LOG('Error during Update of okl_ext_sell_invs_tl, rollback to U7');
PRINT_TO_LOG('Performing final bulk update for xls tl, record count is '||g_xls_tl_tbl.COUNT );
update okl_xtl_sell_invs_tl
set xtrx_contract = g_xls_tl_tbl(t).xtrx_contract,
xtrx_asset = g_xls_tl_tbl(t).xtrx_asset,
xtrx_stream_type = g_xls_tl_tbl(t).xtrx_stream_type,
xtrx_stream_group = g_xls_tl_tbl(t).xtrx_stream_group,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where id = g_xls_tl_tbl(t).id;
g_xls_tl_tbl.delete;
PRINT_TO_LOG('Error during Update of okl_xtl_sell_invs_tl, rollback to U8');
cons_bill_tbl.delete;
l_update_tbl.delete;
g_cnr_tbl.DELETE;
g_cnr_tl_tbl.DELETE;
g_lln_tbl.DELETE;
g_lln_tl_tbl.DELETE;
g_lsm_tbl.DELETE;
g_lsm_tl_tbl.DELETE;
g_xsi_tbl.DELETE;
g_xsi_tl_tbl.DELETE;
g_xls_tbl.DELETE;
g_xls_tl_tbl.DELETE;
cons_bill_tbl.delete;
p_update_tbl => l_update_tbl);
l_update_tbl);
cons_bill_tbl.delete;
l_update_tbl.delete;
g_cnr_tl_tbl(tl_count).last_updated_by := fnd_global.user_id;
g_cnr_tl_tbl(tl_count).last_update_date := sysdate;
g_cnr_tl_tbl(tl_count).last_update_login := fnd_global.login_id;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT(
p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,p_imav_rec
,x_imav_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
PRINT_TO_LOG('Performing final bulk insert for cnr, record count is '||g_cnr_tbl.count);
insert into okl_cnsld_ar_hdrs_b
values g_cnr_tbl(x);
insert into okl_cnsld_ar_hdrs_tl
values g_cnr_tl_tbl(d);
PRINT_TO_LOG('Error during Header Insertion, rollback to H2');
g_cnr_tbl.DELETE;
g_cnr_tl_tbl.DELETE;
g_lln_tbl.DELETE;
g_lln_tl_tbl.DELETE;
g_lsm_tbl.DELETE;
g_lsm_tl_tbl.DELETE;
g_xsi_tbl.DELETE;
g_xsi_tl_tbl.DELETE;
g_xls_tbl.DELETE;
g_xls_tl_tbl.DELETE;
g_cnr_tbl.delete;
g_cnr_tl_tbl.delete;
g_lln_tl_tbl(tl_count).last_updated_by := fnd_global.user_id;
g_lln_tl_tbl(tl_count).last_update_date := sysdate;
g_lln_tl_tbl(tl_count).last_update_login := fnd_global.login_id;
PRINT_TO_LOG('Performing final bulk insert for lln, record count is '||g_lln_tbl.count);
insert into okl_cnsld_ar_lines_b
values g_lln_tbl(x);
insert into okl_cnsld_ar_lines_tl
values g_lln_tl_tbl(e);
PRINT_TO_LOG('Error during Line Insertion, rollback to L2');
g_lln_tl_tbl.DELETE;
g_lsm_tbl.DELETE;
g_lsm_tl_tbl.DELETE;
g_xsi_tbl.DELETE;
g_xsi_tl_tbl.DELETE;
g_xls_tbl.DELETE;
g_xls_tl_tbl.DELETE;
delete from okl_cnsld_ar_hdrs_b
where id = g_lln_tbl(e).cnr_id;
g_lln_tbl.DELETE;
g_lln_tbl.delete;
g_lln_tl_tbl.delete;
g_lsm_tl_tbl(tl_count).last_updated_by := fnd_global.user_id;
g_lsm_tl_tbl(tl_count).last_update_date := sysdate;
g_lsm_tl_tbl(tl_count).last_update_login := fnd_global.login_id;
PRINT_TO_LOG('Performing final bulk insert for lsm, record count is '||g_lsm_tbl.count);
insert into okl_cnsld_ar_strms_b
values g_lsm_tbl(x);
insert into okl_cnsld_ar_strms_tl
values g_lsm_tl_tbl(f);
PRINT_TO_LOG('Error during Stream Insertion, rollback to D2');
g_cnr_tl_tbl.delete;
g_lln_tl_tbl.DELETE;
g_lsm_tl_tbl.DELETE;
g_xsi_tbl.DELETE;
g_xsi_tl_tbl.DELETE;
g_xls_tbl.DELETE;
g_xls_tl_tbl.DELETE;
delete from okl_cnsld_ar_hdrs_b
where id = g_lln_tbl(f).cnr_id;
delete from okl_cnsld_ar_lines_b
where id = g_lsm_tbl(e).lln_id;
g_lsm_tbl.DELETE;
g_lln_tbl.DELETE;
g_cnr_tbl.DELETE;
g_lsm_tbl.delete;
g_lsm_tl_tbl.delete;
Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
Okl_Inv_Mssg_Att_Pub.INSERT_INV_MSSG_ATT(
p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,g_imav_tbl
,x_imav_tbl
);
Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
g_imav_tbl.DELETE;
PRINT_TO_LOG('Performing final bulk update for xsi, record count is '||g_xsi_tbl.COUNT );
update okl_ext_sell_invs_b
set trx_status_code = g_xsi_tbl(indx).trx_status_code,
-- xtrx_invoice_pull_yn = g_xsi_tbl(indx).xtrx_invoice_pull_yn,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where id = g_xsi_tbl(indx).id;
g_xsi_tbl.delete;
PRINT_TO_LOG('Error during Update of okl_ext_sell_invs_b, rollback to U5');
PRINT_TO_LOG('Performing final bulk update for xls, record count is '||g_xls_tbl.COUNT );
update okl_xtl_sell_invs_b
set lsm_id = g_xls_tbl(s).lsm_id,
xtrx_cons_stream_id = g_xls_tbl(s).lsm_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where id = g_xls_tbl(s).id;
g_xls_tbl.delete;
PRINT_TO_LOG('Error during Update of okl_xtl_sell_invs_b, rollback to U6');
PRINT_TO_LOG('Performing final bulk update for xsi tl, record count is '||g_xsi_tl_tbl.COUNT );
update okl_ext_sell_invs_tl
set xtrx_cons_invoice_number = g_xsi_tl_tbl(u).xtrx_cons_invoice_number,
xtrx_format_type = g_xsi_tl_tbl(u).xtrx_format_type,
xtrx_private_label = g_xsi_tl_tbl(u).xtrx_private_label,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where id = g_xsi_tl_tbl(u).id;
g_xsi_tl_tbl.delete;
PRINT_TO_LOG('Error during Update of okl_ext_sell_invs_tl, rollback to U7');
PRINT_TO_LOG('Performing final bulk update for xls tl, record count is '||g_xls_tl_tbl.COUNT );
update okl_xtl_sell_invs_tl
set xtrx_contract = g_xls_tl_tbl(t).xtrx_contract,
xtrx_asset = g_xls_tl_tbl(t).xtrx_asset,
xtrx_stream_type = g_xls_tl_tbl(t).xtrx_stream_type,
xtrx_stream_group = g_xls_tl_tbl(t).xtrx_stream_group,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where id = g_xls_tl_tbl(t).id;
g_xls_tl_tbl.delete;
PRINT_TO_LOG('Error during Update of okl_xtl_sell_invs_tl, rollback to U8');
delete okl_parallel_processes
where assigned_process = p_assigned_process;
delete okl_parallel_processes
where assigned_process = p_assigned_process;
delete okl_parallel_processes
where assigned_process = p_assigned_process;