[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
' update ' || lv_tbl || ' s '
||' set s.top_transaction_id = '
|| ' ( select nwk.top_transaction_id '
|| ' from msc_job_operation_networks nwk '
|| ' where nwk.to_transaction_id = s.transaction_id '
|| ' and nwk.plan_id = s.plan_id '
|| ' and nwk.sr_instance_id = s.sr_instance_id '
|| ' and nwk.top_transaction_id is not null '
|| ' and nwk.plan_id = -1 '
|| ' and rownum = 1 )'
|| ' WHERE s.plan_id = -1 '
|| ' and s.sr_instance_id = '|| MSC_CL_COLLECTION.v_instance_id
|| ' and s.order_type = 70'; /* eam supply order type */
SELECT NVL( CLEANSED_FLAG, MSC_UTIL.SYS_NO)
INTO CLEANSED_FLAG
FROM MSC_APPS_INSTANCES mai
WHERE mai.INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
UPDATE MSC_APPS_INSTANCES mai
SET mai.CLEANSED_FLAG= MSC_UTIL.SYS_YES,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
WHERE mai.Instance_ID= MSC_CL_COLLECTION.v_instance_id;
SELECT decode(nvl(fnd_profile.value('MSC_PURGE_ST_CONTROL'),'N'),'Y',1,2)
INTO lv_control_flag
FROM dual;
UPDATE MSC_APPS_INSTANCES
SET so_tbl_status= MSC_UTIL.SYS_NO
WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
UPDATE MSC_APPS_INSTANCES
SET so_tbl_status= MSC_UTIL.SYS_NO
WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
UPDATE MSC_APPS_INSTANCES
SET so_tbl_status= MSC_UTIL.SYS_NO
WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
UPDATE MSC_APPS_INSTANCES
SET so_tbl_status= MSC_UTIL.SYS_NO
WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
UPDATE MSC_APPS_INSTANCES
SET so_tbl_status= MSC_UTIL.SYS_NO
WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
UPDATE MSC_APPS_INSTANCES
SET so_tbl_status= MSC_UTIL.SYS_NO
WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
UPDATE MSC_APPS_INSTANCES
SET so_tbl_status= MSC_UTIL.SYS_YES
WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
UPDATE MSC_APPS_INSTANCES
SET so_tbl_status= MSC_UTIL.SYS_YES
WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
UPDATE MSC_APPS_INSTANCES
SET so_tbl_status= MSC_UTIL.SYS_YES
WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
UPDATE MSC_APPS_INSTANCES
SET so_tbl_status= MSC_UTIL.SYS_YES
WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
UPDATE MSC_APPS_INSTANCES
SET so_tbl_status= MSC_UTIL.SYS_YES
WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
UPDATE MSC_APPS_INSTANCES
SET so_tbl_status= MSC_UTIL.SYS_YES
WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
SELECT distinct
msca.ASSOCIATION_TYPE,
msca.CALENDAR_CODE,
msca.CALENDAR_TYPE,
til.TP_ID PARTNER_ID,
tsil.TP_SITE_ID PARTNER_SITE_ID,
msca.ORGANIZATION_ID,
msca.SR_INSTANCE_ID,
mtil.TP_ID CARRIER_PARTNER_ID,
msca.PARTNER_TYPE,
msca.ASSOCIATION_LEVEL,
msca.SHIP_METHOD_CODE
FROM MSC_TP_ID_LID til,
MSC_TP_SITE_ID_LID tsil,
MSC_ST_CALENDAR_ASSIGNMENTS msca,
MSC_TP_ID_LID mtil
WHERE til.SR_INSTANCE_ID(+)= msca.SR_INSTANCE_ID
AND til.SR_TP_ID(+)= msca.PARTNER_ID
AND til.PARTNER_TYPE(+)= msca.PARTNER_TYPE
AND tsil.SR_INSTANCE_ID(+)= msca.SR_INSTANCE_ID
AND tsil.SR_TP_SITE_ID(+)= msca.PARTNER_SITE_ID
AND tsil.PARTNER_TYPE(+)= msca.PARTNER_TYPE
AND msca.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND mtil.SR_INSTANCE_ID(+)= msca.SR_INSTANCE_ID
AND mtil.SR_TP_ID(+)= msca.CARRIER_PARTNER_ID
AND mtil.PARTNER_TYPE(+)=4;
--If instance type is not 'others', then insert into msc_calendars those calendars present in msc_calendar_dates
CURSOR c5 IS
SELECT distinct
mscd.CALENDAR_CODE,
mscd.CALENDAR_START_DATE,
mscd.CALENDAR_END_DATE,
mscd.DESCRIPTION,
mscd.SR_INSTANCE_ID
FROM MSC_ST_CALENDAR_DATES mscd
WHERE mscd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
SELECT
Min(CALENDAR_DATE) FIRST_WORKING_DATE,
Max(CALENDAR_DATE) LAST_WORKING_DATE,
CALENDAR_CODE,
SR_INSTANCE_ID
FROM MSC_CALENDAR_DATES mscd
WHERE mscd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
and seq_num is not null
GROUP BY CALENDAR_CODE, SR_INSTANCE_ID;
SELECT
mscd.CALENDAR_DATE,
mscd.CALENDAR_CODE,
mscd.SEQ_NUM,
mscd.NEXT_SEQ_NUM,
mscd.PRIOR_SEQ_NUM,
mscd.NEXT_DATE,
mscd.PRIOR_DATE,
mscd.CALENDAR_START_DATE,
mscd.CALENDAR_END_DATE,
mscd.DESCRIPTION,
mscd.EXCEPTION_SET_ID,
mscd.SR_INSTANCE_ID
FROM MSC_ST_CALENDAR_DATES mscd
WHERE mscd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
SELECT
mspsd.CALENDAR_CODE,
mspsd.EXCEPTION_SET_ID,
mspsd.PERIOD_START_DATE,
mspsd.PERIOD_SEQUENCE_NUM,
substrb(mspsd.PERIOD_NAME,1,3) PERIOD_NAME, --added for the NLS bug3463401
mspsd.NEXT_DATE,
mspsd.PRIOR_DATE,
mspsd.SR_INSTANCE_ID
FROM MSC_ST_PERIOD_START_DATES mspsd
WHERE mspsd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
SELECT
mscysd.CALENDAR_CODE,
mscysd.EXCEPTION_SET_ID,
mscysd.YEAR_START_DATE,
mscysd.SR_INSTANCE_ID
FROM MSC_ST_CAL_YEAR_START_DATES mscysd
WHERE mscysd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
SELECT
mscwsd.CALENDAR_CODE,
mscwsd.EXCEPTION_SET_ID,
mscwsd.WEEK_START_DATE,
mscwsd.NEXT_DATE,
mscwsd.PRIOR_DATE,
mscwsd.SEQ_NUM,
mscwsd.SR_INSTANCE_ID
FROM MSC_ST_CAL_WEEK_START_DATES mscwsd
WHERE mscwsd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PERIOD_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CAL_YEAR_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CAL_WEEK_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
UPDATE MSC_CALENDAR_DATES
SET DELETED_FLAG= MSC_UTIL.SYS_YES,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE
SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND exception_set_id = -1;
' INSERT INTO MSC_CALENDAR_DATES '
||'( CALENDAR_DATE, '
||' CALENDAR_CODE, '
||' SEQ_NUM, '
||' NEXT_SEQ_NUM, '
||' PRIOR_SEQ_NUM, '
||' NEXT_DATE, '
||' PRIOR_DATE, '
||' CALENDAR_START_DATE, '
||' CALENDAR_END_DATE, '
||' DESCRIPTION, '
||' EXCEPTION_SET_ID, '
||' SR_INSTANCE_ID, '
||' REFRESH_NUMBER, '
||' DELETED_FLAG, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY) '
||' VALUES '
||'( :CALENDAR_DATE, '
||' :CALENDAR_CODE, '
||' :SEQ_NUM, '
||' :NEXT_SEQ_NUM, '
||' :PRIOR_SEQ_NUM, '
||' :NEXT_DATE, '
||' :PRIOR_DATE, '
||' :CALENDAR_START_DATE, '
||' :CALENDAR_END_DATE, '
||' :DESCRIPTION, '
||' :EXCEPTION_SET_ID, '
||' :SR_INSTANCE_ID, '
||' :v_last_collection_id, '
||' :SYS_NO, '
||' :v_current_date, '
||' :v_current_user, '
||' :v_current_date, '
||' :v_current_user ) ';
UPDATE MSC_CALENDAR_DATES
SET
SEQ_NUM= lb_SEQ_NUM(j),
NEXT_SEQ_NUM= lb_NEXT_SEQ_NUM(j),
PRIOR_SEQ_NUM= lb_PRIOR_SEQ_NUM(j),
NEXT_DATE= lb_NEXT_DATE(j),
PRIOR_DATE= lb_PRIOR_DATE(j),
CALENDAR_START_DATE= lb_CALENDAR_START_DATE(j),
CALENDAR_END_DATE= lb_CALENDAR_END_DATE(j),
DESCRIPTION= lb_DESCRIPTION(j),
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
Deleted_Flag= MSC_UTIL.SYS_NO,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE CALENDAR_DATE= lb_CALENDAR_DATE(j)
AND CALENDAR_CODE= lb_CALENDAR_CODE(j)
AND EXCEPTION_SET_ID= lb_EXCEPTION_SET_ID(j)
AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j);
DELETE MSC_CALENDAR_DATES
WHERE DELETED_FLAG= MSC_UTIL.SYS_YES
AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND exception_set_id = -1;
--If it is complete or partial or continuous refresh, delete existing calendars in the current instance from MSC_CALENDARS
IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_CALENDARS', MSC_CL_COLLECTION.v_instance_id, NULL);
INSERT INTO MSC_CALENDARS
( CALENDAR_CODE,
DESCRIPTION,
CALENDAR_START_DATE,
CALENDAR_END_DATE,
SR_INSTANCE_ID,
REFRESH_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( c_rec.CALENDAR_CODE,
c_rec.DESCRIPTION,
c_rec.CALENDAR_START_DATE,
c_rec.CALENDAR_END_DATE,
c_rec.SR_INSTANCE_ID,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user );
UPDATE MSC_CALENDARS
SET
FIRST_WORKING_DATE = c_rec.FIRST_WORKING_DATE,
LAST_WORKING_DATE = c_rec.LAST_WORKING_DATE
WHERE CALENDAR_CODE = c_rec.CALENDAR_CODE
AND SR_INSTANCE_ID = c_rec. SR_INSTANCE_ID;
MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_CALENDAR_ASSIGNMENTS', MSC_CL_COLLECTION.v_instance_id, NULL);
INSERT INTO MSC_CALENDAR_ASSIGNMENTS
( ASSOCIATION_TYPE,
CALENDAR_CODE,
CALENDAR_TYPE,
PARTNER_ID,
PARTNER_SITE_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
CARRIER_PARTNER_ID,
PARTNER_TYPE,
ASSOCIATION_LEVEL,
SHIP_METHOD_CODE,
REFRESH_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
VALUES
( c_rec.ASSOCIATION_TYPE,
c_rec.CALENDAR_CODE,
c_rec.CALENDAR_TYPE,
c_rec.PARTNER_ID,
c_rec.PARTNER_SITE_ID,
c_rec.ORGANIZATION_ID,
c_rec.SR_INSTANCE_ID,
c_rec.CARRIER_PARTNER_ID,
c_rec.PARTNER_TYPE,
c_rec.ASSOCIATION_LEVEL,
c_rec.SHIP_METHOD_CODE,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_user);
UPDATE MSC_PERIOD_START_DATES
SET
PERIOD_SEQUENCE_NUM= c_rec.PERIOD_SEQUENCE_NUM,
PERIOD_NAME= c_rec.PERIOD_NAME,
NEXT_DATE= c_rec.NEXT_DATE,
PRIOR_DATE= c_rec.PRIOR_DATE,
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
AND PERIOD_START_DATE= c_rec.PERIOD_START_DATE
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
INSERT INTO MSC_PERIOD_START_DATES
( CALENDAR_CODE,
EXCEPTION_SET_ID,
PERIOD_START_DATE,
PERIOD_SEQUENCE_NUM,
PERIOD_NAME,
NEXT_DATE,
PRIOR_DATE,
SR_INSTANCE_ID,
REFRESH_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( c_rec.CALENDAR_CODE,
c_rec.EXCEPTION_SET_ID,
c_rec.PERIOD_START_DATE,
c_rec.PERIOD_SEQUENCE_NUM,
c_rec.PERIOD_NAME,
c_rec.NEXT_DATE,
c_rec.PRIOR_DATE,
c_rec.SR_INSTANCE_ID,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user );
UPDATE MSC_CAL_YEAR_START_DATES
SET
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
AND YEAR_START_DATE= c_rec.YEAR_START_DATE
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
INSERT INTO MSC_CAL_YEAR_START_DATES
( CALENDAR_CODE,
EXCEPTION_SET_ID,
YEAR_START_DATE,
SR_INSTANCE_ID,
REFRESH_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( c_rec.CALENDAR_CODE,
c_rec.EXCEPTION_SET_ID,
c_rec.YEAR_START_DATE,
c_rec.SR_INSTANCE_ID,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user );
UPDATE MSC_CAL_WEEK_START_DATES
SET
NEXT_DATE= c_rec.NEXT_DATE,
PRIOR_DATE= c_rec.PRIOR_DATE,
SEQ_NUM= c_rec.SEQ_NUM,
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
AND WEEK_START_DATE= c_rec.WEEK_START_DATE
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
INSERT INTO MSC_CAL_WEEK_START_DATES
( CALENDAR_CODE,
EXCEPTION_SET_ID,
WEEK_START_DATE,
NEXT_DATE,
PRIOR_DATE,
SEQ_NUM,
SR_INSTANCE_ID,
REFRESH_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( c_rec.CALENDAR_CODE,
c_rec.EXCEPTION_SET_ID,
c_rec.WEEK_START_DATE,
c_rec.NEXT_DATE,
c_rec.PRIOR_DATE,
c_rec.SEQ_NUM,
c_rec.SR_INSTANCE_ID,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user );
SELECT
msrs.DEPARTMENT_ID,
msrs.RESOURCE_ID,
msrs.SHIFT_NUM,
msrs.CAPACITY_UNITS,
msrs.SR_INSTANCE_ID
FROM MSC_ST_RESOURCE_SHIFTS msrs
WHERE msrs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
SELECT
mscs.CALENDAR_CODE,
mscs.SHIFT_NUM,
mscs.DAYS_ON,
mscs.DAYS_OFF,
mscs.DESCRIPTION,
mscs.SR_INSTANCE_ID
FROM MSC_ST_CALENDAR_SHIFTS mscs
WHERE mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND nvl(mscs.process_flag, -99) <> MSC_UTIL.G_ERROR;
SELECT
mssd.CALENDAR_CODE,
mssd.EXCEPTION_SET_ID,
mssd.SHIFT_NUM,
mssd.SHIFT_DATE,
mssd.SEQ_NUM,
mssd.NEXT_SEQ_NUM,
mssd.PRIOR_SEQ_NUM,
mssd.NEXT_DATE,
mssd.PRIOR_DATE,
mssd.SR_INSTANCE_ID
FROM MSC_ST_SHIFT_DATES mssd
WHERE mssd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
SELECT
msrc.DEPARTMENT_ID,
msrc.RESOURCE_ID,
msrc.SHIFT_NUM,
msrc.FROM_DATE,
msrc.TO_DATE,
msrc.FROM_TIME,
msrc.TO_TIME,
msrc.CAPACITY_CHANGE,
msrc.SIMULATION_SET,
msrc.ACTION_TYPE,
msrc.DELETED_FLAG,
msrc.SR_INSTANCE_ID
FROM MSC_ST_RESOURCE_CHANGES msrc
WHERE msrc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
ORDER BY
msrc.DELETED_FLAG;
SELECT
msst.CALENDAR_CODE,
msst.SHIFT_NUM,
msst.FROM_TIME,
msst.TO_TIME,
msst.SR_INSTANCE_ID
FROM MSC_ST_SHIFT_TIMES msst
WHERE msst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
SELECT
msse.CALENDAR_CODE,
msse.SHIFT_NUM,
msse.EXCEPTION_SET_ID,
msse.EXCEPTION_DATE,
msse.EXCEPTION_TYPE,
msse.SR_INSTANCE_ID
FROM MSC_ST_SHIFT_EXCEPTIONS msse
WHERE msse.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CALENDAR_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_CHANGES', MSC_CL_COLLECTION.v_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_TIMES', MSC_CL_COLLECTION.v_instance_id, NULL);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_EXCEPTIONS', MSC_CL_COLLECTION.v_instance_id, NULL);
UPDATE MSC_RESOURCE_SHIFTS
SET
CAPACITY_UNITS=c_rec.CAPACITY_UNITS,
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE DEPARTMENT_ID= c_rec.DEPARTMENT_ID
AND RESOURCE_ID= c_rec.RESOURCE_ID
AND SHIFT_NUM= c_rec.SHIFT_NUM
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
INSERT INTO MSC_RESOURCE_SHIFTS
( DEPARTMENT_ID,
RESOURCE_ID,
SHIFT_NUM,
CAPACITY_UNITS,
SR_INSTANCE_ID,
REFRESH_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( c_rec.DEPARTMENT_ID,
c_rec.RESOURCE_ID,
c_rec.SHIFT_NUM,
c_rec.CAPACITY_UNITS,
c_rec.SR_INSTANCE_ID,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user );
UPDATE MSC_CALENDAR_SHIFTS
SET
DAYS_ON= c_rec.DAYS_ON,
DAYS_OFF= c_rec.DAYS_OFF,
DESCRIPTION= c_rec.DESCRIPTION,
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
AND SHIFT_NUM= c_rec.SHIFT_NUM
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
INSERT INTO MSC_CALENDAR_SHIFTS
( CALENDAR_CODE,
SHIFT_NUM,
DAYS_ON,
DAYS_OFF,
DESCRIPTION,
SR_INSTANCE_ID,
REFRESH_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( c_rec.CALENDAR_CODE,
c_rec.SHIFT_NUM,
c_rec.DAYS_ON,
c_rec.DAYS_OFF,
c_rec.DESCRIPTION,
c_rec.SR_INSTANCE_ID,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user );
UPDATE MSC_SHIFT_DATES
SET
SEQ_NUM= c_rec.SEQ_NUM,
NEXT_SEQ_NUM= c_rec.NEXT_SEQ_NUM,
PRIOR_SEQ_NUM= c_rec.PRIOR_SEQ_NUM,
NEXT_DATE= c_rec.NEXT_DATE,
PRIOR_DATE= c_rec.PRIOR_DATE,
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
AND SHIFT_NUM= c_rec.SHIFT_NUM
AND SHIFT_DATE= c_rec.SHIFT_DATE
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
INSERT INTO MSC_SHIFT_DATES
( CALENDAR_CODE,
EXCEPTION_SET_ID,
SHIFT_NUM,
SHIFT_DATE,
SEQ_NUM,
NEXT_SEQ_NUM,
PRIOR_SEQ_NUM,
NEXT_DATE,
PRIOR_DATE,
SR_INSTANCE_ID,
REFRESH_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( c_rec.CALENDAR_CODE,
c_rec.EXCEPTION_SET_ID,
c_rec.SHIFT_NUM,
c_rec.SHIFT_DATE,
c_rec.SEQ_NUM,
c_rec.NEXT_SEQ_NUM,
c_rec.PRIOR_SEQ_NUM,
c_rec.NEXT_DATE,
c_rec.PRIOR_DATE,
c_rec.SR_INSTANCE_ID,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user );
IF MSC_CL_COLLECTION.v_is_incremental_refresh AND c_rec.DELETED_FLAG= MSC_UTIL.SYS_YES THEN
-- set SR_INSTANCE_ID to negative to indicate a SOFT delete
DELETE MSC_RESOURCE_CHANGES
WHERE DEPARTMENT_ID= c_rec.DEPARTMENT_ID
AND RESOURCE_ID= c_rec.RESOURCE_ID
AND SHIFT_NUM= c_rec.SHIFT_NUM
AND FROM_DATE= c_rec.FROM_DATE
AND NVL(TO_DATE,MSC_UTIL.NULL_DATE)= NVL(c_rec.TO_DATE,MSC_UTIL.NULL_DATE)
AND NVL(FROM_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.FROM_TIME,MSC_UTIL.NULL_VALUE)
AND NVL(TO_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.TO_TIME,MSC_UTIL.NULL_VALUE)
AND SIMULATION_SET= c_rec.SIMULATION_SET
AND ACTION_TYPE= c_rec.ACTION_TYPE
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
INSERT INTO MSC_RESOURCE_CHANGES
( DEPARTMENT_ID,
RESOURCE_ID,
SHIFT_NUM,
FROM_DATE,
TO_DATE,
FROM_TIME,
TO_TIME,
CAPACITY_CHANGE,
SIMULATION_SET,
ACTION_TYPE,
SR_INSTANCE_ID,
REFRESH_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( c_rec.DEPARTMENT_ID,
c_rec.RESOURCE_ID,
c_rec.SHIFT_NUM,
c_rec.FROM_DATE,
c_rec.TO_DATE,
c_rec.FROM_TIME,
c_rec.TO_TIME,
c_rec.CAPACITY_CHANGE,
c_rec.SIMULATION_SET,
c_rec.ACTION_TYPE,
c_rec.SR_INSTANCE_ID,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user );
UPDATE MSC_SHIFT_TIMES
SET
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
AND SHIFT_NUM= c_rec.SHIFT_NUM
AND FROM_TIME= c_rec.FROM_TIME
AND TO_TIME= c_rec.TO_TIME
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
INSERT INTO MSC_SHIFT_TIMES
( CALENDAR_CODE,
SHIFT_NUM,
FROM_TIME,
TO_TIME,
SR_INSTANCE_ID,
REFRESH_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( c_rec.CALENDAR_CODE,
c_rec.SHIFT_NUM,
c_rec.FROM_TIME,
c_rec.TO_TIME,
c_rec.SR_INSTANCE_ID,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user );
UPDATE MSC_SHIFT_EXCEPTIONS
SET
EXCEPTION_TYPE= c_rec.EXCEPTION_TYPE,
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
AND SHIFT_NUM= c_rec.SHIFT_NUM
AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
AND EXCEPTION_DATE= c_rec.EXCEPTION_DATE
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
INSERT INTO MSC_SHIFT_EXCEPTIONS
( CALENDAR_CODE,
SHIFT_NUM,
EXCEPTION_SET_ID,
EXCEPTION_DATE,
EXCEPTION_TYPE,
SR_INSTANCE_ID,
REFRESH_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( c_rec.CALENDAR_CODE,
c_rec.SHIFT_NUM,
c_rec.EXCEPTION_SET_ID,
c_rec.EXCEPTION_DATE,
c_rec.EXCEPTION_TYPE,
c_rec.SR_INSTANCE_ID,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user );
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL',
MSC_CL_COLLECTION.v_instance_id, -1);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL',
MSC_CL_COLLECTION.v_instance_id, -1);
SELECT DECODE(M2A_DBLINK,
NULL,'',
'@'||M2A_DBLINK),
DECODE( A2M_DBLINK,
NULL,MSC_UTIL.NULL_DBLINK,
A2M_DBLINK),
INSTANCE_CODE
INTO lv_dblink,
lv_dest_a2m,
lv_instance_code
FROM MSC_APPS_INSTANCES
WHERE INSTANCE_ID=MSC_CL_COLLECTION.v_instance_id;
lv_sql_stmt:= 'SELECT nvl(mar.LRD,sysdate)- '||lv_res_avail_before_sysdate
||' FROM MRP_AP_APPS_INSTANCES_ALL'||lv_dblink||' mar'
||' WHERE INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
||' AND INSTANCE_CODE = '''||lv_instance_code||''''
||' AND nvl(A2M_DBLINK,'''||MSC_UTIL.NULL_DBLINK||''') = '''||lv_dest_a2m||'''' ;
SELECT
/* SCE Change starts */
decode(mc.COMPANY_ID, MSC_CL_COLLECTION.G_MY_COMPANY_ID, null, mc.COMPANY_ID) COMPANY_ID ,
/* SCE change ends */
mst.ORGANIZATION_CODE,
mst.ORGANIZATION_TYPE,
mst.SR_TP_ID,
mst.DISABLE_DATE,
mst.STATUS,
mst.MASTER_ORGANIZATION,
mst.SOURCE_ORG_ID,
mst.WEIGHT_UOM,
mst.MAXIMUM_WEIGHT,
mst.VOLUME_UOM,
mst.MAXIMUM_VOLUME,
mst.PARTNER_TYPE,
mst.PARTNER_NAME,
mst.PARTNER_NUMBER,
mst.CALENDAR_CODE,
mst.CURRENCY_CODE,
mst.CALENDAR_EXCEPTION_SET_ID,
mst.OPERATING_UNIT,
mst.SR_INSTANCE_ID,
mst.PROJECT_REFERENCE_ENABLED,
mst.PROJECT_CONTROL_LEVEL,
mst.DEMAND_LATENESS_COST,
mst.SUPPLIER_CAP_OVERUTIL_COST,
mst.RESOURCE_CAP_OVERUTIL_COST,
mst.TRANSPORT_CAP_OVER_UTIL_COST,
mst.DEFAULT_ATP_RULE_ID,
mst.DEFAULT_DEMAND_CLASS,
mst.MATERIAL_ACCOUNT,
mst.EXPENSE_ACCOUNT,
tilc.TP_ID MODELED_CUSTOMER_ID,
tsilc.TP_SITE_ID MODELED_CUSTOMER_SITE_ID,
tils.TP_ID MODELED_SUPPLIER_ID,
tsils.TP_SITE_ID MODELED_SUPPLIER_SITE_ID,
mst.USE_PHANTOM_ROUTINGS,
mst.INHERIT_PHANTOM_OP_SEQ,
mst.INHERIT_OC_OP_SEQ_NUM,
mst.BUSINESS_GROUP_ID,
mst.LEGAL_ENTITY,
mst.SET_OF_BOOKS_ID,
mst.CHART_OF_ACCOUNTS_ID,
mst.BUSINESS_GROUP_NAME,
mst.LEGAL_ENTITY_NAME,
mst.OPERATING_UNIT_NAME,
mst.subcontracting_source_org
FROM MSC_TP_ID_LID tilc,
MSC_TP_ID_LID tils,
MSC_TP_SITE_ID_LID tsilc,
MSC_TP_SITE_ID_LID tsils,
MSC_ST_TRADING_PARTNERS mst,
MSC_COMPANIES MC
WHERE mst.PARTNER_TYPE= 3
/* SCE Change starts */
AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = MC.company_name
-- AND nvl( mst.company_id, -1) = -1 -- commented for aerox
/* SCE Change Ends */
AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND tilc.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
AND tilc.PARTNER_TYPE(+)= 2
AND tilc.SR_TP_ID(+)= mst.MODELED_CUSTOMER_ID
AND tils.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
AND tils.PARTNER_TYPE(+)= 1
AND tils.SR_TP_ID(+)= mst.MODELED_SUPPLIER_ID
AND tsilc.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
AND tsilc.PARTNER_TYPE(+)= 2
AND tsilc.SR_TP_SITE_ID(+)= mst.MODELED_CUSTOMER_SITE_ID
AND tsils.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
AND tsils.PARTNER_TYPE(+)= 1
AND tsils.SR_TP_SITE_ID(+)= mst.MODELED_SUPPLIER_SITE_ID;
SELECT
mtp.PARTNER_ID,
substrb(msts.PARTNER_ADDRESS,1,1600) PARTNER_ADDRESS,--added for the NLS bug3463401
msts.SR_TP_ID,
msts.SR_TP_SITE_ID,
msts.SR_INSTANCE_ID,
msts.TP_SITE_CODE,
msts.LOCATION,
msts.LONGITUDE,
msts.LATITUDE
FROM MSC_TRADING_PARTNERS mtp,
MSC_ST_TRADING_PARTNER_SITES msts
WHERE msts.PARTNER_TYPE= 3
AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND mtp.SR_TP_ID= msts.SR_TP_ID
AND mtp.PARTNER_TYPE= 3
AND mtp.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
SELECT DISTINCT
msta.LOCATION_ID,
msta.LOCATION_CODE,
til.TP_ID PARTNER_ID,
tsil.TP_SITE_ID PARTNER_SITE_ID,
msta.organization_id,
msta.SR_INSTANCE_ID
FROM MSC_TP_ID_LID til,
MSC_TP_SITE_ID_LID tsil,
MSC_ST_LOCATION_ASSOCIATIONS msta
WHERE til.SR_INSTANCE_ID= msta.SR_INSTANCE_ID
AND til.SR_TP_ID= msta.SR_TP_ID
AND til.PARTNER_TYPE= msta.PARTNER_TYPE
AND tsil.SR_INSTANCE_ID= msta.SR_INSTANCE_ID
AND tsil.SR_TP_SITE_ID= msta.SR_TP_SITE_ID
AND tsil.PARTNER_TYPE= msta.PARTNER_TYPE
AND msta.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND msta.PARTNER_TYPE IN (1,2);
SELECT
pc.PARTNER_TYPE,
DECODE( pc.PARTNER_TYPE,
1, til.TP_ID,
2, til.TP_ID,
4, pc.PARTNER_ID) PARTNER_ID,
DECODE( pc.PARTNER_TYPE,
1, tsil.TP_SITE_ID,
2, tsil.TP_SITE_ID,
NULL) PARTNER_SITE_ID,
pc.NAME,
pc.DISPLAY_NAME,
pc.EMAIL,
pc.FAX,
pc.ENABLED_FLAG,
pc.DELETED_FLAG
FROM MSC_TP_ID_LID til,
MSC_TP_SITE_ID_LID tsil,
MSC_ST_PARTNER_CONTACTS pc
WHERE pc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND pc.DELETED_FLAG in (1, 2)
AND til.sr_tp_id(+)= pc.partner_id
AND til.partner_type(+)= DECODE( pc.PARTNER_TYPE,1,1,2,2,NULL)
AND til.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id
AND tsil.sr_tp_site_id(+)= pc.partner_site_id
AND tsil.partner_type(+)= DECODE( pc.PARTNER_TYPE,1,1,2,2,NULL)
AND tsil.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id
ORDER BY
1,2,3,4 ASC;
SELECT
mtps.PARTNER_ID,
msta.LOCATION_ID,
msta.LOCATION_CODE,
msta.SR_TP_ID ORGANIZATION_ID,
msta.LOCATION_ID PARTNER_SITE_ID,
msta.SR_INSTANCE_ID
FROM MSC_TRADING_PARTNERS mtps,
MSC_ST_LOCATION_ASSOCIATIONS msta
WHERE msta.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND msta.partner_type = 3
AND msta.SR_INSTANCE_ID= mtps.SR_INSTANCE_ID
AND msta.SR_TP_ID= mtps.SR_TP_ID
AND msta.PARTNER_TYPE= mtps.PARTNER_TYPE;
DELETE MSC_TRADING_PARTNERS
WHERE sr_instance_id= MSC_CL_COLLECTION.v_instance_id
AND partner_type=3
AND nvl(ORG_SUPPLIER_MAPPED,'N') <> 'Y';
-- MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_TRADING_PARTNERS', MSC_CL_COLLECTION.v_instance_id, NULL,
-- 'AND PARTNER_TYPE=3');
'UPDATE MSC_TRADING_PARTNERS '
||' SET '
||' COMPANY_ID = :company_id,'
||' ORGANIZATION_CODE = :ORG_CODE,'
||' ORGANIZATION_TYPE = :ORG_TYPE,'
||' DISABLE_DATE = :DISABLE_DATE,'
||' STATUS = :STATUS,'
||' MASTER_ORGANIZATION = :MASTER_ORG,'
||' SOURCE_ORG_ID = :SOURCE_ORG_ID,'
||' WEIGHT_UOM = :WEIGHT_UOM,'
||' MAXIMUM_WEIGHT = :MAXIMUM_WEIGHT,'
||' VOLUME_UOM = :VOLUME_UOM,'
||' MAXIMUM_VOLUME = :MAXIMUM_VOLUME,'
||' PARTNER_NAME = :PARTNER_NAME,'
||' PARTNER_NUMBER = :PARTNER_NUMBER,'
||' CALENDAR_CODE = :CALENDAR_CODE,'
||' CURRENCY_CODE = :CURRENCY_CODE,'
||' CALENDAR_EXCEPTION_SET_ID = :CAL_EXP_SET_ID,'
||' OPERATING_UNIT = :OPERATING_UNIT,'
||' PROJECT_REFERENCE_ENABLED = :PROJ_REF_ENABLED,'
||' PROJECT_CONTROL_LEVEL = :PROJ_CON_LEVEL,'
||' DEMAND_LATENESS_COST = :DMD_LATE_COST,'
||' SUPPLIER_CAP_OVERUTIL_COST = :SUPP_CAP_OVR_COST,'
||' RESOURCE_CAP_OVERUTIL_COST = :RES_CAP_OVER_COST,'
||' TRANSPORT_CAP_OVER_UTIL_COST= :TR_CAP_OV_UTIL_COST,'
||' MODELED_CUSTOMER_ID = decode(ORG_SUPPLIER_MAPPED,''Y'',MODELED_CUSTOMER_ID,:MOD_CUS_ID),'
||' MODELED_CUSTOMER_SITE_ID = decode(ORG_SUPPLIER_MAPPED,''Y'',MODELED_CUSTOMER_SITE_ID,:MOD_CUS_SITE_ID),'
||' MODELED_SUPPLIER_ID = decode(ORG_SUPPLIER_MAPPED,''Y'',MODELED_SUPPLIER_ID,:MOD_SUPP_ID),'
||' MODELED_SUPPLIER_SITE_ID = decode(ORG_SUPPLIER_MAPPED,''Y'',MODELED_SUPPLIER_SITE_ID,:MOD_SUPP_SITE_ID),'
||' USE_PHANTOM_ROUTINGS = :USE_PH_ROUTINGS,'
||' INHERIT_PHANTOM_OP_SEQ = :INH_PH_OP_SEQ,'
||' INHERIT_OC_OP_SEQ_NUM = :INH_OC_OP_SEQ_NUM,'
||' DEFAULT_ATP_RULE_ID = :DEF_ATP_RULE_ID,'
||' DEFAULT_DEMAND_CLASS = :DEF_DEMAND_CLASS,'
||' MATERIAL_ACCOUNT = :MATERIAL_ACCOUNT,'
||' EXPENSE_ACCOUNT = :EXPENSE_ACCOUNT,'
||' SR_BUSINESS_GROUP_ID = :BUSINESS_GROUP_ID,'
||' SR_LEGAL_ENTITY = :LEGAL_ENTITY,'
||' SR_SET_OF_BOOKS_ID = :SET_OF_BOOKS_ID,'
||' SR_CHART_OF_ACCOUNTS_ID = :CHART_OF_ACCOUNTS_ID,'
||' BUSINESS_GROUP_NAME = :BUSINESS_GROUP_NAME,'
||' LEGAL_ENTITY_NAME = :LEGAL_ENTITY_NAME,'
||' OPERATING_UNIT_NAME = :OPERATING_UNIT_NAME,'
||' SUBCONTRACTING_SOURCE_ORG = :SUBCONTRACTING_SOURCE_ORG,'
||' REFRESH_NUMBER = :v_last_collection_id,'
||' LAST_UPDATE_DATE = :v_current_date,'
||' LAST_UPDATED_BY = :v_current_user'
||' WHERE SR_TP_ID = :SR_TP_ID'
||' AND SR_INSTANCE_ID = :SR_INSTANCE_ID'
||' AND PARTNER_TYPE = :PARTNER_TYPE';
INSERT INTO MSC_TRADING_PARTNERS
( PARTNER_ID,
/* SCE change starts */
COMPANY_ID,
/* SCE change ends */
ORGANIZATION_CODE,
ORGANIZATION_TYPE,
SR_TP_ID,
DISABLE_DATE,
STATUS,
MASTER_ORGANIZATION,
SOURCE_ORG_ID,
WEIGHT_UOM,
MAXIMUM_WEIGHT,
VOLUME_UOM,
MAXIMUM_VOLUME,
PARTNER_TYPE,
PARTNER_NAME,
PARTNER_NUMBER,
CALENDAR_CODE,
CURRENCY_CODE,
CALENDAR_EXCEPTION_SET_ID,
OPERATING_UNIT,
SR_INSTANCE_ID,
PROJECT_REFERENCE_ENABLED,
PROJECT_CONTROL_LEVEL,
DEMAND_LATENESS_COST,
SUPPLIER_CAP_OVERUTIL_COST,
RESOURCE_CAP_OVERUTIL_COST,
TRANSPORT_CAP_OVER_UTIL_COST,
MODELED_CUSTOMER_ID,
MODELED_CUSTOMER_SITE_ID,
MODELED_SUPPLIER_ID,
MODELED_SUPPLIER_SITE_ID,
USE_PHANTOM_ROUTINGS,
INHERIT_PHANTOM_OP_SEQ,
DEFAULT_ATP_RULE_ID,
DEFAULT_DEMAND_CLASS,
MATERIAL_ACCOUNT,
EXPENSE_ACCOUNT,
SR_BUSINESS_GROUP_ID,
SR_LEGAL_ENTITY,
SR_SET_OF_BOOKS_ID,
SR_CHART_OF_ACCOUNTS_ID,
BUSINESS_GROUP_NAME,
LEGAL_ENTITY_NAME,
OPERATING_UNIT_NAME,
SUBCONTRACTING_SOURCE_ORG,
REFRESH_NUMBER,
INHERIT_OC_OP_SEQ_NUM,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( MSC_Trading_Partners_S.NEXTVAL,
/* SCE change starts */
c_rec.company_id,
/* SCE change ends */
c_rec.ORGANIZATION_CODE,
c_rec.ORGANIZATION_TYPE,
c_rec.SR_TP_ID,
c_rec.DISABLE_DATE,
c_rec.STATUS,
c_rec.MASTER_ORGANIZATION,
c_rec.SOURCE_ORG_ID,
c_rec.WEIGHT_UOM,
c_rec.MAXIMUM_WEIGHT,
c_rec.VOLUME_UOM,
c_rec.MAXIMUM_VOLUME,
c_rec.PARTNER_TYPE,
c_rec.PARTNER_NAME,
c_rec.PARTNER_NUMBER,
c_rec.CALENDAR_CODE,
c_rec.CURRENCY_CODE,
c_rec.CALENDAR_EXCEPTION_SET_ID,
c_rec.OPERATING_UNIT,
c_rec.SR_INSTANCE_ID,
c_rec.PROJECT_REFERENCE_ENABLED,
c_rec.PROJECT_CONTROL_LEVEL,
c_rec.DEMAND_LATENESS_COST,
c_rec.SUPPLIER_CAP_OVERUTIL_COST,
c_rec.RESOURCE_CAP_OVERUTIL_COST,
c_rec.TRANSPORT_CAP_OVER_UTIL_COST,
c_rec.MODELED_CUSTOMER_ID,
c_rec.MODELED_CUSTOMER_SITE_ID,
c_rec.MODELED_SUPPLIER_ID,
c_rec.MODELED_SUPPLIER_SITE_ID,
c_rec.USE_PHANTOM_ROUTINGS,
c_rec.INHERIT_PHANTOM_OP_SEQ,
c_rec.DEFAULT_ATP_RULE_ID,
c_rec.DEFAULT_DEMAND_CLASS,
c_rec.MATERIAL_ACCOUNT,
c_rec.EXPENSE_ACCOUNT,
c_rec.BUSINESS_GROUP_ID,
c_rec.LEGAL_ENTITY,
c_rec.SET_OF_BOOKS_ID,
c_rec.CHART_OF_ACCOUNTS_ID,
c_rec.BUSINESS_GROUP_NAME,
c_rec.LEGAL_ENTITY_NAME,
c_rec.OPERATING_UNIT_NAME,
c_rec.SUBCONTRACTING_SOURCE_ORG,
MSC_CL_COLLECTION.v_last_collection_id,
c_rec.INHERIT_OC_OP_SEQ_NUM,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user );
' select 1 from MSC_INSTANCE_ORGS '
||' where ORGANIZATION_ID = :sr_tp_id '
||' and SR_INSTANCE_ID = :instance_id ' USING c_rec.SR_TP_ID, c_rec.SR_INSTANCE_ID;
INSERT INTO MSC_INSTANCE_ORGS(
SR_INSTANCE_ID,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
ENABLED_FLAG)
VALUES
(c_rec.SR_INSTANCE_ID,
c_rec.SR_TP_ID,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
1);
' select 1 from MSC_PARAMETERS '
||' where ORGANIZATION_ID = :sr_tp_id '
||' and SR_INSTANCE_ID = :instance_id ' USING c_rec.SR_TP_ID, c_rec.SR_INSTANCE_ID;
INSERT INTO MSC_PARAMETERS
( ORGANIZATION_ID,
SR_INSTANCE_ID,
DEMAND_TIME_FENCE_FLAG,
PLANNING_TIME_FENCE_FLAG,
OPERATION_SCHEDULE_TYPE,
CONSIDER_WIP,
CONSIDER_PO,
SNAPSHOT_LOCK,
PLAN_SAFETY_STOCK,
CONSIDER_RESERVATIONS,
PART_INCLUDE_TYPE,
PERIOD_TYPE,
NETWORK_SCHEDULING_METHOD, /* hard coded to 1 (primary)*/
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
values
(c_rec.SR_TP_ID,
c_rec.SR_INSTANCE_ID,
1,
1,
1,
1,
1,
1,
1,
1,
1,
1,
1,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user );
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_TRADING_PARTNER_SITES', MSC_CL_COLLECTION.v_instance_id, NULL,
'AND PARTNER_TYPE=3');
UPDATE MSC_TRADING_PARTNER_SITES
SET
PARTNER_ADDRESS= c_rec.PARTNER_ADDRESS,
TP_SITE_CODE= c_rec.TP_SITE_CODE,
LOCATION= c_rec.LOCATION,
LONGITUDE= c_rec.LONGITUDE,
LATITUDE= c_rec.LATITUDE,
DELETED_FLAG= MSC_UTIL.SYS_NO,
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE PARTNER_TYPE= 3
AND SR_TP_ID= c_rec.SR_TP_ID
AND SR_TP_SITE_ID= c_rec.SR_TP_SITE_ID
AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
INSERT INTO MSC_Trading_Partner_Sites
( PARTNER_ID,
PARTNER_SITE_ID,
PARTNER_ADDRESS,
LONGITUDE,
LATITUDE,
PARTNER_TYPE,
SR_TP_ID,
SR_TP_SITE_ID,
SR_INSTANCE_ID,
TP_SITE_CODE,
LOCATION,
DELETED_FLAG,
REFRESH_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( c_rec.PARTNER_ID,
MSC_Trading_Partner_Sites_S.NEXTVAL,
c_rec.PARTNER_ADDRESS,
c_rec.LONGITUDE,
c_rec.LATITUDE,
3,
c_rec.SR_TP_ID,
c_rec.SR_TP_SITE_ID,
c_rec.SR_INSTANCE_ID,
c_rec.TP_SITE_CODE,
c_rec.LOCATION,
MSC_UTIL.SYS_NO,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user );
DELETE MSC_LOCATION_ASSOCIATIONS
WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
-- MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_LOCATION_ASSOCIATIONS', MSC_CL_COLLECTION.v_instance_id, NULL);
INSERT INTO MSC_LOCATION_ASSOCIATIONS
( LOCATION_ID,
LOCATION_CODE,
PARTNER_ID,
PARTNER_SITE_ID,
organization_id,
SR_INSTANCE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( c_rec.LOCATION_ID,
c_rec.LOCATION_CODE,
c_rec.PARTNER_ID,
c_rec.PARTNER_SITE_ID,
c_rec.organization_id,
c_rec.SR_INSTANCE_ID,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user );
INSERT INTO MSC_LOCATION_ASSOCIATIONS
( LOCATION_ID,
LOCATION_CODE,
PARTNER_ID,
PARTNER_SITE_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( c_rec.LOCATION_ID,
c_rec.LOCATION_CODE,
c_rec.PARTNER_ID,
c_rec.PARTNER_SITE_ID,
c_rec.ORGANIZATION_ID,
c_rec.SR_INSTANCE_ID,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PARTNER_CONTACTS', MSC_CL_COLLECTION.v_instance_id, NULL);
if (c_rec.DELETED_FLAG =MSC_UTIL.SYS_YES) then
delete from MSC_PARTNER_CONTACTS where
partner_id=c_rec.PARTNER_ID
and nvl(partner_site_id,-99999)=nvl(c_rec.PARTNER_SITE_ID,-99999)
and partner_type=c_rec.PARTNER_TYPE
and SR_INSTANCE_ID =MSC_CL_COLLECTION.v_instance_id
and c_rec.DELETED_FLAG =MSC_UTIL.SYS_YES;
UPDATE MSC_PARTNER_CONTACTS
set Name=c_rec.NAME,
DISPLAY_NAME =c_rec.DISPLAY_NAME,
EMAIL = c_rec.EMAIL,
FAX = c_rec.FAX,
ENABLED_FLAG =c_rec.ENABLED_FLAG,
REFRESH_NUMBER =MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
CREATION_DATE =MSC_CL_COLLECTION.v_current_date,
CREATED_BY =MSC_CL_COLLECTION.v_current_user
where
partner_id=c_rec.PARTNER_ID
and nvl(partner_site_id,-99999)=nvl(c_rec.PARTNER_SITE_ID,-99999)
and partner_type=c_rec.PARTNER_TYPE
and SR_INSTANCE_ID =MSC_CL_COLLECTION.v_instance_id;
IF MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh OR (SQL%NOTFOUND and c_rec.DELETED_FLAG =MSC_UTIL.SYS_NO)
THEN
IF lv_old_partner_id <> NVL(c_rec.partner_id,0) OR
lv_old_partner_site_id <> NVL(c_rec.partner_site_id,0) OR
lv_old_partner_type <> c_rec.partner_type THEN
BEGIN
INSERT INTO MSC_PARTNER_CONTACTS
( PARTNER_ID,
PARTNER_SITE_ID,
PARTNER_TYPE,
NAME,
DISPLAY_NAME,
EMAIL,
FAX,
ENABLED_FLAG,
SR_INSTANCE_ID,
REFRESH_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( c_rec.PARTNER_ID,
c_rec.PARTNER_SITE_ID,
c_rec.PARTNER_TYPE,
c_rec.NAME,
c_rec.DISPLAY_NAME,
c_rec.EMAIL,
c_rec.FAX,
c_rec.ENABLED_FLAG,
MSC_CL_COLLECTION.v_instance_id,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user );
SELECT
msp.ORGANIZATION_ID,
msp.DEMAND_TIME_FENCE_FLAG,
msp.PLANNING_TIME_FENCE_FLAG,
msp.OPERATION_SCHEDULE_TYPE,
msp.CONSIDER_WIP,
msp.CONSIDER_PO,
msp.SNAPSHOT_LOCK,
msp.PLAN_SAFETY_STOCK,
msp.CONSIDER_RESERVATIONS,
msp.PART_INCLUDE_TYPE,
msp.DEFAULT_ABC_ASSIGNMENT_GROUP,
msp.PERIOD_TYPE,
msp.RESCHED_ASSUMPTION,
msp.PLAN_DATE_DEFAULT_TYPE,
msp.INCLUDE_REP_SUPPLY_DAYS,
msp.INCLUDE_MDS_DAYS,
msp.REPETITIVE_HORIZON1,
msp.REPETITIVE_HORIZON2,
msp.REPETITIVE_BUCKET_SIZE1,
msp.REPETITIVE_BUCKET_SIZE2,
msp.REPETITIVE_BUCKET_SIZE3,
msp.REPETITIVE_ANCHOR_DATE,
msp.SR_INSTANCE_ID
FROM MSC_ST_PARAMETERS msp
WHERE msp.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
--DELETE FROM MSC_PARAMETERS
-- WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
UPDATE MSC_PARAMETERS
SET
DEMAND_TIME_FENCE_FLAG= c_rec.DEMAND_TIME_FENCE_FLAG,
PLANNING_TIME_FENCE_FLAG= c_rec.PLANNING_TIME_FENCE_FLAG,
OPERATION_SCHEDULE_TYPE= c_rec.OPERATION_SCHEDULE_TYPE,
CONSIDER_WIP= c_rec.CONSIDER_WIP,
CONSIDER_PO= c_rec.CONSIDER_PO,
SNAPSHOT_LOCK= c_rec.SNAPSHOT_LOCK,
PLAN_SAFETY_STOCK= c_rec.PLAN_SAFETY_STOCK,
CONSIDER_RESERVATIONS= c_rec.CONSIDER_RESERVATIONS,
PART_INCLUDE_TYPE= c_rec.PART_INCLUDE_TYPE,
DEFAULT_ABC_ASSIGNMENT_GROUP= c_rec.DEFAULT_ABC_ASSIGNMENT_GROUP,
PERIOD_TYPE= c_rec.PERIOD_TYPE,
RESCHED_ASSUMPTION= c_rec.RESCHED_ASSUMPTION,
PLAN_DATE_DEFAULT_TYPE= c_rec.PLAN_DATE_DEFAULT_TYPE,
INCLUDE_REP_SUPPLY_DAYS= c_rec.INCLUDE_REP_SUPPLY_DAYS,
INCLUDE_MDS_DAYS= c_rec.INCLUDE_MDS_DAYS,
REPETITIVE_HORIZON1= c_rec.REPETITIVE_HORIZON1,
REPETITIVE_HORIZON2= c_rec.REPETITIVE_HORIZON2,
REPETITIVE_BUCKET_SIZE1= c_rec.REPETITIVE_BUCKET_SIZE1,
REPETITIVE_BUCKET_SIZE2= c_rec.REPETITIVE_BUCKET_SIZE2,
REPETITIVE_BUCKET_SIZE3= c_rec.REPETITIVE_BUCKET_SIZE3,
REPETITIVE_ANCHOR_DATE= c_rec.REPETITIVE_ANCHOR_DATE,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID;
/* Bug: 1993151 remove the collected flag from the update statement */
-- AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
INSERT INTO MSC_PARAMETERS
( ORGANIZATION_ID,
DEMAND_TIME_FENCE_FLAG,
PLANNING_TIME_FENCE_FLAG,
OPERATION_SCHEDULE_TYPE,
CONSIDER_WIP,
CONSIDER_PO,
SNAPSHOT_LOCK,
PLAN_SAFETY_STOCK,
CONSIDER_RESERVATIONS,
PART_INCLUDE_TYPE,
DEFAULT_ABC_ASSIGNMENT_GROUP,
PERIOD_TYPE,
RESCHED_ASSUMPTION,
PLAN_DATE_DEFAULT_TYPE,
INCLUDE_REP_SUPPLY_DAYS,
INCLUDE_MDS_DAYS,
REPETITIVE_HORIZON1,
REPETITIVE_HORIZON2,
REPETITIVE_BUCKET_SIZE1,
REPETITIVE_BUCKET_SIZE2,
REPETITIVE_BUCKET_SIZE3,
REPETITIVE_ANCHOR_DATE,
NETWORK_SCHEDULING_METHOD, /* hard coded to 1 (primary)*/
COLLECTED_FLAG,
SR_INSTANCE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( c_rec.ORGANIZATION_ID,
c_rec.DEMAND_TIME_FENCE_FLAG,
c_rec.PLANNING_TIME_FENCE_FLAG,
c_rec.OPERATION_SCHEDULE_TYPE,
c_rec.CONSIDER_WIP,
c_rec.CONSIDER_PO,
c_rec.SNAPSHOT_LOCK,
c_rec.PLAN_SAFETY_STOCK,
c_rec.CONSIDER_RESERVATIONS,
c_rec.PART_INCLUDE_TYPE,
c_rec.DEFAULT_ABC_ASSIGNMENT_GROUP,
c_rec.PERIOD_TYPE,
c_rec.RESCHED_ASSUMPTION,
c_rec.PLAN_DATE_DEFAULT_TYPE,
c_rec.INCLUDE_REP_SUPPLY_DAYS,
c_rec.INCLUDE_MDS_DAYS,
c_rec.REPETITIVE_HORIZON1,
c_rec.REPETITIVE_HORIZON2,
c_rec.REPETITIVE_BUCKET_SIZE1,
c_rec.REPETITIVE_BUCKET_SIZE2,
c_rec.REPETITIVE_BUCKET_SIZE3,
c_rec.REPETITIVE_ANCHOR_DATE,
1,
MSC_UTIL.SYS_YES,
c_rec.SR_INSTANCE_ID,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user );
select
msuom.UNIT_OF_MEASURE,
msuom.UOM_CODE,
msuom.UOM_CLASS,
msuom.BASE_UOM_FLAG,
msuom.DISABLE_DATE,
msuom.DESCRIPTION,
msuom.SR_INSTANCE_ID
from MSC_ST_UNITS_OF_MEASURE msuom
where msuom.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
order by UNIT_OF_MEASURE; /* use order by to avoid dead locking */
select
NVL( t1.INVENTORY_ITEM_ID,0) INVENTORY_ITEM_ID, -- 0 means resource
msucc.INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID,
msucc.FROM_UNIT_OF_MEASURE,
msucc.FROM_UOM_CODE,
msucc.FROM_UOM_CLASS,
msucc.TO_UNIT_OF_MEASURE,
msucc.TO_UOM_CODE,
msucc.TO_UOM_CLASS,
msucc.CONVERSION_RATE,
msucc.DISABLE_DATE,
msucc.SR_INSTANCE_ID
from MSC_ITEM_ID_LID t1,
MSC_ST_UOM_CLASS_CONVERSIONS msucc
WHERE t1.SR_INVENTORY_ITEM_ID(+)= msucc.Inventory_Item_ID
AND t1.sr_instance_id(+)= msucc.sr_instance_id
AND DECODE( t1.INVENTORY_ITEM_ID, NULL, msucc.Inventory_ITEM_ID,0 )= 0
AND msucc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
ORDER BY
1,
msucc.FROM_UNIT_OF_MEASURE,
msucc.TO_UNIT_OF_MEASURE;
SELECT
msuc.UNIT_OF_MEASURE,
msuc.UOM_CODE,
msuc.UOM_CLASS,
NVL( t1.INVENTORY_ITEM_ID,0) Inventory_Item_ID,
msuc.CONVERSION_RATE,
msuc.DEFAULT_CONVERSION_FLAG,
msuc.DISABLE_DATE,
msuc.SR_INSTANCE_ID,
msuc.Inventory_Item_ID SR_Inventory_Item_ID
from MSC_ITEM_ID_LID t1,
MSC_ST_UOM_CONVERSIONS msuc
WHERE t1.SR_INVENTORY_ITEM_ID(+)= msuc.Inventory_Item_ID
AND t1.sr_instance_id(+)= msuc.sr_instance_id
AND DECODE( t1.INVENTORY_ITEM_ID, NULL, msuc.Inventory_ITEM_ID,0 )= 0
AND msuc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
ORDER BY
4,1;
DELETE FROM MSC_UNITS_OF_MEASURE
WHERE SR_INSTANCE_ID IN ( MSC_CL_COLLECTION.v_instance_id, -MSC_CL_COLLECTION.v_instance_id);
UPDATE MSC_UNITS_OF_MEASURE muom
SET muom.UOM_CODE= c_rec.UOM_CODE,
muom.UOM_CLASS= c_rec.UOM_CLASS,
muom.BASE_UOM_FLAG= c_rec.BASE_UOM_FLAG,
muom.DISABLE_DATE= c_rec.DISABLE_DATE,
muom.DESCRIPTION= c_rec.DESCRIPTION,
muom.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
muom.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
muom.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
muom.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE muom.UNIT_OF_MEASURE= c_rec.UNIT_OF_MEASURE;
INSERT INTO MSC_UNITS_OF_MEASURE
( UNIT_OF_MEASURE,
UOM_CODE,
UOM_CLASS,
BASE_UOM_FLAG,
DISABLE_DATE,
DESCRIPTION,
SR_INSTANCE_ID,
REFRESH_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( c_rec.UNIT_OF_MEASURE,
c_rec.UOM_CODE,
c_rec.UOM_CLASS,
c_rec.BASE_UOM_FLAG,
c_rec.DISABLE_DATE,
c_rec.DESCRIPTION,
c_rec.SR_INSTANCE_ID,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user );
DELETE FROM MSC_UOM_CLASS_CONVERSIONS
WHERE SR_INSTANCE_ID IN ( MSC_CL_COLLECTION.v_instance_id, -MSC_CL_COLLECTION.v_instance_id);
UPDATE MSC_UOM_CLASS_CONVERSIONS mucc
SET mucc.FROM_UOM_CODE= c_rec.FROM_UOM_CODE,
mucc.FROM_UOM_CLASS= c_rec.FROM_UOM_CLASS,
mucc.TO_UOM_CODE= c_rec.TO_UOM_CODE,
mucc.TO_UOM_CLASS= c_rec.TO_UOM_CLASS,
mucc.CONVERSION_RATE= c_rec.CONVERSION_RATE,
mucc.DISABLE_DATE= c_rec.DISABLE_DATE,
mucc.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
mucc.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
mucc.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
mucc.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE mucc.INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
AND mucc.FROM_UNIT_OF_MEASURE= c_rec.FROM_UNIT_OF_MEASURE
AND mucc.TO_UNIT_OF_MEASURE= c_rec.TO_UNIT_OF_MEASURE;
insert into MSC_UOM_CLASS_CONVERSIONS
( INVENTORY_ITEM_ID,
FROM_UNIT_OF_MEASURE,
FROM_UOM_CODE,
FROM_UOM_CLASS,
TO_UNIT_OF_MEASURE,
TO_UOM_CODE,
TO_UOM_CLASS,
CONVERSION_RATE,
DISABLE_DATE,
SR_INSTANCE_ID,
REFRESH_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( c_rec.INVENTORY_ITEM_ID,
c_rec.FROM_UNIT_OF_MEASURE,
c_rec.FROM_UOM_CODE,
c_rec.FROM_UOM_CLASS,
c_rec.TO_UNIT_OF_MEASURE,
c_rec.TO_UOM_CODE,
c_rec.TO_UOM_CLASS,
c_rec.CONVERSION_RATE,
c_rec.DISABLE_DATE,
c_rec.SR_INSTANCE_ID,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user );
DELETE FROM MSC_UOM_CONVERSIONS
WHERE SR_INSTANCE_ID IN ( MSC_CL_COLLECTION.v_instance_id, -MSC_CL_COLLECTION.v_instance_id);
/* changed the where cond to update the row based on UOM code as a new
index on MSC_UOM_CONVERSIONS(INVENTORY_ITEM_ID,UOM_CODE) is introduced */
UPDATE MSC_UOM_CONVERSIONS muc
SET UNIT_OF_MEASURE= c_rec.UNIT_OF_MEASURE,
UOM_CODE= c_rec.UOM_CODE,
UOM_CLASS= c_rec.UOM_CLASS,
INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
CONVERSION_RATE= c_rec.CONVERSION_RATE,
DEFAULT_CONVERSION_FLAGS= c_rec.DEFAULT_CONVERSION_FLAG,
DISABLE_DATE= c_rec.DISABLE_DATE,
SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE muc.INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
AND muc.UOM_CODE = c_rec.UOM_CODE;
insert into MSC_UOM_CONVERSIONS
( UNIT_OF_MEASURE,
UOM_CODE,
UOM_CLASS,
INVENTORY_ITEM_ID,
CONVERSION_RATE,
DEFAULT_CONVERSION_FLAGS,
DISABLE_DATE,
SR_INSTANCE_ID,
REFRESH_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( c_rec.UNIT_OF_MEASURE,
c_rec.UOM_CODE,
c_rec.UOM_CLASS,
c_rec.INVENTORY_ITEM_ID,
c_rec.CONVERSION_RATE,
c_rec.DEFAULT_CONVERSION_FLAG,
c_rec.DISABLE_DATE,
c_rec.SR_INSTANCE_ID,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user);
select distinct
SUPPLIER_ID,
SUPPLIER_SITE_ID ,
ORGANIZATION_ID ,
SR_INSTANCE_ID,
INVENTORY_ITEM_ID,
INCLUDE_LIABILITY_AGREEMENT,
ASL_LIABILITY_AGREEMENT_BASIS,USING_ORGANIZATION_ID
from
msc_asl_auth_details
where
plan_id = -1 and
sr_instance_id = MSC_CL_COLLECTION.v_instance_id ;
/* This deletes the agreements form MSC_ASL_AUTH_DETAILS that dont have parent records in msc_item_suppliers*/
delete msc_asl_auth_details a
where not exists (select 1 from msc_item_suppliers p
where p.PLAN_ID = a.PLAN_ID and
p.SUPPLIER_ID = a. SUPPLIER_ID and
p.SUPPLIER_SITE_ID = a.SUPPLIER_SITE_ID and
p.ORGANIZATION_ID = a.ORGANIZATION_ID and
p.SR_INSTANCE_ID = a.SR_INSTANCE_ID and
p.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID and
p.using_organization_id =a.using_organization_id and
p.sr_instance_id = MSC_CL_COLLECTION.v_instance_id and
p.plan_id = -1
)
and sr_instance_id = MSC_CL_COLLECTION.v_instance_id
and plan_id = -1 ;
/* Updates the msc_item_supplier : include_liability_agreement , asl_liability_agreement_basis */
FOR x_sup_item_org in c_sup_item_org
LOOP
UPDATE msc_item_suppliers
set INCLUDE_LIABILITY_AGREEMENT = x_sup_item_org.INCLUDE_LIABILITY_AGREEMENT ,
ASL_LIABILITY_AGREEMENT_BASIS = x_sup_item_org.ASL_LIABILITY_AGREEMENT_BASIS
where
SUPPLIER_ID = x_sup_item_org.SUPPLIER_ID and
SUPPLIER_SITE_ID = x_sup_item_org.SUPPLIER_SITE_ID and
ORGANIZATION_ID = x_sup_item_org.ORGANIZATION_ID and
SR_INSTANCE_ID = x_sup_item_org.SR_INSTANCE_ID and
INVENTORY_ITEM_ID = x_sup_item_org.INVENTORY_ITEM_ID and
USING_ORGANIZATION_ID = x_sup_item_org.USING_ORGANIZATION_ID and
plan_id = -1
;
SELECT mtil.TP_ID,
mtil.PARTNER_TYPE,
mst.ORGANIZATION_CODE,
mst.SR_TP_ID,
mst.DISABLE_DATE,
mst.STATUS,
mst.MASTER_ORGANIZATION,
mst.WEIGHT_UOM,
mst.MAXIMUM_WEIGHT,
mst.VOLUME_UOM,
mst.MAXIMUM_VOLUME,
mst.PARTNER_NUMBER,
mst.CALENDAR_CODE,
mst.CALENDAR_EXCEPTION_SET_ID,
mst.OPERATING_UNIT,
mst.SR_INSTANCE_ID,
mst.PROJECT_REFERENCE_ENABLED,
mst.PROJECT_CONTROL_LEVEL,
mst.CUSTOMER_CLASS_CODE,
mst.CUSTOMER_TYPE
FROM MSC_TP_ID_LID mtil,
MSC_ST_TRADING_PARTNERS mst
WHERE mtil.SR_TP_ID= mst.SR_TP_ID
AND mtil.SR_INSTANCE_ID= mst.SR_INSTANCE_ID
AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND mtil.Partner_Type= mst.Partner_Type
AND mst.Partner_Type IN (1,2,4) --Vendor/Customer/Carrier
/* SCE Change Starts */
AND nvl(mst.company_id, -1) = mtil.sr_company_id
AND nvl(mst.company_id, -1) = -1
/* SCE Change Starts */
ORDER BY
mtil.TP_ID;
SELECT mtsil.TP_SITE_ID,
mtsil.Partner_Type,
substrb(msts.PARTNER_ADDRESS,1,1600) PARTNER_ADDRESS,--added for the NLS bug3463401
msts.POSTAL_CODE,
substrb(msts.CITY,1,60) CITY,--added for the NLS bug3463401
msts.STATE,
msts.COUNTRY,
msts.LONGITUDE,
msts.LATITUDE,
msts.SR_TP_SITE_ID,
msts.SR_INSTANCE_ID,
msts.SHIPPING_CONTROL
FROM MSC_TP_SITE_ID_LID mtsil,
MSC_ST_TRADING_PARTNER_SITES msts
WHERE mtsil.SR_TP_SITE_ID= msts.SR_TP_SITE_ID
AND mtsil.SR_INSTANCE_ID= msts.SR_INSTANCE_ID
AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND mtsil.Partner_Type= msts.Partner_Type
AND msts.Partner_Type IN (1,2) --Vendor/Customer
/* SCE Change Starts */
AND nvl(msts.company_id, -1) = mtsil.sr_company_id
/* SCE Change Starts */
ORDER BY
mtsil.TP_SITE_ID;
SELECT distinct mst.Partner_Name, mst.Partner_Type
FROM MSC_ST_TRADING_PARTNERS mst
WHERE NOT EXISTS ( select 1
from MSC_TRADING_PARTNERS mtp
where mtp.Partner_Name= mst.Partner_Name
and mtp.Partner_Type= mst.Partner_Type)
AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND mst.Partner_type IN (1,2)
ORDER BY mst.Partner_Type,
mst.Partner_Name; -- using ORDER BY to avoid dead lock
SELECT decode(mc.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID, null, mc.company_id) company_id1,
mst.partner_name partner_name,
mst.partner_type partner_type
from MSC_ST_TRADING_PARTNERS mst,
MSC_COMPANIES MC
where nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
and mst.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
and mst.partner_type IN (1,2,4) --Vendor/Customer/Carrier
/* SCE CHANGE STARTS */
and nvl(mst.company_id , -1) = -1
/* SCE CHANGE ENDS */
MINUS
SELECT decode(mtp.company_id,null, null, mtp.company_id) company_id,
mtp.partner_name partner_name,
mtp.partner_type partner_type
from msc_trading_partners mtp
where mtp.partner_type IN (1,2,4) --Vendor/Customer/Carrier
ORDER BY partner_type,
company_id1,
partner_name ;
SELECT distinct mst.Partner_Name, mst.sr_tp_id, mst.sr_instance_id, mst.Partner_Type,
decode(mc.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID, -1, mc.company_id) company_id1
FROM MSC_ST_TRADING_PARTNERS mst,
MSC_COMPANIES MC
WHERE EXISTS ( select 1
from MSC_TRADING_PARTNERS mtp
where mtp.sr_tp_id= mst.sr_tp_id
and mtp.sr_instance_id= mst.sr_instance_id
and mtp.Partner_Type= mst.Partner_Type
and nvl(mtp.COMPANY_ID,MSC_CL_COLLECTION.G_MY_COMPANY_ID) = MC.COMPANY_ID
and mtp.Partner_Name <> mst.Partner_Name)
AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND mst.Partner_type IN (1,2)
AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
AND nvl(mst.company_id , -1) = -1
ORDER BY mst.Partner_Name; -- using ORDER BY to avoid dead lock
SELECT distinct mtil.TP_ID, msts.tp_site_code
FROM MSC_ST_TRADING_PARTNER_SITES msts,
MSC_TP_ID_LID mtil
WHERE NOT EXISTS ( select 1
from MSC_TRADING_PARTNER_SITES mtps
where mtps.TP_Site_Code= msts.TP_Site_Code
and mtps.Partner_ID= mtil.tp_id)
AND msts.SR_Instance_ID= mtil.SR_INSTANCE_ID
AND msts.SR_TP_ID= mtil.SR_TP_ID
/* SCE CHANGE */
AND nvl(msts.company_id, -1) = mtil.sr_company_id
AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND msts.Partner_Type=1
AND mtil.Partner_type=1
ORDER BY mtil.TP_ID,
msts.TP_Site_Code; -- using ORDER BY to avoid dead lock
SELECT distinct
mtil.TP_ID,
msts.Operating_Unit_Name,
msts.TP_Site_Code,
msts.Location
FROM MSC_ST_TRADING_PARTNER_SITES msts,
MSC_TP_ID_LID mtil
WHERE NOT EXISTS ( select 1
from MSC_TRADING_PARTNER_SITES mtps
where NVL(mtps.Operating_Unit_Name, ' ')=
NVL(msts.Operating_Unit_Name, ' ')
and mtps.TP_Site_Code= msts.TP_Site_Code
and mtps.Location= msts.Location
and mtps.Partner_ID= mtil.TP_ID)
AND msts.SR_Instance_ID= mtil.SR_INSTANCE_ID
AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND msts.SR_TP_ID= mtil.SR_TP_ID
/* SCE CHANGE starts*/
AND nvl(msts.company_id, -1) = mtil.sr_company_id
/* SCE CHANGE ends*/
AND msts.Partner_Type=2
AND mtil.Partner_type=2
ORDER BY mtil.TP_ID,
msts.TP_Site_Code,
msts.Location; -- using ORDER BY to avoid dead lock
SELECT distinct
nvl(msts.company_id, -1) SR_COMPANY_ID,
msts.SR_TP_SITE_ID,
msts.SR_INSTANCE_ID,
mtp.PARTNER_SITE_ID
FROM MSC_ST_TRADING_PARTNER_SITES msts,
MSC_TP_ID_LID mtil,
MSC_TRADING_PARTNER_SITES mtp
WHERE NOT EXISTS( select 1
from MSC_TP_SITE_ID_LID mtsil
where msts.SR_TP_SITE_ID= mtsil.SR_TP_SITE_ID
and msts.SR_INSTANCE_ID= mtsil.SR_INSTANCE_ID
and mtsil.Partner_Type= 1
and nvl(msts.company_id, -1) = mtsil.sr_company_id)
AND msts.TP_Site_Code= mtp.TP_Site_Code
AND msts.SR_TP_ID= mtil.SR_TP_ID
AND msts.SR_INSTANCE_ID= mtil.SR_INSTANCE_ID
AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
/* SCE Change starts */
AND nvl(msts.company_id, -1) = mtil.SR_COMPANY_ID
/* SCE changes ends */
AND mtil.TP_ID= mtp.Partner_ID
AND mtp.partner_type = mtil.partner_type
AND mtil.Partner_Type= msts.partner_type
AND msts.Partner_Type= 1;
SELECT distinct
/* SCE Change starts*/
/* Added sr_company_id for SCE purpose */
nvl(msts.company_id, -1) SR_COMPANY_ID,
/* SCE Change ends*/
msts.SR_TP_SITE_ID,
msts.SR_INSTANCE_ID,
mtp.PARTNER_SITE_ID
FROM MSC_ST_TRADING_PARTNER_SITES msts,
MSC_TP_ID_LID mtil,
MSC_TRADING_PARTNER_SITES mtp
WHERE NOT EXISTS( select 1
from MSC_TP_SITE_ID_LID mtsil
where msts.SR_TP_SITE_ID= mtsil.SR_TP_SITE_ID
and msts.SR_INSTANCE_ID= mtsil.SR_INSTANCE_ID
and mtsil.Partner_Type= 2
and nvl(msts.company_id, -1) = mtsil.sr_company_id)
AND NVL( msts.Operating_Unit_Name, ' ')=
NVL( mtp.Operating_Unit_Name, ' ')
AND msts.TP_Site_Code= mtp.TP_Site_Code
AND nvl(msts.Location, ' ')= nvl(mtp.Location, ' ')
AND msts.SR_TP_ID= mtil.SR_TP_ID
AND msts.SR_INSTANCE_ID= mtil.SR_INSTANCE_ID
AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
/* SCE Change stars*/
/* Added sr_company_id for SCE purpose */
AND nvl(msts.company_id, -1) = mtil.SR_COMPANY_ID
/* SCE Change stars*/
AND mtil.TP_ID= mtp.Partner_ID
AND mtp.partner_type = mtil.partner_type
AND mtil.Partner_Type= msts.partner_type
AND msts.Partner_Type= 2;
SELECT distinct
/* SCE Change starts */
nvl(mst.company_id, -1) SR_COMPANY_ID,
/* SCE change ends */
mst.SR_TP_ID,
mst.SR_INSTANCE_ID,
mst.Partner_Type,
mtp.PARTNER_ID
FROM MSC_ST_TRADING_PARTNERS mst,
MSC_TRADING_PARTNERS mtp,
/* SCE Change starts */
msc_companies mc
/* SCE Change ends */
WHERE NOT EXISTS( select 1
from MSC_TP_ID_LID mtil
where mst.SR_TP_ID= mtil.SR_TP_ID
and mst.SR_INSTANCE_ID= mtil.SR_INSTANCE_ID
and mst.Partner_Type= mtil.Partner_Type
-- SCE Change
-- Join with company_id
and nvl( mst.company_id, -1) = nvl(mtil.sr_company_id, -1)
and nvl( mst.company_id, -1) = -1)
AND mst.Partner_NAME= mtp.Partner_NAME
AND mst.Partner_Type= mtp.Partner_Type
AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND mst.Partner_Type IN ( 1, 2)
/* SCE Change starts */
-- Add join with msc_companies
AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
AND mc.company_id = nvl(mtp.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID );
-- ============ Cursor for UPDATE MSC_TP_ID_LID SRP Changes ==================== --
CURSOR c13 IS
Select resource_type, sr_instance_id , partner_type,sr_tp_id
From msc_st_trading_partners
Where sr_instance_id = MSC_CL_COLLECTION.v_instance_id
And partner_type=2;
SELECT decode(nvl(fnd_profile.value('MSC_PURGE_ST_CONTROL'),'N'),'Y',1,2)
INTO lv_control_flag
FROM dual;
select num_rows,decode (stale_stats,'NO', MSC_UTIL.SYS_NO, MSC_UTIL.SYS_YES )
into lv_tp_id_count, lv_tp_stat_stale
from dba_TAB_STATISTICS
where table_name = 'MSC_TP_ID_LID';
select num_rows,decode (stale_stats,'NO', MSC_UTIL.SYS_NO, MSC_UTIL.SYS_YES )
into lv_tp_site_id_count, lv_tp_site_stat_stale
from dba_TAB_STATISTICS
where table_name ='MSC_TP_SITE_ID_LID';
DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1;
DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
--In case of continuous and targetted collections, delete carrier records from MSC_TP_ID_LID
--when sourcing SRS launch parameter is Yes --and delete supplier and customer records
--from MSC_TP_ID_LID when either Supplier or Customer SRS launch parameter is Yes.
IF (MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.V_IS_CONT_REFRESH) THEN
IF (MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag=MSC_UTIL.SYS_YES) THEN
DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type=4;
DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type=4;
-- We do not delete data if the profile "MSC_COLLECTION_WINDOW_FOR_TP_CHANGES" is set to not null.
IF lv_msc_tp_coll_window = 0 THEN
IF lv_control_flag = 2 THEN
DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type in (1,2);
DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type in (1,2);
DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
-- We do not delete data if the profile "MSC_COLLECTION_WINDOW_FOR_TP_CHANGES" is set to not null.
IF lv_msc_tp_coll_window = 0 THEN
IF lv_control_flag = 2 THEN
DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type in (1,2);
DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type in (1,2);
DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
SELECT count(*)
INTO lv_partner_count
FROM msc_trading_partners
WHERE
partner_name = c_rec.partner_name
AND partner_type = c_rec.partner_type
AND nvl(company_id, -1) = c_rec.company_id1;
UPDATE MSC_TRADING_PARTNERS mtp
SET mtp.PARTNER_NAME= c_rec.PARTNER_NAME
WHERE mtp.SR_TP_ID= c_rec.SR_TP_ID
AND mtp.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
AND mtp.PARTNER_TYPE= c_rec.PARTNER_TYPE
AND nvl(mtp.company_id,-1) = c_rec.company_id1;
SELECT partner_id
INTO lv_old_partner_id
FROM msc_trading_partners
WHERE
sr_tp_id = c_rec.SR_TP_ID
AND SR_INSTANCE_ID = c_rec.SR_INSTANCE_ID
AND PARTNER_TYPE = c_rec.PARTNER_TYPE
AND nvl(company_id,-1) = c_rec.company_id1;
SELECT partner_id
INTO lv_new_partner_id
FROM msc_trading_partners
WHERE
partner_name= c_rec.partner_name
AND SR_INSTANCE_ID = c_rec.SR_INSTANCE_ID
AND PARTNER_TYPE = c_rec.PARTNER_TYPE
AND nvl(company_id,-1) = c_rec.company_id1;
/*DELETE FROM MSC_TRADING_PARTNERS
WHERE SR_TP_ID = c_rec.SR_TP_ID
AND SR_INSTANCE_ID = c_rec.SR_INSTANCE_ID
AND PARTNER_TYPE = c_rec.PARTNER_TYPE;
UPDATE MSC_TRADING_PARTNER_SITES
SET PARTNER_ID = lv_new_partner_id,
LAST_UPDATE_DATE = v_current_date,
LAST_UPDATED_BY = v_current_user
WHERE PARTNER_ID = lv_old_partner_id
AND SR_INSTANCE_ID = c_rec.SR_INSTANCE_ID
AND PARTNER_TYPE = c_rec.PARTNER_TYPE;
UPDATE MSC_TP_ID_LID
SET TP_ID = lv_new_partner_id
WHERE SR_TP_ID = c_rec.SR_TP_ID
AND SR_INSTANCE_ID = c_rec.SR_INSTANCE_ID
AND PARTNER_TYPE = c_rec.PARTNER_TYPE;
INSERT INTO MSC_TRADING_PARTNERS
( /* SCE Change starts */
COMPANY_ID,
/* SCE Change ends */
PARTNER_NAME,
PARTNER_ID,
SR_TP_ID,
PARTNER_TYPE,
PARTNER_NUMBER,
MASTER_ORGANIZATION,
SR_INSTANCE_ID,
REFRESH_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( /* SCE Change starts */
c_rec.COMPANY_ID1,
/* SCE Change ends */
c_rec.Partner_Name,
MSC_Trading_Partners_S.NEXTVAL,
MSC_Trading_Partners_S.NEXTVAL, -- dummy value to satisfy the unique constraint
c_rec.Partner_Type,
-1,
-1,
-1,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user );
Insert-as-select to improve performance */
FOR c_rec IN c12 LOOP
BEGIN
INSERT INTO MSC_TP_ID_LID
( /* SCE Change starts */
SR_COMPANY_ID,
/* SCE change ends */
SR_TP_ID,
SR_INSTANCE_ID,
Partner_Type,
TP_ID)
VALUES
( c_rec.SR_COMPANY_ID,
c_rec.SR_TP_ID,
c_rec.SR_INSTANCE_ID,
c_rec.PARTNER_TYPE,
c_rec.PARTNER_ID);
INSERT /*+ APPEND */ INTO MSC_TP_ID_LID
( SR_COMPANY_ID,
SR_TP_ID,
SR_INSTANCE_ID,
Partner_Type,
TP_ID,
AGGREGATE_DEMAND_FLAG,
SR_CUST_ACCOUNT_NUMBER)
SELECT distinct
nvl(mst.company_id, -1) SR_COMPANY_ID,
mst.SR_TP_ID,
mst.SR_INSTANCE_ID,
mst.Partner_Type,
mtp.PARTNER_ID,
mst.AGGREGATE_DEMAND_FLAG,
mst.CUST_ACCOUNT_NUMBER
FROM MSC_ST_TRADING_PARTNERS mst,
MSC_TRADING_PARTNERS mtp,
MSC_COMPANIES mc
WHERE NOT EXISTS( select 1
from MSC_TP_ID_LID mtil
where mst.SR_TP_ID= mtil.SR_TP_ID
and mst.SR_INSTANCE_ID= mtil.SR_INSTANCE_ID
and nvl( mst.company_id, -1) = nvl(mtil.sr_company_id, -1)
and nvl( mst.company_id, -1) = -1
and mst.Partner_Type= mtil.Partner_Type)
AND mst.Partner_NAME= mtp.Partner_NAME
AND mst.Partner_Type= mtp.Partner_Type
AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND mst.Partner_Type IN ( 1, 2,4)
AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
AND mc.company_id = nvl(mtp.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID)
and nvl( mst.company_id, -1) = -1;
-- ==== Update msc_tp_id_lid with resource_type ==== SRP Changes
FOR c_rec IN c13 LOOP
BEGIN
UPDATE MSC_TP_ID_LID
set
resource_type = c_rec.resource_type
WHERE sr_tp_id= c_rec.sr_tp_id And
partner_type=2 And
sr_instance_id = MSC_CL_COLLECTION.v_instance_id;
INSERT INTO MSC_TRADING_PARTNER_SITES
( TP_SITE_CODE,
PARTNER_ID,
PARTNER_SITE_ID,
SR_TP_SITE_ID,
PARTNER_TYPE,
SR_INSTANCE_ID,
REFRESH_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( c_rec.TP_Site_Code,
c_rec.TP_ID,
MSC_Trading_Partner_Sites_S.NEXTVAL,
MSC_Trading_Partner_Sites_S.NEXTVAL,
1,
MSC_CL_COLLECTION.v_instance_id,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user);
Insert-as-select to improve performance */
FOR c_rec IN c9 LOOP
BEGIN
INSERT INTO MSC_TP_SITE_ID_LID
( /* SCE Change starts */
SR_COMPANY_ID,
/* SCE Change ends */
SR_TP_SITE_ID,
SR_INSTANCE_ID,
Partner_Type,
TP_SITE_ID)
VALUES
( c_rec.SR_COMPANY_ID,
c_rec.SR_TP_SITE_ID,
c_rec.SR_INSTANCE_ID,
1,
c_rec.PARTNER_SITE_ID);
INSERT /*+ APPEND */ INTO MSC_TP_SITE_ID_LID
( SR_COMPANY_ID,
SR_TP_SITE_ID,
SR_INSTANCE_ID,
Partner_Type,
location_id,
operating_unit,
TP_SITE_ID)
SELECT distinct
nvl(msts.company_id, -1) SR_COMPANY_ID,
msts.SR_TP_SITE_ID,
msts.SR_INSTANCE_ID,
1,
msts.location_id,
msts.operating_unit,
mtp.PARTNER_SITE_ID
FROM MSC_ST_TRADING_PARTNER_SITES msts,
MSC_TP_ID_LID mtil,
MSC_TRADING_PARTNER_SITES mtp
WHERE NOT EXISTS( select 1
from MSC_TP_SITE_ID_LID mtsil
where msts.SR_TP_SITE_ID= mtsil.SR_TP_SITE_ID
and msts.SR_INSTANCE_ID= mtsil.SR_INSTANCE_ID
and mtsil.Partner_Type= 1
and nvl(msts.company_id, -1) = mtsil.sr_company_id)
AND msts.TP_Site_Code= mtp.TP_Site_Code
AND msts.SR_TP_ID= mtil.SR_TP_ID
AND msts.SR_INSTANCE_ID= mtil.SR_INSTANCE_ID
AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND nvl(msts.company_id, -1) = mtil.SR_COMPANY_ID
AND mtil.TP_ID= mtp.Partner_ID
AND mtp.partner_type = mtil.partner_type
AND mtil.Partner_Type= msts.partner_type
AND msts.Partner_Type= 1;
INSERT INTO MSC_TRADING_PARTNER_SITES
( TP_SITE_CODE,
LOCATION,
OPERATING_UNIT_NAME,
PARTNER_ID,
PARTNER_SITE_ID,
SR_TP_SITE_ID,
PARTNER_TYPE,
SR_INSTANCE_ID,
REFRESH_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
( c_rec.TP_Site_Code,
c_rec.Location,
c_rec.OPERATING_UNIT_NAME,
c_rec.TP_ID,
MSC_Trading_Partner_Sites_S.NEXTVAL,
MSC_Trading_Partner_Sites_S.NEXTVAL,
2,
MSC_CL_COLLECTION.v_instance_id,
MSC_CL_COLLECTION.v_last_collection_id,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user,
MSC_CL_COLLECTION.v_current_date,
MSC_CL_COLLECTION.v_current_user );
Insert-as-select to improve performance */
FOR c_rec IN c10 LOOP
BEGIN
INSERT INTO MSC_TP_SITE_ID_LID
( /* SCE Change starts*/
SR_COMPANY_ID,
/* SCE Change ends*/
SR_TP_SITE_ID,
SR_INSTANCE_ID,
Partner_Type,
TP_SITE_ID)
VALUES
( c_rec.SR_COMPANY_ID,
c_rec.SR_TP_SITE_ID,
c_rec.SR_INSTANCE_ID,
2,
c_rec.PARTNER_SITE_ID);
INSERT /*+ APPEND */ INTO MSC_TP_SITE_ID_LID
( SR_COMPANY_ID,
SR_TP_SITE_ID,
SR_INSTANCE_ID,
Partner_Type,
location_id,
TP_SITE_ID,
SR_CUST_ACCT_ID)
SELECT distinct
nvl(msts.company_id, -1) SR_COMPANY_ID,
msts.SR_TP_SITE_ID,
msts.SR_INSTANCE_ID,
2,
msts.location_id,
mtp.PARTNER_SITE_ID,
msts.SR_TP_ID
FROM MSC_ST_TRADING_PARTNER_SITES msts,
MSC_TP_ID_LID mtil,
MSC_TRADING_PARTNER_SITES mtp
WHERE NOT EXISTS( select 1
from MSC_TP_SITE_ID_LID mtsil
where msts.SR_TP_SITE_ID= mtsil.SR_TP_SITE_ID
and msts.SR_INSTANCE_ID= mtsil.SR_INSTANCE_ID
and mtsil.Partner_Type= 2
and nvl(msts.company_id, -1) = mtsil.sr_company_id)
AND NVL( msts.Operating_Unit_Name, ' ')=
NVL( mtp.Operating_Unit_Name, ' ')
AND msts.TP_Site_Code= mtp.TP_Site_Code
AND msts.Location= mtp.Location
AND msts.SR_TP_ID= mtil.SR_TP_ID
AND msts.SR_INSTANCE_ID= mtil.SR_INSTANCE_ID
AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND nvl(msts.company_id, -1) = mtil.SR_COMPANY_ID
AND mtil.TP_ID= mtp.Partner_ID
AND mtp.partner_type = mtil.partner_type
AND mtil.Partner_Type= msts.partner_type
AND msts.Partner_Type= 2;
UPDATE MSC_TRADING_PARTNERS mtp
SET ORGANIZATION_CODE= c_rec.ORGANIZATION_CODE,
SR_TP_ID= c_rec.SR_TP_ID,
DISABLE_DATE= c_rec.Disable_Date,
STATUS= c_rec.Status,
MASTER_ORGANIZATION= c_rec.Master_Organization,
WEIGHT_UOM= c_rec.WEIGHT_UOM,
MAXIMUM_WEIGHT= c_rec.MAXIMUM_WEIGHT,
VOLUME_UOM= c_rec.VOLUME_UOM,
MAXIMUM_VOLUME= c_rec.MAXIMUM_VOLUME,
PARTNER_NUMBER= c_rec.PARTNER_NUMBER,
CALENDAR_CODE= c_rec.CALENDAR_CODE,
CALENDAR_EXCEPTION_SET_ID= c_rec.CALENDAR_EXCEPTION_SET_ID,
OPERATING_UNIT= c_rec.OPERATING_UNIT,
SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
PROJECT_REFERENCE_ENABLED= c_rec.PROJECT_REFERENCE_ENABLED,
PROJECT_CONTROL_LEVEL= c_rec.PROJECT_CONTROL_LEVEL,
CUSTOMER_CLASS_CODE = c_rec.CUSTOMER_CLASS_CODE,
CUSTOMER_TYPE = c_rec.CUSTOMER_TYPE,
LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
CREATION_DATE = MSC_CL_COLLECTION.v_current_date,
CREATED_BY = MSC_CL_COLLECTION.v_current_user
WHERE mtp.Partner_ID= c_rec.TP_ID;
UPDATE MSC_Trading_Partner_Sites mtps
SET mtps.PARTNER_ADDRESS= c_rec.PARTNER_ADDRESS,
mtps.LONGITUDE= NVL( c_rec.LONGITUDE, mtps.LONGITUDE),
mtps.LATITUDE= NVL( c_rec.LATITUDE, mtps.LATITUDE),
mtps.SR_TP_SITE_ID= c_rec.SR_TP_SITE_ID,
mtps.PARTNER_TYPE= c_rec.Partner_Type,
mtps.POSTAL_CODE = c_rec.POSTAL_CODE,
mtps.CITY = c_rec.CITY,
mtps.STATE = c_rec.STATE,
mtps.COUNTRY = c_rec.COUNTRY,
mtps.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
mtps.DELETED_FLAG= MSC_UTIL.SYS_NO,
mtps.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
mtps.SHIPPING_CONTROL=c_rec.SHIPPING_CONTROL,
mtps.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
mtps.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
mtps.CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
mtps.CREATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE mtps.PARTNER_SITE_ID= c_rec.TP_SITE_ID;
SELECT delete_ods_data,org_group, supplier_capacity, atp_rules,
bom, bor, calendar_check, demand_class,ITEM_SUBSTITUTES, forecast, item,
decode(MSC_CL_COLLECTION.v_apps_ver, MSC_UTIL.G_APPS122, MSC_UTIL.SYS_NO, kpi_targets_bis), mds, mps, oh, parameter, planners,
projects, po, reservations, nra, safety_stock,
sales_order, sourcing_history, sourcing, sub_inventories,
customer, supplier, unit_numbers, uom, user_supply_demand, wip, user_comp_association,po_receipts,
bom_sn_flag, bor_sn_flag, item_sn_flag, oh_sn_flag,
usup_sn_flag, udmd_sn_flag, so_sn_flag, fcst_sn_flag,
wip_sn_flag, supcap_sn_flag, po_sn_flag, mds_sn_flag,
mps_sn_flag, nosnap_flag
/* CP-ACK starts */
,supplier_response
/* CP-ACK ends */
/* CP-AUTO */
,suprep_sn_flag, trip,trip_sn_flag,ds_mode
, sales_channel, fiscal_calendar,internal_repair,external_repair -- changed for bug 5909379 SRP addition
,payback_demand_supply
,currency_conversion,delivery_details,CMRO,cmro_forecasts,ibuc_history, notes_attach,
eam_info,eam_forecasts,cmro_closed_wo,ret_fcst,osp_supply -- bug 13839374
INTO MSC_CL_COLLECTION.v_coll_prec.purge_ods_flag,MSC_CL_COLLECTION.v_coll_prec.org_group_flag, MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag,
MSC_CL_COLLECTION.v_coll_prec.atp_rules_flag, MSC_CL_COLLECTION.v_coll_prec.bom_flag,
MSC_CL_COLLECTION.v_coll_prec.bor_flag, MSC_CL_COLLECTION.v_coll_prec.calendar_flag,
MSC_CL_COLLECTION.v_coll_prec.demand_class_flag,MSC_CL_COLLECTION.v_coll_prec.item_subst_flag, MSC_CL_COLLECTION.v_coll_prec.forecast_flag,
MSC_CL_COLLECTION.v_coll_prec.item_flag, MSC_CL_COLLECTION.v_coll_prec.kpi_bis_flag,
MSC_CL_COLLECTION.v_coll_prec.mds_flag, MSC_CL_COLLECTION.v_coll_prec.mps_flag,
MSC_CL_COLLECTION.v_coll_prec.oh_flag, MSC_CL_COLLECTION.v_coll_prec.parameter_flag,
MSC_CL_COLLECTION.v_coll_prec.planner_flag, MSC_CL_COLLECTION.v_coll_prec.project_flag,
MSC_CL_COLLECTION.v_coll_prec.po_flag, MSC_CL_COLLECTION.v_coll_prec.reserves_flag,
MSC_CL_COLLECTION.v_coll_prec.resource_nra_flag, MSC_CL_COLLECTION.v_coll_prec.saf_stock_flag,
MSC_CL_COLLECTION.v_coll_prec.sales_order_flag, MSC_CL_COLLECTION.v_coll_prec.source_hist_flag,
MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag, MSC_CL_COLLECTION.v_coll_prec.sub_inventory_flag,
MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag, MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag,
MSC_CL_COLLECTION.v_coll_prec.unit_number_flag, MSC_CL_COLLECTION.v_coll_prec.uom_flag,
MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag, MSC_CL_COLLECTION.v_coll_prec.wip_flag, MSC_CL_COLLECTION.v_coll_prec.user_company_flag,
MSC_CL_COLLECTION.v_coll_prec.po_receipts_flag,
MSC_CL_COLLECTION.v_coll_prec.bom_sn_flag, MSC_CL_COLLECTION.v_coll_prec.bor_sn_flag,
MSC_CL_COLLECTION.v_coll_prec.item_sn_flag, MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag,
MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag, MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag,
MSC_CL_COLLECTION.v_coll_prec.so_sn_flag, MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag,
MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag,
MSC_CL_COLLECTION.v_coll_prec.supcap_sn_flag, MSC_CL_COLLECTION.v_coll_prec.po_sn_flag,
MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag, MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag,
MSC_CL_COLLECTION.v_coll_prec.nosnap_flag
/* CP-ACK starts */
,MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag
/* CP-ACK ends */
/* CP-AUTO */
,MSC_CL_COLLECTION.v_coll_prec.suprep_sn_flag, MSC_CL_COLLECTION.v_coll_prec.trip_flag,MSC_CL_COLLECTION.v_coll_prec.trip_sn_flag , MSC_CL_COLLECTION.v_coll_prec.ds_mode
,MSC_CL_COLLECTION.v_coll_prec.sales_channel_flag,MSC_CL_COLLECTION.v_coll_prec.fiscal_calendar_flag,MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag,MSC_CL_COLLECTION.v_coll_prec.external_repair_flag
,MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag
,MSC_CL_COLLECTION.v_coll_prec.currency_conversion_flag
,MSC_CL_COLLECTION.v_coll_prec.delivery_details_flag,MSC_CL_COLLECTION.v_coll_prec.CMRO_flag,MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag,
MSC_CL_COLLECTION.v_coll_prec.ibuc_history_flag,MSC_CL_COLLECTION.v_coll_prec.notes_attach_flag,
MSC_CL_COLLECTION.v_coll_prec.eam_info_flag,MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag,MSC_CL_COLLECTION.v_coll_prec.cmro_closed_wo,MSC_CL_COLLECTION.v_coll_prec.ret_fcst_flag,
MSC_CL_COLLECTION.v_coll_prec.osp_supply
FROM msc_coll_parameters
WHERE instance_id = p_instance_id;
SELECT delete_ods_data,org_group, supplier_capacity, atp_rules,
bom, bor, calendar_check, demand_class,ITEM_SUBSTITUTES, forecast, item,
decode(MSC_CL_COLLECTION.v_apps_ver, MSC_UTIL.G_APPS122, MSC_UTIL.SYS_NO, kpi_targets_bis), mds, mps, oh, parameter, planners,
projects, po, reservations, nra, safety_stock,
sales_order, sourcing_history, sourcing, sub_inventories,
customer, supplier, unit_numbers, uom, user_supply_demand, wip, user_comp_association,
po_receipts,
bom_sn_flag, bor_sn_flag, item_sn_flag, oh_sn_flag,
usup_sn_flag, udmd_sn_flag, so_sn_flag, fcst_sn_flag,
wip_sn_flag, supcap_sn_flag, po_sn_flag, mds_sn_flag,
mps_sn_flag, nosnap_flag
/* CP-ACK starts */
,supplier_response
/* CP-ACK ends */
/* CP-AUTO */
,suprep_sn_flag, trip,trip_sn_flag, ds_mode
,sales_channel,fiscal_calendar,internal_repair,external_repair
,payback_demand_supply
,currency_conversion,delivery_details,CMRO,cmro_forecasts,ibuc_history,notes_attach,
eam_info,eam_forecasts,cmro_closed_wo,ret_fcst,osp_supply
INTO v_prec.purge_ods_flag,v_prec.org_group_flag, v_prec.app_supp_cap_flag,
v_prec.atp_rules_flag, v_prec.bom_flag,
v_prec.bor_flag, v_prec.calendar_flag,
v_prec.demand_class_flag, v_prec.item_subst_flag,v_prec.forecast_flag,
v_prec.item_flag, v_prec.kpi_bis_flag,
v_prec.mds_flag, v_prec.mps_flag,
v_prec.oh_flag, v_prec.parameter_flag,
v_prec.planner_flag, v_prec.project_flag,
v_prec.po_flag, v_prec.reserves_flag,
v_prec.resource_nra_flag, v_prec.saf_stock_flag,
v_prec.sales_order_flag, v_prec.source_hist_flag,
v_prec.sourcing_rule_flag, v_prec.sub_inventory_flag,
v_prec.tp_customer_flag, v_prec.tp_vendor_flag,
v_prec.unit_number_flag, v_prec.uom_flag,
v_prec.user_supply_demand_flag, v_prec.wip_flag, v_prec.user_company_flag,
v_prec.po_receipts_flag,
v_prec.bom_sn_flag, v_prec.bor_sn_flag,
v_prec.item_sn_flag, v_prec.oh_sn_flag,
v_prec.usup_sn_flag, v_prec.udmd_sn_flag,
v_prec.so_sn_flag, v_prec.fcst_sn_flag,
v_prec.wip_sn_flag,
v_prec.supcap_sn_flag, v_prec.po_sn_flag,
v_prec.mds_sn_flag, v_prec.mps_sn_flag,
v_prec.nosnap_flag
/* CP-ACK starts */
,v_prec.supplier_response_flag
/* CP-ACK ends */
/* CP-AUTO */
,v_prec.suprep_sn_flag,v_prec.trip_flag,v_prec.trip_sn_flag,v_prec.ds_mode
,v_prec.sales_channel_flag,v_prec.fiscal_calendar_flag,v_prec.internal_repair_flag,v_prec.external_repair_flag
,v_prec.payback_demand_supply_flag
,v_prec.currency_conversion_flag
,v_prec.delivery_details_flag,v_prec.cmro_flag,v_prec.cmro_forecasts_flag,v_prec.ibuc_history_flag,v_prec.notes_attach_flag,
v_prec.eam_info_flag,v_prec.eam_forecasts_flag,v_prec.cmro_closed_wo,v_prec.ret_fcst_flag -- bug 13839374 ret_fcst_flag added
,v_prec.osp_supply
FROM msc_coll_parameters
WHERE instance_id = p_instance_id;