[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct m.CUSTOMER_MERGE_HEADER_ID
,yt.TRANSACTION_ID
,yt.customer_id
,yt.ship_id
,yt.bill_id
FROM MRP_FORECAST_DATES yt, ra_customer_merges m
WHERE (
yt.customer_id = m.DUPLICATE_ID
OR yt.ship_id = m.DUPLICATE_SITE_ID
OR yt.bill_id = m.DUPLICATE_SITE_ID
) AND m.process_flag = 'N'
AND yt.origination_type = '10' /* Overconsumption */
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,
NUM_COL3_ORIG,
NUM_COL3_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,
'MRP_FORECAST_DATES',
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),
NUM_COL3_ORIG_LIST(I),
NUM_COL3_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 MRP_FORECAST_DATES yt SET
customer_id=NUM_COL1_NEW_LIST(I)
,ship_id=NUM_COL2_NEW_LIST(I)
,bill_id=NUM_COL3_NEW_LIST(I)
, LAST_UPDATE_DATE=SYSDATE
, last_updated_by=arp_standard.profile.user_id
, last_update_login=arp_standard.profile.last_update_login
, REQUEST_ID=req_id
, PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
, PROGRAM_ID=arp_standard.profile.program_id
, PROGRAM_UPDATE_DATE=SYSDATE
WHERE TRANSACTION_ID=PRIMARY_KEY_ID_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct m.CUSTOMER_MERGE_HEADER_ID
,yt.ORGANIZATION_ID
,yt.FORECAST_DESIGNATOR
,yt.customer_id
,yt.ship_id
,yt.bill_id
FROM MRP_FORECAST_DESIGNATORS yt, ra_customer_merges m
WHERE (
yt.customer_id = m.DUPLICATE_ID
OR yt.ship_id = m.DUPLICATE_SITE_ID
OR yt.bill_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_KEY1,
PRIMARY_KEY2,
NUM_COL1_ORIG,
NUM_COL1_NEW,
NUM_COL2_ORIG,
NUM_COL2_NEW,
NUM_COL3_ORIG,
NUM_COL3_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,
'MRP_FORECAST_DESIGNATORS',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY1_LIST(I),
PRIMARY_KEY2_LIST(I),
NUM_COL1_ORIG_LIST(I),
NUM_COL1_NEW_LIST(I),
NUM_COL2_ORIG_LIST(I),
NUM_COL2_NEW_LIST(I),
NUM_COL3_ORIG_LIST(I),
NUM_COL3_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 MRP_FORECAST_DESIGNATORS yt SET
customer_id=NUM_COL1_NEW_LIST(I)
,ship_id=NUM_COL2_NEW_LIST(I)
,bill_id=NUM_COL3_NEW_LIST(I)
, LAST_UPDATE_DATE=SYSDATE
, last_updated_by=arp_standard.profile.user_id
, last_update_login=arp_standard.profile.last_update_login
, REQUEST_ID=req_id
, PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
, PROGRAM_ID=arp_standard.profile.program_id
, PROGRAM_UPDATE_DATE=SYSDATE
WHERE ORGANIZATION_ID=PRIMARY_KEY1_LIST(I)
AND FORECAST_DESIGNATOR=PRIMARY_KEY2_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
| Account merge procedure for the table, MRP_FORECAST_UPDATES
|
| NOTES:
|
|--------------------------------------------------------------*/
PROCEDURE MRP_FU (
req_id NUMBER,
set_num NUMBER,
process_mode VARCHAR2) IS
TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
INDEX BY BINARY_INTEGER;
MRP_FORECAST_UPDATES.TRANSACTION_ID%TYPE
INDEX BY BINARY_INTEGER;
MRP_FORECAST_UPDATES.customer_id%TYPE
INDEX BY BINARY_INTEGER;
MRP_FORECAST_UPDATES.ship_id%TYPE
INDEX BY BINARY_INTEGER;
MRP_FORECAST_UPDATES.bill_id%TYPE
INDEX BY BINARY_INTEGER;
SELECT distinct m.CUSTOMER_MERGE_HEADER_ID
,yt.TRANSACTION_ID
,yt.customer_id
,yt.ship_id
,yt.bill_id
FROM MRP_FORECAST_UPDATES yt, ra_customer_merges m
WHERE (
yt.customer_id = m.DUPLICATE_ID
OR yt.ship_id = m.DUPLICATE_SITE_ID
OR yt.bill_id = m.DUPLICATE_SITE_ID
) AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
ARP_MESSAGE.SET_TOKEN('TABLE_NAME','MRP_FORECAST_UPDATES',FALSE);
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,
NUM_COL3_ORIG,
NUM_COL3_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,
'MRP_FORECAST_UPDATES',
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),
NUM_COL3_ORIG_LIST(I),
NUM_COL3_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 MRP_FORECAST_UPDATES yt SET
customer_id=NUM_COL1_NEW_LIST(I)
,ship_id=NUM_COL2_NEW_LIST(I)
,bill_id=NUM_COL3_NEW_LIST(I)
, LAST_UPDATE_DATE=SYSDATE
, last_updated_by=arp_standard.profile.user_id
, last_update_login=arp_standard.profile.last_update_login
, REQUEST_ID=req_id
, PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
, PROGRAM_ID=arp_standard.profile.program_id
, PROGRAM_UPDATE_DATE=SYSDATE
WHERE TRANSACTION_ID=PRIMARY_KEY_ID_LIST(I)
AND CUSTOMER_ID=NUM_COL1_ORIG_LIST(I)
AND NVL(SHIP_ID,-23453)=NVL(NUM_COL2_ORIG_LIST(I),-23453)
AND NVL(BILL_ID,-23453)=NVL(NUM_COL3_ORIG_LIST(I),-23453)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
| Account merge procedure for the table, MRP_SALES_ORDER_UPDATES
|
|
|--------------------------------------------------------------*/
PROCEDURE MRP_SOU (
req_id NUMBER,
set_num NUMBER,
process_mode VARCHAR2) IS
TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
INDEX BY BINARY_INTEGER;
TYPE UPDATE_SEQ_NUM_LIST_TYPE IS TABLE OF
MRP_SALES_ORDER_UPDATES.UPDATE_SEQ_NUM%TYPE
INDEX BY BINARY_INTEGER;
PRIMARY_KEY_ID_LIST UPDATE_SEQ_NUM_LIST_TYPE;
MRP_SALES_ORDER_UPDATES.current_customer_id%TYPE
INDEX BY BINARY_INTEGER;
MRP_SALES_ORDER_UPDATES.current_ship_id%TYPE
INDEX BY BINARY_INTEGER;
MRP_SALES_ORDER_UPDATES.current_bill_id%TYPE
INDEX BY BINARY_INTEGER;
MRP_SALES_ORDER_UPDATES.previous_customer_id%TYPE
INDEX BY BINARY_INTEGER;
MRP_SALES_ORDER_UPDATES.previous_bill_id%TYPE
INDEX BY BINARY_INTEGER;
MRP_SALES_ORDER_UPDATES.previous_ship_id%TYPE
INDEX BY BINARY_INTEGER;
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,UPDATE_SEQ_NUM
,current_customer_id
,current_ship_id
,current_bill_id
,previous_customer_id
,previous_bill_id
,previous_ship_id
FROM MRP_SALES_ORDER_UPDATES yt, ra_customer_merges m
WHERE (
yt.current_customer_id = m.DUPLICATE_ID
OR yt.current_ship_id = m.DUPLICATE_SITE_ID
OR yt.current_bill_id = m.DUPLICATE_SITE_ID
OR yt.previous_customer_id = m.DUPLICATE_ID
OR yt.previous_bill_id = m.DUPLICATE_SITE_ID
OR yt.previous_ship_id = m.DUPLICATE_SITE_ID
) AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
ARP_MESSAGE.SET_TOKEN('TABLE_NAME','MRP_SALES_ORDER_UPDATES',FALSE);
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,
NUM_COL3_ORIG,
NUM_COL3_NEW,
NUM_COL4_ORIG,
NUM_COL4_NEW,
NUM_COL5_ORIG,
NUM_COL5_NEW,
NUM_COL6_ORIG,
NUM_COL6_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,
'MRP_SALES_ORDER_UPDATES',
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),
NUM_COL3_ORIG_LIST(I),
NUM_COL3_NEW_LIST(I),
NUM_COL4_ORIG_LIST(I),
NUM_COL4_NEW_LIST(I),
NUM_COL5_ORIG_LIST(I),
NUM_COL5_NEW_LIST(I),
NUM_COL6_ORIG_LIST(I),
NUM_COL6_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 MRP_SALES_ORDER_UPDATES yt SET
current_customer_id=NUM_COL1_NEW_LIST(I)
,current_ship_id=NUM_COL2_NEW_LIST(I)
,current_bill_id=NUM_COL3_NEW_LIST(I)
,previous_customer_id=NUM_COL4_NEW_LIST(I)
,previous_bill_id=NUM_COL5_NEW_LIST(I)
,previous_ship_id=NUM_COL6_NEW_LIST(I)
, LAST_UPDATE_DATE=SYSDATE
, last_updated_by=arp_standard.profile.user_id
, last_update_login=arp_standard.profile.last_update_login
, REQUEST_ID=req_id
, PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
, PROGRAM_ID=arp_standard.profile.program_id
, PROGRAM_UPDATE_DATE=SYSDATE
WHERE UPDATE_SEQ_NUM=PRIMARY_KEY_ID_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct m.CUSTOMER_MERGE_HEADER_ID
,yt.ASSIGNMENT_ID
,yt.customer_id
,yt.ship_to_site_id
FROM MRP_SR_ASSIGNMENTS yt, ra_customer_merges m
WHERE (
yt.customer_id = m.DUPLICATE_ID
OR yt.ship_to_site_id = m.DUPLICATE_SITE_ID
) AND yt.assignment_type in (4,5,6)
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
select v1.assignment_set_id, v1.assignment_type,
v1.organization_id, r1.duplicate_id, r1.duplicate_site_id,
v1.sourcing_rule_type, v1.inventory_item_id, v1.category_id,
v1.customer_id, v1.customer_site_id
from ra_customer_merges r1, (
select m.number2 assignment_set_id,
m.number3 assignment_type,
nvl(m.number4, -23453) organization_id,
nvl(r.customer_id, -23453) customer_id,
nvl(r.customer_site_id, -23453) customer_site_id,
m.number7 sourcing_rule_type,
nvl(m.number8, -23453) inventory_item_id,
nvl(m.number9, -23453) category_id,
count(*)
from msc_form_query m, ra_customer_merges r
where m.number5 = r.duplicate_id
and m.number6 = r.duplicate_site_id
and m.query_id = q_id
and r.process_flag = 'N'
and r.request_id = req_id
and r.set_number = set_num
group by m.number2, m.number3,
nvl(m.number4, -23453), nvl(r.customer_id, -23453),
nvl(r.customer_site_id, -23453), m.number7,
nvl(m.number8, -23453), nvl(m.number9, -23453)
having count(*) > 1
) v1
where r1.customer_id = v1.customer_id
and r1.customer_site_id = v1.customer_site_id
and r1.process_flag = 'N'
and r1.request_id = req_id
and r1.set_number = set_num
order by v1.assignment_set_id, v1.assignment_type,
v1.organization_id, v1.sourcing_rule_type, v1.inventory_item_id,
v1.category_id, v1.customer_id, v1.customer_site_id;
select msc_form_query_s.nextval
into q_id
from dual;
insert into msc_form_query
(query_id, last_update_date, last_updated_by, creation_date,
created_by,
number1,
number2,
number3,
number4,
number5,
number6,
number7,
number8,
number9)
select q_id, sysdate, -1, sysdate, -1,
m.assignment_id,
m.assignment_set_id,
m.assignment_type,
m.organization_id,
m.customer_id,
m.ship_to_site_id,
m.sourcing_rule_type,
m.inventory_item_id,
m.category_id
from mrp_sr_assignments m, ra_customer_merges r
where (m.customer_id = r.duplicate_id
and m.ship_to_site_id = r.duplicate_site_id)
and m.assignment_type in (4, 5, 6)
and r.process_flag = 'N'
and r.request_id = req_id
and r.set_number = set_num;
delete /*+ index (msa mrp_sr_assignments_u1) */
from mrp_sr_assignments msa
where assignment_set_id = rec.assignment_set_id
and assignment_type = rec.assignment_type
and nvl(organization_id, -23453) = rec.organization_id
and nvl(customer_id, -23453) = rec.duplicate_id
and nvl(ship_to_site_id, -23453) = rec.duplicate_site_id
and sourcing_rule_type = rec.sourcing_rule_type
and nvl(inventory_item_id, -23453) = rec.inventory_item_id
and nvl(category_id, -23453) = rec.category_id
and assignment_id in (
select number1
from msc_form_query
where query_id = q_id
);
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,
'MRP_SR_ASSIGNMENTS',
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
);
delete from mrp_sr_assignments
where assignment_id IN
(SELECT
assignment_id
FROM mrp_sr_assignments
WHERE
(ASSIGNMENT_SET_ID ,
ASSIGNMENT_TYPE,
NVL(ORGANIZATION_ID, -23453),
NVL(CUSTOMER_ID, -23453),
NVL(SHIP_TO_SITE_ID, -23453),
SOURCING_RULE_TYPE,
NVL(INVENTORY_ITEM_ID, -23453),
NVL(CATEGORY_ID, -23453)) IN
(SELECT
ASSIGNMENT_SET_ID ,
ASSIGNMENT_TYPE,
NVL(ORGANIZATION_ID, -23453),
NVL(num_col1_new_list(I) , -23453),
NVL(num_col2_new_list(I), -23453),
SOURCING_RULE_TYPE,
NVL(INVENTORY_ITEM_ID, -23453),
NVL(CATEGORY_ID, -23453)
FROM mrp_sr_assignments
WHERE assignment_id = primary_key_id_list(I)));
UPDATE MRP_SR_ASSIGNMENTS yt SET
customer_id=NUM_COL1_NEW_LIST(I)
,ship_to_site_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
, REQUEST_ID=req_id
, PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
, PROGRAM_ID=arp_standard.profile.program_id
, PROGRAM_UPDATE_DATE=SYSDATE
WHERE ASSIGNMENT_ID=PRIMARY_KEY_ID_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');