[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PL_SELECTION_CONTROL_ID NUMBER(38);
PL_SELECT_ACCOUNT_ID NUMBER(38);
PL_CS_SELECTION_STATUS VARCHAR2(30);
var_selection_control_chk NUMBER;
var_selection_control NUMBER;
SELECT bordero_status, selection_control_id,bordero_type
FROM jl_br_ar_borderos
WHERE bordero_id = P_BORDERO_ID
AND bordero_status in ('SELECTED', 'FORMATTED')
FOR UPDATE NOWAIT;
SELECT selection_control_id
FROM jl_br_ar_select_controls
WHERE selection_control_id = var_selection_control
FOR UPDATE;
select PS.PAYMENT_SCHEDULE_ID, DC.DOCUMENT_ID,
DC.PORTFOLIO_CODE, PS.GLOBAL_ATTRIBUTE10,
DC.FACTORING_AMOUNT, DC.BANK_INSTRUCTION_CODE1,
PS.AMOUNT_DUE_REMAINING, DC.BANK_INSTRUCTION_CODE2,
DC.NUM_DAYS_INSTRUCTION, PS.DUE_DATE,
DC.BANK_CHARGE_AMOUNT, DC.RECEIPT_METHOD_ID,
jlbo.BANK_NUMBER,
jlbo.BANK_OCCURRENCE_CODE,
jlbo.BANK_OCCURRENCE_TYPE,
b.OUTPUT_FORMAT,
JLBRRMA.BANK_CHARGES_CCID,
JLBRRMA.COLL_ENDORSEMENT_CCID,
JLBRRMA.BILLS_COLLECTION_CCID,
JLBRRMA.OTHER_CREDITS_CCID,
JLBRRMA.FACTORING_DOCS_CCID,
JLBRRMA.CALCULATED_INTEREST_CCID,
JLBRRMA.INTEREST_WRITEOFF_CCID,
RMA.CASH_CCID,
RMA.EARNED_CCID,
RMA.ON_ACCOUNT_CCID,
RMA.UNAPPLIED_CCID,
RMA.UNEARNED_CCID,
RMA.UNIDENTIFIED_CCID,
JLBRRMA.ABATEMENT_WRITEOFF_CCID,
JLBRRMA.ABATEMENT_REVENUE_CCID,
JLBRRMA.INTEREST_REVENUE_CCID,
JLBRRMA.CALCULATED_INTEREST_RECTRX_ID,
JLBRRMA.INTEREST_WRITEOFF_RECTRX_ID,
JLBRRMA.INTEREST_REVENUE_RECTRX_ID,
JLBRRMA.ABATEMENT_WRITEOFF_RECTRX_ID,
JLBRRMA.ABATEMENT_REVENUE_RECTRX_ID,
CSC.GL_DATE
from AR_PAYMENT_SCHEDULES_ALL PS, JL_BR_AR_COLLECTION_DOCS_ALL DC,
JL_BR_AR_BORDEROS B,
AR_RECEIPT_METHOD_ACCOUNTS_ALL RMA,
JL_BR_AR_SELECT_ACCOUNTS_ALL CSC,
JL_BR_AR_REC_MET_ACCTS_EXT_ALL JLBRRMA,
AP_BANK_ACCOUNTS_ALL apba,
AP_BANK_BRANCHES apbb,
JL_BR_AR_BANK_OCCURRENCES jlbo
where DC.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
and PS.STATUS = 'OP'
and B.BORDERO_STATUS = 'SELECTED'
and DC.DOCUMENT_STATUS = 'SELECTED'
and B.BORDERO_ID = DC.BORDERO_ID
and DC.BORDERO_ID = P_BORDERO_ID
and CSC.SELECT_ACCOUNT_ID = B.SELECT_ACCOUNT_ID
and JLBRRMA.RECEIPT_METHOD_ID = DC.RECEIPT_METHOD_ID
and JLBRRMA.BANK_ACCOUNT_ID = B.BANK_ACCOUNT_ID
and RMA.RECEIPT_METHOD_ID = DC.RECEIPT_METHOD_ID
and RMA.BANK_ACCOUNT_ID = B.BANK_ACCOUNT_ID
and apba.BANK_ACCOUNT_ID =b.bank_account_id
and apba.bank_branch_id = apbb.bank_branch_id
and jlbo.BANK_NUMBER = apbb.bank_number
and jlbo.STD_OCCURRENCE_CODE = 'REMITTANCE'
and jlbo.BANK_OCCURRENCE_TYPE = 'REMITTANCE_OCCURRENCE';
select PS.PAYMENT_SCHEDULE_ID, DC.DOCUMENT_ID,
DC.PORTFOLIO_CODE, PS.GLOBAL_ATTRIBUTE10,
DC.FACTORING_AMOUNT, DC.BANK_INSTRUCTION_CODE1,
PS.AMOUNT_DUE_REMAINING, DC.BANK_INSTRUCTION_CODE2,
DC.NUM_DAYS_INSTRUCTION, PS.DUE_DATE,
DC.BANK_CHARGE_AMOUNT, DC.RECEIPT_METHOD_ID,
--jlbo.BANK_NUMBER,
jlbo.bank_party_id,
jlbo.BANK_OCCURRENCE_CODE,
jlbo.BANK_OCCURRENCE_TYPE,
b.OUTPUT_FORMAT,
JLBRRMA.BANK_CHARGES_CCID,
JLBRRMA.COLL_ENDORSEMENT_CCID,
JLBRRMA.BILLS_COLLECTION_CCID,
JLBRRMA.OTHER_CREDITS_CCID,
JLBRRMA.FACTORING_DOCS_CCID,
JLBRRMA.CALCULATED_INTEREST_CCID,
JLBRRMA.INTEREST_WRITEOFF_CCID,
JLBRRMA.BILLS_DISCOUNT_CCID,
JLBRRMA.DISC_ENDORSEMENT_CCID,
JLBRRMA.DISCOUNTED_BILLS_CCID,
JLBRRMA.FACTORING_INTEREST_CCID,
RMA.CASH_CCID,
RMA.EARNED_CCID,
RMA.ON_ACCOUNT_CCID,
RMA.UNAPPLIED_CCID,
RMA.UNEARNED_CCID,
RMA.UNIDENTIFIED_CCID,
JLBRRMA.ABATEMENT_WRITEOFF_CCID,
JLBRRMA.ABATEMENT_REVENUE_CCID,
JLBRRMA.INTEREST_REVENUE_CCID,
JLBRRMA.CALCULATED_INTEREST_RECTRX_ID,
JLBRRMA.INTEREST_WRITEOFF_RECTRX_ID,
JLBRRMA.INTEREST_REVENUE_RECTRX_ID,
JLBRRMA.ABATEMENT_WRITEOFF_RECTRX_ID,
JLBRRMA.ABATEMENT_REVENUE_RECTRX_ID,
CSC.GL_DATE,
CeBankAccount.bank_account_id,
DC.org_id,
PS.CUSTOMER_ID
from AR_PAYMENT_SCHEDULES_ALL PS, JL_BR_AR_COLLECTION_DOCS_ALL DC,
JL_BR_AR_BORDEROS B,
AR_RECEIPT_METHOD_ACCOUNTS_ALL RMA,
JL_BR_AR_SELECT_ACCOUNTS_ALL CSC,
JL_BR_AR_REC_MET_ACCTS_EXT_ALL JLBRRMA,
JL_BR_AR_BANK_OCCURRENCES jlbo,
CE_BANK_ACCOUNTS CeBankAccount,
CE_BANK_ACCT_USES_ALL CeBankAcctUse,
HZ_PARTIES HzPartyBank
Where b.bank_acct_use_id = CeBankAcctUse.bank_acct_use_id
And CeBankAccount.bank_account_id = CeBankAcctUse.bank_account_id
And CeBankAccount.BANK_ID = HzPartyBank.PARTY_ID
--And HzPartyBank.COUNTRY = 'BR'
And DC.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
And PS.STATUS = 'OP'
And B.BORDERO_STATUS = 'SELECTED'
And DC.DOCUMENT_STATUS = 'SELECTED'
And B.BORDERO_ID = DC.BORDERO_ID
And DC.BORDERO_ID = P_BORDERO_ID
And CSC.SELECT_ACCOUNT_ID = B.SELECT_ACCOUNT_ID
And JLBRRMA.RECEIPT_METHOD_ID = DC.RECEIPT_METHOD_ID
And JLBRRMA.BANK_ACCT_USE_ID = B.BANK_ACCT_USE_ID
And RMA.RECEIPT_METHOD_ID = DC.RECEIPT_METHOD_ID
And RMA.REMIT_BANK_ACCT_USE_ID = B.BANK_ACCT_USE_ID
And jlbo.BANK_PARTY_ID = HzPartyBank.party_id
And jlbo.STD_OCCURRENCE_CODE = 'REMITTANCE'
And jlbo.BANK_OCCURRENCE_TYPE = 'REMITTANCE_OCCURRENCE';
select org_id into l_org_id from jl_br_ar_borderos_all
where bordero_id = p_bordero_id;
SELECT g.set_of_books_id, g.currency_code
INTO l_set_of_books_id, l_currency_code
FROM gl_sets_of_books g, ar_system_parameters a
where g.set_of_books_id = a.set_of_books_id
and rownum=1;
FETCH check1 INTO var_bordero_status_chk, var_selection_control,var_bordero_type;
FETCH check2 INTO var_selection_control_chk;
select JL_BR_AR_OCCURRENCE_DOCS_S.NEXTVAL
into l_occurrence_id
from dual;
insert into JL_BR_AR_OCCURRENCE_DOCS_ALL
(OCCURRENCE_ID,
DOCUMENT_ID,
BANK_OCCURRENCE_CODE,
--BANK_NUMBER,
BANK_PARTY_ID,
BANK_OCCURRENCE_TYPE,
OCCURRENCE_DATE,
OCCURRENCE_STATUS,
ORIGINAL_REMITTANCE_MEDIA,
REMITTANCE_MEDIA,
SELECTION_DATE,
BORDERO_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
PORTFOLIO_CODE,
TRADE_NOTE_NUMBER,
DUE_DATE,
DOCUMENT_AMOUNT,
BANK_INSTRUCTION_CODE1,
BANK_INSTRUCTION_CODE2,
NUM_DAYS_INSTRUCTION,
INTEREST_PERCENT,
INTEREST_PERIOD,
INTEREST_AMOUNT,
GRACE_DAYS,
DISCOUNT_LIMIT_DATE,
DISCOUNT_AMOUNT,
CUSTOMER_ID,
SITE_USE_ID,
ABATEMENT_AMOUNT,
FLAG_POST_GL,
GL_DATE,
ENDORSEMENT_CREDIT_CCID,
ENDORSEMENT_DEBIT_CCID,
ENDORSEMENT_DEBIT_AMOUNT,
ENDORSEMENT_CREDIT_AMOUNT,
BANK_CHARGES_CREDIT_CCID,
BANK_CHARGES_DEBIT_CCID,
BANK_CHARGES_DEBIT_AMOUNT,
BANK_CHARGES_CREDIT_AMOUNT,
ORG_ID)
select
l_occurrence_id, -- SLA Uptake - Bug#4301543
CURSOR_PS.DOCUMENT_ID,
CURSOR_PS.BANK_OCCURRENCE_CODE,
--CURSOR_PS.BANK_NUMBER,
CURSOR_PS.BANK_PARTY_ID,
CURSOR_PS.BANK_OCCURRENCE_TYPE,
SYSDATE,
'CONFIRMED',
CURSOR_PS.OUTPUT_FORMAT,
CURSOR_PS.OUTPUT_FORMAT,
null,
P_BORDERO_ID,
SYSDATE,
P_USER_ID,
P_USER_ID,
SYSDATE,
P_USER_ID,
CURSOR_PS.PORTFOLIO_CODE,
CURSOR_PS.GLOBAL_ATTRIBUTE10,
CURSOR_PS.DUE_DATE,
CURSOR_PS.AMOUNT_DUE_REMAINING,
CURSOR_PS.BANK_INSTRUCTION_CODE1,
CURSOR_PS.BANK_INSTRUCTION_CODE2,
CURSOR_PS.NUM_DAYS_INSTRUCTION,
fnd_number.canonical_to_number(nvl(CT.GLOBAL_ATTRIBUTE2,'')), /* Bug 2724399 */
nvl(CT.GLOBAL_ATTRIBUTE3,''),
fnd_number.canonical_to_number(nvl(CT.GLOBAL_ATTRIBUTE2,'')), /* Bug 2724399 */
nvl(CT.GLOBAL_ATTRIBUTE5,''),
nvl(T.DISCOUNT_DAYS,0) + CURSOR_PS.DUE_DATE,
nvl(CURSOR_PS.FACTORING_AMOUNT,0),
CT.BILL_TO_CUSTOMER_ID,
CT.BILL_TO_SITE_USE_ID,
0,
'N',
CURSOR_PS.GL_DATE,
CURSOR_PS.COLL_ENDORSEMENT_CCID,
CURSOR_PS.BILLS_COLLECTION_CCID,
CURSOR_PS.AMOUNT_DUE_REMAINING,
CURSOR_PS.AMOUNT_DUE_REMAINING,
decode(CURSOR_PS.BANK_CHARGE_AMOUNT,'','',0,'', CURSOR_PS.CASH_CCID),
decode(CURSOR_PS.BANK_CHARGE_AMOUNT,'','',0,'',CURSOR_PS.BANK_CHARGES_CCID),
CURSOR_PS.BANK_CHARGE_AMOUNT,
CURSOR_PS.BANK_CHARGE_AMOUNT,
CURSOR_PS.org_id
from RA_CUSTOMER_TRX CT, RA_TERMS_LINES_DISCOUNTS T,
AR_PAYMENT_SCHEDULES_ALL PS
where CT.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID
and T.TERM_ID(+) = PS.TERM_ID
and T.SEQUENCE_NUM(+) = PS.TERMS_SEQUENCE_NUMBER
and PS.PAYMENT_SCHEDULE_ID = CURSOR_PS.PAYMENT_SCHEDULE_ID;
/* select occurrence_id into l_occ from jl_br_ar_occurrence_docs_all
where document_id = cursor_ps.document_id; */
UPDATE JL_BR_AR_OCCURRENCE_DOCS_ALL
SET event_id =l_event_id
WHERE occurrence_id = l_occurrence_id;
/* Update collection flag to WRITE_OFF */
/* update AR_PAYMENT_SCHEDULES
set GLOBAL_ATTRIBUTE11 = 'Y',
GLOBAL_ATTRIBUTE9 = 'BANK'
where PAYMENT_SCHEDULE_ID = CURSOR_PS.PAYMENT_SCHEDULE_ID;
arp_ps_pkg.update_p(l_ps_rec, CURSOR_PS.PAYMENT_SCHEDULE_ID);
/* Update status to FORMATTED and update bank accounts */
update JL_BR_AR_COLLECTION_DOCS_ALL
set DOCUMENT_STATUS = 'FORMATTED',
CASH_CCID = cursor_ps.CASH_CCID,
BANK_CHARGES_CCID = cursor_ps.BANK_CHARGES_CCID,
COLL_ENDORSEMENTS_CCID = cursor_ps.COLL_ENDORSEMENT_CCID,
BILLS_COLLECTION_CCID = cursor_ps.BILLS_COLLECTION_CCID,
CALCULATED_INTEREST_CCID = cursor_ps.CALCULATED_INTEREST_CCID,
INTEREST_WRITEOFF_CCID = cursor_ps.INTEREST_WRITEOFF_CCID,
ABATEMENT_WRITEOFF_CCID = cursor_ps.ABATEMENT_WRITEOFF_CCID,
ABATEMENT_REVENUE_CCID = cursor_ps.ABATEMENT_REVENUE_CCID,
INTEREST_REVENUE_CCID = cursor_ps.INTEREST_REVENUE_CCID,
CALCULATED_INTEREST_RECTRX_ID = cursor_ps.CALCULATED_INTEREST_RECTRX_ID,
INTEREST_WRITEOFF_RECTRX_ID = cursor_ps.INTEREST_WRITEOFF_RECTRX_ID,
INTEREST_REVENUE_RECTRX_ID = cursor_ps.INTEREST_REVENUE_RECTRX_ID,
ABATEMENT_WRITEOFF_RECTRX_ID = cursor_ps.ABATEMENT_WRITEOFF_RECTRX_ID,
ABATE_REVENUE_RECTRX_ID = cursor_ps.ABATEMENT_REVENUE_RECTRX_ID
where DOCUMENT_ID = CURSOR_PS.DOCUMENT_ID;
select JL_BR_AR_OCCURRENCE_DOCS_S.NEXTVAL
into l_occurrence_id
from dual;
INSERT INTO JL_BR_AR_OCCURRENCE_DOCS_ALL
(OCCURRENCE_ID,
DOCUMENT_ID,
BANK_OCCURRENCE_CODE,
--BANK_NUMBER,
BANK_PARTY_ID,
BANK_OCCURRENCE_TYPE,
OCCURRENCE_DATE,
OCCURRENCE_STATUS,
ORIGINAL_REMITTANCE_MEDIA,
REMITTANCE_MEDIA,
SELECTION_DATE,
BORDERO_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
PORTFOLIO_CODE,
TRADE_NOTE_NUMBER,
DUE_DATE,
DOCUMENT_AMOUNT,
BANK_INSTRUCTION_CODE1,
BANK_INSTRUCTION_CODE2,
NUM_DAYS_INSTRUCTION,
INTEREST_PERCENT,
INTEREST_PERIOD,
INTEREST_AMOUNT,
GRACE_DAYS,
DISCOUNT_LIMIT_DATE,
DISCOUNT_AMOUNT,
CUSTOMER_ID,
SITE_USE_ID,
ABATEMENT_AMOUNT,
FLAG_POST_GL,
GL_DATE,
ENDORSEMENT_CREDIT_CCID,
ENDORSEMENT_DEBIT_CCID,
ENDORSEMENT_DEBIT_AMOUNT,
ENDORSEMENT_CREDIT_AMOUNT,
BANK_CHARGES_CREDIT_CCID,
BANK_CHARGES_DEBIT_CCID,
BANK_CHARGES_DEBIT_AMOUNT,
BANK_CHARGES_CREDIT_AMOUNT,
FACTOR_INTEREST_CREDIT_CCID,
FACTOR_INTEREST_DEBIT_CCID,
FACTOR_INTEREST_DEBIT_AMOUNT,
FACTOR_INTEREST_CREDIT_AMOUNT,
ORG_ID)
select
l_occurrence_id, -- SLA Uptake - Bug#4301543
CURSOR_PS.DOCUMENT_ID,
CURSOR_PS.BANK_OCCURRENCE_CODE,
--CURSOR_PS.BANK_NUMBER,
CURSOR_PS.BANK_PARTY_ID,
CURSOR_PS.BANK_OCCURRENCE_TYPE,
SYSDATE,
'CONFIRMED',
CURSOR_PS.OUTPUT_FORMAT,
CURSOR_PS.OUTPUT_FORMAT,
null,
P_BORDERO_ID,
SYSDATE,
P_USER_ID,
P_USER_ID,
SYSDATE,
P_USER_ID,
CURSOR_PS.PORTFOLIO_CODE,
CURSOR_PS.GLOBAL_ATTRIBUTE10,
CURSOR_PS.DUE_DATE,
CURSOR_PS.AMOUNT_DUE_REMAINING,
CURSOR_PS.BANK_INSTRUCTION_CODE1,
CURSOR_PS.BANK_INSTRUCTION_CODE2,
CURSOR_PS.NUM_DAYS_INSTRUCTION,
fnd_number.canonical_to_number(nvl(CT.GLOBAL_ATTRIBUTE2,'')), -- Bug 3107496
nvl(CT.GLOBAL_ATTRIBUTE3,''),
fnd_number.canonical_to_number(nvl(CT.GLOBAL_ATTRIBUTE2,'')), -- Bug 3107496
nvl(CT.GLOBAL_ATTRIBUTE5,''),
nvl(T.DISCOUNT_DAYS,0) + CURSOR_PS.DUE_DATE,
nvl(CURSOR_PS.FACTORING_AMOUNT,0),
CT.BILL_TO_CUSTOMER_ID,
CT.BILL_TO_SITE_USE_ID,
0,
'N',
CURSOR_PS.GL_DATE,
-- Bug#8302889 in factoring
-- CURSOR_PS.BILLS_DISCOUNT_CCID,
CURSOR_PS.DISC_ENDORSEMENT_CCID,
CURSOR_PS.BILLS_DISCOUNT_CCID,
CURSOR_PS.AMOUNT_DUE_REMAINING,
CURSOR_PS.AMOUNT_DUE_REMAINING,
decode(CURSOR_PS.BANK_CHARGE_AMOUNT,'','',0,'', CURSOR_PS.CASH_CCID),
decode(CURSOR_PS.BANK_CHARGE_AMOUNT,'','',0,'',CURSOR_PS.BANK_CHARGES_CCID),
CURSOR_PS.BANK_CHARGE_AMOUNT,
CURSOR_PS.BANK_CHARGE_AMOUNT,
decode(CURSOR_PS.FACTORING_AMOUNT,'','',0,'', CURSOR_PS.CASH_CCID),
decode(CURSOR_PS.FACTORING_AMOUNT,'','',0,'',CURSOR_PS.FACTORING_INTEREST_CCID),
CURSOR_PS.FACTORING_AMOUNT,
CURSOR_PS.FACTORING_AMOUNT,
CURSOR_PS.ORG_ID
from RA_CUSTOMER_TRX CT, RA_TERMS_LINES_DISCOUNTS T,
AR_PAYMENT_SCHEDULES PS
where CT.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID
and T.TERM_ID(+) = PS.TERM_ID
and T.SEQUENCE_NUM(+) = PS.TERMS_SEQUENCE_NUMBER
and PS.PAYMENT_SCHEDULE_ID = CURSOR_PS.PAYMENT_SCHEDULE_ID;
UPDATE JL_BR_AR_OCCURRENCE_DOCS_ALL
SET event_id =l_event_id
WHERE occurrence_id = l_occurrence_id;
arp_ps_pkg.update_p(l_ps_rec, CURSOR_PS.PAYMENT_SCHEDULE_ID);
/* Update status to FORMATTED and update bank accounts */
update JL_BR_AR_COLLECTION_DOCS_ALL
set DOCUMENT_STATUS = 'FORMATTED',
CASH_CCID = cursor_ps.CASH_CCID,
BANK_CHARGES_CCID = cursor_ps.BANK_CHARGES_CCID,
COLL_ENDORSEMENTS_CCID = cursor_ps.DISC_ENDORSEMENT_CCID,
BILLS_COLLECTION_CCID = cursor_ps.BILLS_DISCOUNT_CCID,
CALCULATED_INTEREST_CCID = cursor_ps.CALCULATED_INTEREST_CCID,
INTEREST_WRITEOFF_CCID = cursor_ps.INTEREST_WRITEOFF_CCID,
ABATEMENT_WRITEOFF_CCID = cursor_ps.ABATEMENT_WRITEOFF_CCID,
ABATEMENT_REVENUE_CCID = cursor_ps.ABATEMENT_REVENUE_CCID,
INTEREST_REVENUE_CCID = cursor_ps.INTEREST_REVENUE_CCID,
CALCULATED_INTEREST_RECTRX_ID = cursor_ps.CALCULATED_INTEREST_RECTRX_ID,
INTEREST_WRITEOFF_RECTRX_ID = cursor_ps.INTEREST_WRITEOFF_RECTRX_ID, INTEREST_REVENUE_RECTRX_ID = cursor_ps.INTEREST_REVENUE_RECTRX_ID,
ABATEMENT_WRITEOFF_RECTRX_ID = cursor_ps.ABATEMENT_WRITEOFF_RECTRX_ID,
ABATE_REVENUE_RECTRX_ID = cursor_ps.ABATEMENT_REVENUE_RECTRX_ID,
CASH_RECEIPT_ID = x_cash_receipt_id
where DOCUMENT_ID = CURSOR_PS.DOCUMENT_ID;
select CS.SELECTION_STATUS,
B.SELECTION_CONTROL_ID,
B.SELECT_ACCOUNT_ID
into PL_CS_SELECTION_STATUS,
PL_SELECTION_CONTROL_ID,
PL_SELECT_ACCOUNT_ID
from JL_BR_AR_SELECT_CONTROLS_ALL CS,
JL_BR_AR_BORDEROS B
where CS.SELECTION_CONTROL_ID = B.SELECTION_CONTROL_ID
and B.BORDERO_ID = P_BORDERO_ID;
if PL_CS_SELECTION_STATUS = 'SELECTED'
then
/* Update status to FORMATTED */
update JL_BR_AR_SELECT_CONTROLS
set SELECTION_STATUS = 'FORMATTED',
REMITTANCE_DATE = SYSDATE
where SELECTION_CONTROL_ID = PL_SELECTION_CONTROL_ID;
update JL_BR_AR_SELECT_ACCOUNTS
set FORMAT_DATE= SYSDATE,
REMITTANCE_DATE = SYSDATE
where SELECT_ACCOUNT_ID = PL_SELECT_ACCOUNT_ID;
/* Update remittance date */
update JL_BR_AR_SELECT_CONTROLS
set REMITTANCE_DATE = SYSDATE
where SELECTION_CONTROL_ID = PL_SELECTION_CONTROL_ID;
update JL_BR_AR_SELECT_ACCOUNTS
set REMITTANCE_DATE = SYSDATE
where SELECT_ACCOUNT_ID = PL_SELECT_ACCOUNT_ID;
/* Update status to FORMATTED */
update JL_BR_AR_BORDEROS
set BORDERO_STATUS = 'FORMATTED',
REMITTANCE_DATE = SYSDATE
where BORDERO_ID = P_BORDERO_ID;
insert into JL_BR_AR_REMIT_BORDEROS_ALL
(FORMAT_REQUEST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
BORDERO_ID,
OUTPUT_PROGRAM_ID,
ORG_ID)
select
fnd_global.conc_request_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
sysdate,
fnd_global.user_id,
bordero_id,
output_program_id,
org_id
from jl_br_ar_borderos_all
where bordero_id = P_BORDERO_ID;
PL_SELECTION_CONTROL_ID number;
PL_SELECT_ACCOUNT_ID number;
PL_CS_SELECTION_STATUS varchar2(30);
var_selection_control NUMBER;
var_selection_control_chk NUMBER;
SELECT bordero_status, selection_control_id
FROM jl_br_ar_borderos_all
WHERE bordero_id = P_BORDERO_ID
AND bordero_status in ('SELECTED', 'FORMATTED')
FOR UPDATE NOWAIT;
SELECT selection_control_id
FROM jl_br_ar_select_controls_all
WHERE selection_control_id = var_selection_control
FOR UPDATE;
select OD.OCCURRENCE_ID, BO.STD_OCCURRENCE_CODE, OD.DOCUMENT_ID
from JL_BR_AR_OCCURRENCE_DOCS_ALL OD,
JL_BR_AR_BORDEROS B,
JL_BR_AR_BANK_OCCURRENCES BO
where OD.OCCURRENCE_STATUS = 'SELECTED'
and B.BORDERO_STATUS = 'SELECTED'
and B.BORDERO_ID = OD.BORDERO_ID
and OD.BORDERO_ID = P_BORDERO_ID
and BO.BANK_OCCURRENCE_CODE = OD.BANK_OCCURRENCE_CODE
--and BO.BANK_NUMBER = OD.BANK_NUMBER;
FETCH check1 INTO var_bordero_status_chk, var_selection_control;
FETCH check2 INTO var_selection_control_chk;
/* Update occurrence status to CONFIRMED */
update JL_BR_AR_OCCURRENCE_DOCS_ALL
set OCCURRENCE_STATUS = 'CONFIRMED'
where OCCURRENCE_ID = TMP.OCCURRENCE_ID;
/* update AR_PAYMENT_SCHEDULES
set GLOBAL_ATTRIBUTE9 = 'REGISTRY'
where PAYMENT_SCHEDULE_ID = (select PAYMENT_SCHEDULE_ID
from JL_BR_AR_COLLECTION_DOCS
where DOCUMENT_ID = TMP.DOCUMENT_ID);
/* Replace Update by AR's Table Handlers. Bug # 2249731 */
SELECT payment_schedule_id
INTO l_ps_id
FROM jl_br_ar_collection_docs
WHERE document_id = TMP.DOCUMENT_ID;
arp_ps_pkg.update_p(l_ps_rec, l_ps_id);
/* UPDATE ar_payment_schedules
SET selected_for_receipt_batch_id = NULL,
global_attribute9 = 'MANUAL_RECEIPT',
global_attribute11 = 'N'
where PAYMENT_SCHEDULE_ID = (select PAYMENT_SCHEDULE_ID
from JL_BR_AR_COLLECTION_DOCS
where DOCUMENT_ID = TMP.DOCUMENT_ID);
/* Replace Update by AR's Table Handlers. Bug # 2249731 */
SELECT payment_schedule_id
INTO l_ps_id
FROM jl_br_ar_collection_docs
WHERE document_id = TMP.DOCUMENT_ID;
l_ps_rec.selected_for_receipt_batch_id := NULL;
arp_ps_pkg.update_p(l_ps_rec, l_ps_id);
select B.SELECTION_CONTROL_ID,
B.SELECT_ACCOUNT_ID,
CS.SELECTION_STATUS
into PL_SELECTION_CONTROL_ID,
PL_SELECT_ACCOUNT_ID,
PL_CS_SELECTION_STATUS
from JL_BR_AR_BORDEROS_ALL B, JL_BR_AR_SELECT_CONTROLS_ALL CS
where B.BORDERO_ID = P_BORDERO_ID
and CS.SELECTION_CONTROL_ID = B.SELECTION_CONTROL_ID;
/* Update bordero status to FORMATTED */
update JL_BR_AR_BORDEROS_ALL
set BORDERO_STATUS = 'FORMATTED',
REMITTANCE_DATE = SYSDATE
where BORDERO_ID = P_BORDERO_ID;
if PL_CS_SELECTION_STATUS = 'SELECTED'
then
/* Update selection status to FORMATTED */
update JL_BR_AR_SELECT_CONTROLS_ALL
set SELECTION_STATUS = 'FORMATTED',
GENERATION_DATE = SYSDATE,
REMITTANCE_DATE = SYSDATE
where SELECTION_CONTROL_ID = PL_SELECTION_CONTROL_ID;
update JL_BR_AR_SELECT_ACCOUNTS
set FORMAT_DATE = SYSDATE,
REMITTANCE_DATE = SYSDATE
where SELECT_ACCOUNT_ID = PL_SELECT_ACCOUNT_ID;
/* Update remittance date */
update JL_BR_AR_SELECT_CONTROLS_ALL
set REMITTANCE_DATE = SYSDATE
where SELECTION_CONTROL_ID = PL_SELECTION_CONTROL_ID;
update JL_BR_AR_SELECT_ACCOUNTS
set REMITTANCE_DATE = SYSDATE
where SELECT_ACCOUNT_ID = PL_SELECT_ACCOUNT_ID;
insert into JL_BR_AR_REMIT_BORDEROS_ALL
(FORMAT_REQUEST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
BORDERO_ID,
OUTPUT_PROGRAM_ID,
ORG_ID)
select
fnd_global.conc_request_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
sysdate,
fnd_global.user_id,
bordero_id,
output_program_id,
mo_global.get_current_org_id
from jl_br_ar_borderos
where bordero_id = P_BORDERO_ID;
SELECT meaning
INTO name
FROM fnd_lookups
WHERE lookup_code = code
AND lookup_type = 'JLBR_AR_SLA_ACCT_LINE_TYPE';
SELECT meaning
INTO name
FROM fnd_lookups
WHERE lookup_code = trx_class
AND lookup_type = 'JLBR_AR_SLA_TRX_CLASS';