[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,RETURN_ID
,yt.CUSTOMER_ID
FROM JL_BR_AR_BANK_RETURNS yt, ra_customer_merges m
WHERE (
yt.CUSTOMER_ID = m.DUPLICATE_ID
) AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID,
NUM_COL1_ORIG,
NUM_COL1_NEW,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
'JL_BR_AR_BANK_RETURNS',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY_ID_LIST(I),
NUM_COL1_ORIG_LIST(I),
NUM_COL1_NEW_LIST(I),
'U',
req_id,
hz_utility_pub.CREATED_BY,
hz_utility_pub.CREATION_DATE,
hz_utility_pub.LAST_UPDATE_LOGIN,
hz_utility_pub.LAST_UPDATE_DATE,
hz_utility_pub.LAST_UPDATED_BY
);
UPDATE JL_BR_AR_BANK_RETURNS yt SET
CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
, LAST_UPDATE_DATE=SYSDATE
, last_updated_by=arp_standard.profile.user_id
, last_update_login=arp_standard.profile.last_update_login
WHERE RETURN_ID=PRIMARY_KEY_ID_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,OCCURRENCE_ID
,yt.CUSTOMER_ID
,yt.SITE_USE_ID
FROM JL_BR_AR_OCCURRENCE_DOCS yt, ra_customer_merges m
WHERE (
yt.CUSTOMER_ID = m.DUPLICATE_ID
OR yt.SITE_USE_ID = m.DUPLICATE_SITE_ID
) AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID,
NUM_COL1_ORIG,
NUM_COL1_NEW,
NUM_COL2_ORIG,
NUM_COL2_NEW,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
'JL_BR_AR_OCCURRENCE_DOCS',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY_ID_LIST(I),
NUM_COL1_ORIG_LIST(I),
NUM_COL1_NEW_LIST(I),
NUM_COL2_ORIG_LIST(I),
NUM_COL2_NEW_LIST(I),
'U',
req_id,
hz_utility_pub.CREATED_BY,
hz_utility_pub.CREATION_DATE,
hz_utility_pub.LAST_UPDATE_LOGIN,
hz_utility_pub.LAST_UPDATE_DATE,
hz_utility_pub.LAST_UPDATED_BY
);
UPDATE JL_BR_AR_OCCURRENCE_DOCS yt SET
CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
,SITE_USE_ID=NUM_COL2_NEW_LIST(I)
, LAST_UPDATE_DATE=SYSDATE
, last_updated_by=arp_standard.profile.user_id
, last_update_login=arp_standard.profile.last_update_login
WHERE OCCURRENCE_ID=PRIMARY_KEY_ID_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,PAYMENT_SCHEDULE_ID
,yt.CUSTOMER_SITE_USE_ID
FROM JL_BR_AR_PAY_SCHED_AUX yt, ra_customer_merges m
WHERE (
yt.CUSTOMER_SITE_USE_ID = m.DUPLICATE_SITE_ID
) AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID,
NUM_COL1_ORIG,
NUM_COL1_NEW,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
'JL_BR_AR_PAY_SCHED_AUX',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY_ID_LIST(I),
NUM_COL1_ORIG_LIST(I),
NUM_COL1_NEW_LIST(I),
'U',
req_id,
hz_utility_pub.CREATED_BY,
hz_utility_pub.CREATION_DATE,
hz_utility_pub.LAST_UPDATE_LOGIN,
hz_utility_pub.LAST_UPDATE_DATE,
hz_utility_pub.LAST_UPDATED_BY
);
UPDATE JL_BR_AR_PAY_SCHED_AUX yt SET
CUSTOMER_SITE_USE_ID=NUM_COL1_NEW_LIST(I)
WHERE PAYMENT_SCHEDULE_ID=PRIMARY_KEY_ID_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,application_id
,set_of_books_id
,code_combination_id
,personnel_id
,accounting_date
,trans_description
,trans_id
,installment
,period_set_name
,period_name
,personnel_id
,journal_balance_flag
FROM JL_BR_JOURNALS_ALL yt, ra_customer_merges m
WHERE yt.PERSONNEL_ID = m.duplicate_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND yt.application_id=222;
UPDATE jl_br_journals_all j
SET personnel_id = (SELECT distinct m.customer_id
FROM ra_customer_merges m
WHERE j.personnel_id = m.duplicate_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num),
last_update_date = SYSDATE,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login,
JOURNAL_BALANCE_FLAG = 'N'
WHERE (personnel_id) IN (SELECT unique m.duplicate_id
FROM ra_customer_merges m
WHERE m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num)
AND application_id=222;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID,
PRIMARY_KEY_ID1,
PRIMARY_KEY_ID2,
PRIMARY_KEY_ID3,
PRIMARY_KEY1,
PRIMARY_KEY2,
PRIMARY_KEY3,
PRIMARY_KEY4,
PRIMARY_KEY5,
PRIMARY_KEY6,
NUM_COL1_ORIG,
NUM_COL1_NEW,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
'JL_BR_JOURNALS_ALL',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY1_LIST(I),
PRIMARY_KEY2_LIST(I),
PRIMARY_KEY3_LIST(I),
PRIMARY_KEY4_LIST(I),
to_char( PRIMARY_KEY5_LIST(I)),
PRIMARY_KEY6_LIST(I),
PRIMARY_KEY7_LIST(I),
to_char( PRIMARY_KEY8_LIST(I)),
PRIMARY_KEY9_LIST(I),
PRIMARY_KEY10_LIST(I),
NUM_COL1_ORIG_LIST(I),
NUM_COL1_NEW_LIST(I),
VCHAR_COL1_ORIG_LIST(I),
VCHAR_COL1_NEW_LIST(I),
'U',
req_id,
hz_utility_pub.CREATED_BY,
hz_utility_pub.CREATION_DATE,
hz_utility_pub.LAST_UPDATE_LOGIN,
hz_utility_pub.LAST_UPDATE_DATE,
hz_utility_pub.LAST_UPDATED_BY
);
UPDATE JL_BR_JOURNALS_ALL
SET
PERSONNEL_ID=NUM_COL1_NEW_LIST(I)
,JOURNAL_BALANCE_FLAG=VCHAR_COL1_NEW_LIST(I)
,last_update_date = SYSDATE
,last_updated_by = arp_standard.profile.user_id
,last_update_login = arp_standard.profile.last_update_login
WHERE personnel_id = NUM_COL1_ORIG_LIST(I)
AND application_id=222;
arp_message.set_name ('AR','AR_ROWS_UPDATED');
SELECT SET_OF_BOOKS_ID
FROM jl_br_balances_all
WHERE (personnel_id) IN (SELECT unique m.duplicate_id
FROM ra_customer_merges m
WHERE m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num)
AND application_id=222
FOR UPDATE NOWAIT;
SELECT distinct
m.CUSTOMER_MERGE_HEADER_ID
,yt.application_id
,yt.set_of_books_id
,yt.period_set_name
,yt.period_name
,yt.code_combination_id
,yt.personnel_id
,yt.personnel_id
,yt.ending_balance
,yt.org_id
,yt.ending_balance_sign
,yt.period_year
,yt.period_num
FROM JL_BR_BALANCES_ALL yt,
ra_customer_merges m
WHERE (yt.personnel_id = m.duplicate_id)
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
and yt.application_id =222;
SELECT /*+ ORDERED */
jb.set_of_books_id sob,
jb.period_set_name perset,
gp.period_year pyear,
gp.period_num pnum,
jb.period_name per,
jb.code_combination_id ccid,
jb.personnel_id venid,
SUM(DECODE(jb.trans_value_sign,'D',-1*jb.trans_value,jb.trans_value)) bal,
jb.org_id org_id
FROM jl_br_journals jb,
gl_periods gp
WHERE application_id=222
AND journal_balance_flag='N'
AND jb.period_name = gp.period_name
AND jb.period_set_name = gp.period_set_name
AND jb.personnel_id = pl_personnel_id
GROUP BY jb.set_of_books_id,
jb.period_set_name,
gp.period_year,
gp.period_num,
jb.period_name,
jb.code_combination_id,
jb.personnel_id,
jb.org_id
ORDER BY jb.set_of_books_id, gp.period_year, gp.period_num;
select period_name pername,
period_year peryear,
period_num pernum
from gl_periods
where period_set_name = pl_per_set
and (period_year = pl_max_pyear
and period_num > pl_max_pnum)
or (period_year > pl_max_pyear
and period_year < pl_pyear)
or (period_year = pl_pyear
and period_num < pl_pnum)
order by period_year, period_num;
Select unique org_id
from jl_br_journals_all
where application_id=222; */
Select org_id
from ar_system_parameters_all
where global_attribute_category = 'JL.BR.ARXSYSPA.Additional Info';
SELECT unique m.customer_id
FROM ra_customer_merges m
WHERE m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
select nvl(max(period_year),0)
into pl_max_pyear
from jl_br_balances
where application_id = 222
and set_of_books_id = r_bmb.sob
and period_set_name = r_bmb.perset;
select nvl(max(period_num),0)
into pl_max_pnum
from jl_br_balances
where application_id = 222
and set_of_books_id = r_bmb.sob
and period_set_name = r_bmb.perset
and period_year = pl_max_pyear;
select nvl(min(period_year),0)
into pl_min_pyear
from jl_br_balances
where application_id = 222
and set_of_books_id = r_bmb.sob
and period_set_name = r_bmb.perset
and code_combination_id = r_bmb.ccid
and personnel_id = r_bmb.venid;
SELECT NVL(MIN(period_num),0)
INTO pl_min_pnum
FROM jl_br_balances
WHERE application_id = 222
AND set_of_books_id = r_bmb.sob
AND period_set_name = r_bmb.perset
AND code_combination_id = r_bmb.ccid
AND personnel_id = r_bmb.venid
AND period_year = pl_min_pyear;
INSERT INTO jl_br_balances
(application_id,
set_of_books_id,
period_set_name,
period_name,
period_year,
period_num,
code_combination_id,
personnel_id,
ending_balance_sign,
ending_balance,
balance_error_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
org_id)
SELECT
222,
r_bmb.sob,
r_bmb.perset,
r_bmb.per,
r_bmb.pyear,
r_bmb.pnum,
code_combination_id,
personnel_id,
ending_balance_sign,
ending_balance,
'',
sysdate,
pl_user,
sysdate,
pl_user,
r_bmb.org_id
FROM jl_br_balances
WHERE application_id = 222
AND set_of_books_id = r_bmb.sob
AND period_year = pl_max_pyear
AND period_num = pl_max_pnum;
INSERT INTO jl_br_balances
(application_id,
set_of_books_id,
period_set_name,
period_name,
period_year,
period_num,
code_combination_id,
personnel_id,
ending_balance_sign,
ending_balance,
balance_error_flag,
creation_date,
last_update_date,
last_updated_by,
org_id)
SELECT
222,
b.set_of_books_id,
b.period_set_name,
g.period_name,
g.period_year,
g.period_num,
b.code_combination_id,
b.personnel_id,
b.ending_balance_sign,
b.ending_balance,
b.balance_error_flag,
sysdate,
sysdate,
pl_user,
b.org_id
FROM jl_br_balances b,
gl_periods g
WHERE b.application_id = 222
and b.period_set_name = g.period_set_name
and b.period_year = g.period_year
and b.period_year = pl_max_pyear
and b.period_num = pl_max_pnum
and g.period_num > pl_max_pnum
and g.period_num < r_bmb.pnum;
INSERT INTO jl_br_balances
(application_id,
set_of_books_id,
period_set_name,
period_name,
period_year,
period_num,
code_combination_id,
personnel_id,
ending_balance_sign,
ending_balance,
balance_error_flag,
creation_date,
last_update_date,
last_updated_by,
org_id)
SELECT
222,
set_of_books_id,
period_set_name,
r_per.pername,
r_per.peryear,
r_per.pernum,
code_combination_id,
personnel_id,
ending_balance_sign,
ending_balance,
balance_error_flag,
sysdate,
sysdate,
pl_user,
r_bmb.org_id
FROM jl_br_balances
WHERE application_id = 222
and set_of_books_id = r_bmb.sob
and period_set_name = r_bmb.perset
and period_year = pl_max_pyear
and period_num = pl_max_pnum;
WHEN NO_DATA_FOUND THEN NULL; -- Will be treated as First Insert
After insert new lines to jl_br_balances to new periods,
update pl_max_pyear and pl_max_pnum.
***************************************************************/
pl_max_pyear := pl_pyear;
INSERT INTO jl_br_balances
(application_id,
set_of_books_id,
period_set_name,
period_name,
period_year,
period_num,
code_combination_id,
personnel_id,
ending_balance_sign,
ending_balance,
creation_date,
created_by,
last_update_date,
last_updated_by,
org_id)
SELECT
222,
r_bmb.sob,
period_set_name,
period_name,
period_year,
period_num,
r_bmb.ccid,
r_bmb.venid,
'C',
0,
sysdate,
pl_user,
sysdate,
pl_user,
r_bmb.org_id
FROM gl_periods
WHERE period_set_name = r_bmb.perset
AND (r_bmb.pyear = pl_min_pyear
AND period_year = pl_min_pyear
AND period_num >= r_bmb.pnum
AND period_num < pl_min_pnum)
OR (r_bmb.pyear < pl_min_pyear
AND period_year = r_bmb.pyear
AND period_num >= r_bmb.pnum)
OR (r_bmb.pyear < pl_min_pyear
AND period_year > r_bmb.pyear
AND period_year < pl_min_pyear)
OR (r_bmb.pyear < pl_min_pyear
AND period_year = pl_min_pyear
AND period_num < pl_min_pnum);
After insert new lines to jl_br_balances to new periods,
update pl_min_pyear and pl_min_pnum.
***************************************************************/
pl_min_pyear := pl_pyear;
UPDATE jl_br_balances
SET ending_balance = ABS(ending_balance + r_bmb.bal),
ending_balance_sign = decode(sign(ending_balance + r_bmb.bal),-1,'D','C'),
balance_error_flag = '',
last_update_date = sysdate,
last_updated_by = pl_user,
last_update_login = FND_GLOBAL.login_id
WHERE application_id = 222
and set_of_books_id = r_bmb.sob
and period_set_name = r_bmb.perset
and code_combination_id = r_bmb.ccid
and personnel_id = r_bmb.venid
and ((period_year = r_bmb.pyear
and period_num >= r_bmb.pnum)
or period_year > r_bmb.pyear);
INSERT INTO jl_br_balances
(application_id,
set_of_books_id,
period_set_name,
period_name,
period_year,
period_num,
code_combination_id,
personnel_id,
ending_balance_sign,
ending_balance,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
created_by,
org_id)
VALUES (
222,
r_bmb.sob,
r_bmb.perset,
r_bmb.per,
r_bmb.pyear,
r_bmb.pnum,
r_bmb.ccid,
r_bmb.venid,
pl_sign,
pl_val,
sysdate,
sysdate,
pl_user,
FND_GLOBAL.login_id,
pl_user,
r_bmb.org_id);
UPDATE jl_br_journals
SET journal_balance_flag = 'Y'
WHERE application_id = 222
AND set_of_books_id = r_bmb.sob
AND code_combination_id = r_bmb.ccid
AND personnel_id = r_bmb.venid
AND period_set_name = r_bmb.perset
AND period_name = r_bmb.per
AND journal_balance_flag='N';
delete jl_br_balances_all
where (personnel_id) IN (SELECT unique m.duplicate_id
FROM ra_customer_merges m
WHERE m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num)
AND application_id=222;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY1,
PRIMARY_KEY2,
PRIMARY_KEY3,
PRIMARY_KEY4,
PRIMARY_KEY5,
PRIMARY_KEY6,
DEL_COL1,
DEL_COL2,
DEL_COL3,
DEL_COL4,
DEL_COL5,
DEL_COL6,
DEL_COL7,
DEL_COL8,
DEL_COL9,
DEL_COL10,
DEL_COL11,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) VALUES (
HZ_CUSTOMER_MERGE_LOG_s.nextval,
'JL_BR_BALANCES_ALL',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY1_LIST(I),
PRIMARY_KEY2_LIST(I),
PRIMARY_KEY3_LIST(I),
PRIMARY_KEY4_LIST(I),
PRIMARY_KEY5_LIST(I),
PRIMARY_KEY6_LIST(I),
PRIMARY_KEY1_LIST(I),
PRIMARY_KEY2_LIST(I),
PRIMARY_KEY3_LIST(I),
PRIMARY_KEY4_LIST(I),
PRIMARY_KEY5_LIST(I),
PRIMARY_KEY6_LIST(I),
NUM_COL2_ORIG_LIST(I),
NUM_COL3_ORIG_LIST(I),
NUM_COL4_ORIG_LIST(I),
NUM_COL5_ORIG_LIST(I),
NUM_COL6_ORIG_LIST(I),
'D',
req_id,
hz_utility_pub.CREATED_BY,
hz_utility_pub.CREATION_DATE,
hz_utility_pub.LAST_UPDATE_LOGIN,
hz_utility_pub.LAST_UPDATE_DATE,
hz_utility_pub.LAST_UPDATED_BY
);
DELETE JL_BR_BALANCES_ALL
WHERE application_id=PRIMARY_KEY1_LIST(I)
AND set_of_books_id=PRIMARY_KEY2_LIST(I)
AND period_set_name=PRIMARY_KEY3_LIST(I)
AND period_name=PRIMARY_KEY4_LIST(I)
AND code_combination_id=PRIMARY_KEY5_LIST(I)
AND personnel_id=PRIMARY_KEY6_LIST(I);
arp_message.set_name ('AR','AR_ROWS_UPDATED');