[Home] [Help]
1: PACKAGE BODY MSC_CL_SETUP_ODS_LOAD AS -- specification
2: /* $Header: MSCLSTPB.pls 120.25.12020000.3 2012/09/04 12:08:36 neelredd ship $ */
3: -- SYS_YES Number:= MSC_UTIL.SYS_YES ;
4: -- SYS_NO Number:= MSC_CL_COLLECTION.SYS_NO ;
5: -- G_SUCCESS NUMBER := MSC_CL_COLLECTION.G_SUCCESS;
6: -- G_WARNING NUMBER := MSC_CL_COLLECTION.G_WARNING;
7: -- G_ERROR NUMBER := MSC_CL_COLLECTION.G_ERROR;
8: -- G_COLLECTION_PROGRAM NUMBER := MSC_CL_COLLECTION.G_COLLECTION_PROGRAM;
1: PACKAGE BODY MSC_CL_SETUP_ODS_LOAD AS -- specification
2: /* $Header: MSCLSTPB.pls 120.25.12020000.3 2012/09/04 12:08:36 neelredd ship $ */
3: -- SYS_YES Number:= MSC_UTIL.SYS_YES ;
4: -- SYS_NO Number:= MSC_CL_COLLECTION.SYS_NO ;
5: -- G_SUCCESS NUMBER := MSC_CL_COLLECTION.G_SUCCESS;
6: -- G_WARNING NUMBER := MSC_CL_COLLECTION.G_WARNING;
7: -- G_ERROR NUMBER := MSC_CL_COLLECTION.G_ERROR;
8: -- G_COLLECTION_PROGRAM NUMBER := MSC_CL_COLLECTION.G_COLLECTION_PROGRAM;
9: -- SYS_TGT NUMBER:=MSC_CL_COLLECTION.SYS_TGT;
2: /* $Header: MSCLSTPB.pls 120.25.12020000.3 2012/09/04 12:08:36 neelredd ship $ */
3: -- SYS_YES Number:= MSC_UTIL.SYS_YES ;
4: -- SYS_NO Number:= MSC_CL_COLLECTION.SYS_NO ;
5: -- G_SUCCESS NUMBER := MSC_CL_COLLECTION.G_SUCCESS;
6: -- G_WARNING NUMBER := MSC_CL_COLLECTION.G_WARNING;
7: -- G_ERROR NUMBER := MSC_CL_COLLECTION.G_ERROR;
8: -- G_COLLECTION_PROGRAM NUMBER := MSC_CL_COLLECTION.G_COLLECTION_PROGRAM;
9: -- SYS_TGT NUMBER:=MSC_CL_COLLECTION.SYS_TGT;
10: -- G_INS_OTHER NUMBER:= MSC_CL_COLLECTION.G_INS_OTHER;
3: -- SYS_YES Number:= MSC_UTIL.SYS_YES ;
4: -- SYS_NO Number:= MSC_CL_COLLECTION.SYS_NO ;
5: -- G_SUCCESS NUMBER := MSC_CL_COLLECTION.G_SUCCESS;
6: -- G_WARNING NUMBER := MSC_CL_COLLECTION.G_WARNING;
7: -- G_ERROR NUMBER := MSC_CL_COLLECTION.G_ERROR;
8: -- G_COLLECTION_PROGRAM NUMBER := MSC_CL_COLLECTION.G_COLLECTION_PROGRAM;
9: -- SYS_TGT NUMBER:=MSC_CL_COLLECTION.SYS_TGT;
10: -- G_INS_OTHER NUMBER:= MSC_CL_COLLECTION.G_INS_OTHER;
11: -- NULL_VALUE NUMBER:=MSC_UTIL.NULL_VALUE;
4: -- SYS_NO Number:= MSC_CL_COLLECTION.SYS_NO ;
5: -- G_SUCCESS NUMBER := MSC_CL_COLLECTION.G_SUCCESS;
6: -- G_WARNING NUMBER := MSC_CL_COLLECTION.G_WARNING;
7: -- G_ERROR NUMBER := MSC_CL_COLLECTION.G_ERROR;
8: -- G_COLLECTION_PROGRAM NUMBER := MSC_CL_COLLECTION.G_COLLECTION_PROGRAM;
9: -- SYS_TGT NUMBER:=MSC_CL_COLLECTION.SYS_TGT;
10: -- G_INS_OTHER NUMBER:= MSC_CL_COLLECTION.G_INS_OTHER;
11: -- NULL_VALUE NUMBER:=MSC_UTIL.NULL_VALUE;
12: -- NULL_DATE DATE:=MSC_CL_COLLECTION.NULL_DATE;
5: -- G_SUCCESS NUMBER := MSC_CL_COLLECTION.G_SUCCESS;
6: -- G_WARNING NUMBER := MSC_CL_COLLECTION.G_WARNING;
7: -- G_ERROR NUMBER := MSC_CL_COLLECTION.G_ERROR;
8: -- G_COLLECTION_PROGRAM NUMBER := MSC_CL_COLLECTION.G_COLLECTION_PROGRAM;
9: -- SYS_TGT NUMBER:=MSC_CL_COLLECTION.SYS_TGT;
10: -- G_INS_OTHER NUMBER:= MSC_CL_COLLECTION.G_INS_OTHER;
11: -- NULL_VALUE NUMBER:=MSC_UTIL.NULL_VALUE;
12: -- NULL_DATE DATE:=MSC_CL_COLLECTION.NULL_DATE;
13: -- NULL_DBLINK VARCHAR2(1):=MSC_UTIL.NULL_DBLINK;
6: -- G_WARNING NUMBER := MSC_CL_COLLECTION.G_WARNING;
7: -- G_ERROR NUMBER := MSC_CL_COLLECTION.G_ERROR;
8: -- G_COLLECTION_PROGRAM NUMBER := MSC_CL_COLLECTION.G_COLLECTION_PROGRAM;
9: -- SYS_TGT NUMBER:=MSC_CL_COLLECTION.SYS_TGT;
10: -- G_INS_OTHER NUMBER:= MSC_CL_COLLECTION.G_INS_OTHER;
11: -- NULL_VALUE NUMBER:=MSC_UTIL.NULL_VALUE;
12: -- NULL_DATE DATE:=MSC_CL_COLLECTION.NULL_DATE;
13: -- NULL_DBLINK VARCHAR2(1):=MSC_UTIL.NULL_DBLINK;
14: -- G_MY_COMPANY_ID NUMBER := MSC_CL_COLLECTION.G_MY_COMPANY_ID;
8: -- G_COLLECTION_PROGRAM NUMBER := MSC_CL_COLLECTION.G_COLLECTION_PROGRAM;
9: -- SYS_TGT NUMBER:=MSC_CL_COLLECTION.SYS_TGT;
10: -- G_INS_OTHER NUMBER:= MSC_CL_COLLECTION.G_INS_OTHER;
11: -- NULL_VALUE NUMBER:=MSC_UTIL.NULL_VALUE;
12: -- NULL_DATE DATE:=MSC_CL_COLLECTION.NULL_DATE;
13: -- NULL_DBLINK VARCHAR2(1):=MSC_UTIL.NULL_DBLINK;
14: -- G_MY_COMPANY_ID NUMBER := MSC_CL_COLLECTION.G_MY_COMPANY_ID;
15: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
16: -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
10: -- G_INS_OTHER NUMBER:= MSC_CL_COLLECTION.G_INS_OTHER;
11: -- NULL_VALUE NUMBER:=MSC_UTIL.NULL_VALUE;
12: -- NULL_DATE DATE:=MSC_CL_COLLECTION.NULL_DATE;
13: -- NULL_DBLINK VARCHAR2(1):=MSC_UTIL.NULL_DBLINK;
14: -- G_MY_COMPANY_ID NUMBER := MSC_CL_COLLECTION.G_MY_COMPANY_ID;
15: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
16: -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
17: -- G_APPS107 NUMBER :=MSC_CL_COLLECTION.G_APPS107;
18: -- G_APPS120 NUMBER :=MSC_CL_COLLECTION.G_APPS120;
11: -- NULL_VALUE NUMBER:=MSC_UTIL.NULL_VALUE;
12: -- NULL_DATE DATE:=MSC_CL_COLLECTION.NULL_DATE;
13: -- NULL_DBLINK VARCHAR2(1):=MSC_UTIL.NULL_DBLINK;
14: -- G_MY_COMPANY_ID NUMBER := MSC_CL_COLLECTION.G_MY_COMPANY_ID;
15: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
16: -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
17: -- G_APPS107 NUMBER :=MSC_CL_COLLECTION.G_APPS107;
18: -- G_APPS120 NUMBER :=MSC_CL_COLLECTION.G_APPS120;
19: -- G_ALL_ORGANIZATIONS VARCHAR2(6):= MSC_CL_COLLECTION.G_ALL_ORGANIZATIONS;
12: -- NULL_DATE DATE:=MSC_CL_COLLECTION.NULL_DATE;
13: -- NULL_DBLINK VARCHAR2(1):=MSC_UTIL.NULL_DBLINK;
14: -- G_MY_COMPANY_ID NUMBER := MSC_CL_COLLECTION.G_MY_COMPANY_ID;
15: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
16: -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
17: -- G_APPS107 NUMBER :=MSC_CL_COLLECTION.G_APPS107;
18: -- G_APPS120 NUMBER :=MSC_CL_COLLECTION.G_APPS120;
19: -- G_ALL_ORGANIZATIONS VARCHAR2(6):= MSC_CL_COLLECTION.G_ALL_ORGANIZATIONS;
20:
13: -- NULL_DBLINK VARCHAR2(1):=MSC_UTIL.NULL_DBLINK;
14: -- G_MY_COMPANY_ID NUMBER := MSC_CL_COLLECTION.G_MY_COMPANY_ID;
15: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
16: -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
17: -- G_APPS107 NUMBER :=MSC_CL_COLLECTION.G_APPS107;
18: -- G_APPS120 NUMBER :=MSC_CL_COLLECTION.G_APPS120;
19: -- G_ALL_ORGANIZATIONS VARCHAR2(6):= MSC_CL_COLLECTION.G_ALL_ORGANIZATIONS;
20:
21: FUNCTION LINK_SUPPLY_TOP_LINK_ID
14: -- G_MY_COMPANY_ID NUMBER := MSC_CL_COLLECTION.G_MY_COMPANY_ID;
15: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
16: -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
17: -- G_APPS107 NUMBER :=MSC_CL_COLLECTION.G_APPS107;
18: -- G_APPS120 NUMBER :=MSC_CL_COLLECTION.G_APPS120;
19: -- G_ALL_ORGANIZATIONS VARCHAR2(6):= MSC_CL_COLLECTION.G_ALL_ORGANIZATIONS;
20:
21: FUNCTION LINK_SUPPLY_TOP_LINK_ID
22: RETURN BOOLEAN
15: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
16: -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
17: -- G_APPS107 NUMBER :=MSC_CL_COLLECTION.G_APPS107;
18: -- G_APPS120 NUMBER :=MSC_CL_COLLECTION.G_APPS120;
19: -- G_ALL_ORGANIZATIONS VARCHAR2(6):= MSC_CL_COLLECTION.G_ALL_ORGANIZATIONS;
20:
21: FUNCTION LINK_SUPPLY_TOP_LINK_ID
22: RETURN BOOLEAN
23: IS
26: begin
27:
28: lv_task_start_time := SYSDATE;
29:
30: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
31: lv_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
32:
33: ELSE
34: lv_tbl:= 'MSC_SUPPLIES';
27:
28: lv_task_start_time := SYSDATE;
29:
30: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
31: lv_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
32:
33: ELSE
34: lv_tbl:= 'MSC_SUPPLIES';
35: END IF;
45: || ' and nwk.top_transaction_id is not null '
46: || ' and nwk.plan_id = -1 '
47: || ' and rownum = 1 )'
48: || ' WHERE s.plan_id = -1 '
49: || ' and s.sr_instance_id = '|| MSC_CL_COLLECTION.v_instance_id
50: || ' and s.order_type = 70'; /* eam supply order type */
51:
52: COMMIT;
53:
82:
83: SELECT NVL( CLEANSED_FLAG, MSC_UTIL.SYS_NO)
84: INTO CLEANSED_FLAG
85: FROM MSC_APPS_INSTANCES mai
86: WHERE mai.INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
87:
88: IF CLEANSED_FLAG= MSC_UTIL.SYS_NO THEN
89:
90: MSC_CL_CLEANSE.CLEANSE( ERRBUF,
88: IF CLEANSED_FLAG= MSC_UTIL.SYS_NO THEN
89:
90: MSC_CL_CLEANSE.CLEANSE( ERRBUF,
91: RETCODE,
92: MSC_CL_COLLECTION.v_instance_id);
93:
94: IF RETCODE= MSC_UTIL.G_SUCCESS OR RETCODE= MSC_UTIL.G_WARNING THEN
95:
96: UPDATE MSC_APPS_INSTANCES mai
94: IF RETCODE= MSC_UTIL.G_SUCCESS OR RETCODE= MSC_UTIL.G_WARNING THEN
95:
96: UPDATE MSC_APPS_INSTANCES mai
97: SET mai.CLEANSED_FLAG= MSC_UTIL.SYS_YES,
98: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
99: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
100: REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
101: WHERE mai.Instance_ID= MSC_CL_COLLECTION.v_instance_id;
102:
95:
96: UPDATE MSC_APPS_INSTANCES mai
97: SET mai.CLEANSED_FLAG= MSC_UTIL.SYS_YES,
98: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
99: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
100: REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
101: WHERE mai.Instance_ID= MSC_CL_COLLECTION.v_instance_id;
102:
103: COMMIT;
97: SET mai.CLEANSED_FLAG= MSC_UTIL.SYS_YES,
98: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
99: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
100: REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
101: WHERE mai.Instance_ID= MSC_CL_COLLECTION.v_instance_id;
102:
103: COMMIT;
104:
105: ELSE
105: ELSE
106:
107: ROLLBACK;
108:
109: MSC_CL_COLLECTION.log_message( MSC_CL_COLLECTION.G_COLLECTION_PROGRAM,
110: MSC_CL_COLLECTION.v_last_collection_id,
111: MSC_CL_COLLECTION.v_current_date,
112: MSC_UTIL.G_ERROR,
113: 'MSC_CL_COLLECTION.CLEANSE_DATA',
106:
107: ROLLBACK;
108:
109: MSC_CL_COLLECTION.log_message( MSC_CL_COLLECTION.G_COLLECTION_PROGRAM,
110: MSC_CL_COLLECTION.v_last_collection_id,
111: MSC_CL_COLLECTION.v_current_date,
112: MSC_UTIL.G_ERROR,
113: 'MSC_CL_COLLECTION.CLEANSE_DATA',
114: 'UNKNOWN',
107: ROLLBACK;
108:
109: MSC_CL_COLLECTION.log_message( MSC_CL_COLLECTION.G_COLLECTION_PROGRAM,
110: MSC_CL_COLLECTION.v_last_collection_id,
111: MSC_CL_COLLECTION.v_current_date,
112: MSC_UTIL.G_ERROR,
113: 'MSC_CL_COLLECTION.CLEANSE_DATA',
114: 'UNKNOWN',
115: TO_CHAR( MSC_CL_COLLECTION.v_instance_id),
109: MSC_CL_COLLECTION.log_message( MSC_CL_COLLECTION.G_COLLECTION_PROGRAM,
110: MSC_CL_COLLECTION.v_last_collection_id,
111: MSC_CL_COLLECTION.v_current_date,
112: MSC_UTIL.G_ERROR,
113: 'MSC_CL_COLLECTION.CLEANSE_DATA',
114: 'UNKNOWN',
115: TO_CHAR( MSC_CL_COLLECTION.v_instance_id),
116: ERRBUF);
117:
111: MSC_CL_COLLECTION.v_current_date,
112: MSC_UTIL.G_ERROR,
113: 'MSC_CL_COLLECTION.CLEANSE_DATA',
114: 'UNKNOWN',
115: TO_CHAR( MSC_CL_COLLECTION.v_instance_id),
116: ERRBUF);
117:
118: RETURN FALSE;
119:
151: FROM dual;
152:
153: /* for bug: 2605884, added this piece of code to set the so_tbl_status to NO */
154:
155: IF (MSC_CL_COLLECTION.v_inv_ctp_val = 4) THEN -- PDS ATP , set SO_TBL_STATUS=2 , no matter type of collections
156: UPDATE MSC_APPS_INSTANCES
157: SET so_tbl_status= MSC_UTIL.SYS_NO
158: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
159: commit;
154:
155: IF (MSC_CL_COLLECTION.v_inv_ctp_val = 4) THEN -- PDS ATP , set SO_TBL_STATUS=2 , no matter type of collections
156: UPDATE MSC_APPS_INSTANCES
157: SET so_tbl_status= MSC_UTIL.SYS_NO
158: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
159: commit;
160: ELSIF (MSC_CL_COLLECTION.v_inv_ctp_val = 5) THEN -- ODS ATP
161: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
162: IF (NOT MSC_CL_COLLECTION.v_is_so_complete_refresh) THEN -- In complete refresh, if the SO flag is no
156: UPDATE MSC_APPS_INSTANCES
157: SET so_tbl_status= MSC_UTIL.SYS_NO
158: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
159: commit;
160: ELSIF (MSC_CL_COLLECTION.v_inv_ctp_val = 5) THEN -- ODS ATP
161: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
162: IF (NOT MSC_CL_COLLECTION.v_is_so_complete_refresh) THEN -- In complete refresh, if the SO flag is no
163: UPDATE MSC_APPS_INSTANCES
164: SET so_tbl_status= MSC_UTIL.SYS_NO
157: SET so_tbl_status= MSC_UTIL.SYS_NO
158: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
159: commit;
160: ELSIF (MSC_CL_COLLECTION.v_inv_ctp_val = 5) THEN -- ODS ATP
161: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
162: IF (NOT MSC_CL_COLLECTION.v_is_so_complete_refresh) THEN -- In complete refresh, if the SO flag is no
163: UPDATE MSC_APPS_INSTANCES
164: SET so_tbl_status= MSC_UTIL.SYS_NO
165: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
158: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
159: commit;
160: ELSIF (MSC_CL_COLLECTION.v_inv_ctp_val = 5) THEN -- ODS ATP
161: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
162: IF (NOT MSC_CL_COLLECTION.v_is_so_complete_refresh) THEN -- In complete refresh, if the SO flag is no
163: UPDATE MSC_APPS_INSTANCES
164: SET so_tbl_status= MSC_UTIL.SYS_NO
165: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
166: commit;
161: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
162: IF (NOT MSC_CL_COLLECTION.v_is_so_complete_refresh) THEN -- In complete refresh, if the SO flag is no
163: UPDATE MSC_APPS_INSTANCES
164: SET so_tbl_status= MSC_UTIL.SYS_NO
165: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
166: commit;
167: END IF;
168: ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
169: IF (MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES) THEN -- in partial refresh, if SO is not collected
164: SET so_tbl_status= MSC_UTIL.SYS_NO
165: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
166: commit;
167: END IF;
168: ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
169: IF (MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES) THEN -- in partial refresh, if SO is not collected
170: UPDATE MSC_APPS_INSTANCES
171: SET so_tbl_status= MSC_UTIL.SYS_NO
172: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
165: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
166: commit;
167: END IF;
168: ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
169: IF (MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES) THEN -- in partial refresh, if SO is not collected
170: UPDATE MSC_APPS_INSTANCES
171: SET so_tbl_status= MSC_UTIL.SYS_NO
172: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
173: commit;
168: ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
169: IF (MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES) THEN -- in partial refresh, if SO is not collected
170: UPDATE MSC_APPS_INSTANCES
171: SET so_tbl_status= MSC_UTIL.SYS_NO
172: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
173: commit;
174: END IF;
175: ELSIF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
176: UPDATE MSC_APPS_INSTANCES
171: SET so_tbl_status= MSC_UTIL.SYS_NO
172: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
173: commit;
174: END IF;
175: ELSIF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
176: UPDATE MSC_APPS_INSTANCES
177: SET so_tbl_status= MSC_UTIL.SYS_NO
178: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
179: commit;
174: END IF;
175: ELSIF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
176: UPDATE MSC_APPS_INSTANCES
177: SET so_tbl_status= MSC_UTIL.SYS_NO
178: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
179: commit;
180: ELSIF MSC_CL_COLLECTION.v_is_cont_refresh THEN
181: IF ( MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES ) THEN
182: UPDATE MSC_APPS_INSTANCES
176: UPDATE MSC_APPS_INSTANCES
177: SET so_tbl_status= MSC_UTIL.SYS_NO
178: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
179: commit;
180: ELSIF MSC_CL_COLLECTION.v_is_cont_refresh THEN
181: IF ( MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES ) THEN
182: UPDATE MSC_APPS_INSTANCES
183: SET so_tbl_status= MSC_UTIL.SYS_NO
184: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
177: SET so_tbl_status= MSC_UTIL.SYS_NO
178: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
179: commit;
180: ELSIF MSC_CL_COLLECTION.v_is_cont_refresh THEN
181: IF ( MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES ) THEN
182: UPDATE MSC_APPS_INSTANCES
183: SET so_tbl_status= MSC_UTIL.SYS_NO
184: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
185: commit;
180: ELSIF MSC_CL_COLLECTION.v_is_cont_refresh THEN
181: IF ( MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES ) THEN
182: UPDATE MSC_APPS_INSTANCES
183: SET so_tbl_status= MSC_UTIL.SYS_NO
184: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
185: commit;
186: ELSE
187: IF ( MSC_CL_COLLECTION.v_coll_prec.so_sn_flag <> MSC_UTIL.SYS_TGT ) THEN
188: UPDATE MSC_APPS_INSTANCES
183: SET so_tbl_status= MSC_UTIL.SYS_NO
184: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
185: commit;
186: ELSE
187: IF ( MSC_CL_COLLECTION.v_coll_prec.so_sn_flag <> MSC_UTIL.SYS_TGT ) THEN
188: UPDATE MSC_APPS_INSTANCES
189: SET so_tbl_status= MSC_UTIL.SYS_NO
190: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
191: commit;
186: ELSE
187: IF ( MSC_CL_COLLECTION.v_coll_prec.so_sn_flag <> MSC_UTIL.SYS_TGT ) THEN
188: UPDATE MSC_APPS_INSTANCES
189: SET so_tbl_status= MSC_UTIL.SYS_NO
190: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
191: commit;
192: END IF;
193: END IF;
194: END IF;
201: 'MSCITTK',
202: NULL, -- description
203: NULL, -- start date
204: FALSE, -- TRUE,
205: MSC_CL_COLLECTION.v_instance_id);
206: COMMIT;
207:
208: IF lvs_request_id(1) = 0 THEN
209: FND_MESSAGE.SET_NAME('MSC', 'MSC_PROGRAM_LAUNCH_FAIL');
206: COMMIT;
207:
208: IF lvs_request_id(1) = 0 THEN
209: FND_MESSAGE.SET_NAME('MSC', 'MSC_PROGRAM_LAUNCH_FAIL');
210: FND_MESSAGE.SET_TOKEN('PROGRAM_NAME', 'MSC_CL_COLLECTION.GENERATE_ITEM_KEYS');
211: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
212: RETURN FALSE;
213: ELSE
214: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Submitted request for Items and Category Sets Key Generation');
220: 'MSCTPTK',
221: NULL, -- description
222: NULL, -- start date
223: FALSE, -- TRUE,
224: MSC_CL_COLLECTION.v_instance_id);
225:
226: COMMIT;
227:
228: IF lvs_request_id(2) = 0 THEN
226: COMMIT;
227:
228: IF lvs_request_id(2) = 0 THEN
229: FND_MESSAGE.SET_NAME('MSC', 'MSC_PROGRAM_LAUNCH_FAIL');
230: FND_MESSAGE.SET_TOKEN('PROGRAM_NAME', 'MSC_CL_COLLECTION.GENERATE_TRADING_PARTNER_KEYS');
231: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
232: RETURN FALSE;
233: ELSE
234: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Submitted request for Trading Parteners Key Generation');
250:
251:
252: /* for bug: 2605884, added this piece of code to set the so_tbl_status to YES after the Key transformations */
253:
254: IF (MSC_CL_COLLECTION.v_inv_ctp_val = 4) THEN -- PDS ATP , set SO_TBL_STATUS=1
255: UPDATE MSC_APPS_INSTANCES
256: SET so_tbl_status= MSC_UTIL.SYS_YES
257: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
258: commit;
253:
254: IF (MSC_CL_COLLECTION.v_inv_ctp_val = 4) THEN -- PDS ATP , set SO_TBL_STATUS=1
255: UPDATE MSC_APPS_INSTANCES
256: SET so_tbl_status= MSC_UTIL.SYS_YES
257: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
258: commit;
259: ELSIF (MSC_CL_COLLECTION.v_inv_ctp_val = 5) THEN -- ODS ATP
260: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
261: IF (NOT MSC_CL_COLLECTION.v_is_so_complete_refresh) THEN -- In complete refresh, if the SO flag is no
255: UPDATE MSC_APPS_INSTANCES
256: SET so_tbl_status= MSC_UTIL.SYS_YES
257: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
258: commit;
259: ELSIF (MSC_CL_COLLECTION.v_inv_ctp_val = 5) THEN -- ODS ATP
260: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
261: IF (NOT MSC_CL_COLLECTION.v_is_so_complete_refresh) THEN -- In complete refresh, if the SO flag is no
262: UPDATE MSC_APPS_INSTANCES
263: SET so_tbl_status= MSC_UTIL.SYS_YES
256: SET so_tbl_status= MSC_UTIL.SYS_YES
257: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
258: commit;
259: ELSIF (MSC_CL_COLLECTION.v_inv_ctp_val = 5) THEN -- ODS ATP
260: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
261: IF (NOT MSC_CL_COLLECTION.v_is_so_complete_refresh) THEN -- In complete refresh, if the SO flag is no
262: UPDATE MSC_APPS_INSTANCES
263: SET so_tbl_status= MSC_UTIL.SYS_YES
264: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
257: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
258: commit;
259: ELSIF (MSC_CL_COLLECTION.v_inv_ctp_val = 5) THEN -- ODS ATP
260: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
261: IF (NOT MSC_CL_COLLECTION.v_is_so_complete_refresh) THEN -- In complete refresh, if the SO flag is no
262: UPDATE MSC_APPS_INSTANCES
263: SET so_tbl_status= MSC_UTIL.SYS_YES
264: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
265: commit;
260: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
261: IF (NOT MSC_CL_COLLECTION.v_is_so_complete_refresh) THEN -- In complete refresh, if the SO flag is no
262: UPDATE MSC_APPS_INSTANCES
263: SET so_tbl_status= MSC_UTIL.SYS_YES
264: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
265: commit;
266: END IF;
267: ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
268: IF (MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES) THEN -- in partial refresh, if SO is not collected
263: SET so_tbl_status= MSC_UTIL.SYS_YES
264: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
265: commit;
266: END IF;
267: ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
268: IF (MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES) THEN -- in partial refresh, if SO is not collected
269: UPDATE MSC_APPS_INSTANCES
270: SET so_tbl_status= MSC_UTIL.SYS_YES
271: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
264: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
265: commit;
266: END IF;
267: ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
268: IF (MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES) THEN -- in partial refresh, if SO is not collected
269: UPDATE MSC_APPS_INSTANCES
270: SET so_tbl_status= MSC_UTIL.SYS_YES
271: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
272: commit;
267: ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
268: IF (MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES) THEN -- in partial refresh, if SO is not collected
269: UPDATE MSC_APPS_INSTANCES
270: SET so_tbl_status= MSC_UTIL.SYS_YES
271: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
272: commit;
273: END IF;
274: ELSIF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
275: UPDATE MSC_APPS_INSTANCES
270: SET so_tbl_status= MSC_UTIL.SYS_YES
271: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
272: commit;
273: END IF;
274: ELSIF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
275: UPDATE MSC_APPS_INSTANCES
276: SET so_tbl_status= MSC_UTIL.SYS_YES
277: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
278: commit;
273: END IF;
274: ELSIF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
275: UPDATE MSC_APPS_INSTANCES
276: SET so_tbl_status= MSC_UTIL.SYS_YES
277: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
278: commit;
279: ELSIF MSC_CL_COLLECTION.v_is_cont_refresh THEN
280: IF ( MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES ) THEN
281: UPDATE MSC_APPS_INSTANCES
275: UPDATE MSC_APPS_INSTANCES
276: SET so_tbl_status= MSC_UTIL.SYS_YES
277: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
278: commit;
279: ELSIF MSC_CL_COLLECTION.v_is_cont_refresh THEN
280: IF ( MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES ) THEN
281: UPDATE MSC_APPS_INSTANCES
282: SET so_tbl_status= MSC_UTIL.SYS_YES
283: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
276: SET so_tbl_status= MSC_UTIL.SYS_YES
277: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
278: commit;
279: ELSIF MSC_CL_COLLECTION.v_is_cont_refresh THEN
280: IF ( MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES ) THEN
281: UPDATE MSC_APPS_INSTANCES
282: SET so_tbl_status= MSC_UTIL.SYS_YES
283: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
284: commit;
279: ELSIF MSC_CL_COLLECTION.v_is_cont_refresh THEN
280: IF ( MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES ) THEN
281: UPDATE MSC_APPS_INSTANCES
282: SET so_tbl_status= MSC_UTIL.SYS_YES
283: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
284: commit;
285: ELSE
286: IF ( MSC_CL_COLLECTION.v_coll_prec.so_sn_flag <> MSC_UTIL.SYS_TGT ) THEN
287: UPDATE MSC_APPS_INSTANCES
282: SET so_tbl_status= MSC_UTIL.SYS_YES
283: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
284: commit;
285: ELSE
286: IF ( MSC_CL_COLLECTION.v_coll_prec.so_sn_flag <> MSC_UTIL.SYS_TGT ) THEN
287: UPDATE MSC_APPS_INSTANCES
288: SET so_tbl_status= MSC_UTIL.SYS_YES
289: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
290: commit;
285: ELSE
286: IF ( MSC_CL_COLLECTION.v_coll_prec.so_sn_flag <> MSC_UTIL.SYS_TGT ) THEN
287: UPDATE MSC_APPS_INSTANCES
288: SET so_tbl_status= MSC_UTIL.SYS_YES
289: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
290: commit;
291: END IF;
292: END IF;
293: END IF;
341: AND til.PARTNER_TYPE(+)= msca.PARTNER_TYPE
342: AND tsil.SR_INSTANCE_ID(+)= msca.SR_INSTANCE_ID
343: AND tsil.SR_TP_SITE_ID(+)= msca.PARTNER_SITE_ID
344: AND tsil.PARTNER_TYPE(+)= msca.PARTNER_TYPE
345: AND msca.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
346: AND mtil.SR_INSTANCE_ID(+)= msca.SR_INSTANCE_ID
347: AND mtil.SR_TP_ID(+)= msca.CARRIER_PARTNER_ID
348: AND mtil.PARTNER_TYPE(+)=4;
349:
356: mscd.CALENDAR_END_DATE,
357: mscd.DESCRIPTION,
358: mscd.SR_INSTANCE_ID
359: FROM MSC_ST_CALENDAR_DATES mscd
360: WHERE mscd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
361:
362:
363:
364: --Calculate the first and last working days for each of the calendars in MSC_CALENDARS
368: Max(CALENDAR_DATE) LAST_WORKING_DATE,
369: CALENDAR_CODE,
370: SR_INSTANCE_ID
371: FROM MSC_CALENDAR_DATES mscd
372: WHERE mscd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
373: and seq_num is not null
374: GROUP BY CALENDAR_CODE, SR_INSTANCE_ID;
375:
376:
389: mscd.DESCRIPTION,
390: mscd.EXCEPTION_SET_ID,
391: mscd.SR_INSTANCE_ID
392: FROM MSC_ST_CALENDAR_DATES mscd
393: WHERE mscd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
394:
395: CURSOR c2 IS
396: SELECT
397: mspsd.CALENDAR_CODE,
402: mspsd.NEXT_DATE,
403: mspsd.PRIOR_DATE,
404: mspsd.SR_INSTANCE_ID
405: FROM MSC_ST_PERIOD_START_DATES mspsd
406: WHERE mspsd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
407:
408: CURSOR c3 IS
409: SELECT
410: mscysd.CALENDAR_CODE,
411: mscysd.EXCEPTION_SET_ID,
412: mscysd.YEAR_START_DATE,
413: mscysd.SR_INSTANCE_ID
414: FROM MSC_ST_CAL_YEAR_START_DATES mscysd
415: WHERE mscysd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
416:
417: CURSOR c4 IS
418: SELECT
419: mscwsd.CALENDAR_CODE,
423: mscwsd.PRIOR_DATE,
424: mscwsd.SEQ_NUM,
425: mscwsd.SR_INSTANCE_ID
426: FROM MSC_ST_CAL_WEEK_START_DATES mscwsd
427: WHERE mscwsd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
428:
429: c_count NUMBER:= 0;
430:
431: lv_sql_stmt VARCHAR2(5000);
453:
454: BEGIN
455:
456:
457: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
458:
459: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PERIOD_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
460:
461: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CAL_YEAR_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
455:
456:
457: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
458:
459: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PERIOD_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
460:
461: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CAL_YEAR_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
462:
463: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CAL_WEEK_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
457: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
458:
459: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PERIOD_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
460:
461: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CAL_YEAR_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
462:
463: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CAL_WEEK_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
464:
465: END IF;
459: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PERIOD_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
460:
461: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CAL_YEAR_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
462:
463: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CAL_WEEK_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
464:
465: END IF;
466:
467: -- Calendar Dates --
465: END IF;
466:
467: -- Calendar Dates --
468:
469: if (MSC_CL_COLLECTION.v_is_partial_refresh or MSC_CL_COLLECTION.v_is_complete_refresh ) THEN
470: UPDATE MSC_CALENDAR_DATES
471: SET DELETED_FLAG= MSC_UTIL.SYS_YES,
472: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
473: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
468:
469: if (MSC_CL_COLLECTION.v_is_partial_refresh or MSC_CL_COLLECTION.v_is_complete_refresh ) THEN
470: UPDATE MSC_CALENDAR_DATES
471: SET DELETED_FLAG= MSC_UTIL.SYS_YES,
472: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
473: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
474: WHERE
475: SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
476: AND exception_set_id = -1;
469: if (MSC_CL_COLLECTION.v_is_partial_refresh or MSC_CL_COLLECTION.v_is_complete_refresh ) THEN
470: UPDATE MSC_CALENDAR_DATES
471: SET DELETED_FLAG= MSC_UTIL.SYS_YES,
472: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
473: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
474: WHERE
475: SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
476: AND exception_set_id = -1;
477:
471: SET DELETED_FLAG= MSC_UTIL.SYS_YES,
472: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
473: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
474: WHERE
475: SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
476: AND exception_set_id = -1;
477:
478:
479: COMMIT;
562: FOR j IN 1..lb_CALENDAR_CODE.COUNT LOOP
563:
564: BEGIN
565:
566: --IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
567:
568: UPDATE MSC_CALENDAR_DATES
569: SET
570: SEQ_NUM= lb_SEQ_NUM(j),
574: PRIOR_DATE= lb_PRIOR_DATE(j),
575: CALENDAR_START_DATE= lb_CALENDAR_START_DATE(j),
576: CALENDAR_END_DATE= lb_CALENDAR_END_DATE(j),
577: DESCRIPTION= lb_DESCRIPTION(j),
578: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
579: Deleted_Flag= MSC_UTIL.SYS_NO,
580: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
581: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
582: WHERE CALENDAR_DATE= lb_CALENDAR_DATE(j)
576: CALENDAR_END_DATE= lb_CALENDAR_END_DATE(j),
577: DESCRIPTION= lb_DESCRIPTION(j),
578: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
579: Deleted_Flag= MSC_UTIL.SYS_NO,
580: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
581: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
582: WHERE CALENDAR_DATE= lb_CALENDAR_DATE(j)
583: AND CALENDAR_CODE= lb_CALENDAR_CODE(j)
584: AND EXCEPTION_SET_ID= lb_EXCEPTION_SET_ID(j)
577: DESCRIPTION= lb_DESCRIPTION(j),
578: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
579: Deleted_Flag= MSC_UTIL.SYS_NO,
580: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
581: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
582: WHERE CALENDAR_DATE= lb_CALENDAR_DATE(j)
583: AND CALENDAR_CODE= lb_CALENDAR_CODE(j)
584: AND EXCEPTION_SET_ID= lb_EXCEPTION_SET_ID(j)
585: AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j);
600: lb_CALENDAR_END_DATE(j),
601: lb_DESCRIPTION(j),
602: lb_EXCEPTION_SET_ID(j),
603: lb_SR_INSTANCE_ID(j),
604: MSC_CL_COLLECTION.v_last_collection_id,
605: MSC_UTIL.SYS_NO,
606: MSC_CL_COLLECTION.v_current_date,
607: MSC_CL_COLLECTION.v_current_user,
608: MSC_CL_COLLECTION.v_current_date,
602: lb_EXCEPTION_SET_ID(j),
603: lb_SR_INSTANCE_ID(j),
604: MSC_CL_COLLECTION.v_last_collection_id,
605: MSC_UTIL.SYS_NO,
606: MSC_CL_COLLECTION.v_current_date,
607: MSC_CL_COLLECTION.v_current_user,
608: MSC_CL_COLLECTION.v_current_date,
609: MSC_CL_COLLECTION.v_current_user ;
610:
603: lb_SR_INSTANCE_ID(j),
604: MSC_CL_COLLECTION.v_last_collection_id,
605: MSC_UTIL.SYS_NO,
606: MSC_CL_COLLECTION.v_current_date,
607: MSC_CL_COLLECTION.v_current_user,
608: MSC_CL_COLLECTION.v_current_date,
609: MSC_CL_COLLECTION.v_current_user ;
610:
611: END IF;
604: MSC_CL_COLLECTION.v_last_collection_id,
605: MSC_UTIL.SYS_NO,
606: MSC_CL_COLLECTION.v_current_date,
607: MSC_CL_COLLECTION.v_current_user,
608: MSC_CL_COLLECTION.v_current_date,
609: MSC_CL_COLLECTION.v_current_user ;
610:
611: END IF;
612:
605: MSC_UTIL.SYS_NO,
606: MSC_CL_COLLECTION.v_current_date,
607: MSC_CL_COLLECTION.v_current_user,
608: MSC_CL_COLLECTION.v_current_date,
609: MSC_CL_COLLECTION.v_current_user ;
610:
611: END IF;
612:
613:
626: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
627: RAISE;
628:
629: ELSE
630: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
631:
632: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
633: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
634: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
663:
664:
665: DELETE MSC_CALENDAR_DATES
666: WHERE DELETED_FLAG= MSC_UTIL.SYS_YES
667: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
668: AND exception_set_id = -1;
669:
670: COMMIT;
671:
676:
677: --If it is complete or partial or continuous refresh, delete existing calendars in the current instance from MSC_CALENDARS
678:
679:
680: 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
681: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_CALENDARS', MSC_CL_COLLECTION.v_instance_id, NULL);
682: END IF;
683:
684: FOR c_rec IN c5 LOOP
677: --If it is complete or partial or continuous refresh, delete existing calendars in the current instance from MSC_CALENDARS
678:
679:
680: 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
681: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_CALENDARS', MSC_CL_COLLECTION.v_instance_id, NULL);
682: END IF;
683:
684: FOR c_rec IN c5 LOOP
685:
684: FOR c_rec IN c5 LOOP
685:
686: BEGIN
687: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'cal code='|| c_rec.CALENDAR_CODE);
688: 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
689:
690: IF MSC_CL_COLLECTION.v_instance_type <> MSC_UTIL.G_INS_OTHER THEN
691:
692: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'cal code='|| c_rec.CALENDAR_CODE);
686: BEGIN
687: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'cal code='|| c_rec.CALENDAR_CODE);
688: 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
689:
690: IF MSC_CL_COLLECTION.v_instance_type <> MSC_UTIL.G_INS_OTHER THEN
691:
692: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'cal code='|| c_rec.CALENDAR_CODE);
693:
694: INSERT INTO MSC_CALENDARS
707: c_rec.DESCRIPTION,
708: c_rec.CALENDAR_START_DATE,
709: c_rec.CALENDAR_END_DATE,
710: c_rec.SR_INSTANCE_ID,
711: MSC_CL_COLLECTION.v_last_collection_id,
712: MSC_CL_COLLECTION.v_current_date,
713: MSC_CL_COLLECTION.v_current_user,
714: MSC_CL_COLLECTION.v_current_date,
715: MSC_CL_COLLECTION.v_current_user );
708: c_rec.CALENDAR_START_DATE,
709: c_rec.CALENDAR_END_DATE,
710: c_rec.SR_INSTANCE_ID,
711: MSC_CL_COLLECTION.v_last_collection_id,
712: MSC_CL_COLLECTION.v_current_date,
713: MSC_CL_COLLECTION.v_current_user,
714: MSC_CL_COLLECTION.v_current_date,
715: MSC_CL_COLLECTION.v_current_user );
716:
709: c_rec.CALENDAR_END_DATE,
710: c_rec.SR_INSTANCE_ID,
711: MSC_CL_COLLECTION.v_last_collection_id,
712: MSC_CL_COLLECTION.v_current_date,
713: MSC_CL_COLLECTION.v_current_user,
714: MSC_CL_COLLECTION.v_current_date,
715: MSC_CL_COLLECTION.v_current_user );
716:
717: END IF;
710: c_rec.SR_INSTANCE_ID,
711: MSC_CL_COLLECTION.v_last_collection_id,
712: MSC_CL_COLLECTION.v_current_date,
713: MSC_CL_COLLECTION.v_current_user,
714: MSC_CL_COLLECTION.v_current_date,
715: MSC_CL_COLLECTION.v_current_user );
716:
717: END IF;
718:
711: MSC_CL_COLLECTION.v_last_collection_id,
712: MSC_CL_COLLECTION.v_current_date,
713: MSC_CL_COLLECTION.v_current_user,
714: MSC_CL_COLLECTION.v_current_date,
715: MSC_CL_COLLECTION.v_current_user );
716:
717: END IF;
718:
719: END IF;
730: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
731: RAISE;
732:
733: ELSE
734: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
735:
736: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
737: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
738: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
773: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
774: RAISE;
775:
776: ELSE
777: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
778:
779: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
780: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
781: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
793:
794:
795: --Collection of Calendar Assignments in case of complete, targeted and continuous refresh modes only..
796:
797: 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 MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
798:
799: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_CALENDAR_ASSIGNMENTS', MSC_CL_COLLECTION.v_instance_id, NULL);
800:
801:
795: --Collection of Calendar Assignments in case of complete, targeted and continuous refresh modes only..
796:
797: 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 MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
798:
799: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_CALENDAR_ASSIGNMENTS', MSC_CL_COLLECTION.v_instance_id, NULL);
800:
801:
802: FOR c_rec IN c7 LOOP
803:
832: c_rec.CARRIER_PARTNER_ID,
833: c_rec.PARTNER_TYPE,
834: c_rec.ASSOCIATION_LEVEL,
835: c_rec.SHIP_METHOD_CODE,
836: MSC_CL_COLLECTION.v_last_collection_id,
837: MSC_CL_COLLECTION.v_current_date,
838: MSC_CL_COLLECTION.v_current_user,
839: MSC_CL_COLLECTION.v_current_date,
840: MSC_CL_COLLECTION.v_current_user,
833: c_rec.PARTNER_TYPE,
834: c_rec.ASSOCIATION_LEVEL,
835: c_rec.SHIP_METHOD_CODE,
836: MSC_CL_COLLECTION.v_last_collection_id,
837: MSC_CL_COLLECTION.v_current_date,
838: MSC_CL_COLLECTION.v_current_user,
839: MSC_CL_COLLECTION.v_current_date,
840: MSC_CL_COLLECTION.v_current_user,
841: MSC_CL_COLLECTION.v_current_user);
834: c_rec.ASSOCIATION_LEVEL,
835: c_rec.SHIP_METHOD_CODE,
836: MSC_CL_COLLECTION.v_last_collection_id,
837: MSC_CL_COLLECTION.v_current_date,
838: MSC_CL_COLLECTION.v_current_user,
839: MSC_CL_COLLECTION.v_current_date,
840: MSC_CL_COLLECTION.v_current_user,
841: MSC_CL_COLLECTION.v_current_user);
842:
835: c_rec.SHIP_METHOD_CODE,
836: MSC_CL_COLLECTION.v_last_collection_id,
837: MSC_CL_COLLECTION.v_current_date,
838: MSC_CL_COLLECTION.v_current_user,
839: MSC_CL_COLLECTION.v_current_date,
840: MSC_CL_COLLECTION.v_current_user,
841: MSC_CL_COLLECTION.v_current_user);
842:
843:
836: MSC_CL_COLLECTION.v_last_collection_id,
837: MSC_CL_COLLECTION.v_current_date,
838: MSC_CL_COLLECTION.v_current_user,
839: MSC_CL_COLLECTION.v_current_date,
840: MSC_CL_COLLECTION.v_current_user,
841: MSC_CL_COLLECTION.v_current_user);
842:
843:
844: EXCEPTION
837: MSC_CL_COLLECTION.v_current_date,
838: MSC_CL_COLLECTION.v_current_user,
839: MSC_CL_COLLECTION.v_current_date,
840: MSC_CL_COLLECTION.v_current_user,
841: MSC_CL_COLLECTION.v_current_user);
842:
843:
844: EXCEPTION
845: WHEN OTHERS THEN
854: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
855: RAISE;
856:
857: ELSE
858: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
859:
860: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
861: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
862: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
879: FOR c_rec IN c2 LOOP
880:
881: BEGIN
882:
883: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
884:
885: UPDATE MSC_PERIOD_START_DATES
886: SET
887: PERIOD_SEQUENCE_NUM= c_rec.PERIOD_SEQUENCE_NUM,
887: PERIOD_SEQUENCE_NUM= c_rec.PERIOD_SEQUENCE_NUM,
888: PERIOD_NAME= c_rec.PERIOD_NAME,
889: NEXT_DATE= c_rec.NEXT_DATE,
890: PRIOR_DATE= c_rec.PRIOR_DATE,
891: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
892: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
893: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
894: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
895: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
888: PERIOD_NAME= c_rec.PERIOD_NAME,
889: NEXT_DATE= c_rec.NEXT_DATE,
890: PRIOR_DATE= c_rec.PRIOR_DATE,
891: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
892: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
893: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
894: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
895: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
896: AND PERIOD_START_DATE= c_rec.PERIOD_START_DATE
889: NEXT_DATE= c_rec.NEXT_DATE,
890: PRIOR_DATE= c_rec.PRIOR_DATE,
891: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
892: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
893: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
894: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
895: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
896: AND PERIOD_START_DATE= c_rec.PERIOD_START_DATE
897: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
898:
899: END IF;
900:
901:
902: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
903:
904: INSERT INTO MSC_PERIOD_START_DATES
905: ( CALENDAR_CODE,
906: EXCEPTION_SET_ID,
923: c_rec.PERIOD_NAME,
924: c_rec.NEXT_DATE,
925: c_rec.PRIOR_DATE,
926: c_rec.SR_INSTANCE_ID,
927: MSC_CL_COLLECTION.v_last_collection_id,
928: MSC_CL_COLLECTION.v_current_date,
929: MSC_CL_COLLECTION.v_current_user,
930: MSC_CL_COLLECTION.v_current_date,
931: MSC_CL_COLLECTION.v_current_user );
924: c_rec.NEXT_DATE,
925: c_rec.PRIOR_DATE,
926: c_rec.SR_INSTANCE_ID,
927: MSC_CL_COLLECTION.v_last_collection_id,
928: MSC_CL_COLLECTION.v_current_date,
929: MSC_CL_COLLECTION.v_current_user,
930: MSC_CL_COLLECTION.v_current_date,
931: MSC_CL_COLLECTION.v_current_user );
932:
925: c_rec.PRIOR_DATE,
926: c_rec.SR_INSTANCE_ID,
927: MSC_CL_COLLECTION.v_last_collection_id,
928: MSC_CL_COLLECTION.v_current_date,
929: MSC_CL_COLLECTION.v_current_user,
930: MSC_CL_COLLECTION.v_current_date,
931: MSC_CL_COLLECTION.v_current_user );
932:
933: END IF;
926: c_rec.SR_INSTANCE_ID,
927: MSC_CL_COLLECTION.v_last_collection_id,
928: MSC_CL_COLLECTION.v_current_date,
929: MSC_CL_COLLECTION.v_current_user,
930: MSC_CL_COLLECTION.v_current_date,
931: MSC_CL_COLLECTION.v_current_user );
932:
933: END IF;
934:
927: MSC_CL_COLLECTION.v_last_collection_id,
928: MSC_CL_COLLECTION.v_current_date,
929: MSC_CL_COLLECTION.v_current_user,
930: MSC_CL_COLLECTION.v_current_date,
931: MSC_CL_COLLECTION.v_current_user );
932:
933: END IF;
934:
935: c_count:= c_count+1;
933: END IF;
934:
935: c_count:= c_count+1;
936:
937: IF c_count> MSC_CL_COLLECTION.PBS THEN
938: COMMIT;
939: c_count:= 0;
940: END IF;
941:
954: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
955: RAISE;
956:
957: ELSE
958: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
959:
960: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
961: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
962: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
992: FOR c_rec IN c3 LOOP
993:
994: BEGIN
995:
996: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
997:
998: UPDATE MSC_CAL_YEAR_START_DATES
999: SET
1000: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
996: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
997:
998: UPDATE MSC_CAL_YEAR_START_DATES
999: SET
1000: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1001: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1002: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1003: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1004: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
997:
998: UPDATE MSC_CAL_YEAR_START_DATES
999: SET
1000: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1001: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1002: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1003: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1004: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1005: AND YEAR_START_DATE= c_rec.YEAR_START_DATE
998: UPDATE MSC_CAL_YEAR_START_DATES
999: SET
1000: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1001: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1002: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1003: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1004: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1005: AND YEAR_START_DATE= c_rec.YEAR_START_DATE
1006: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1007:
1008: END IF;
1009:
1010:
1011: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1012:
1013: INSERT INTO MSC_CAL_YEAR_START_DATES
1014: ( CALENDAR_CODE,
1015: EXCEPTION_SET_ID,
1024: ( c_rec.CALENDAR_CODE,
1025: c_rec.EXCEPTION_SET_ID,
1026: c_rec.YEAR_START_DATE,
1027: c_rec.SR_INSTANCE_ID,
1028: MSC_CL_COLLECTION.v_last_collection_id,
1029: MSC_CL_COLLECTION.v_current_date,
1030: MSC_CL_COLLECTION.v_current_user,
1031: MSC_CL_COLLECTION.v_current_date,
1032: MSC_CL_COLLECTION.v_current_user );
1025: c_rec.EXCEPTION_SET_ID,
1026: c_rec.YEAR_START_DATE,
1027: c_rec.SR_INSTANCE_ID,
1028: MSC_CL_COLLECTION.v_last_collection_id,
1029: MSC_CL_COLLECTION.v_current_date,
1030: MSC_CL_COLLECTION.v_current_user,
1031: MSC_CL_COLLECTION.v_current_date,
1032: MSC_CL_COLLECTION.v_current_user );
1033:
1026: c_rec.YEAR_START_DATE,
1027: c_rec.SR_INSTANCE_ID,
1028: MSC_CL_COLLECTION.v_last_collection_id,
1029: MSC_CL_COLLECTION.v_current_date,
1030: MSC_CL_COLLECTION.v_current_user,
1031: MSC_CL_COLLECTION.v_current_date,
1032: MSC_CL_COLLECTION.v_current_user );
1033:
1034: END IF;
1027: c_rec.SR_INSTANCE_ID,
1028: MSC_CL_COLLECTION.v_last_collection_id,
1029: MSC_CL_COLLECTION.v_current_date,
1030: MSC_CL_COLLECTION.v_current_user,
1031: MSC_CL_COLLECTION.v_current_date,
1032: MSC_CL_COLLECTION.v_current_user );
1033:
1034: END IF;
1035:
1028: MSC_CL_COLLECTION.v_last_collection_id,
1029: MSC_CL_COLLECTION.v_current_date,
1030: MSC_CL_COLLECTION.v_current_user,
1031: MSC_CL_COLLECTION.v_current_date,
1032: MSC_CL_COLLECTION.v_current_user );
1033:
1034: END IF;
1035:
1036: c_count:= c_count+1;
1034: END IF;
1035:
1036: c_count:= c_count+1;
1037:
1038: IF c_count> MSC_CL_COLLECTION.PBS THEN
1039: COMMIT;
1040: c_count:= 0;
1041: END IF;
1042:
1053: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1054: RAISE;
1055:
1056: ELSE
1057: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1058:
1059: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1060: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1061: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1091: FOR c_rec IN c4 LOOP
1092:
1093: BEGIN
1094:
1095: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1096:
1097: UPDATE MSC_CAL_WEEK_START_DATES
1098: SET
1099: NEXT_DATE= c_rec.NEXT_DATE,
1098: SET
1099: NEXT_DATE= c_rec.NEXT_DATE,
1100: PRIOR_DATE= c_rec.PRIOR_DATE,
1101: SEQ_NUM= c_rec.SEQ_NUM,
1102: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1103: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1104: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1105: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1106: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1099: NEXT_DATE= c_rec.NEXT_DATE,
1100: PRIOR_DATE= c_rec.PRIOR_DATE,
1101: SEQ_NUM= c_rec.SEQ_NUM,
1102: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1103: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1104: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1105: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1106: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1107: AND WEEK_START_DATE= c_rec.WEEK_START_DATE
1100: PRIOR_DATE= c_rec.PRIOR_DATE,
1101: SEQ_NUM= c_rec.SEQ_NUM,
1102: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1103: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1104: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1105: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1106: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1107: AND WEEK_START_DATE= c_rec.WEEK_START_DATE
1108: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1108: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1109:
1110: END IF;
1111:
1112: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1113:
1114: INSERT INTO MSC_CAL_WEEK_START_DATES
1115: ( CALENDAR_CODE,
1116: EXCEPTION_SET_ID,
1131: c_rec.NEXT_DATE,
1132: c_rec.PRIOR_DATE,
1133: c_rec.SEQ_NUM,
1134: c_rec.SR_INSTANCE_ID,
1135: MSC_CL_COLLECTION.v_last_collection_id,
1136: MSC_CL_COLLECTION.v_current_date,
1137: MSC_CL_COLLECTION.v_current_user,
1138: MSC_CL_COLLECTION.v_current_date,
1139: MSC_CL_COLLECTION.v_current_user );
1132: c_rec.PRIOR_DATE,
1133: c_rec.SEQ_NUM,
1134: c_rec.SR_INSTANCE_ID,
1135: MSC_CL_COLLECTION.v_last_collection_id,
1136: MSC_CL_COLLECTION.v_current_date,
1137: MSC_CL_COLLECTION.v_current_user,
1138: MSC_CL_COLLECTION.v_current_date,
1139: MSC_CL_COLLECTION.v_current_user );
1140:
1133: c_rec.SEQ_NUM,
1134: c_rec.SR_INSTANCE_ID,
1135: MSC_CL_COLLECTION.v_last_collection_id,
1136: MSC_CL_COLLECTION.v_current_date,
1137: MSC_CL_COLLECTION.v_current_user,
1138: MSC_CL_COLLECTION.v_current_date,
1139: MSC_CL_COLLECTION.v_current_user );
1140:
1141: END IF;
1134: c_rec.SR_INSTANCE_ID,
1135: MSC_CL_COLLECTION.v_last_collection_id,
1136: MSC_CL_COLLECTION.v_current_date,
1137: MSC_CL_COLLECTION.v_current_user,
1138: MSC_CL_COLLECTION.v_current_date,
1139: MSC_CL_COLLECTION.v_current_user );
1140:
1141: END IF;
1142:
1135: MSC_CL_COLLECTION.v_last_collection_id,
1136: MSC_CL_COLLECTION.v_current_date,
1137: MSC_CL_COLLECTION.v_current_user,
1138: MSC_CL_COLLECTION.v_current_date,
1139: MSC_CL_COLLECTION.v_current_user );
1140:
1141: END IF;
1142:
1143: c_count:= c_count+1;
1141: END IF;
1142:
1143: c_count:= c_count+1;
1144:
1145: IF c_count> MSC_CL_COLLECTION.PBS THEN
1146: COMMIT;
1147: c_count:= 0;
1148: END IF;
1149:
1160: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1161: RAISE;
1162:
1163: ELSE
1164: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1165:
1166: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1167: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1168: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1207: msrs.SHIFT_NUM,
1208: msrs.CAPACITY_UNITS,
1209: msrs.SR_INSTANCE_ID
1210: FROM MSC_ST_RESOURCE_SHIFTS msrs
1211: WHERE msrs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1212:
1213: CURSOR c6 IS
1214: SELECT
1215: mscs.CALENDAR_CODE,
1218: mscs.DAYS_OFF,
1219: mscs.DESCRIPTION,
1220: mscs.SR_INSTANCE_ID
1221: FROM MSC_ST_CALENDAR_SHIFTS mscs
1222: WHERE mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1223: AND nvl(mscs.process_flag, -99) <> MSC_UTIL.G_ERROR;
1224:
1225: CURSOR c7 IS
1226: SELECT
1234: mssd.NEXT_DATE,
1235: mssd.PRIOR_DATE,
1236: mssd.SR_INSTANCE_ID
1237: FROM MSC_ST_SHIFT_DATES mssd
1238: WHERE mssd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1239:
1240: CURSOR c8 IS
1241: SELECT
1242: msrc.DEPARTMENT_ID,
1251: msrc.ACTION_TYPE,
1252: msrc.DELETED_FLAG,
1253: msrc.SR_INSTANCE_ID
1254: FROM MSC_ST_RESOURCE_CHANGES msrc
1255: WHERE msrc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1256: ORDER BY
1257: msrc.DELETED_FLAG;
1258:
1259: CURSOR c9 IS
1263: msst.FROM_TIME,
1264: msst.TO_TIME,
1265: msst.SR_INSTANCE_ID
1266: FROM MSC_ST_SHIFT_TIMES msst
1267: WHERE msst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1268:
1269:
1270: CURSOR c10 IS
1271: SELECT
1275: msse.EXCEPTION_DATE,
1276: msse.EXCEPTION_TYPE,
1277: msse.SR_INSTANCE_ID
1278: FROM MSC_ST_SHIFT_EXCEPTIONS msse
1279: WHERE msse.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1280:
1281: c_count NUMBER:= 0;
1282: lv_sql_stmt varchar2(500);
1283: lv_dblink varchar2(50);
1290: ex_calc_res_avail EXCEPTION;
1291:
1292: BEGIN
1293:
1294: if ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.calendar_flag = MSC_UTIL.SYS_YES) OR
1295: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
1296:
1297: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1298:
1291:
1292: BEGIN
1293:
1294: if ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.calendar_flag = MSC_UTIL.SYS_YES) OR
1295: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
1296:
1297: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1298:
1299: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
1293:
1294: if ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.calendar_flag = MSC_UTIL.SYS_YES) OR
1295: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
1296:
1297: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1298:
1299: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
1300:
1301: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CALENDAR_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
1295: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
1296:
1297: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1298:
1299: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
1300:
1301: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CALENDAR_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
1302:
1303: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
1297: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1298:
1299: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
1300:
1301: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CALENDAR_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
1302:
1303: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
1304:
1305: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_CHANGES', MSC_CL_COLLECTION.v_instance_id, NULL);
1299: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
1300:
1301: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CALENDAR_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
1302:
1303: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
1304:
1305: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_CHANGES', MSC_CL_COLLECTION.v_instance_id, NULL);
1306:
1307: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_TIMES', MSC_CL_COLLECTION.v_instance_id, NULL);
1301: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CALENDAR_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
1302:
1303: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
1304:
1305: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_CHANGES', MSC_CL_COLLECTION.v_instance_id, NULL);
1306:
1307: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_TIMES', MSC_CL_COLLECTION.v_instance_id, NULL);
1308:
1309: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_EXCEPTIONS', MSC_CL_COLLECTION.v_instance_id, NULL);
1303: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
1304:
1305: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_CHANGES', MSC_CL_COLLECTION.v_instance_id, NULL);
1306:
1307: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_TIMES', MSC_CL_COLLECTION.v_instance_id, NULL);
1308:
1309: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_EXCEPTIONS', MSC_CL_COLLECTION.v_instance_id, NULL);
1310:
1311: END IF;
1305: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_CHANGES', MSC_CL_COLLECTION.v_instance_id, NULL);
1306:
1307: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_TIMES', MSC_CL_COLLECTION.v_instance_id, NULL);
1308:
1309: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_EXCEPTIONS', MSC_CL_COLLECTION.v_instance_id, NULL);
1310:
1311: END IF;
1312:
1313: c_count:= 0;
1315: FOR c_rec IN c5 LOOP
1316:
1317: BEGIN
1318:
1319: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1320:
1321: UPDATE MSC_RESOURCE_SHIFTS
1322: SET
1323: CAPACITY_UNITS=c_rec.CAPACITY_UNITS,
1320:
1321: UPDATE MSC_RESOURCE_SHIFTS
1322: SET
1323: CAPACITY_UNITS=c_rec.CAPACITY_UNITS,
1324: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1325: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1326: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1327: WHERE DEPARTMENT_ID= c_rec.DEPARTMENT_ID
1328: AND RESOURCE_ID= c_rec.RESOURCE_ID
1321: UPDATE MSC_RESOURCE_SHIFTS
1322: SET
1323: CAPACITY_UNITS=c_rec.CAPACITY_UNITS,
1324: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1325: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1326: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1327: WHERE DEPARTMENT_ID= c_rec.DEPARTMENT_ID
1328: AND RESOURCE_ID= c_rec.RESOURCE_ID
1329: AND SHIFT_NUM= c_rec.SHIFT_NUM
1322: SET
1323: CAPACITY_UNITS=c_rec.CAPACITY_UNITS,
1324: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1325: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1326: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1327: WHERE DEPARTMENT_ID= c_rec.DEPARTMENT_ID
1328: AND RESOURCE_ID= c_rec.RESOURCE_ID
1329: AND SHIFT_NUM= c_rec.SHIFT_NUM
1330: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1330: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1331:
1332: END IF;
1333:
1334: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1335:
1336: INSERT INTO MSC_RESOURCE_SHIFTS
1337: ( DEPARTMENT_ID,
1338: RESOURCE_ID,
1349: c_rec.RESOURCE_ID,
1350: c_rec.SHIFT_NUM,
1351: c_rec.CAPACITY_UNITS,
1352: c_rec.SR_INSTANCE_ID,
1353: MSC_CL_COLLECTION.v_last_collection_id,
1354: MSC_CL_COLLECTION.v_current_date,
1355: MSC_CL_COLLECTION.v_current_user,
1356: MSC_CL_COLLECTION.v_current_date,
1357: MSC_CL_COLLECTION.v_current_user );
1350: c_rec.SHIFT_NUM,
1351: c_rec.CAPACITY_UNITS,
1352: c_rec.SR_INSTANCE_ID,
1353: MSC_CL_COLLECTION.v_last_collection_id,
1354: MSC_CL_COLLECTION.v_current_date,
1355: MSC_CL_COLLECTION.v_current_user,
1356: MSC_CL_COLLECTION.v_current_date,
1357: MSC_CL_COLLECTION.v_current_user );
1358:
1351: c_rec.CAPACITY_UNITS,
1352: c_rec.SR_INSTANCE_ID,
1353: MSC_CL_COLLECTION.v_last_collection_id,
1354: MSC_CL_COLLECTION.v_current_date,
1355: MSC_CL_COLLECTION.v_current_user,
1356: MSC_CL_COLLECTION.v_current_date,
1357: MSC_CL_COLLECTION.v_current_user );
1358:
1359: END IF;
1352: c_rec.SR_INSTANCE_ID,
1353: MSC_CL_COLLECTION.v_last_collection_id,
1354: MSC_CL_COLLECTION.v_current_date,
1355: MSC_CL_COLLECTION.v_current_user,
1356: MSC_CL_COLLECTION.v_current_date,
1357: MSC_CL_COLLECTION.v_current_user );
1358:
1359: END IF;
1360:
1353: MSC_CL_COLLECTION.v_last_collection_id,
1354: MSC_CL_COLLECTION.v_current_date,
1355: MSC_CL_COLLECTION.v_current_user,
1356: MSC_CL_COLLECTION.v_current_date,
1357: MSC_CL_COLLECTION.v_current_user );
1358:
1359: END IF;
1360:
1361: c_count:= c_count+1;
1359: END IF;
1360:
1361: c_count:= c_count+1;
1362:
1363: IF c_count> MSC_CL_COLLECTION.PBS THEN
1364: COMMIT;
1365: c_count:= 0;
1366: END IF;
1367:
1378: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1379: RAISE;
1380:
1381: ELSE
1382: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1383:
1384: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1385: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1386: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1416: FOR c_rec IN c6 LOOP
1417:
1418: BEGIN
1419:
1420: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1421:
1422: UPDATE MSC_CALENDAR_SHIFTS
1423: SET
1424: DAYS_ON= c_rec.DAYS_ON,
1423: SET
1424: DAYS_ON= c_rec.DAYS_ON,
1425: DAYS_OFF= c_rec.DAYS_OFF,
1426: DESCRIPTION= c_rec.DESCRIPTION,
1427: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1428: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1429: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1430: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1431: AND SHIFT_NUM= c_rec.SHIFT_NUM
1424: DAYS_ON= c_rec.DAYS_ON,
1425: DAYS_OFF= c_rec.DAYS_OFF,
1426: DESCRIPTION= c_rec.DESCRIPTION,
1427: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1428: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1429: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1430: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1431: AND SHIFT_NUM= c_rec.SHIFT_NUM
1432: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1425: DAYS_OFF= c_rec.DAYS_OFF,
1426: DESCRIPTION= c_rec.DESCRIPTION,
1427: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1428: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1429: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1430: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1431: AND SHIFT_NUM= c_rec.SHIFT_NUM
1432: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1433:
1432: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1433:
1434: END IF;
1435:
1436: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1437:
1438: INSERT INTO MSC_CALENDAR_SHIFTS
1439: ( CALENDAR_CODE,
1440: SHIFT_NUM,
1453: c_rec.DAYS_ON,
1454: c_rec.DAYS_OFF,
1455: c_rec.DESCRIPTION,
1456: c_rec.SR_INSTANCE_ID,
1457: MSC_CL_COLLECTION.v_last_collection_id,
1458: MSC_CL_COLLECTION.v_current_date,
1459: MSC_CL_COLLECTION.v_current_user,
1460: MSC_CL_COLLECTION.v_current_date,
1461: MSC_CL_COLLECTION.v_current_user );
1454: c_rec.DAYS_OFF,
1455: c_rec.DESCRIPTION,
1456: c_rec.SR_INSTANCE_ID,
1457: MSC_CL_COLLECTION.v_last_collection_id,
1458: MSC_CL_COLLECTION.v_current_date,
1459: MSC_CL_COLLECTION.v_current_user,
1460: MSC_CL_COLLECTION.v_current_date,
1461: MSC_CL_COLLECTION.v_current_user );
1462:
1455: c_rec.DESCRIPTION,
1456: c_rec.SR_INSTANCE_ID,
1457: MSC_CL_COLLECTION.v_last_collection_id,
1458: MSC_CL_COLLECTION.v_current_date,
1459: MSC_CL_COLLECTION.v_current_user,
1460: MSC_CL_COLLECTION.v_current_date,
1461: MSC_CL_COLLECTION.v_current_user );
1462:
1463: END IF;
1456: c_rec.SR_INSTANCE_ID,
1457: MSC_CL_COLLECTION.v_last_collection_id,
1458: MSC_CL_COLLECTION.v_current_date,
1459: MSC_CL_COLLECTION.v_current_user,
1460: MSC_CL_COLLECTION.v_current_date,
1461: MSC_CL_COLLECTION.v_current_user );
1462:
1463: END IF;
1464:
1457: MSC_CL_COLLECTION.v_last_collection_id,
1458: MSC_CL_COLLECTION.v_current_date,
1459: MSC_CL_COLLECTION.v_current_user,
1460: MSC_CL_COLLECTION.v_current_date,
1461: MSC_CL_COLLECTION.v_current_user );
1462:
1463: END IF;
1464:
1465: c_count:= c_count+1;
1463: END IF;
1464:
1465: c_count:= c_count+1;
1466:
1467: IF c_count> MSC_CL_COLLECTION.PBS THEN
1468: COMMIT;
1469: c_count:= 0;
1470: END IF;
1471:
1483: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1484: RAISE;
1485:
1486: ELSE
1487: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1488:
1489: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1490: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1491: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1516: FOR c_rec IN c7 LOOP
1517:
1518: BEGIN
1519:
1520: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1521:
1522: UPDATE MSC_SHIFT_DATES
1523: SET
1524: SEQ_NUM= c_rec.SEQ_NUM,
1525: NEXT_SEQ_NUM= c_rec.NEXT_SEQ_NUM,
1526: PRIOR_SEQ_NUM= c_rec.PRIOR_SEQ_NUM,
1527: NEXT_DATE= c_rec.NEXT_DATE,
1528: PRIOR_DATE= c_rec.PRIOR_DATE,
1529: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1530: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1531: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1532: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1533: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1526: PRIOR_SEQ_NUM= c_rec.PRIOR_SEQ_NUM,
1527: NEXT_DATE= c_rec.NEXT_DATE,
1528: PRIOR_DATE= c_rec.PRIOR_DATE,
1529: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1530: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1531: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1532: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1533: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1534: AND SHIFT_NUM= c_rec.SHIFT_NUM
1527: NEXT_DATE= c_rec.NEXT_DATE,
1528: PRIOR_DATE= c_rec.PRIOR_DATE,
1529: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1530: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1531: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1532: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1533: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1534: AND SHIFT_NUM= c_rec.SHIFT_NUM
1535: AND SHIFT_DATE= c_rec.SHIFT_DATE
1536: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1537:
1538: END IF;
1539:
1540: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1541:
1542: INSERT INTO MSC_SHIFT_DATES
1543: ( CALENDAR_CODE,
1544: EXCEPTION_SET_ID,
1565: c_rec.PRIOR_SEQ_NUM,
1566: c_rec.NEXT_DATE,
1567: c_rec.PRIOR_DATE,
1568: c_rec.SR_INSTANCE_ID,
1569: MSC_CL_COLLECTION.v_last_collection_id,
1570: MSC_CL_COLLECTION.v_current_date,
1571: MSC_CL_COLLECTION.v_current_user,
1572: MSC_CL_COLLECTION.v_current_date,
1573: MSC_CL_COLLECTION.v_current_user );
1566: c_rec.NEXT_DATE,
1567: c_rec.PRIOR_DATE,
1568: c_rec.SR_INSTANCE_ID,
1569: MSC_CL_COLLECTION.v_last_collection_id,
1570: MSC_CL_COLLECTION.v_current_date,
1571: MSC_CL_COLLECTION.v_current_user,
1572: MSC_CL_COLLECTION.v_current_date,
1573: MSC_CL_COLLECTION.v_current_user );
1574:
1567: c_rec.PRIOR_DATE,
1568: c_rec.SR_INSTANCE_ID,
1569: MSC_CL_COLLECTION.v_last_collection_id,
1570: MSC_CL_COLLECTION.v_current_date,
1571: MSC_CL_COLLECTION.v_current_user,
1572: MSC_CL_COLLECTION.v_current_date,
1573: MSC_CL_COLLECTION.v_current_user );
1574:
1575: END IF;
1568: c_rec.SR_INSTANCE_ID,
1569: MSC_CL_COLLECTION.v_last_collection_id,
1570: MSC_CL_COLLECTION.v_current_date,
1571: MSC_CL_COLLECTION.v_current_user,
1572: MSC_CL_COLLECTION.v_current_date,
1573: MSC_CL_COLLECTION.v_current_user );
1574:
1575: END IF;
1576:
1569: MSC_CL_COLLECTION.v_last_collection_id,
1570: MSC_CL_COLLECTION.v_current_date,
1571: MSC_CL_COLLECTION.v_current_user,
1572: MSC_CL_COLLECTION.v_current_date,
1573: MSC_CL_COLLECTION.v_current_user );
1574:
1575: END IF;
1576:
1577: c_count:= c_count+1;
1575: END IF;
1576:
1577: c_count:= c_count+1;
1578:
1579: IF c_count> MSC_CL_COLLECTION.PBS THEN
1580: COMMIT;
1581: c_count:= 0;
1582: END IF;
1583:
1594: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1595: RAISE;
1596:
1597: ELSE
1598: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1599:
1600: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1601: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1602: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1637: FOR c_rec IN c8 LOOP
1638:
1639: BEGIN
1640:
1641: IF MSC_CL_COLLECTION.v_is_incremental_refresh AND c_rec.DELETED_FLAG= MSC_UTIL.SYS_YES THEN
1642:
1643: -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
1644:
1645: DELETE MSC_RESOURCE_CHANGES
1684: c_rec.CAPACITY_CHANGE,
1685: c_rec.SIMULATION_SET,
1686: c_rec.ACTION_TYPE,
1687: c_rec.SR_INSTANCE_ID,
1688: MSC_CL_COLLECTION.v_last_collection_id,
1689: MSC_CL_COLLECTION.v_current_date,
1690: MSC_CL_COLLECTION.v_current_user,
1691: MSC_CL_COLLECTION.v_current_date,
1692: MSC_CL_COLLECTION.v_current_user );
1685: c_rec.SIMULATION_SET,
1686: c_rec.ACTION_TYPE,
1687: c_rec.SR_INSTANCE_ID,
1688: MSC_CL_COLLECTION.v_last_collection_id,
1689: MSC_CL_COLLECTION.v_current_date,
1690: MSC_CL_COLLECTION.v_current_user,
1691: MSC_CL_COLLECTION.v_current_date,
1692: MSC_CL_COLLECTION.v_current_user );
1693:
1686: c_rec.ACTION_TYPE,
1687: c_rec.SR_INSTANCE_ID,
1688: MSC_CL_COLLECTION.v_last_collection_id,
1689: MSC_CL_COLLECTION.v_current_date,
1690: MSC_CL_COLLECTION.v_current_user,
1691: MSC_CL_COLLECTION.v_current_date,
1692: MSC_CL_COLLECTION.v_current_user );
1693:
1694: END IF;
1687: c_rec.SR_INSTANCE_ID,
1688: MSC_CL_COLLECTION.v_last_collection_id,
1689: MSC_CL_COLLECTION.v_current_date,
1690: MSC_CL_COLLECTION.v_current_user,
1691: MSC_CL_COLLECTION.v_current_date,
1692: MSC_CL_COLLECTION.v_current_user );
1693:
1694: END IF;
1695:
1688: MSC_CL_COLLECTION.v_last_collection_id,
1689: MSC_CL_COLLECTION.v_current_date,
1690: MSC_CL_COLLECTION.v_current_user,
1691: MSC_CL_COLLECTION.v_current_date,
1692: MSC_CL_COLLECTION.v_current_user );
1693:
1694: END IF;
1695:
1696: c_count:= c_count+1;
1694: END IF;
1695:
1696: c_count:= c_count+1;
1697:
1698: IF c_count> MSC_CL_COLLECTION.PBS THEN
1699: COMMIT;
1700: c_count:= 0;
1701: END IF;
1702:
1714: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1715: RAISE;
1716:
1717: ELSE
1718: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1719:
1720: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1721: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1722: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1766: FOR c_rec IN c9 LOOP
1767:
1768: BEGIN
1769:
1770: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1771:
1772: UPDATE MSC_SHIFT_TIMES
1773: SET
1774: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1770: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1771:
1772: UPDATE MSC_SHIFT_TIMES
1773: SET
1774: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1775: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1776: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1777: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1778: AND SHIFT_NUM= c_rec.SHIFT_NUM
1771:
1772: UPDATE MSC_SHIFT_TIMES
1773: SET
1774: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1775: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1776: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1777: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1778: AND SHIFT_NUM= c_rec.SHIFT_NUM
1779: AND FROM_TIME= c_rec.FROM_TIME
1772: UPDATE MSC_SHIFT_TIMES
1773: SET
1774: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1775: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1776: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1777: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1778: AND SHIFT_NUM= c_rec.SHIFT_NUM
1779: AND FROM_TIME= c_rec.FROM_TIME
1780: AND TO_TIME= c_rec.TO_TIME
1781: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1782:
1783: END IF;
1784:
1785: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1786:
1787: INSERT INTO MSC_SHIFT_TIMES
1788: ( CALENDAR_CODE,
1789: SHIFT_NUM,
1800: c_rec.SHIFT_NUM,
1801: c_rec.FROM_TIME,
1802: c_rec.TO_TIME,
1803: c_rec.SR_INSTANCE_ID,
1804: MSC_CL_COLLECTION.v_last_collection_id,
1805: MSC_CL_COLLECTION.v_current_date,
1806: MSC_CL_COLLECTION.v_current_user,
1807: MSC_CL_COLLECTION.v_current_date,
1808: MSC_CL_COLLECTION.v_current_user );
1801: c_rec.FROM_TIME,
1802: c_rec.TO_TIME,
1803: c_rec.SR_INSTANCE_ID,
1804: MSC_CL_COLLECTION.v_last_collection_id,
1805: MSC_CL_COLLECTION.v_current_date,
1806: MSC_CL_COLLECTION.v_current_user,
1807: MSC_CL_COLLECTION.v_current_date,
1808: MSC_CL_COLLECTION.v_current_user );
1809:
1802: c_rec.TO_TIME,
1803: c_rec.SR_INSTANCE_ID,
1804: MSC_CL_COLLECTION.v_last_collection_id,
1805: MSC_CL_COLLECTION.v_current_date,
1806: MSC_CL_COLLECTION.v_current_user,
1807: MSC_CL_COLLECTION.v_current_date,
1808: MSC_CL_COLLECTION.v_current_user );
1809:
1810: END IF;
1803: c_rec.SR_INSTANCE_ID,
1804: MSC_CL_COLLECTION.v_last_collection_id,
1805: MSC_CL_COLLECTION.v_current_date,
1806: MSC_CL_COLLECTION.v_current_user,
1807: MSC_CL_COLLECTION.v_current_date,
1808: MSC_CL_COLLECTION.v_current_user );
1809:
1810: END IF;
1811:
1804: MSC_CL_COLLECTION.v_last_collection_id,
1805: MSC_CL_COLLECTION.v_current_date,
1806: MSC_CL_COLLECTION.v_current_user,
1807: MSC_CL_COLLECTION.v_current_date,
1808: MSC_CL_COLLECTION.v_current_user );
1809:
1810: END IF;
1811:
1812: c_count:= c_count+1;
1810: END IF;
1811:
1812: c_count:= c_count+1;
1813:
1814: IF c_count> MSC_CL_COLLECTION.PBS THEN
1815: COMMIT;
1816: c_count:= 0;
1817: END IF;
1818:
1829: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1830: RAISE;
1831:
1832: ELSE
1833: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1834:
1835: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1836: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1837: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1872: FOR c_rec IN c10 LOOP
1873:
1874: BEGIN
1875:
1876: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1877:
1878: UPDATE MSC_SHIFT_EXCEPTIONS
1879: SET
1880: EXCEPTION_TYPE= c_rec.EXCEPTION_TYPE,
1877:
1878: UPDATE MSC_SHIFT_EXCEPTIONS
1879: SET
1880: EXCEPTION_TYPE= c_rec.EXCEPTION_TYPE,
1881: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1882: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1883: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1884: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1885: AND SHIFT_NUM= c_rec.SHIFT_NUM
1878: UPDATE MSC_SHIFT_EXCEPTIONS
1879: SET
1880: EXCEPTION_TYPE= c_rec.EXCEPTION_TYPE,
1881: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1882: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1883: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1884: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1885: AND SHIFT_NUM= c_rec.SHIFT_NUM
1886: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1879: SET
1880: EXCEPTION_TYPE= c_rec.EXCEPTION_TYPE,
1881: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1882: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1883: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1884: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1885: AND SHIFT_NUM= c_rec.SHIFT_NUM
1886: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1887: AND EXCEPTION_DATE= c_rec.EXCEPTION_DATE
1888: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1889:
1890: END IF;
1891:
1892: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1893:
1894: INSERT INTO MSC_SHIFT_EXCEPTIONS
1895: ( CALENDAR_CODE,
1896: SHIFT_NUM,
1909: c_rec.EXCEPTION_SET_ID,
1910: c_rec.EXCEPTION_DATE,
1911: c_rec.EXCEPTION_TYPE,
1912: c_rec.SR_INSTANCE_ID,
1913: MSC_CL_COLLECTION.v_last_collection_id,
1914: MSC_CL_COLLECTION.v_current_date,
1915: MSC_CL_COLLECTION.v_current_user,
1916: MSC_CL_COLLECTION.v_current_date,
1917: MSC_CL_COLLECTION.v_current_user );
1910: c_rec.EXCEPTION_DATE,
1911: c_rec.EXCEPTION_TYPE,
1912: c_rec.SR_INSTANCE_ID,
1913: MSC_CL_COLLECTION.v_last_collection_id,
1914: MSC_CL_COLLECTION.v_current_date,
1915: MSC_CL_COLLECTION.v_current_user,
1916: MSC_CL_COLLECTION.v_current_date,
1917: MSC_CL_COLLECTION.v_current_user );
1918:
1911: c_rec.EXCEPTION_TYPE,
1912: c_rec.SR_INSTANCE_ID,
1913: MSC_CL_COLLECTION.v_last_collection_id,
1914: MSC_CL_COLLECTION.v_current_date,
1915: MSC_CL_COLLECTION.v_current_user,
1916: MSC_CL_COLLECTION.v_current_date,
1917: MSC_CL_COLLECTION.v_current_user );
1918:
1919: END IF;
1912: c_rec.SR_INSTANCE_ID,
1913: MSC_CL_COLLECTION.v_last_collection_id,
1914: MSC_CL_COLLECTION.v_current_date,
1915: MSC_CL_COLLECTION.v_current_user,
1916: MSC_CL_COLLECTION.v_current_date,
1917: MSC_CL_COLLECTION.v_current_user );
1918:
1919: END IF;
1920:
1913: MSC_CL_COLLECTION.v_last_collection_id,
1914: MSC_CL_COLLECTION.v_current_date,
1915: MSC_CL_COLLECTION.v_current_user,
1916: MSC_CL_COLLECTION.v_current_date,
1917: MSC_CL_COLLECTION.v_current_user );
1918:
1919: END IF;
1920:
1921: c_count:= c_count+1;
1919: END IF;
1920:
1921: c_count:= c_count+1;
1922:
1923: IF c_count> MSC_CL_COLLECTION.PBS THEN
1924: COMMIT;
1925: c_count:= 0;
1926: END IF;
1927:
1938: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1939: RAISE;
1940:
1941: ELSE
1942: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1943:
1944: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1945: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1946: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1975:
1976: COMMIT;
1977: end if;
1978:
1979: if ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.bom_flag = MSC_UTIL.SYS_YES) OR
1980: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
1981:
1982: FND_MESSAGE.SET_NAME('MSC', 'MSC_DP_TASK_START');
1983: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE');
1976: COMMIT;
1977: end if;
1978:
1979: if ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.bom_flag = MSC_UTIL.SYS_YES) OR
1980: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
1981:
1982: FND_MESSAGE.SET_NAME('MSC', 'MSC_DP_TASK_START');
1983: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE');
1984: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1981:
1982: FND_MESSAGE.SET_NAME('MSC', 'MSC_DP_TASK_START');
1983: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE');
1984: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1985: if (MSC_CL_COLLECTION.v_bom_refresh_type <> 3) then
1986: MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE;
1987: end if ;
1988: end if;
1989: IF MSC_CL_COLLECTION.v_recalc_nra= MSC_UTIL.SYS_YES THEN
1985: if (MSC_CL_COLLECTION.v_bom_refresh_type <> 3) then
1986: MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE;
1987: end if ;
1988: end if;
1989: IF MSC_CL_COLLECTION.v_recalc_nra= MSC_UTIL.SYS_YES THEN
1990: IF MSC_CL_COLLECTION.v_discrete_flag= MSC_UTIL.SYS_YES THEN
1991: BEGIN
1992: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1993: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL',
1986: MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE;
1987: end if ;
1988: end if;
1989: IF MSC_CL_COLLECTION.v_recalc_nra= MSC_UTIL.SYS_YES THEN
1990: IF MSC_CL_COLLECTION.v_discrete_flag= MSC_UTIL.SYS_YES THEN
1991: BEGIN
1992: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1993: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL',
1994: MSC_CL_COLLECTION.v_instance_id, -1);
1988: end if;
1989: IF MSC_CL_COLLECTION.v_recalc_nra= MSC_UTIL.SYS_YES THEN
1990: IF MSC_CL_COLLECTION.v_discrete_flag= MSC_UTIL.SYS_YES THEN
1991: BEGIN
1992: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1993: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL',
1994: MSC_CL_COLLECTION.v_instance_id, -1);
1995: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL',
1996: MSC_CL_COLLECTION.v_instance_id, -1);
1989: IF MSC_CL_COLLECTION.v_recalc_nra= MSC_UTIL.SYS_YES THEN
1990: IF MSC_CL_COLLECTION.v_discrete_flag= MSC_UTIL.SYS_YES THEN
1991: BEGIN
1992: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1993: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL',
1994: MSC_CL_COLLECTION.v_instance_id, -1);
1995: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL',
1996: MSC_CL_COLLECTION.v_instance_id, -1);
1997: END IF;
1990: IF MSC_CL_COLLECTION.v_discrete_flag= MSC_UTIL.SYS_YES THEN
1991: BEGIN
1992: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1993: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL',
1994: MSC_CL_COLLECTION.v_instance_id, -1);
1995: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL',
1996: MSC_CL_COLLECTION.v_instance_id, -1);
1997: END IF;
1998:
1991: BEGIN
1992: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1993: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL',
1994: MSC_CL_COLLECTION.v_instance_id, -1);
1995: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL',
1996: MSC_CL_COLLECTION.v_instance_id, -1);
1997: END IF;
1998:
1999: SELECT DECODE(M2A_DBLINK,
1992: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1993: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL',
1994: MSC_CL_COLLECTION.v_instance_id, -1);
1995: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL',
1996: MSC_CL_COLLECTION.v_instance_id, -1);
1997: END IF;
1998:
1999: SELECT DECODE(M2A_DBLINK,
2000: NULL,'',
2006: INTO lv_dblink,
2007: lv_dest_a2m,
2008: lv_instance_code
2009: FROM MSC_APPS_INSTANCES
2010: WHERE INSTANCE_ID=MSC_CL_COLLECTION.v_instance_id;
2011:
2012: lv_res_avail_before_sysdate := nvl(TO_NUMBER(FND_PROFILE.VAlUE('MSC_RES_AVAIL_BEFORE_SYSDAT')),1);
2013: IF MSC_CL_COLLECTION.v_instance_type <> MSC_UTIL.G_INS_OTHER THEN
2014: lv_sql_stmt:= 'SELECT nvl(mar.LRD,sysdate)- '||lv_res_avail_before_sysdate
2009: FROM MSC_APPS_INSTANCES
2010: WHERE INSTANCE_ID=MSC_CL_COLLECTION.v_instance_id;
2011:
2012: lv_res_avail_before_sysdate := nvl(TO_NUMBER(FND_PROFILE.VAlUE('MSC_RES_AVAIL_BEFORE_SYSDAT')),1);
2013: IF MSC_CL_COLLECTION.v_instance_type <> MSC_UTIL.G_INS_OTHER THEN
2014: lv_sql_stmt:= 'SELECT nvl(mar.LRD,sysdate)- '||lv_res_avail_before_sysdate
2015: ||' FROM MRP_AP_APPS_INSTANCES_ALL'||lv_dblink||' mar'
2016: ||' WHERE INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2017: ||' AND INSTANCE_CODE = '''||lv_instance_code||''''
2012: lv_res_avail_before_sysdate := nvl(TO_NUMBER(FND_PROFILE.VAlUE('MSC_RES_AVAIL_BEFORE_SYSDAT')),1);
2013: IF MSC_CL_COLLECTION.v_instance_type <> MSC_UTIL.G_INS_OTHER THEN
2014: lv_sql_stmt:= 'SELECT nvl(mar.LRD,sysdate)- '||lv_res_avail_before_sysdate
2015: ||' FROM MRP_AP_APPS_INSTANCES_ALL'||lv_dblink||' mar'
2016: ||' WHERE INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2017: ||' AND INSTANCE_CODE = '''||lv_instance_code||''''
2018: ||' AND nvl(A2M_DBLINK,'''||MSC_UTIL.NULL_DBLINK||''') = '''||lv_dest_a2m||'''' ;
2019: EXECUTE IMMEDIATE lv_sql_stmt INTO lv_resource_start_time;
2020: END IF;
2017: ||' AND INSTANCE_CODE = '''||lv_instance_code||''''
2018: ||' AND nvl(A2M_DBLINK,'''||MSC_UTIL.NULL_DBLINK||''') = '''||lv_dest_a2m||'''' ;
2019: EXECUTE IMMEDIATE lv_sql_stmt INTO lv_resource_start_time;
2020: END IF;
2021: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
2022: lv_resource_start_time := lv_resource_start_time - nvl(lv_res_avail_before_sysdate,1);
2023: END IF;
2024:
2025: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'@@before net res avai : debug 1');
2022: lv_resource_start_time := lv_resource_start_time - nvl(lv_res_avail_before_sysdate,1);
2023: END IF;
2024:
2025: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'@@before net res avai : debug 1');
2026: lv_ret_res_ava:=MSC_RESOURCE_AVAILABILITY.CALC_RESOURCE_AVAILABILITY(lv_resource_start_time,MSC_CL_COLLECTION.v_coll_prec.org_group_flag,FALSE);
2027:
2028: IF lv_ret_res_ava = 2 THEN
2029: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
2030: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2027:
2028: IF lv_ret_res_ava = 2 THEN
2029: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
2030: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2031: MSC_CL_COLLECTION.v_warning_flag:=MSC_UTIL.SYS_YES;
2032: ELSIF lv_ret_res_ava <> 0 THEN
2033: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
2034: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2035: RAISE ex_calc_res_avail;
2058:
2059: CURSOR c1 IS
2060: SELECT
2061: /* SCE Change starts */
2062: decode(mc.COMPANY_ID, MSC_CL_COLLECTION.G_MY_COMPANY_ID, null, mc.COMPANY_ID) COMPANY_ID ,
2063: /* SCE change ends */
2064: mst.ORGANIZATION_CODE,
2065: mst.ORGANIZATION_TYPE,
2066: mst.SR_TP_ID,
2112: MSC_ST_TRADING_PARTNERS mst,
2113: MSC_COMPANIES MC
2114: WHERE mst.PARTNER_TYPE= 3
2115: /* SCE Change starts */
2116: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = MC.company_name
2117: -- AND nvl( mst.company_id, -1) = -1 -- commented for aerox
2118: /* SCE Change Ends */
2119: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2120: AND tilc.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2115: /* SCE Change starts */
2116: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = MC.company_name
2117: -- AND nvl( mst.company_id, -1) = -1 -- commented for aerox
2118: /* SCE Change Ends */
2119: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2120: AND tilc.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2121: AND tilc.PARTNER_TYPE(+)= 2
2122: AND tilc.SR_TP_ID(+)= mst.MODELED_CUSTOMER_ID
2123: AND tils.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2116: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = MC.company_name
2117: -- AND nvl( mst.company_id, -1) = -1 -- commented for aerox
2118: /* SCE Change Ends */
2119: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2120: AND tilc.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2121: AND tilc.PARTNER_TYPE(+)= 2
2122: AND tilc.SR_TP_ID(+)= mst.MODELED_CUSTOMER_ID
2123: AND tils.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2124: AND tils.PARTNER_TYPE(+)= 1
2119: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2120: AND tilc.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2121: AND tilc.PARTNER_TYPE(+)= 2
2122: AND tilc.SR_TP_ID(+)= mst.MODELED_CUSTOMER_ID
2123: AND tils.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2124: AND tils.PARTNER_TYPE(+)= 1
2125: AND tils.SR_TP_ID(+)= mst.MODELED_SUPPLIER_ID
2126: AND tsilc.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2127: AND tsilc.PARTNER_TYPE(+)= 2
2122: AND tilc.SR_TP_ID(+)= mst.MODELED_CUSTOMER_ID
2123: AND tils.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2124: AND tils.PARTNER_TYPE(+)= 1
2125: AND tils.SR_TP_ID(+)= mst.MODELED_SUPPLIER_ID
2126: AND tsilc.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2127: AND tsilc.PARTNER_TYPE(+)= 2
2128: AND tsilc.SR_TP_SITE_ID(+)= mst.MODELED_CUSTOMER_SITE_ID
2129: AND tsils.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2130: AND tsils.PARTNER_TYPE(+)= 1
2125: AND tils.SR_TP_ID(+)= mst.MODELED_SUPPLIER_ID
2126: AND tsilc.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2127: AND tsilc.PARTNER_TYPE(+)= 2
2128: AND tsilc.SR_TP_SITE_ID(+)= mst.MODELED_CUSTOMER_SITE_ID
2129: AND tsils.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2130: AND tsils.PARTNER_TYPE(+)= 1
2131: AND tsils.SR_TP_SITE_ID(+)= mst.MODELED_SUPPLIER_SITE_ID;
2132:
2133:
2144: msts.LATITUDE
2145: FROM MSC_TRADING_PARTNERS mtp,
2146: MSC_ST_TRADING_PARTNER_SITES msts
2147: WHERE msts.PARTNER_TYPE= 3
2148: AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2149: AND mtp.SR_TP_ID= msts.SR_TP_ID
2150: AND mtp.PARTNER_TYPE= 3
2151: AND mtp.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2152:
2147: WHERE msts.PARTNER_TYPE= 3
2148: AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2149: AND mtp.SR_TP_ID= msts.SR_TP_ID
2150: AND mtp.PARTNER_TYPE= 3
2151: AND mtp.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2152:
2153: /* For bug#2198339 modified this cursor to bring data only for Vendors-Customers */
2154: CURSOR c3 IS
2155: SELECT DISTINCT
2167: AND til.PARTNER_TYPE= msta.PARTNER_TYPE
2168: AND tsil.SR_INSTANCE_ID= msta.SR_INSTANCE_ID
2169: AND tsil.SR_TP_SITE_ID= msta.SR_TP_SITE_ID
2170: AND tsil.PARTNER_TYPE= msta.PARTNER_TYPE
2171: AND msta.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2172: AND msta.PARTNER_TYPE IN (1,2);
2173:
2174: CURSOR c4 IS
2175: SELECT
2190: pc.DELETED_FLAG
2191: FROM MSC_TP_ID_LID til,
2192: MSC_TP_SITE_ID_LID tsil,
2193: MSC_ST_PARTNER_CONTACTS pc
2194: WHERE pc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2195: AND pc.DELETED_FLAG in (1, 2)
2196: AND til.sr_tp_id(+)= pc.partner_id
2197: AND til.partner_type(+)= DECODE( pc.PARTNER_TYPE,1,1,2,2,NULL)
2198: AND til.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id
2194: WHERE pc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2195: AND pc.DELETED_FLAG in (1, 2)
2196: AND til.sr_tp_id(+)= pc.partner_id
2197: AND til.partner_type(+)= DECODE( pc.PARTNER_TYPE,1,1,2,2,NULL)
2198: AND til.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id
2199: AND tsil.sr_tp_site_id(+)= pc.partner_site_id
2200: AND tsil.partner_type(+)= DECODE( pc.PARTNER_TYPE,1,1,2,2,NULL)
2201: AND tsil.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id
2202: ORDER BY
2197: AND til.partner_type(+)= DECODE( pc.PARTNER_TYPE,1,1,2,2,NULL)
2198: AND til.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id
2199: AND tsil.sr_tp_site_id(+)= pc.partner_site_id
2200: AND tsil.partner_type(+)= DECODE( pc.PARTNER_TYPE,1,1,2,2,NULL)
2201: AND tsil.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id
2202: ORDER BY
2203: 1,2,3,4 ASC;
2204:
2205: /* For bug#2198339 added this cursor to bring Locations associations data only for Organizations */
2212: msta.LOCATION_ID PARTNER_SITE_ID,
2213: msta.SR_INSTANCE_ID
2214: FROM MSC_TRADING_PARTNERS mtps,
2215: MSC_ST_LOCATION_ASSOCIATIONS msta
2216: WHERE msta.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2217: AND msta.partner_type = 3
2218: AND msta.SR_INSTANCE_ID= mtps.SR_INSTANCE_ID
2219: AND msta.SR_TP_ID= mtps.SR_TP_ID
2220: AND msta.PARTNER_TYPE= mtps.PARTNER_TYPE;
2232: BEGIN
2233:
2234: -- Organization
2235:
2236: 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
2237:
2238: DELETE MSC_TRADING_PARTNERS
2239: WHERE sr_instance_id= MSC_CL_COLLECTION.v_instance_id
2240: AND partner_type=3
2235:
2236: 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
2237:
2238: DELETE MSC_TRADING_PARTNERS
2239: WHERE sr_instance_id= MSC_CL_COLLECTION.v_instance_id
2240: AND partner_type=3
2241: AND nvl(ORG_SUPPLIER_MAPPED,'N') <> 'Y';
2242:
2243: -- MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_TRADING_PARTNERS', MSC_CL_COLLECTION.v_instance_id, NULL,
2239: WHERE sr_instance_id= MSC_CL_COLLECTION.v_instance_id
2240: AND partner_type=3
2241: AND nvl(ORG_SUPPLIER_MAPPED,'N') <> 'Y';
2242:
2243: -- MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_TRADING_PARTNERS', MSC_CL_COLLECTION.v_instance_id, NULL,
2244: -- 'AND PARTNER_TYPE=3');
2245:
2246: END IF;
2247:
2344: c_rec.BUSINESS_GROUP_NAME,
2345: c_rec.LEGAL_ENTITY_NAME,
2346: c_rec.OPERATING_UNIT_NAME,
2347: c_rec.SUBCONTRACTING_SOURCE_ORG,
2348: MSC_CL_COLLECTION.v_last_collection_id,
2349: MSC_CL_COLLECTION.v_current_date,
2350: MSC_CL_COLLECTION.v_current_user,
2351: c_rec.SR_TP_ID,
2352: c_rec.SR_INSTANCE_ID,
2345: c_rec.LEGAL_ENTITY_NAME,
2346: c_rec.OPERATING_UNIT_NAME,
2347: c_rec.SUBCONTRACTING_SOURCE_ORG,
2348: MSC_CL_COLLECTION.v_last_collection_id,
2349: MSC_CL_COLLECTION.v_current_date,
2350: MSC_CL_COLLECTION.v_current_user,
2351: c_rec.SR_TP_ID,
2352: c_rec.SR_INSTANCE_ID,
2353: c_rec.PARTNER_TYPE;
2346: c_rec.OPERATING_UNIT_NAME,
2347: c_rec.SUBCONTRACTING_SOURCE_ORG,
2348: MSC_CL_COLLECTION.v_last_collection_id,
2349: MSC_CL_COLLECTION.v_current_date,
2350: MSC_CL_COLLECTION.v_current_user,
2351: c_rec.SR_TP_ID,
2352: c_rec.SR_INSTANCE_ID,
2353: c_rec.PARTNER_TYPE;
2354:
2456: c_rec.BUSINESS_GROUP_NAME,
2457: c_rec.LEGAL_ENTITY_NAME,
2458: c_rec.OPERATING_UNIT_NAME,
2459: c_rec.SUBCONTRACTING_SOURCE_ORG,
2460: MSC_CL_COLLECTION.v_last_collection_id,
2461: c_rec.INHERIT_OC_OP_SEQ_NUM,
2462: MSC_CL_COLLECTION.v_current_date,
2463: MSC_CL_COLLECTION.v_current_user,
2464: MSC_CL_COLLECTION.v_current_date,
2458: c_rec.OPERATING_UNIT_NAME,
2459: c_rec.SUBCONTRACTING_SOURCE_ORG,
2460: MSC_CL_COLLECTION.v_last_collection_id,
2461: c_rec.INHERIT_OC_OP_SEQ_NUM,
2462: MSC_CL_COLLECTION.v_current_date,
2463: MSC_CL_COLLECTION.v_current_user,
2464: MSC_CL_COLLECTION.v_current_date,
2465: MSC_CL_COLLECTION.v_current_user );
2466:
2459: c_rec.SUBCONTRACTING_SOURCE_ORG,
2460: MSC_CL_COLLECTION.v_last_collection_id,
2461: c_rec.INHERIT_OC_OP_SEQ_NUM,
2462: MSC_CL_COLLECTION.v_current_date,
2463: MSC_CL_COLLECTION.v_current_user,
2464: MSC_CL_COLLECTION.v_current_date,
2465: MSC_CL_COLLECTION.v_current_user );
2466:
2467: /************** LEGACY_CHANGE_START*************************/
2460: MSC_CL_COLLECTION.v_last_collection_id,
2461: c_rec.INHERIT_OC_OP_SEQ_NUM,
2462: MSC_CL_COLLECTION.v_current_date,
2463: MSC_CL_COLLECTION.v_current_user,
2464: MSC_CL_COLLECTION.v_current_date,
2465: MSC_CL_COLLECTION.v_current_user );
2466:
2467: /************** LEGACY_CHANGE_START*************************/
2468:
2461: c_rec.INHERIT_OC_OP_SEQ_NUM,
2462: MSC_CL_COLLECTION.v_current_date,
2463: MSC_CL_COLLECTION.v_current_user,
2464: MSC_CL_COLLECTION.v_current_date,
2465: MSC_CL_COLLECTION.v_current_user );
2466:
2467: /************** LEGACY_CHANGE_START*************************/
2468:
2469: -- added for Legacy and Exchange
2467: /************** LEGACY_CHANGE_START*************************/
2468:
2469: -- added for Legacy and Exchange
2470:
2471: IF MSC_CL_COLLECTION.v_is_legacy_refresh THEN -- change for l-flow
2472:
2473: lv_exist := 0;
2474:
2475: OPEN c_org_exist FOR
2491: ENABLED_FLAG)
2492: VALUES
2493: (c_rec.SR_INSTANCE_ID,
2494: c_rec.SR_TP_ID,
2495: MSC_CL_COLLECTION.v_current_date,
2496: MSC_CL_COLLECTION.v_current_user,
2497: MSC_CL_COLLECTION.v_current_date,
2498: MSC_CL_COLLECTION.v_current_user,
2499: 1);
2492: VALUES
2493: (c_rec.SR_INSTANCE_ID,
2494: c_rec.SR_TP_ID,
2495: MSC_CL_COLLECTION.v_current_date,
2496: MSC_CL_COLLECTION.v_current_user,
2497: MSC_CL_COLLECTION.v_current_date,
2498: MSC_CL_COLLECTION.v_current_user,
2499: 1);
2500: END IF;
2493: (c_rec.SR_INSTANCE_ID,
2494: c_rec.SR_TP_ID,
2495: MSC_CL_COLLECTION.v_current_date,
2496: MSC_CL_COLLECTION.v_current_user,
2497: MSC_CL_COLLECTION.v_current_date,
2498: MSC_CL_COLLECTION.v_current_user,
2499: 1);
2500: END IF;
2501:
2494: c_rec.SR_TP_ID,
2495: MSC_CL_COLLECTION.v_current_date,
2496: MSC_CL_COLLECTION.v_current_user,
2497: MSC_CL_COLLECTION.v_current_date,
2498: MSC_CL_COLLECTION.v_current_user,
2499: 1);
2500: END IF;
2501:
2502: lv_exist:= 0;
2541: 1,
2542: 1,
2543: 1,
2544: 1,
2545: MSC_CL_COLLECTION.v_current_date,
2546: MSC_CL_COLLECTION.v_current_user,
2547: MSC_CL_COLLECTION.v_current_date,
2548: MSC_CL_COLLECTION.v_current_user );
2549: END IF;
2542: 1,
2543: 1,
2544: 1,
2545: MSC_CL_COLLECTION.v_current_date,
2546: MSC_CL_COLLECTION.v_current_user,
2547: MSC_CL_COLLECTION.v_current_date,
2548: MSC_CL_COLLECTION.v_current_user );
2549: END IF;
2550:
2543: 1,
2544: 1,
2545: MSC_CL_COLLECTION.v_current_date,
2546: MSC_CL_COLLECTION.v_current_user,
2547: MSC_CL_COLLECTION.v_current_date,
2548: MSC_CL_COLLECTION.v_current_user );
2549: END IF;
2550:
2551: END IF;
2544: 1,
2545: MSC_CL_COLLECTION.v_current_date,
2546: MSC_CL_COLLECTION.v_current_user,
2547: MSC_CL_COLLECTION.v_current_date,
2548: MSC_CL_COLLECTION.v_current_user );
2549: END IF;
2550:
2551: END IF;
2552:
2567: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2568: RAISE;
2569:
2570: ELSE
2571: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2572:
2573: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2574: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2575: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2596: COMMIT;
2597:
2598: -- Organization Site
2599:
2600: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2601:
2602: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_TRADING_PARTNER_SITES', MSC_CL_COLLECTION.v_instance_id, NULL,
2603: 'AND PARTNER_TYPE=3');
2604:
2598: -- Organization Site
2599:
2600: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2601:
2602: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_TRADING_PARTNER_SITES', MSC_CL_COLLECTION.v_instance_id, NULL,
2603: 'AND PARTNER_TYPE=3');
2604:
2605: END IF;
2606:
2607: FOR c_rec IN c2 LOOP
2608:
2609: BEGIN
2610:
2611: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2612:
2613: UPDATE MSC_TRADING_PARTNER_SITES
2614: SET
2615: PARTNER_ADDRESS= c_rec.PARTNER_ADDRESS,
2617: LOCATION= c_rec.LOCATION,
2618: LONGITUDE= c_rec.LONGITUDE,
2619: LATITUDE= c_rec.LATITUDE,
2620: DELETED_FLAG= MSC_UTIL.SYS_NO,
2621: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2622: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2623: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2624: WHERE PARTNER_TYPE= 3
2625: AND SR_TP_ID= c_rec.SR_TP_ID
2618: LONGITUDE= c_rec.LONGITUDE,
2619: LATITUDE= c_rec.LATITUDE,
2620: DELETED_FLAG= MSC_UTIL.SYS_NO,
2621: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2622: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2623: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2624: WHERE PARTNER_TYPE= 3
2625: AND SR_TP_ID= c_rec.SR_TP_ID
2626: AND SR_TP_SITE_ID= c_rec.SR_TP_SITE_ID
2619: LATITUDE= c_rec.LATITUDE,
2620: DELETED_FLAG= MSC_UTIL.SYS_NO,
2621: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2622: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2623: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2624: WHERE PARTNER_TYPE= 3
2625: AND SR_TP_ID= c_rec.SR_TP_ID
2626: AND SR_TP_SITE_ID= c_rec.SR_TP_SITE_ID
2627: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
2627: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
2628:
2629: END IF;
2630:
2631: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2632:
2633: INSERT INTO MSC_Trading_Partner_Sites
2634: ( PARTNER_ID,
2635: PARTNER_SITE_ID,
2660: c_rec.SR_INSTANCE_ID,
2661: c_rec.TP_SITE_CODE,
2662: c_rec.LOCATION,
2663: MSC_UTIL.SYS_NO,
2664: MSC_CL_COLLECTION.v_last_collection_id,
2665: MSC_CL_COLLECTION.v_current_date,
2666: MSC_CL_COLLECTION.v_current_user,
2667: MSC_CL_COLLECTION.v_current_date,
2668: MSC_CL_COLLECTION.v_current_user );
2661: c_rec.TP_SITE_CODE,
2662: c_rec.LOCATION,
2663: MSC_UTIL.SYS_NO,
2664: MSC_CL_COLLECTION.v_last_collection_id,
2665: MSC_CL_COLLECTION.v_current_date,
2666: MSC_CL_COLLECTION.v_current_user,
2667: MSC_CL_COLLECTION.v_current_date,
2668: MSC_CL_COLLECTION.v_current_user );
2669:
2662: c_rec.LOCATION,
2663: MSC_UTIL.SYS_NO,
2664: MSC_CL_COLLECTION.v_last_collection_id,
2665: MSC_CL_COLLECTION.v_current_date,
2666: MSC_CL_COLLECTION.v_current_user,
2667: MSC_CL_COLLECTION.v_current_date,
2668: MSC_CL_COLLECTION.v_current_user );
2669:
2670: END IF;
2663: MSC_UTIL.SYS_NO,
2664: MSC_CL_COLLECTION.v_last_collection_id,
2665: MSC_CL_COLLECTION.v_current_date,
2666: MSC_CL_COLLECTION.v_current_user,
2667: MSC_CL_COLLECTION.v_current_date,
2668: MSC_CL_COLLECTION.v_current_user );
2669:
2670: END IF;
2671:
2664: MSC_CL_COLLECTION.v_last_collection_id,
2665: MSC_CL_COLLECTION.v_current_date,
2666: MSC_CL_COLLECTION.v_current_user,
2667: MSC_CL_COLLECTION.v_current_date,
2668: MSC_CL_COLLECTION.v_current_user );
2669:
2670: END IF;
2671:
2672: EXCEPTION
2683: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2684: RAISE;
2685:
2686: ELSE
2687: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2688:
2689: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2690: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2691: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2710: END LOOP;
2711:
2712: COMMIT;
2713:
2714: 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
2715:
2716: DELETE MSC_LOCATION_ASSOCIATIONS
2717: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2718:
2713:
2714: 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
2715:
2716: DELETE MSC_LOCATION_ASSOCIATIONS
2717: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2718:
2719: -- MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_LOCATION_ASSOCIATIONS', MSC_CL_COLLECTION.v_instance_id, NULL);
2720:
2721: END IF;
2715:
2716: DELETE MSC_LOCATION_ASSOCIATIONS
2717: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2718:
2719: -- MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_LOCATION_ASSOCIATIONS', MSC_CL_COLLECTION.v_instance_id, NULL);
2720:
2721: END IF;
2722:
2723: FOR c_rec IN c3 LOOP
2723: FOR c_rec IN c3 LOOP
2724:
2725: BEGIN
2726:
2727: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_legacy_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2728:
2729: INSERT INTO MSC_LOCATION_ASSOCIATIONS
2730: ( LOCATION_ID,
2731: LOCATION_CODE,
2743: c_rec.PARTNER_ID,
2744: c_rec.PARTNER_SITE_ID,
2745: c_rec.organization_id,
2746: c_rec.SR_INSTANCE_ID,
2747: MSC_CL_COLLECTION.v_current_date,
2748: MSC_CL_COLLECTION.v_current_user,
2749: MSC_CL_COLLECTION.v_current_date,
2750: MSC_CL_COLLECTION.v_current_user );
2751:
2744: c_rec.PARTNER_SITE_ID,
2745: c_rec.organization_id,
2746: c_rec.SR_INSTANCE_ID,
2747: MSC_CL_COLLECTION.v_current_date,
2748: MSC_CL_COLLECTION.v_current_user,
2749: MSC_CL_COLLECTION.v_current_date,
2750: MSC_CL_COLLECTION.v_current_user );
2751:
2752: END IF;
2745: c_rec.organization_id,
2746: c_rec.SR_INSTANCE_ID,
2747: MSC_CL_COLLECTION.v_current_date,
2748: MSC_CL_COLLECTION.v_current_user,
2749: MSC_CL_COLLECTION.v_current_date,
2750: MSC_CL_COLLECTION.v_current_user );
2751:
2752: END IF;
2753:
2746: c_rec.SR_INSTANCE_ID,
2747: MSC_CL_COLLECTION.v_current_date,
2748: MSC_CL_COLLECTION.v_current_user,
2749: MSC_CL_COLLECTION.v_current_date,
2750: MSC_CL_COLLECTION.v_current_user );
2751:
2752: END IF;
2753:
2754: EXCEPTION
2766: RAISE;
2767:
2768: ELSE
2769:
2770: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2771:
2772: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2773: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2774: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2799: FOR c_rec IN c5 LOOP
2800:
2801: BEGIN
2802:
2803: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_legacy_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2804:
2805: INSERT INTO MSC_LOCATION_ASSOCIATIONS
2806: ( LOCATION_ID,
2807: LOCATION_CODE,
2819: c_rec.PARTNER_ID,
2820: c_rec.PARTNER_SITE_ID,
2821: c_rec.ORGANIZATION_ID,
2822: c_rec.SR_INSTANCE_ID,
2823: MSC_CL_COLLECTION.v_current_date,
2824: MSC_CL_COLLECTION.v_current_user,
2825: MSC_CL_COLLECTION.v_current_date,
2826: MSC_CL_COLLECTION.v_current_user);
2827:
2820: c_rec.PARTNER_SITE_ID,
2821: c_rec.ORGANIZATION_ID,
2822: c_rec.SR_INSTANCE_ID,
2823: MSC_CL_COLLECTION.v_current_date,
2824: MSC_CL_COLLECTION.v_current_user,
2825: MSC_CL_COLLECTION.v_current_date,
2826: MSC_CL_COLLECTION.v_current_user);
2827:
2828: END IF;
2821: c_rec.ORGANIZATION_ID,
2822: c_rec.SR_INSTANCE_ID,
2823: MSC_CL_COLLECTION.v_current_date,
2824: MSC_CL_COLLECTION.v_current_user,
2825: MSC_CL_COLLECTION.v_current_date,
2826: MSC_CL_COLLECTION.v_current_user);
2827:
2828: END IF;
2829:
2822: c_rec.SR_INSTANCE_ID,
2823: MSC_CL_COLLECTION.v_current_date,
2824: MSC_CL_COLLECTION.v_current_user,
2825: MSC_CL_COLLECTION.v_current_date,
2826: MSC_CL_COLLECTION.v_current_user);
2827:
2828: END IF;
2829:
2830: EXCEPTION
2840: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2841: RAISE;
2842:
2843: ELSE
2844: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2845:
2846: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2847: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2848: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2869: COMMIT;
2870:
2871: -- Partner Contacts
2872:
2873: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_legacy_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2874:
2875: IF NOT MSC_CL_COLLECTION.v_is_legacy_refresh THEN
2876: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PARTNER_CONTACTS', MSC_CL_COLLECTION.v_instance_id, NULL);
2877: END IF;
2871: -- Partner Contacts
2872:
2873: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_legacy_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2874:
2875: IF NOT MSC_CL_COLLECTION.v_is_legacy_refresh THEN
2876: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PARTNER_CONTACTS', MSC_CL_COLLECTION.v_instance_id, NULL);
2877: END IF;
2878:
2879: FOR c_rec IN c4 LOOP
2872:
2873: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_legacy_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2874:
2875: IF NOT MSC_CL_COLLECTION.v_is_legacy_refresh THEN
2876: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PARTNER_CONTACTS', MSC_CL_COLLECTION.v_instance_id, NULL);
2877: END IF;
2878:
2879: FOR c_rec IN c4 LOOP
2880:
2877: END IF;
2878:
2879: FOR c_rec IN c4 LOOP
2880:
2881: if(MSC_CL_COLLECTION.v_is_legacy_refresh) then
2882:
2883: if (c_rec.DELETED_FLAG =MSC_UTIL.SYS_YES) then
2884:
2885: delete from MSC_PARTNER_CONTACTS where
2885: delete from MSC_PARTNER_CONTACTS where
2886: partner_id=c_rec.PARTNER_ID
2887: and nvl(partner_site_id,-99999)=nvl(c_rec.PARTNER_SITE_ID,-99999)
2888: and partner_type=c_rec.PARTNER_TYPE
2889: and SR_INSTANCE_ID =MSC_CL_COLLECTION.v_instance_id
2890: and c_rec.DELETED_FLAG =MSC_UTIL.SYS_YES;
2891: else
2892: UPDATE MSC_PARTNER_CONTACTS
2893: set Name=c_rec.NAME,
2894: DISPLAY_NAME =c_rec.DISPLAY_NAME,
2895: EMAIL = c_rec.EMAIL,
2896: FAX = c_rec.FAX,
2897: ENABLED_FLAG =c_rec.ENABLED_FLAG,
2898: REFRESH_NUMBER =MSC_CL_COLLECTION.v_last_collection_id,
2899: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
2900: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
2901: CREATION_DATE =MSC_CL_COLLECTION.v_current_date,
2902: CREATED_BY =MSC_CL_COLLECTION.v_current_user
2895: EMAIL = c_rec.EMAIL,
2896: FAX = c_rec.FAX,
2897: ENABLED_FLAG =c_rec.ENABLED_FLAG,
2898: REFRESH_NUMBER =MSC_CL_COLLECTION.v_last_collection_id,
2899: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
2900: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
2901: CREATION_DATE =MSC_CL_COLLECTION.v_current_date,
2902: CREATED_BY =MSC_CL_COLLECTION.v_current_user
2903: where
2896: FAX = c_rec.FAX,
2897: ENABLED_FLAG =c_rec.ENABLED_FLAG,
2898: REFRESH_NUMBER =MSC_CL_COLLECTION.v_last_collection_id,
2899: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
2900: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
2901: CREATION_DATE =MSC_CL_COLLECTION.v_current_date,
2902: CREATED_BY =MSC_CL_COLLECTION.v_current_user
2903: where
2904: partner_id=c_rec.PARTNER_ID
2897: ENABLED_FLAG =c_rec.ENABLED_FLAG,
2898: REFRESH_NUMBER =MSC_CL_COLLECTION.v_last_collection_id,
2899: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
2900: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
2901: CREATION_DATE =MSC_CL_COLLECTION.v_current_date,
2902: CREATED_BY =MSC_CL_COLLECTION.v_current_user
2903: where
2904: partner_id=c_rec.PARTNER_ID
2905: and nvl(partner_site_id,-99999)=nvl(c_rec.PARTNER_SITE_ID,-99999)
2898: REFRESH_NUMBER =MSC_CL_COLLECTION.v_last_collection_id,
2899: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
2900: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
2901: CREATION_DATE =MSC_CL_COLLECTION.v_current_date,
2902: CREATED_BY =MSC_CL_COLLECTION.v_current_user
2903: where
2904: partner_id=c_rec.PARTNER_ID
2905: and nvl(partner_site_id,-99999)=nvl(c_rec.PARTNER_SITE_ID,-99999)
2906: and partner_type=c_rec.PARTNER_TYPE
2903: where
2904: partner_id=c_rec.PARTNER_ID
2905: and nvl(partner_site_id,-99999)=nvl(c_rec.PARTNER_SITE_ID,-99999)
2906: and partner_type=c_rec.PARTNER_TYPE
2907: and SR_INSTANCE_ID =MSC_CL_COLLECTION.v_instance_id;
2908: END IF ;
2909:
2910: END IF ;
2911:
2909:
2910: END IF ;
2911:
2912:
2913: 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)
2914: THEN
2915: IF lv_old_partner_id <> NVL(c_rec.partner_id,0) OR
2916: lv_old_partner_site_id <> NVL(c_rec.partner_site_id,0) OR
2917: lv_old_partner_type <> c_rec.partner_type THEN
2941: c_rec.DISPLAY_NAME,
2942: c_rec.EMAIL,
2943: c_rec.FAX,
2944: c_rec.ENABLED_FLAG,
2945: MSC_CL_COLLECTION.v_instance_id,
2946: MSC_CL_COLLECTION.v_last_collection_id,
2947: MSC_CL_COLLECTION.v_current_date,
2948: MSC_CL_COLLECTION.v_current_user,
2949: MSC_CL_COLLECTION.v_current_date,
2942: c_rec.EMAIL,
2943: c_rec.FAX,
2944: c_rec.ENABLED_FLAG,
2945: MSC_CL_COLLECTION.v_instance_id,
2946: MSC_CL_COLLECTION.v_last_collection_id,
2947: MSC_CL_COLLECTION.v_current_date,
2948: MSC_CL_COLLECTION.v_current_user,
2949: MSC_CL_COLLECTION.v_current_date,
2950: MSC_CL_COLLECTION.v_current_user );
2943: c_rec.FAX,
2944: c_rec.ENABLED_FLAG,
2945: MSC_CL_COLLECTION.v_instance_id,
2946: MSC_CL_COLLECTION.v_last_collection_id,
2947: MSC_CL_COLLECTION.v_current_date,
2948: MSC_CL_COLLECTION.v_current_user,
2949: MSC_CL_COLLECTION.v_current_date,
2950: MSC_CL_COLLECTION.v_current_user );
2951:
2944: c_rec.ENABLED_FLAG,
2945: MSC_CL_COLLECTION.v_instance_id,
2946: MSC_CL_COLLECTION.v_last_collection_id,
2947: MSC_CL_COLLECTION.v_current_date,
2948: MSC_CL_COLLECTION.v_current_user,
2949: MSC_CL_COLLECTION.v_current_date,
2950: MSC_CL_COLLECTION.v_current_user );
2951:
2952: EXCEPTION
2945: MSC_CL_COLLECTION.v_instance_id,
2946: MSC_CL_COLLECTION.v_last_collection_id,
2947: MSC_CL_COLLECTION.v_current_date,
2948: MSC_CL_COLLECTION.v_current_user,
2949: MSC_CL_COLLECTION.v_current_date,
2950: MSC_CL_COLLECTION.v_current_user );
2951:
2952: EXCEPTION
2953: WHEN OTHERS THEN
2946: MSC_CL_COLLECTION.v_last_collection_id,
2947: MSC_CL_COLLECTION.v_current_date,
2948: MSC_CL_COLLECTION.v_current_user,
2949: MSC_CL_COLLECTION.v_current_date,
2950: MSC_CL_COLLECTION.v_current_user );
2951:
2952: EXCEPTION
2953: WHEN OTHERS THEN
2954: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2962: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2963: RAISE;
2964:
2965: ELSE
2966: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2967:
2968: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2969: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2970: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
3035: msp.REPETITIVE_BUCKET_SIZE3,
3036: msp.REPETITIVE_ANCHOR_DATE,
3037: msp.SR_INSTANCE_ID
3038: FROM MSC_ST_PARAMETERS msp
3039: WHERE msp.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
3040:
3041: c_count NUMBER:= 0;
3042:
3043: BEGIN
3041: c_count NUMBER:= 0;
3042:
3043: BEGIN
3044:
3045: --IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
3046:
3047: --DELETE FROM MSC_PARAMETERS
3048: -- WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
3049: --END IF;
3044:
3045: --IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
3046:
3047: --DELETE FROM MSC_PARAMETERS
3048: -- WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
3049: --END IF;
3050:
3051: FOR c_rec IN c1 LOOP
3052:
3074: REPETITIVE_BUCKET_SIZE1= c_rec.REPETITIVE_BUCKET_SIZE1,
3075: REPETITIVE_BUCKET_SIZE2= c_rec.REPETITIVE_BUCKET_SIZE2,
3076: REPETITIVE_BUCKET_SIZE3= c_rec.REPETITIVE_BUCKET_SIZE3,
3077: REPETITIVE_ANCHOR_DATE= c_rec.REPETITIVE_ANCHOR_DATE,
3078: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3079: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3080: WHERE SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3081: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID;
3082: /* Bug: 1993151 remove the collected flag from the update statement */
3075: REPETITIVE_BUCKET_SIZE2= c_rec.REPETITIVE_BUCKET_SIZE2,
3076: REPETITIVE_BUCKET_SIZE3= c_rec.REPETITIVE_BUCKET_SIZE3,
3077: REPETITIVE_ANCHOR_DATE= c_rec.REPETITIVE_ANCHOR_DATE,
3078: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3079: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3080: WHERE SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3081: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID;
3082: /* Bug: 1993151 remove the collected flag from the update statement */
3083: -- AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
3139: c_rec.REPETITIVE_ANCHOR_DATE,
3140: 1,
3141: MSC_UTIL.SYS_YES,
3142: c_rec.SR_INSTANCE_ID,
3143: MSC_CL_COLLECTION.v_current_date,
3144: MSC_CL_COLLECTION.v_current_user,
3145: MSC_CL_COLLECTION.v_current_date,
3146: MSC_CL_COLLECTION.v_current_user );
3147:
3140: 1,
3141: MSC_UTIL.SYS_YES,
3142: c_rec.SR_INSTANCE_ID,
3143: MSC_CL_COLLECTION.v_current_date,
3144: MSC_CL_COLLECTION.v_current_user,
3145: MSC_CL_COLLECTION.v_current_date,
3146: MSC_CL_COLLECTION.v_current_user );
3147:
3148: END IF;
3141: MSC_UTIL.SYS_YES,
3142: c_rec.SR_INSTANCE_ID,
3143: MSC_CL_COLLECTION.v_current_date,
3144: MSC_CL_COLLECTION.v_current_user,
3145: MSC_CL_COLLECTION.v_current_date,
3146: MSC_CL_COLLECTION.v_current_user );
3147:
3148: END IF;
3149:
3142: c_rec.SR_INSTANCE_ID,
3143: MSC_CL_COLLECTION.v_current_date,
3144: MSC_CL_COLLECTION.v_current_user,
3145: MSC_CL_COLLECTION.v_current_date,
3146: MSC_CL_COLLECTION.v_current_user );
3147:
3148: END IF;
3149:
3150: EXCEPTION
3163: RAISE;
3164:
3165: ELSE
3166:
3167: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3168:
3169: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3170: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3171: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PARAMETER');
3175: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3176: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3177: FND_MESSAGE.SET_TOKEN('VALUE',
3178: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3179: MSC_CL_COLLECTION.v_instance_id));
3180: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3181:
3182: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3183: END IF;
3204: msuom.DISABLE_DATE,
3205: msuom.DESCRIPTION,
3206: msuom.SR_INSTANCE_ID
3207: from MSC_ST_UNITS_OF_MEASURE msuom
3208: where msuom.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3209: order by UNIT_OF_MEASURE; /* use order by to avoid dead locking */
3210:
3211: CURSOR c2 IS
3212: select
3225: MSC_ST_UOM_CLASS_CONVERSIONS msucc
3226: WHERE t1.SR_INVENTORY_ITEM_ID(+)= msucc.Inventory_Item_ID
3227: AND t1.sr_instance_id(+)= msucc.sr_instance_id
3228: AND DECODE( t1.INVENTORY_ITEM_ID, NULL, msucc.Inventory_ITEM_ID,0 )= 0
3229: AND msucc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3230: ORDER BY
3231: 1,
3232: msucc.FROM_UNIT_OF_MEASURE,
3233: msucc.TO_UNIT_OF_MEASURE;
3247: MSC_ST_UOM_CONVERSIONS msuc
3248: WHERE t1.SR_INVENTORY_ITEM_ID(+)= msuc.Inventory_Item_ID
3249: AND t1.sr_instance_id(+)= msuc.sr_instance_id
3250: AND DECODE( t1.INVENTORY_ITEM_ID, NULL, msuc.Inventory_ITEM_ID,0 )= 0
3251: AND msuc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3252: ORDER BY
3253: 4,1;
3254:
3255: c_count NUMBER:= 0;
3256:
3257: BEGIN
3258:
3259: /*
3260: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
3261:
3262: DELETE FROM MSC_UNITS_OF_MEASURE
3263: WHERE SR_INSTANCE_ID IN ( MSC_CL_COLLECTION.v_instance_id, -MSC_CL_COLLECTION.v_instance_id);
3264:
3259: /*
3260: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
3261:
3262: DELETE FROM MSC_UNITS_OF_MEASURE
3263: WHERE SR_INSTANCE_ID IN ( MSC_CL_COLLECTION.v_instance_id, -MSC_CL_COLLECTION.v_instance_id);
3264:
3265: END IF;
3266: */
3267:
3277: muom.BASE_UOM_FLAG= c_rec.BASE_UOM_FLAG,
3278: muom.DISABLE_DATE= c_rec.DISABLE_DATE,
3279: muom.DESCRIPTION= c_rec.DESCRIPTION,
3280: muom.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
3281: muom.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3282: muom.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3283: muom.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3284: WHERE muom.UNIT_OF_MEASURE= c_rec.UNIT_OF_MEASURE;
3285:
3278: muom.DISABLE_DATE= c_rec.DISABLE_DATE,
3279: muom.DESCRIPTION= c_rec.DESCRIPTION,
3280: muom.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
3281: muom.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3282: muom.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3283: muom.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3284: WHERE muom.UNIT_OF_MEASURE= c_rec.UNIT_OF_MEASURE;
3285:
3286: IF SQL%NOTFOUND THEN
3279: muom.DESCRIPTION= c_rec.DESCRIPTION,
3280: muom.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
3281: muom.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3282: muom.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3283: muom.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3284: WHERE muom.UNIT_OF_MEASURE= c_rec.UNIT_OF_MEASURE;
3285:
3286: IF SQL%NOTFOUND THEN
3287:
3305: c_rec.BASE_UOM_FLAG,
3306: c_rec.DISABLE_DATE,
3307: c_rec.DESCRIPTION,
3308: c_rec.SR_INSTANCE_ID,
3309: MSC_CL_COLLECTION.v_last_collection_id,
3310: MSC_CL_COLLECTION.v_current_date,
3311: MSC_CL_COLLECTION.v_current_user,
3312: MSC_CL_COLLECTION.v_current_date,
3313: MSC_CL_COLLECTION.v_current_user );
3306: c_rec.DISABLE_DATE,
3307: c_rec.DESCRIPTION,
3308: c_rec.SR_INSTANCE_ID,
3309: MSC_CL_COLLECTION.v_last_collection_id,
3310: MSC_CL_COLLECTION.v_current_date,
3311: MSC_CL_COLLECTION.v_current_user,
3312: MSC_CL_COLLECTION.v_current_date,
3313: MSC_CL_COLLECTION.v_current_user );
3314:
3307: c_rec.DESCRIPTION,
3308: c_rec.SR_INSTANCE_ID,
3309: MSC_CL_COLLECTION.v_last_collection_id,
3310: MSC_CL_COLLECTION.v_current_date,
3311: MSC_CL_COLLECTION.v_current_user,
3312: MSC_CL_COLLECTION.v_current_date,
3313: MSC_CL_COLLECTION.v_current_user );
3314:
3315: END IF;
3308: c_rec.SR_INSTANCE_ID,
3309: MSC_CL_COLLECTION.v_last_collection_id,
3310: MSC_CL_COLLECTION.v_current_date,
3311: MSC_CL_COLLECTION.v_current_user,
3312: MSC_CL_COLLECTION.v_current_date,
3313: MSC_CL_COLLECTION.v_current_user );
3314:
3315: END IF;
3316:
3309: MSC_CL_COLLECTION.v_last_collection_id,
3310: MSC_CL_COLLECTION.v_current_date,
3311: MSC_CL_COLLECTION.v_current_user,
3312: MSC_CL_COLLECTION.v_current_date,
3313: MSC_CL_COLLECTION.v_current_user );
3314:
3315: END IF;
3316:
3317: c_count:= c_count+1;
3315: END IF;
3316:
3317: c_count:= c_count+1;
3318:
3319: IF c_count> MSC_CL_COLLECTION.PBS THEN
3320: COMMIT;
3321: c_count:= 0;
3322: END IF;
3323:
3336: RAISE;
3337:
3338: ELSE
3339:
3340: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3341:
3342: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3343: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3344: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3364:
3365: COMMIT;
3366:
3367: /*
3368: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
3369:
3370: DELETE FROM MSC_UOM_CLASS_CONVERSIONS
3371: WHERE SR_INSTANCE_ID IN ( MSC_CL_COLLECTION.v_instance_id, -MSC_CL_COLLECTION.v_instance_id);
3372:
3367: /*
3368: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
3369:
3370: DELETE FROM MSC_UOM_CLASS_CONVERSIONS
3371: WHERE SR_INSTANCE_ID IN ( MSC_CL_COLLECTION.v_instance_id, -MSC_CL_COLLECTION.v_instance_id);
3372:
3373: COMMIT;
3374:
3375: END IF;
3388: mucc.TO_UOM_CLASS= c_rec.TO_UOM_CLASS,
3389: mucc.CONVERSION_RATE= c_rec.CONVERSION_RATE,
3390: mucc.DISABLE_DATE= c_rec.DISABLE_DATE,
3391: mucc.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
3392: mucc.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3393: mucc.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3394: mucc.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3395: WHERE mucc.INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
3396: AND mucc.FROM_UNIT_OF_MEASURE= c_rec.FROM_UNIT_OF_MEASURE
3389: mucc.CONVERSION_RATE= c_rec.CONVERSION_RATE,
3390: mucc.DISABLE_DATE= c_rec.DISABLE_DATE,
3391: mucc.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
3392: mucc.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3393: mucc.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3394: mucc.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3395: WHERE mucc.INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
3396: AND mucc.FROM_UNIT_OF_MEASURE= c_rec.FROM_UNIT_OF_MEASURE
3397: AND mucc.TO_UNIT_OF_MEASURE= c_rec.TO_UNIT_OF_MEASURE;
3390: mucc.DISABLE_DATE= c_rec.DISABLE_DATE,
3391: mucc.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
3392: mucc.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3393: mucc.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3394: mucc.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3395: WHERE mucc.INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
3396: AND mucc.FROM_UNIT_OF_MEASURE= c_rec.FROM_UNIT_OF_MEASURE
3397: AND mucc.TO_UNIT_OF_MEASURE= c_rec.TO_UNIT_OF_MEASURE;
3398:
3424: c_rec.TO_UOM_CLASS,
3425: c_rec.CONVERSION_RATE,
3426: c_rec.DISABLE_DATE,
3427: c_rec.SR_INSTANCE_ID,
3428: MSC_CL_COLLECTION.v_last_collection_id,
3429: MSC_CL_COLLECTION.v_current_date,
3430: MSC_CL_COLLECTION.v_current_user,
3431: MSC_CL_COLLECTION.v_current_date,
3432: MSC_CL_COLLECTION.v_current_user );
3425: c_rec.CONVERSION_RATE,
3426: c_rec.DISABLE_DATE,
3427: c_rec.SR_INSTANCE_ID,
3428: MSC_CL_COLLECTION.v_last_collection_id,
3429: MSC_CL_COLLECTION.v_current_date,
3430: MSC_CL_COLLECTION.v_current_user,
3431: MSC_CL_COLLECTION.v_current_date,
3432: MSC_CL_COLLECTION.v_current_user );
3433:
3426: c_rec.DISABLE_DATE,
3427: c_rec.SR_INSTANCE_ID,
3428: MSC_CL_COLLECTION.v_last_collection_id,
3429: MSC_CL_COLLECTION.v_current_date,
3430: MSC_CL_COLLECTION.v_current_user,
3431: MSC_CL_COLLECTION.v_current_date,
3432: MSC_CL_COLLECTION.v_current_user );
3433:
3434: END IF;
3427: c_rec.SR_INSTANCE_ID,
3428: MSC_CL_COLLECTION.v_last_collection_id,
3429: MSC_CL_COLLECTION.v_current_date,
3430: MSC_CL_COLLECTION.v_current_user,
3431: MSC_CL_COLLECTION.v_current_date,
3432: MSC_CL_COLLECTION.v_current_user );
3433:
3434: END IF;
3435:
3428: MSC_CL_COLLECTION.v_last_collection_id,
3429: MSC_CL_COLLECTION.v_current_date,
3430: MSC_CL_COLLECTION.v_current_user,
3431: MSC_CL_COLLECTION.v_current_date,
3432: MSC_CL_COLLECTION.v_current_user );
3433:
3434: END IF;
3435:
3436: c_count:= c_count+1;
3434: END IF;
3435:
3436: c_count:= c_count+1;
3437:
3438: IF c_count> MSC_CL_COLLECTION.PBS THEN
3439: COMMIT;
3440: c_count:= 0;
3441: END IF;
3442:
3455: RAISE;
3456:
3457: ELSE
3458:
3459: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3460:
3461: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3462: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3463: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3488:
3489: COMMIT;
3490:
3491: /*
3492: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
3493:
3494: DELETE FROM MSC_UOM_CONVERSIONS
3495: WHERE SR_INSTANCE_ID IN ( MSC_CL_COLLECTION.v_instance_id, -MSC_CL_COLLECTION.v_instance_id);
3496:
3491: /*
3492: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
3493:
3494: DELETE FROM MSC_UOM_CONVERSIONS
3495: WHERE SR_INSTANCE_ID IN ( MSC_CL_COLLECTION.v_instance_id, -MSC_CL_COLLECTION.v_instance_id);
3496:
3497: COMMIT;
3498:
3499: END IF;
3516: CONVERSION_RATE= c_rec.CONVERSION_RATE,
3517: DEFAULT_CONVERSION_FLAGS= c_rec.DEFAULT_CONVERSION_FLAG,
3518: DISABLE_DATE= c_rec.DISABLE_DATE,
3519: SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
3520: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3521: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3522: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3523: WHERE muc.INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
3524: AND muc.UOM_CODE = c_rec.UOM_CODE;
3517: DEFAULT_CONVERSION_FLAGS= c_rec.DEFAULT_CONVERSION_FLAG,
3518: DISABLE_DATE= c_rec.DISABLE_DATE,
3519: SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
3520: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3521: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3522: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3523: WHERE muc.INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
3524: AND muc.UOM_CODE = c_rec.UOM_CODE;
3525:
3518: DISABLE_DATE= c_rec.DISABLE_DATE,
3519: SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
3520: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3521: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3522: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3523: WHERE muc.INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
3524: AND muc.UOM_CODE = c_rec.UOM_CODE;
3525:
3526: IF SQL%NOTFOUND THEN
3547: c_rec.CONVERSION_RATE,
3548: c_rec.DEFAULT_CONVERSION_FLAG,
3549: c_rec.DISABLE_DATE,
3550: c_rec.SR_INSTANCE_ID,
3551: MSC_CL_COLLECTION.v_last_collection_id,
3552: MSC_CL_COLLECTION.v_current_date,
3553: MSC_CL_COLLECTION.v_current_user,
3554: MSC_CL_COLLECTION.v_current_date,
3555: MSC_CL_COLLECTION.v_current_user);
3548: c_rec.DEFAULT_CONVERSION_FLAG,
3549: c_rec.DISABLE_DATE,
3550: c_rec.SR_INSTANCE_ID,
3551: MSC_CL_COLLECTION.v_last_collection_id,
3552: MSC_CL_COLLECTION.v_current_date,
3553: MSC_CL_COLLECTION.v_current_user,
3554: MSC_CL_COLLECTION.v_current_date,
3555: MSC_CL_COLLECTION.v_current_user);
3556:
3549: c_rec.DISABLE_DATE,
3550: c_rec.SR_INSTANCE_ID,
3551: MSC_CL_COLLECTION.v_last_collection_id,
3552: MSC_CL_COLLECTION.v_current_date,
3553: MSC_CL_COLLECTION.v_current_user,
3554: MSC_CL_COLLECTION.v_current_date,
3555: MSC_CL_COLLECTION.v_current_user);
3556:
3557: END IF;
3550: c_rec.SR_INSTANCE_ID,
3551: MSC_CL_COLLECTION.v_last_collection_id,
3552: MSC_CL_COLLECTION.v_current_date,
3553: MSC_CL_COLLECTION.v_current_user,
3554: MSC_CL_COLLECTION.v_current_date,
3555: MSC_CL_COLLECTION.v_current_user);
3556:
3557: END IF;
3558:
3551: MSC_CL_COLLECTION.v_last_collection_id,
3552: MSC_CL_COLLECTION.v_current_date,
3553: MSC_CL_COLLECTION.v_current_user,
3554: MSC_CL_COLLECTION.v_current_date,
3555: MSC_CL_COLLECTION.v_current_user);
3556:
3557: END IF;
3558:
3559: c_count:= c_count+1;
3557: END IF;
3558:
3559: c_count:= c_count+1;
3560:
3561: IF c_count> MSC_CL_COLLECTION.PBS THEN
3562: COMMIT;
3563: c_count:= 0;
3564: END IF;
3565:
3578: RAISE;
3579:
3580: ELSE
3581:
3582: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3583:
3584: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3585: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3586: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3634: from
3635: msc_asl_auth_details
3636: where
3637: plan_id = -1 and
3638: sr_instance_id = MSC_CL_COLLECTION.v_instance_id ;
3639:
3640:
3641:
3642:
3651: p.ORGANIZATION_ID = a.ORGANIZATION_ID and
3652: p.SR_INSTANCE_ID = a.SR_INSTANCE_ID and
3653: p.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID and
3654: p.using_organization_id =a.using_organization_id and
3655: p.sr_instance_id = MSC_CL_COLLECTION.v_instance_id and
3656: p.plan_id = -1
3657: )
3658: and sr_instance_id = MSC_CL_COLLECTION.v_instance_id
3659: and plan_id = -1 ;
3654: p.using_organization_id =a.using_organization_id and
3655: p.sr_instance_id = MSC_CL_COLLECTION.v_instance_id and
3656: p.plan_id = -1
3657: )
3658: and sr_instance_id = MSC_CL_COLLECTION.v_instance_id
3659: and plan_id = -1 ;
3660: commit ;
3661:
3662:
3720: FROM MSC_TP_ID_LID mtil,
3721: MSC_ST_TRADING_PARTNERS mst
3722: WHERE mtil.SR_TP_ID= mst.SR_TP_ID
3723: AND mtil.SR_INSTANCE_ID= mst.SR_INSTANCE_ID
3724: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3725: AND mtil.Partner_Type= mst.Partner_Type
3726: AND mst.Partner_Type IN (1,2,4) --Vendor/Customer/Carrier
3727: /* SCE Change Starts */
3728: AND nvl(mst.company_id, -1) = mtil.sr_company_id
3747: FROM MSC_TP_SITE_ID_LID mtsil,
3748: MSC_ST_TRADING_PARTNER_SITES msts
3749: WHERE mtsil.SR_TP_SITE_ID= msts.SR_TP_SITE_ID
3750: AND mtsil.SR_INSTANCE_ID= msts.SR_INSTANCE_ID
3751: AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3752: AND mtsil.Partner_Type= msts.Partner_Type
3753: AND msts.Partner_Type IN (1,2) --Vendor/Customer
3754: /* SCE Change Starts */
3755: AND nvl(msts.company_id, -1) = mtsil.sr_company_id
3764: WHERE NOT EXISTS ( select 1
3765: from MSC_TRADING_PARTNERS mtp
3766: where mtp.Partner_Name= mst.Partner_Name
3767: and mtp.Partner_Type= mst.Partner_Type)
3768: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3769: AND mst.Partner_type IN (1,2)
3770: ORDER BY mst.Partner_Type,
3771: mst.Partner_Name; -- using ORDER BY to avoid dead lock
3772: */
3772: */
3773:
3774: -- ==== New Customers and Suppliers ====
3775: Cursor c5 IS
3776: SELECT decode(mc.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID, null, mc.company_id) company_id1,
3777: mst.partner_name partner_name,
3778: mst.partner_type partner_type
3779: from MSC_ST_TRADING_PARTNERS mst,
3780: MSC_COMPANIES MC
3777: mst.partner_name partner_name,
3778: mst.partner_type partner_type
3779: from MSC_ST_TRADING_PARTNERS mst,
3780: MSC_COMPANIES MC
3781: where nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
3782: and mst.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
3783: and mst.partner_type IN (1,2,4) --Vendor/Customer/Carrier
3784: /* SCE CHANGE STARTS */
3785: and nvl(mst.company_id , -1) = -1
3778: mst.partner_type partner_type
3779: from MSC_ST_TRADING_PARTNERS mst,
3780: MSC_COMPANIES MC
3781: where nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
3782: and mst.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
3783: and mst.partner_type IN (1,2,4) --Vendor/Customer/Carrier
3784: /* SCE CHANGE STARTS */
3785: and nvl(mst.company_id , -1) = -1
3786: /* SCE CHANGE ENDS */
3795: partner_name ;
3796:
3797: Cursor c5_tpname IS
3798: SELECT distinct mst.Partner_Name, mst.sr_tp_id, mst.sr_instance_id, mst.Partner_Type,
3799: decode(mc.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID, -1, mc.company_id) company_id1
3800: FROM MSC_ST_TRADING_PARTNERS mst,
3801: MSC_COMPANIES MC
3802: WHERE EXISTS ( select 1
3803: from MSC_TRADING_PARTNERS mtp
3803: from MSC_TRADING_PARTNERS mtp
3804: where mtp.sr_tp_id= mst.sr_tp_id
3805: and mtp.sr_instance_id= mst.sr_instance_id
3806: and mtp.Partner_Type= mst.Partner_Type
3807: and nvl(mtp.COMPANY_ID,MSC_CL_COLLECTION.G_MY_COMPANY_ID) = MC.COMPANY_ID
3808: and mtp.Partner_Name <> mst.Partner_Name)
3809: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3810: AND mst.Partner_type IN (1,2)
3811: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
3805: and mtp.sr_instance_id= mst.sr_instance_id
3806: and mtp.Partner_Type= mst.Partner_Type
3807: and nvl(mtp.COMPANY_ID,MSC_CL_COLLECTION.G_MY_COMPANY_ID) = MC.COMPANY_ID
3808: and mtp.Partner_Name <> mst.Partner_Name)
3809: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3810: AND mst.Partner_type IN (1,2)
3811: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
3812: AND nvl(mst.company_id , -1) = -1
3813: ORDER BY mst.Partner_Name; -- using ORDER BY to avoid dead lock
3807: and nvl(mtp.COMPANY_ID,MSC_CL_COLLECTION.G_MY_COMPANY_ID) = MC.COMPANY_ID
3808: and mtp.Partner_Name <> mst.Partner_Name)
3809: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3810: AND mst.Partner_type IN (1,2)
3811: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
3812: AND nvl(mst.company_id , -1) = -1
3813: ORDER BY mst.Partner_Name; -- using ORDER BY to avoid dead lock
3814:
3815:
3826:
3827: /* SCE CHANGE */
3828: AND nvl(msts.company_id, -1) = mtil.sr_company_id
3829:
3830: AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3831: AND msts.Partner_Type=1
3832: AND mtil.Partner_type=1
3833: ORDER BY mtil.TP_ID,
3834: msts.TP_Site_Code; -- using ORDER BY to avoid dead lock
3848: and mtps.TP_Site_Code= msts.TP_Site_Code
3849: and mtps.Location= msts.Location
3850: and mtps.Partner_ID= mtil.TP_ID)
3851: AND msts.SR_Instance_ID= mtil.SR_INSTANCE_ID
3852: AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3853: AND msts.SR_TP_ID= mtil.SR_TP_ID
3854: /* SCE CHANGE starts*/
3855: AND nvl(msts.company_id, -1) = mtil.sr_company_id
3856: /* SCE CHANGE ends*/
3877: and nvl(msts.company_id, -1) = mtsil.sr_company_id)
3878: AND msts.TP_Site_Code= mtp.TP_Site_Code
3879: AND msts.SR_TP_ID= mtil.SR_TP_ID
3880: AND msts.SR_INSTANCE_ID= mtil.SR_INSTANCE_ID
3881: AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3882: /* SCE Change starts */
3883: AND nvl(msts.company_id, -1) = mtil.SR_COMPANY_ID
3884: /* SCE changes ends */
3885: AND mtil.TP_ID= mtp.Partner_ID
3912: AND msts.TP_Site_Code= mtp.TP_Site_Code
3913: AND nvl(msts.Location, ' ')= nvl(mtp.Location, ' ')
3914: AND msts.SR_TP_ID= mtil.SR_TP_ID
3915: AND msts.SR_INSTANCE_ID= mtil.SR_INSTANCE_ID
3916: AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3917: /* SCE Change stars*/
3918: /* Added sr_company_id for SCE purpose */
3919: AND nvl(msts.company_id, -1) = mtil.SR_COMPANY_ID
3920: /* SCE Change stars*/
3948: and nvl( mst.company_id, -1) = nvl(mtil.sr_company_id, -1)
3949: and nvl( mst.company_id, -1) = -1)
3950: AND mst.Partner_NAME= mtp.Partner_NAME
3951: AND mst.Partner_Type= mtp.Partner_Type
3952: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3953: AND mst.Partner_Type IN ( 1, 2)
3954: /* SCE Change starts */
3955: -- Add join with msc_companies
3956: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
3952: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3953: AND mst.Partner_Type IN ( 1, 2)
3954: /* SCE Change starts */
3955: -- Add join with msc_companies
3956: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
3957: AND mc.company_id = nvl(mtp.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID );
3958: -- AND nvl( mst.company_id, -1) = -1; -- commented for aerox
3959: /* SCE Change ends */
3960:
3953: AND mst.Partner_Type IN ( 1, 2)
3954: /* SCE Change starts */
3955: -- Add join with msc_companies
3956: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
3957: AND mc.company_id = nvl(mtp.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID );
3958: -- AND nvl( mst.company_id, -1) = -1; -- commented for aerox
3959: /* SCE Change ends */
3960:
3961:
3962: -- ============ Cursor for UPDATE MSC_TP_ID_LID SRP Changes ==================== --
3963: CURSOR c13 IS
3964: Select resource_type, sr_instance_id , partner_type,sr_tp_id
3965: From msc_st_trading_partners
3966: Where sr_instance_id = MSC_CL_COLLECTION.v_instance_id
3967: And partner_type=2;
3968:
3969: lv_control_flag NUMBER;
3970: lv_msc_tp_coll_window NUMBER;
3981: BEGIN
3982:
3983:
3984: GET_COLL_PARAM (pINSTANCE_ID);
3985: MSC_CL_COLLECTION.INITIALIZE( pINSTANCE_ID);
3986: MSC_CL_COLLECTION.v_my_company_name := MSC_CL_SCE_COLLECTION.GET_MY_COMPANY;
3987:
3988:
3989: SELECT decode(nvl(fnd_profile.value('MSC_PURGE_ST_CONTROL'),'N'),'Y',1,2)
3982:
3983:
3984: GET_COLL_PARAM (pINSTANCE_ID);
3985: MSC_CL_COLLECTION.INITIALIZE( pINSTANCE_ID);
3986: MSC_CL_COLLECTION.v_my_company_name := MSC_CL_SCE_COLLECTION.GET_MY_COMPANY;
3987:
3988:
3989: SELECT decode(nvl(fnd_profile.value('MSC_PURGE_ST_CONTROL'),'N'),'Y',1,2)
3990: INTO lv_control_flag
3996: WHEN OTHERS THEN
3997: lv_msc_tp_coll_window := 0;
3998: END;
3999:
4000: IF (MSC_CL_COLLECTION.v_apps_ver = MSC_UTIL.G_APPS107) OR (MSC_CL_COLLECTION.v_apps_ver = MSC_UTIL.G_APPS110) OR lv_msc_tp_coll_window IS NULL THEN
4001: lv_msc_tp_coll_window := 0;
4002: END IF;
4003:
4004: begin
4019: lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4020: end;
4021:
4022: /* if complete refresh, regen the key mapping data */
4023: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
4024:
4025: IF lv_control_flag = 2 THEN
4026: IF lv_msc_tp_coll_window = 0 THEN
4027: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4023: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
4024:
4025: IF lv_control_flag = 2 THEN
4026: IF lv_msc_tp_coll_window = 0 THEN
4027: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4028: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4029: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1;
4030: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4031: END IF;
4024:
4025: IF lv_control_flag = 2 THEN
4026: IF lv_msc_tp_coll_window = 0 THEN
4027: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4028: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4029: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1;
4030: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4031: END IF;
4032:
4031: END IF;
4032:
4033: ELSE
4034: IF lv_msc_tp_coll_window = 0 THEN
4035: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4036: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4037: END IF;
4038:
4039: END IF;
4032:
4033: ELSE
4034: IF lv_msc_tp_coll_window = 0 THEN
4035: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4036: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4037: END IF;
4038:
4039: END IF;
4040: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4047: --In case of continuous and targetted collections, delete carrier records from MSC_TP_ID_LID
4048: --when sourcing SRS launch parameter is Yes --and delete supplier and customer records
4049: --from MSC_TP_ID_LID when either Supplier or Customer SRS launch parameter is Yes.
4050:
4051: IF (MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.V_IS_CONT_REFRESH) THEN
4052: IF (MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag=MSC_UTIL.SYS_YES) THEN
4053: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type=4;
4054: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type=4;
4055: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4048: --when sourcing SRS launch parameter is Yes --and delete supplier and customer records
4049: --from MSC_TP_ID_LID when either Supplier or Customer SRS launch parameter is Yes.
4050:
4051: IF (MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.V_IS_CONT_REFRESH) THEN
4052: IF (MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag=MSC_UTIL.SYS_YES) THEN
4053: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type=4;
4054: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type=4;
4055: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4056: END IF;
4049: --from MSC_TP_ID_LID when either Supplier or Customer SRS launch parameter is Yes.
4050:
4051: IF (MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.V_IS_CONT_REFRESH) THEN
4052: IF (MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag=MSC_UTIL.SYS_YES) THEN
4053: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type=4;
4054: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type=4;
4055: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4056: END IF;
4057: END IF;
4055: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4056: END IF;
4057: END IF;
4058:
4059: IF MSC_CL_COLLECTION.v_is_partial_refresh THEN
4060:
4061: IF ((MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag = MSC_UTIL.SYS_YES) or
4062: (MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag = MSC_UTIL.SYS_YES)) THEN
4063:
4057: END IF;
4058:
4059: IF MSC_CL_COLLECTION.v_is_partial_refresh THEN
4060:
4061: IF ((MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag = MSC_UTIL.SYS_YES) or
4062: (MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag = MSC_UTIL.SYS_YES)) THEN
4063:
4064: -- Note now vendor or customer cannot be refreshed
4065: -- separately. If that functionality needs to be provided
4058:
4059: IF MSC_CL_COLLECTION.v_is_partial_refresh THEN
4060:
4061: IF ((MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag = MSC_UTIL.SYS_YES) or
4062: (MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag = MSC_UTIL.SYS_YES)) THEN
4063:
4064: -- Note now vendor or customer cannot be refreshed
4065: -- separately. If that functionality needs to be provided
4066: -- in future then the the ID_LID tables will have to
4071: -- We do not delete data if the profile "MSC_COLLECTION_WINDOW_FOR_TP_CHANGES" is set to not null.
4072:
4073: IF lv_msc_tp_coll_window = 0 THEN
4074: IF lv_control_flag = 2 THEN
4075: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type in (1,2);
4076: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4077: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type in (1,2);
4078: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4079: ELSE
4072:
4073: IF lv_msc_tp_coll_window = 0 THEN
4074: IF lv_control_flag = 2 THEN
4075: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type in (1,2);
4076: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4077: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type in (1,2);
4078: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4079: ELSE
4080: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4076: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4077: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type in (1,2);
4078: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4079: ELSE
4080: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4081: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4082: END IF;
4083: END IF;
4084: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4077: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type in (1,2);
4078: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4079: ELSE
4080: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4081: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4082: END IF;
4083: END IF;
4084: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4085: lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4089:
4090: /*************** PREPLACE CHANGE END *****************/
4091:
4092: --agmcont
4093: IF MSC_CL_COLLECTION.V_IS_CONT_REFRESH THEN
4094:
4095: IF ((MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag = MSC_UTIL.SYS_YES) or
4096: (MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag = MSC_UTIL.SYS_YES)) THEN
4097:
4091:
4092: --agmcont
4093: IF MSC_CL_COLLECTION.V_IS_CONT_REFRESH THEN
4094:
4095: IF ((MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag = MSC_UTIL.SYS_YES) or
4096: (MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag = MSC_UTIL.SYS_YES)) THEN
4097:
4098: -- Note now vendor or customer cannot be refreshed
4099: -- separately. If that functionality needs to be provided
4092: --agmcont
4093: IF MSC_CL_COLLECTION.V_IS_CONT_REFRESH THEN
4094:
4095: IF ((MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag = MSC_UTIL.SYS_YES) or
4096: (MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag = MSC_UTIL.SYS_YES)) THEN
4097:
4098: -- Note now vendor or customer cannot be refreshed
4099: -- separately. If that functionality needs to be provided
4100: -- in future then the the ID_LID tables will have to
4104: -- data using the partner_type as a filter.
4105: -- We do not delete data if the profile "MSC_COLLECTION_WINDOW_FOR_TP_CHANGES" is set to not null.
4106: IF lv_msc_tp_coll_window = 0 THEN
4107: IF lv_control_flag = 2 THEN
4108: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type in (1,2);
4109: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4110: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type in (1,2);
4111: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4112: ELSE
4105: -- We do not delete data if the profile "MSC_COLLECTION_WINDOW_FOR_TP_CHANGES" is set to not null.
4106: IF lv_msc_tp_coll_window = 0 THEN
4107: IF lv_control_flag = 2 THEN
4108: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type in (1,2);
4109: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4110: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type in (1,2);
4111: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4112: ELSE
4113: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4109: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4110: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type in (1,2);
4111: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4112: ELSE
4113: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4114: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4115: END IF;
4116: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4117: lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4110: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type in (1,2);
4111: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4112: ELSE
4113: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4114: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4115: END IF;
4116: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4117: lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4118: END IF;
4204: */
4205:
4206: WHEN OTHERS THEN
4207:
4208: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4209:
4210: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4211: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4212: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'MSC_CL_SETUP_ODS_LOAD.TRANSFORM_KEYS');
4269: c_rec.Partner_Type,
4270: -1,
4271: -1,
4272: -1,
4273: MSC_CL_COLLECTION.v_last_collection_id,
4274: MSC_CL_COLLECTION.v_current_date,
4275: MSC_CL_COLLECTION.v_current_user,
4276: MSC_CL_COLLECTION.v_current_date,
4277: MSC_CL_COLLECTION.v_current_user );
4270: -1,
4271: -1,
4272: -1,
4273: MSC_CL_COLLECTION.v_last_collection_id,
4274: MSC_CL_COLLECTION.v_current_date,
4275: MSC_CL_COLLECTION.v_current_user,
4276: MSC_CL_COLLECTION.v_current_date,
4277: MSC_CL_COLLECTION.v_current_user );
4278:
4271: -1,
4272: -1,
4273: MSC_CL_COLLECTION.v_last_collection_id,
4274: MSC_CL_COLLECTION.v_current_date,
4275: MSC_CL_COLLECTION.v_current_user,
4276: MSC_CL_COLLECTION.v_current_date,
4277: MSC_CL_COLLECTION.v_current_user );
4278:
4279: EXCEPTION
4272: -1,
4273: MSC_CL_COLLECTION.v_last_collection_id,
4274: MSC_CL_COLLECTION.v_current_date,
4275: MSC_CL_COLLECTION.v_current_user,
4276: MSC_CL_COLLECTION.v_current_date,
4277: MSC_CL_COLLECTION.v_current_user );
4278:
4279: EXCEPTION
4280:
4273: MSC_CL_COLLECTION.v_last_collection_id,
4274: MSC_CL_COLLECTION.v_current_date,
4275: MSC_CL_COLLECTION.v_current_user,
4276: MSC_CL_COLLECTION.v_current_date,
4277: MSC_CL_COLLECTION.v_current_user );
4278:
4279: EXCEPTION
4280:
4281: WHEN DUP_VAL_ON_INDEX THEN
4308: COMMIT;
4309:
4310: lv_ins_records := 0;
4311: -- ==== populate msc_tp_id_lid with newly created Suppliers / Customers ====
4312: IF MSC_CL_COLLECTION.v_apps_ver < MSC_UTIL.G_APPS115 AND MSC_CL_COLLECTION.v_apps_ver <> -1 THEN
4313:
4314: /* For 107 and 110 the vendor_id and vendor
4315: site id can be duplicate, therefore we use
4316: the cursors to handle such exceptions, but for 11i we can use a straight
4369: RAISE;
4370:
4371: ELSE
4372:
4373: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4374:
4375: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4376: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4377: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4432: and nvl( mst.company_id, -1) = -1
4433: and mst.Partner_Type= mtil.Partner_Type)
4434: AND mst.Partner_NAME= mtp.Partner_NAME
4435: AND mst.Partner_Type= mtp.Partner_Type
4436: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
4437: AND mst.Partner_Type IN ( 1, 2,4)
4438: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
4439: AND mc.company_id = nvl(mtp.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID)
4440: and nvl( mst.company_id, -1) = -1;
4434: AND mst.Partner_NAME= mtp.Partner_NAME
4435: AND mst.Partner_Type= mtp.Partner_Type
4436: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
4437: AND mst.Partner_Type IN ( 1, 2,4)
4438: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
4439: AND mc.company_id = nvl(mtp.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID)
4440: and nvl( mst.company_id, -1) = -1;
4441: lv_ins_records := SQL%ROWCOUNT;
4442: END IF;
4435: AND mst.Partner_Type= mtp.Partner_Type
4436: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
4437: AND mst.Partner_Type IN ( 1, 2,4)
4438: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
4439: AND mc.company_id = nvl(mtp.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID)
4440: and nvl( mst.company_id, -1) = -1;
4441: lv_ins_records := SQL%ROWCOUNT;
4442: END IF;
4443:
4457: set
4458: resource_type = c_rec.resource_type
4459: WHERE sr_tp_id= c_rec.sr_tp_id And
4460: partner_type=2 And
4461: sr_instance_id = MSC_CL_COLLECTION.v_instance_id;
4462:
4463: END;
4464:
4465: END LOOP; --c13 crec loop
4490: c_rec.TP_ID,
4491: MSC_Trading_Partner_Sites_S.NEXTVAL,
4492: MSC_Trading_Partner_Sites_S.NEXTVAL,
4493: 1,
4494: MSC_CL_COLLECTION.v_instance_id,
4495: MSC_CL_COLLECTION.v_last_collection_id,
4496: MSC_CL_COLLECTION.v_current_date,
4497: MSC_CL_COLLECTION.v_current_user,
4498: MSC_CL_COLLECTION.v_current_date,
4491: MSC_Trading_Partner_Sites_S.NEXTVAL,
4492: MSC_Trading_Partner_Sites_S.NEXTVAL,
4493: 1,
4494: MSC_CL_COLLECTION.v_instance_id,
4495: MSC_CL_COLLECTION.v_last_collection_id,
4496: MSC_CL_COLLECTION.v_current_date,
4497: MSC_CL_COLLECTION.v_current_user,
4498: MSC_CL_COLLECTION.v_current_date,
4499: MSC_CL_COLLECTION.v_current_user);
4492: MSC_Trading_Partner_Sites_S.NEXTVAL,
4493: 1,
4494: MSC_CL_COLLECTION.v_instance_id,
4495: MSC_CL_COLLECTION.v_last_collection_id,
4496: MSC_CL_COLLECTION.v_current_date,
4497: MSC_CL_COLLECTION.v_current_user,
4498: MSC_CL_COLLECTION.v_current_date,
4499: MSC_CL_COLLECTION.v_current_user);
4500:
4493: 1,
4494: MSC_CL_COLLECTION.v_instance_id,
4495: MSC_CL_COLLECTION.v_last_collection_id,
4496: MSC_CL_COLLECTION.v_current_date,
4497: MSC_CL_COLLECTION.v_current_user,
4498: MSC_CL_COLLECTION.v_current_date,
4499: MSC_CL_COLLECTION.v_current_user);
4500:
4501: EXCEPTION
4494: MSC_CL_COLLECTION.v_instance_id,
4495: MSC_CL_COLLECTION.v_last_collection_id,
4496: MSC_CL_COLLECTION.v_current_date,
4497: MSC_CL_COLLECTION.v_current_user,
4498: MSC_CL_COLLECTION.v_current_date,
4499: MSC_CL_COLLECTION.v_current_user);
4500:
4501: EXCEPTION
4502:
4495: MSC_CL_COLLECTION.v_last_collection_id,
4496: MSC_CL_COLLECTION.v_current_date,
4497: MSC_CL_COLLECTION.v_current_user,
4498: MSC_CL_COLLECTION.v_current_date,
4499: MSC_CL_COLLECTION.v_current_user);
4500:
4501: EXCEPTION
4502:
4503: WHEN DUP_VAL_ON_INDEX THEN
4534:
4535: COMMIT;
4536: lv_ins_records := 0;
4537: -- ==== Populate msc_tp_site_id_lid with new Supplier Sites ====
4538: IF MSC_CL_COLLECTION.v_apps_ver < MSC_UTIL.G_APPS115 THEN
4539:
4540: /* For 107 and 110 the vendor_id and vendor
4541: site id can be duplicate, therefore we use
4542: the cursors to handle such exceptions, but for 11i we can use a straight
4564: EXCEPTION
4565:
4566: WHEN DUP_VAL_ON_INDEX THEN
4567:
4568: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4569:
4570: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4571: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4572: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4654: and nvl(msts.company_id, -1) = mtsil.sr_company_id)
4655: AND msts.TP_Site_Code= mtp.TP_Site_Code
4656: AND msts.SR_TP_ID= mtil.SR_TP_ID
4657: AND msts.SR_INSTANCE_ID= mtil.SR_INSTANCE_ID
4658: AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
4659: AND nvl(msts.company_id, -1) = mtil.SR_COMPANY_ID
4660: AND mtil.TP_ID= mtp.Partner_ID
4661: AND mtp.partner_type = mtil.partner_type
4662: AND mtil.Partner_Type= msts.partner_type
4699: c_rec.TP_ID,
4700: MSC_Trading_Partner_Sites_S.NEXTVAL,
4701: MSC_Trading_Partner_Sites_S.NEXTVAL,
4702: 2,
4703: MSC_CL_COLLECTION.v_instance_id,
4704: MSC_CL_COLLECTION.v_last_collection_id,
4705: MSC_CL_COLLECTION.v_current_date,
4706: MSC_CL_COLLECTION.v_current_user,
4707: MSC_CL_COLLECTION.v_current_date,
4700: MSC_Trading_Partner_Sites_S.NEXTVAL,
4701: MSC_Trading_Partner_Sites_S.NEXTVAL,
4702: 2,
4703: MSC_CL_COLLECTION.v_instance_id,
4704: MSC_CL_COLLECTION.v_last_collection_id,
4705: MSC_CL_COLLECTION.v_current_date,
4706: MSC_CL_COLLECTION.v_current_user,
4707: MSC_CL_COLLECTION.v_current_date,
4708: MSC_CL_COLLECTION.v_current_user );
4701: MSC_Trading_Partner_Sites_S.NEXTVAL,
4702: 2,
4703: MSC_CL_COLLECTION.v_instance_id,
4704: MSC_CL_COLLECTION.v_last_collection_id,
4705: MSC_CL_COLLECTION.v_current_date,
4706: MSC_CL_COLLECTION.v_current_user,
4707: MSC_CL_COLLECTION.v_current_date,
4708: MSC_CL_COLLECTION.v_current_user );
4709:
4702: 2,
4703: MSC_CL_COLLECTION.v_instance_id,
4704: MSC_CL_COLLECTION.v_last_collection_id,
4705: MSC_CL_COLLECTION.v_current_date,
4706: MSC_CL_COLLECTION.v_current_user,
4707: MSC_CL_COLLECTION.v_current_date,
4708: MSC_CL_COLLECTION.v_current_user );
4709:
4710: EXCEPTION
4703: MSC_CL_COLLECTION.v_instance_id,
4704: MSC_CL_COLLECTION.v_last_collection_id,
4705: MSC_CL_COLLECTION.v_current_date,
4706: MSC_CL_COLLECTION.v_current_user,
4707: MSC_CL_COLLECTION.v_current_date,
4708: MSC_CL_COLLECTION.v_current_user );
4709:
4710: EXCEPTION
4711:
4704: MSC_CL_COLLECTION.v_last_collection_id,
4705: MSC_CL_COLLECTION.v_current_date,
4706: MSC_CL_COLLECTION.v_current_user,
4707: MSC_CL_COLLECTION.v_current_date,
4708: MSC_CL_COLLECTION.v_current_user );
4709:
4710: EXCEPTION
4711:
4712: WHEN DUP_VAL_ON_INDEX THEN
4754:
4755: COMMIT;
4756:
4757: lv_ins_records := 0;
4758: IF MSC_CL_COLLECTION.v_apps_ver < MSC_UTIL.G_APPS115 AND MSC_CL_COLLECTION.v_apps_ver <> -1 THEN
4759:
4760: /* For 107 and 110 the vendor_id and vendor
4761: site id can be duplicate, therefore we use
4762: the cursors to handle such exceptions, but for 11i we can use a straight
4785: EXCEPTION
4786:
4787: WHEN DUP_VAL_ON_INDEX THEN
4788:
4789: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4790:
4791: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4792: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4793: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4878: AND msts.TP_Site_Code= mtp.TP_Site_Code
4879: AND msts.Location= mtp.Location
4880: AND msts.SR_TP_ID= mtil.SR_TP_ID
4881: AND msts.SR_INSTANCE_ID= mtil.SR_INSTANCE_ID
4882: AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
4883: AND nvl(msts.company_id, -1) = mtil.SR_COMPANY_ID
4884: AND mtil.TP_ID= mtp.Partner_ID
4885: AND mtp.partner_type = mtil.partner_type
4886: AND mtil.Partner_Type= msts.partner_type
4920: PROJECT_REFERENCE_ENABLED= c_rec.PROJECT_REFERENCE_ENABLED,
4921: PROJECT_CONTROL_LEVEL= c_rec.PROJECT_CONTROL_LEVEL,
4922: CUSTOMER_CLASS_CODE = c_rec.CUSTOMER_CLASS_CODE,
4923: CUSTOMER_TYPE = c_rec.CUSTOMER_TYPE,
4924: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
4925: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
4926: CREATION_DATE = MSC_CL_COLLECTION.v_current_date,
4927: CREATED_BY = MSC_CL_COLLECTION.v_current_user
4928: WHERE mtp.Partner_ID= c_rec.TP_ID;
4921: PROJECT_CONTROL_LEVEL= c_rec.PROJECT_CONTROL_LEVEL,
4922: CUSTOMER_CLASS_CODE = c_rec.CUSTOMER_CLASS_CODE,
4923: CUSTOMER_TYPE = c_rec.CUSTOMER_TYPE,
4924: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
4925: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
4926: CREATION_DATE = MSC_CL_COLLECTION.v_current_date,
4927: CREATED_BY = MSC_CL_COLLECTION.v_current_user
4928: WHERE mtp.Partner_ID= c_rec.TP_ID;
4929:
4922: CUSTOMER_CLASS_CODE = c_rec.CUSTOMER_CLASS_CODE,
4923: CUSTOMER_TYPE = c_rec.CUSTOMER_TYPE,
4924: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
4925: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
4926: CREATION_DATE = MSC_CL_COLLECTION.v_current_date,
4927: CREATED_BY = MSC_CL_COLLECTION.v_current_user
4928: WHERE mtp.Partner_ID= c_rec.TP_ID;
4929:
4930: EXCEPTION
4923: CUSTOMER_TYPE = c_rec.CUSTOMER_TYPE,
4924: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
4925: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
4926: CREATION_DATE = MSC_CL_COLLECTION.v_current_date,
4927: CREATED_BY = MSC_CL_COLLECTION.v_current_user
4928: WHERE mtp.Partner_ID= c_rec.TP_ID;
4929:
4930: EXCEPTION
4931:
4960: RAISE;
4961:
4962: ELSE
4963:
4964: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4965:
4966: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4967: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4968: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
5007: mtps.STATE = c_rec.STATE,
5008: mtps.COUNTRY = c_rec.COUNTRY,
5009: mtps.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
5010: mtps.DELETED_FLAG= MSC_UTIL.SYS_NO,
5011: mtps.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
5012: mtps.SHIPPING_CONTROL=c_rec.SHIPPING_CONTROL,
5013: mtps.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
5014: mtps.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
5015: mtps.CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
5009: mtps.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
5010: mtps.DELETED_FLAG= MSC_UTIL.SYS_NO,
5011: mtps.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
5012: mtps.SHIPPING_CONTROL=c_rec.SHIPPING_CONTROL,
5013: mtps.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
5014: mtps.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
5015: mtps.CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
5016: mtps.CREATED_BY= MSC_CL_COLLECTION.v_current_user
5017: WHERE mtps.PARTNER_SITE_ID= c_rec.TP_SITE_ID;
5010: mtps.DELETED_FLAG= MSC_UTIL.SYS_NO,
5011: mtps.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
5012: mtps.SHIPPING_CONTROL=c_rec.SHIPPING_CONTROL,
5013: mtps.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
5014: mtps.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
5015: mtps.CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
5016: mtps.CREATED_BY= MSC_CL_COLLECTION.v_current_user
5017: WHERE mtps.PARTNER_SITE_ID= c_rec.TP_SITE_ID;
5018:
5011: mtps.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
5012: mtps.SHIPPING_CONTROL=c_rec.SHIPPING_CONTROL,
5013: mtps.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
5014: mtps.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
5015: mtps.CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
5016: mtps.CREATED_BY= MSC_CL_COLLECTION.v_current_user
5017: WHERE mtps.PARTNER_SITE_ID= c_rec.TP_SITE_ID;
5018:
5019: EXCEPTION
5012: mtps.SHIPPING_CONTROL=c_rec.SHIPPING_CONTROL,
5013: mtps.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
5014: mtps.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
5015: mtps.CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
5016: mtps.CREATED_BY= MSC_CL_COLLECTION.v_current_user
5017: WHERE mtps.PARTNER_SITE_ID= c_rec.TP_SITE_ID;
5018:
5019: EXCEPTION
5020:
5045: RAISE;
5046:
5047: ELSE
5048:
5049: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
5050:
5051: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
5052: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
5053: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
5104:
5105: /* Initialize the global prec record variable */
5106:
5107:
5108: IF (MSC_CL_COLLECTION.v_prec_defined = FALSE) THEN
5109: SELECT delete_ods_data,org_group, supplier_capacity, atp_rules,
5110: bom, bor, calendar_check, demand_class,ITEM_SUBSTITUTES, forecast, item,
5111: decode(MSC_CL_COLLECTION.v_apps_ver, MSC_UTIL.G_APPS122, MSC_UTIL.SYS_NO, kpi_targets_bis), mds, mps, oh, parameter, planners,
5112: projects, po, reservations, nra, safety_stock,
5107:
5108: IF (MSC_CL_COLLECTION.v_prec_defined = FALSE) THEN
5109: SELECT delete_ods_data,org_group, supplier_capacity, atp_rules,
5110: bom, bor, calendar_check, demand_class,ITEM_SUBSTITUTES, forecast, item,
5111: decode(MSC_CL_COLLECTION.v_apps_ver, MSC_UTIL.G_APPS122, MSC_UTIL.SYS_NO, kpi_targets_bis), mds, mps, oh, parameter, planners,
5112: projects, po, reservations, nra, safety_stock,
5113: sales_order, sourcing_history, sourcing, sub_inventories,
5114: customer, supplier, unit_numbers, uom, user_supply_demand, wip, user_comp_association,po_receipts,
5115: bom_sn_flag, bor_sn_flag, item_sn_flag, oh_sn_flag,
5124: , sales_channel, fiscal_calendar,internal_repair,external_repair -- changed for bug 5909379 SRP addition
5125: ,payback_demand_supply
5126: ,currency_conversion,delivery_details,CMRO,cmro_forecasts,ibuc_history, notes_attach,
5127: eam_info,eam_forecasts,cmro_closed_wo,ret_fcst,osp_supply -- bug 13839374
5128: 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,
5129: MSC_CL_COLLECTION.v_coll_prec.atp_rules_flag, MSC_CL_COLLECTION.v_coll_prec.bom_flag,
5130: MSC_CL_COLLECTION.v_coll_prec.bor_flag, MSC_CL_COLLECTION.v_coll_prec.calendar_flag,
5131: 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,
5132: MSC_CL_COLLECTION.v_coll_prec.item_flag, MSC_CL_COLLECTION.v_coll_prec.kpi_bis_flag,
5125: ,payback_demand_supply
5126: ,currency_conversion,delivery_details,CMRO,cmro_forecasts,ibuc_history, notes_attach,
5127: eam_info,eam_forecasts,cmro_closed_wo,ret_fcst,osp_supply -- bug 13839374
5128: 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,
5129: MSC_CL_COLLECTION.v_coll_prec.atp_rules_flag, MSC_CL_COLLECTION.v_coll_prec.bom_flag,
5130: MSC_CL_COLLECTION.v_coll_prec.bor_flag, MSC_CL_COLLECTION.v_coll_prec.calendar_flag,
5131: 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,
5132: MSC_CL_COLLECTION.v_coll_prec.item_flag, MSC_CL_COLLECTION.v_coll_prec.kpi_bis_flag,
5133: MSC_CL_COLLECTION.v_coll_prec.mds_flag, MSC_CL_COLLECTION.v_coll_prec.mps_flag,
5126: ,currency_conversion,delivery_details,CMRO,cmro_forecasts,ibuc_history, notes_attach,
5127: eam_info,eam_forecasts,cmro_closed_wo,ret_fcst,osp_supply -- bug 13839374
5128: 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,
5129: MSC_CL_COLLECTION.v_coll_prec.atp_rules_flag, MSC_CL_COLLECTION.v_coll_prec.bom_flag,
5130: MSC_CL_COLLECTION.v_coll_prec.bor_flag, MSC_CL_COLLECTION.v_coll_prec.calendar_flag,
5131: 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,
5132: MSC_CL_COLLECTION.v_coll_prec.item_flag, MSC_CL_COLLECTION.v_coll_prec.kpi_bis_flag,
5133: MSC_CL_COLLECTION.v_coll_prec.mds_flag, MSC_CL_COLLECTION.v_coll_prec.mps_flag,
5134: MSC_CL_COLLECTION.v_coll_prec.oh_flag, MSC_CL_COLLECTION.v_coll_prec.parameter_flag,
5127: eam_info,eam_forecasts,cmro_closed_wo,ret_fcst,osp_supply -- bug 13839374
5128: 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,
5129: MSC_CL_COLLECTION.v_coll_prec.atp_rules_flag, MSC_CL_COLLECTION.v_coll_prec.bom_flag,
5130: MSC_CL_COLLECTION.v_coll_prec.bor_flag, MSC_CL_COLLECTION.v_coll_prec.calendar_flag,
5131: 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,
5132: MSC_CL_COLLECTION.v_coll_prec.item_flag, MSC_CL_COLLECTION.v_coll_prec.kpi_bis_flag,
5133: MSC_CL_COLLECTION.v_coll_prec.mds_flag, MSC_CL_COLLECTION.v_coll_prec.mps_flag,
5134: MSC_CL_COLLECTION.v_coll_prec.oh_flag, MSC_CL_COLLECTION.v_coll_prec.parameter_flag,
5135: MSC_CL_COLLECTION.v_coll_prec.planner_flag, MSC_CL_COLLECTION.v_coll_prec.project_flag,
5128: 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,
5129: MSC_CL_COLLECTION.v_coll_prec.atp_rules_flag, MSC_CL_COLLECTION.v_coll_prec.bom_flag,
5130: MSC_CL_COLLECTION.v_coll_prec.bor_flag, MSC_CL_COLLECTION.v_coll_prec.calendar_flag,
5131: 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,
5132: MSC_CL_COLLECTION.v_coll_prec.item_flag, MSC_CL_COLLECTION.v_coll_prec.kpi_bis_flag,
5133: MSC_CL_COLLECTION.v_coll_prec.mds_flag, MSC_CL_COLLECTION.v_coll_prec.mps_flag,
5134: MSC_CL_COLLECTION.v_coll_prec.oh_flag, MSC_CL_COLLECTION.v_coll_prec.parameter_flag,
5135: MSC_CL_COLLECTION.v_coll_prec.planner_flag, MSC_CL_COLLECTION.v_coll_prec.project_flag,
5136: MSC_CL_COLLECTION.v_coll_prec.po_flag, MSC_CL_COLLECTION.v_coll_prec.reserves_flag,
5129: MSC_CL_COLLECTION.v_coll_prec.atp_rules_flag, MSC_CL_COLLECTION.v_coll_prec.bom_flag,
5130: MSC_CL_COLLECTION.v_coll_prec.bor_flag, MSC_CL_COLLECTION.v_coll_prec.calendar_flag,
5131: 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,
5132: MSC_CL_COLLECTION.v_coll_prec.item_flag, MSC_CL_COLLECTION.v_coll_prec.kpi_bis_flag,
5133: MSC_CL_COLLECTION.v_coll_prec.mds_flag, MSC_CL_COLLECTION.v_coll_prec.mps_flag,
5134: MSC_CL_COLLECTION.v_coll_prec.oh_flag, MSC_CL_COLLECTION.v_coll_prec.parameter_flag,
5135: MSC_CL_COLLECTION.v_coll_prec.planner_flag, MSC_CL_COLLECTION.v_coll_prec.project_flag,
5136: MSC_CL_COLLECTION.v_coll_prec.po_flag, MSC_CL_COLLECTION.v_coll_prec.reserves_flag,
5137: MSC_CL_COLLECTION.v_coll_prec.resource_nra_flag, MSC_CL_COLLECTION.v_coll_prec.saf_stock_flag,
5130: MSC_CL_COLLECTION.v_coll_prec.bor_flag, MSC_CL_COLLECTION.v_coll_prec.calendar_flag,
5131: 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,
5132: MSC_CL_COLLECTION.v_coll_prec.item_flag, MSC_CL_COLLECTION.v_coll_prec.kpi_bis_flag,
5133: MSC_CL_COLLECTION.v_coll_prec.mds_flag, MSC_CL_COLLECTION.v_coll_prec.mps_flag,
5134: MSC_CL_COLLECTION.v_coll_prec.oh_flag, MSC_CL_COLLECTION.v_coll_prec.parameter_flag,
5135: MSC_CL_COLLECTION.v_coll_prec.planner_flag, MSC_CL_COLLECTION.v_coll_prec.project_flag,
5136: MSC_CL_COLLECTION.v_coll_prec.po_flag, MSC_CL_COLLECTION.v_coll_prec.reserves_flag,
5137: MSC_CL_COLLECTION.v_coll_prec.resource_nra_flag, MSC_CL_COLLECTION.v_coll_prec.saf_stock_flag,
5138: MSC_CL_COLLECTION.v_coll_prec.sales_order_flag, MSC_CL_COLLECTION.v_coll_prec.source_hist_flag,
5131: 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,
5132: MSC_CL_COLLECTION.v_coll_prec.item_flag, MSC_CL_COLLECTION.v_coll_prec.kpi_bis_flag,
5133: MSC_CL_COLLECTION.v_coll_prec.mds_flag, MSC_CL_COLLECTION.v_coll_prec.mps_flag,
5134: MSC_CL_COLLECTION.v_coll_prec.oh_flag, MSC_CL_COLLECTION.v_coll_prec.parameter_flag,
5135: MSC_CL_COLLECTION.v_coll_prec.planner_flag, MSC_CL_COLLECTION.v_coll_prec.project_flag,
5136: MSC_CL_COLLECTION.v_coll_prec.po_flag, MSC_CL_COLLECTION.v_coll_prec.reserves_flag,
5137: MSC_CL_COLLECTION.v_coll_prec.resource_nra_flag, MSC_CL_COLLECTION.v_coll_prec.saf_stock_flag,
5138: MSC_CL_COLLECTION.v_coll_prec.sales_order_flag, MSC_CL_COLLECTION.v_coll_prec.source_hist_flag,
5139: MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag, MSC_CL_COLLECTION.v_coll_prec.sub_inventory_flag,
5132: MSC_CL_COLLECTION.v_coll_prec.item_flag, MSC_CL_COLLECTION.v_coll_prec.kpi_bis_flag,
5133: MSC_CL_COLLECTION.v_coll_prec.mds_flag, MSC_CL_COLLECTION.v_coll_prec.mps_flag,
5134: MSC_CL_COLLECTION.v_coll_prec.oh_flag, MSC_CL_COLLECTION.v_coll_prec.parameter_flag,
5135: MSC_CL_COLLECTION.v_coll_prec.planner_flag, MSC_CL_COLLECTION.v_coll_prec.project_flag,
5136: MSC_CL_COLLECTION.v_coll_prec.po_flag, MSC_CL_COLLECTION.v_coll_prec.reserves_flag,
5137: MSC_CL_COLLECTION.v_coll_prec.resource_nra_flag, MSC_CL_COLLECTION.v_coll_prec.saf_stock_flag,
5138: MSC_CL_COLLECTION.v_coll_prec.sales_order_flag, MSC_CL_COLLECTION.v_coll_prec.source_hist_flag,
5139: MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag, MSC_CL_COLLECTION.v_coll_prec.sub_inventory_flag,
5140: MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag, MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag,
5133: MSC_CL_COLLECTION.v_coll_prec.mds_flag, MSC_CL_COLLECTION.v_coll_prec.mps_flag,
5134: MSC_CL_COLLECTION.v_coll_prec.oh_flag, MSC_CL_COLLECTION.v_coll_prec.parameter_flag,
5135: MSC_CL_COLLECTION.v_coll_prec.planner_flag, MSC_CL_COLLECTION.v_coll_prec.project_flag,
5136: MSC_CL_COLLECTION.v_coll_prec.po_flag, MSC_CL_COLLECTION.v_coll_prec.reserves_flag,
5137: MSC_CL_COLLECTION.v_coll_prec.resource_nra_flag, MSC_CL_COLLECTION.v_coll_prec.saf_stock_flag,
5138: MSC_CL_COLLECTION.v_coll_prec.sales_order_flag, MSC_CL_COLLECTION.v_coll_prec.source_hist_flag,
5139: MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag, MSC_CL_COLLECTION.v_coll_prec.sub_inventory_flag,
5140: MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag, MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag,
5141: MSC_CL_COLLECTION.v_coll_prec.unit_number_flag, MSC_CL_COLLECTION.v_coll_prec.uom_flag,
5134: MSC_CL_COLLECTION.v_coll_prec.oh_flag, MSC_CL_COLLECTION.v_coll_prec.parameter_flag,
5135: MSC_CL_COLLECTION.v_coll_prec.planner_flag, MSC_CL_COLLECTION.v_coll_prec.project_flag,
5136: MSC_CL_COLLECTION.v_coll_prec.po_flag, MSC_CL_COLLECTION.v_coll_prec.reserves_flag,
5137: MSC_CL_COLLECTION.v_coll_prec.resource_nra_flag, MSC_CL_COLLECTION.v_coll_prec.saf_stock_flag,
5138: MSC_CL_COLLECTION.v_coll_prec.sales_order_flag, MSC_CL_COLLECTION.v_coll_prec.source_hist_flag,
5139: MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag, MSC_CL_COLLECTION.v_coll_prec.sub_inventory_flag,
5140: MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag, MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag,
5141: MSC_CL_COLLECTION.v_coll_prec.unit_number_flag, MSC_CL_COLLECTION.v_coll_prec.uom_flag,
5142: 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,
5135: MSC_CL_COLLECTION.v_coll_prec.planner_flag, MSC_CL_COLLECTION.v_coll_prec.project_flag,
5136: MSC_CL_COLLECTION.v_coll_prec.po_flag, MSC_CL_COLLECTION.v_coll_prec.reserves_flag,
5137: MSC_CL_COLLECTION.v_coll_prec.resource_nra_flag, MSC_CL_COLLECTION.v_coll_prec.saf_stock_flag,
5138: MSC_CL_COLLECTION.v_coll_prec.sales_order_flag, MSC_CL_COLLECTION.v_coll_prec.source_hist_flag,
5139: MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag, MSC_CL_COLLECTION.v_coll_prec.sub_inventory_flag,
5140: MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag, MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag,
5141: MSC_CL_COLLECTION.v_coll_prec.unit_number_flag, MSC_CL_COLLECTION.v_coll_prec.uom_flag,
5142: 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,
5143: MSC_CL_COLLECTION.v_coll_prec.po_receipts_flag,
5136: MSC_CL_COLLECTION.v_coll_prec.po_flag, MSC_CL_COLLECTION.v_coll_prec.reserves_flag,
5137: MSC_CL_COLLECTION.v_coll_prec.resource_nra_flag, MSC_CL_COLLECTION.v_coll_prec.saf_stock_flag,
5138: MSC_CL_COLLECTION.v_coll_prec.sales_order_flag, MSC_CL_COLLECTION.v_coll_prec.source_hist_flag,
5139: MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag, MSC_CL_COLLECTION.v_coll_prec.sub_inventory_flag,
5140: MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag, MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag,
5141: MSC_CL_COLLECTION.v_coll_prec.unit_number_flag, MSC_CL_COLLECTION.v_coll_prec.uom_flag,
5142: 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,
5143: MSC_CL_COLLECTION.v_coll_prec.po_receipts_flag,
5144: MSC_CL_COLLECTION.v_coll_prec.bom_sn_flag, MSC_CL_COLLECTION.v_coll_prec.bor_sn_flag,
5137: MSC_CL_COLLECTION.v_coll_prec.resource_nra_flag, MSC_CL_COLLECTION.v_coll_prec.saf_stock_flag,
5138: MSC_CL_COLLECTION.v_coll_prec.sales_order_flag, MSC_CL_COLLECTION.v_coll_prec.source_hist_flag,
5139: MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag, MSC_CL_COLLECTION.v_coll_prec.sub_inventory_flag,
5140: MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag, MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag,
5141: MSC_CL_COLLECTION.v_coll_prec.unit_number_flag, MSC_CL_COLLECTION.v_coll_prec.uom_flag,
5142: 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,
5143: MSC_CL_COLLECTION.v_coll_prec.po_receipts_flag,
5144: MSC_CL_COLLECTION.v_coll_prec.bom_sn_flag, MSC_CL_COLLECTION.v_coll_prec.bor_sn_flag,
5145: MSC_CL_COLLECTION.v_coll_prec.item_sn_flag, MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag,
5138: MSC_CL_COLLECTION.v_coll_prec.sales_order_flag, MSC_CL_COLLECTION.v_coll_prec.source_hist_flag,
5139: MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag, MSC_CL_COLLECTION.v_coll_prec.sub_inventory_flag,
5140: MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag, MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag,
5141: MSC_CL_COLLECTION.v_coll_prec.unit_number_flag, MSC_CL_COLLECTION.v_coll_prec.uom_flag,
5142: 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,
5143: MSC_CL_COLLECTION.v_coll_prec.po_receipts_flag,
5144: MSC_CL_COLLECTION.v_coll_prec.bom_sn_flag, MSC_CL_COLLECTION.v_coll_prec.bor_sn_flag,
5145: MSC_CL_COLLECTION.v_coll_prec.item_sn_flag, MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag,
5146: MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag, MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag,
5139: MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag, MSC_CL_COLLECTION.v_coll_prec.sub_inventory_flag,
5140: MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag, MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag,
5141: MSC_CL_COLLECTION.v_coll_prec.unit_number_flag, MSC_CL_COLLECTION.v_coll_prec.uom_flag,
5142: 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,
5143: MSC_CL_COLLECTION.v_coll_prec.po_receipts_flag,
5144: MSC_CL_COLLECTION.v_coll_prec.bom_sn_flag, MSC_CL_COLLECTION.v_coll_prec.bor_sn_flag,
5145: MSC_CL_COLLECTION.v_coll_prec.item_sn_flag, MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag,
5146: MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag, MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag,
5147: MSC_CL_COLLECTION.v_coll_prec.so_sn_flag, MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag,
5140: MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag, MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag,
5141: MSC_CL_COLLECTION.v_coll_prec.unit_number_flag, MSC_CL_COLLECTION.v_coll_prec.uom_flag,
5142: 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,
5143: MSC_CL_COLLECTION.v_coll_prec.po_receipts_flag,
5144: MSC_CL_COLLECTION.v_coll_prec.bom_sn_flag, MSC_CL_COLLECTION.v_coll_prec.bor_sn_flag,
5145: MSC_CL_COLLECTION.v_coll_prec.item_sn_flag, MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag,
5146: MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag, MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag,
5147: MSC_CL_COLLECTION.v_coll_prec.so_sn_flag, MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag,
5148: MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag,
5141: MSC_CL_COLLECTION.v_coll_prec.unit_number_flag, MSC_CL_COLLECTION.v_coll_prec.uom_flag,
5142: 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,
5143: MSC_CL_COLLECTION.v_coll_prec.po_receipts_flag,
5144: MSC_CL_COLLECTION.v_coll_prec.bom_sn_flag, MSC_CL_COLLECTION.v_coll_prec.bor_sn_flag,
5145: MSC_CL_COLLECTION.v_coll_prec.item_sn_flag, MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag,
5146: MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag, MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag,
5147: MSC_CL_COLLECTION.v_coll_prec.so_sn_flag, MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag,
5148: MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag,
5149: MSC_CL_COLLECTION.v_coll_prec.supcap_sn_flag, MSC_CL_COLLECTION.v_coll_prec.po_sn_flag,
5142: 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,
5143: MSC_CL_COLLECTION.v_coll_prec.po_receipts_flag,
5144: MSC_CL_COLLECTION.v_coll_prec.bom_sn_flag, MSC_CL_COLLECTION.v_coll_prec.bor_sn_flag,
5145: MSC_CL_COLLECTION.v_coll_prec.item_sn_flag, MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag,
5146: MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag, MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag,
5147: MSC_CL_COLLECTION.v_coll_prec.so_sn_flag, MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag,
5148: MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag,
5149: MSC_CL_COLLECTION.v_coll_prec.supcap_sn_flag, MSC_CL_COLLECTION.v_coll_prec.po_sn_flag,
5150: MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag, MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag,
5143: MSC_CL_COLLECTION.v_coll_prec.po_receipts_flag,
5144: MSC_CL_COLLECTION.v_coll_prec.bom_sn_flag, MSC_CL_COLLECTION.v_coll_prec.bor_sn_flag,
5145: MSC_CL_COLLECTION.v_coll_prec.item_sn_flag, MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag,
5146: MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag, MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag,
5147: MSC_CL_COLLECTION.v_coll_prec.so_sn_flag, MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag,
5148: MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag,
5149: MSC_CL_COLLECTION.v_coll_prec.supcap_sn_flag, MSC_CL_COLLECTION.v_coll_prec.po_sn_flag,
5150: MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag, MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag,
5151: MSC_CL_COLLECTION.v_coll_prec.nosnap_flag
5144: MSC_CL_COLLECTION.v_coll_prec.bom_sn_flag, MSC_CL_COLLECTION.v_coll_prec.bor_sn_flag,
5145: MSC_CL_COLLECTION.v_coll_prec.item_sn_flag, MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag,
5146: MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag, MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag,
5147: MSC_CL_COLLECTION.v_coll_prec.so_sn_flag, MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag,
5148: MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag,
5149: MSC_CL_COLLECTION.v_coll_prec.supcap_sn_flag, MSC_CL_COLLECTION.v_coll_prec.po_sn_flag,
5150: MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag, MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag,
5151: MSC_CL_COLLECTION.v_coll_prec.nosnap_flag
5152: /* CP-ACK starts */
5145: MSC_CL_COLLECTION.v_coll_prec.item_sn_flag, MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag,
5146: MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag, MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag,
5147: MSC_CL_COLLECTION.v_coll_prec.so_sn_flag, MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag,
5148: MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag,
5149: MSC_CL_COLLECTION.v_coll_prec.supcap_sn_flag, MSC_CL_COLLECTION.v_coll_prec.po_sn_flag,
5150: MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag, MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag,
5151: MSC_CL_COLLECTION.v_coll_prec.nosnap_flag
5152: /* CP-ACK starts */
5153: ,MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag
5146: MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag, MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag,
5147: MSC_CL_COLLECTION.v_coll_prec.so_sn_flag, MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag,
5148: MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag,
5149: MSC_CL_COLLECTION.v_coll_prec.supcap_sn_flag, MSC_CL_COLLECTION.v_coll_prec.po_sn_flag,
5150: MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag, MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag,
5151: MSC_CL_COLLECTION.v_coll_prec.nosnap_flag
5152: /* CP-ACK starts */
5153: ,MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag
5154: /* CP-ACK ends */
5147: MSC_CL_COLLECTION.v_coll_prec.so_sn_flag, MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag,
5148: MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag,
5149: MSC_CL_COLLECTION.v_coll_prec.supcap_sn_flag, MSC_CL_COLLECTION.v_coll_prec.po_sn_flag,
5150: MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag, MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag,
5151: MSC_CL_COLLECTION.v_coll_prec.nosnap_flag
5152: /* CP-ACK starts */
5153: ,MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag
5154: /* CP-ACK ends */
5155: /* CP-AUTO */
5149: MSC_CL_COLLECTION.v_coll_prec.supcap_sn_flag, MSC_CL_COLLECTION.v_coll_prec.po_sn_flag,
5150: MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag, MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag,
5151: MSC_CL_COLLECTION.v_coll_prec.nosnap_flag
5152: /* CP-ACK starts */
5153: ,MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag
5154: /* CP-ACK ends */
5155: /* CP-AUTO */
5156: ,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
5157: ,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
5152: /* CP-ACK starts */
5153: ,MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag
5154: /* CP-ACK ends */
5155: /* CP-AUTO */
5156: ,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
5157: ,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
5158: ,MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag
5159: ,MSC_CL_COLLECTION.v_coll_prec.currency_conversion_flag
5160: ,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,
5153: ,MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag
5154: /* CP-ACK ends */
5155: /* CP-AUTO */
5156: ,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
5157: ,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
5158: ,MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag
5159: ,MSC_CL_COLLECTION.v_coll_prec.currency_conversion_flag
5160: ,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,
5161: MSC_CL_COLLECTION.v_coll_prec.ibuc_history_flag,MSC_CL_COLLECTION.v_coll_prec.notes_attach_flag,
5154: /* CP-ACK ends */
5155: /* CP-AUTO */
5156: ,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
5157: ,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
5158: ,MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag
5159: ,MSC_CL_COLLECTION.v_coll_prec.currency_conversion_flag
5160: ,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,
5161: MSC_CL_COLLECTION.v_coll_prec.ibuc_history_flag,MSC_CL_COLLECTION.v_coll_prec.notes_attach_flag,
5162: 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,
5155: /* CP-AUTO */
5156: ,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
5157: ,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
5158: ,MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag
5159: ,MSC_CL_COLLECTION.v_coll_prec.currency_conversion_flag
5160: ,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,
5161: MSC_CL_COLLECTION.v_coll_prec.ibuc_history_flag,MSC_CL_COLLECTION.v_coll_prec.notes_attach_flag,
5162: 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,
5163: MSC_CL_COLLECTION.v_coll_prec.osp_supply
5156: ,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
5157: ,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
5158: ,MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag
5159: ,MSC_CL_COLLECTION.v_coll_prec.currency_conversion_flag
5160: ,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,
5161: MSC_CL_COLLECTION.v_coll_prec.ibuc_history_flag,MSC_CL_COLLECTION.v_coll_prec.notes_attach_flag,
5162: 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,
5163: MSC_CL_COLLECTION.v_coll_prec.osp_supply
5164: FROM msc_coll_parameters
5157: ,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
5158: ,MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag
5159: ,MSC_CL_COLLECTION.v_coll_prec.currency_conversion_flag
5160: ,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,
5161: MSC_CL_COLLECTION.v_coll_prec.ibuc_history_flag,MSC_CL_COLLECTION.v_coll_prec.notes_attach_flag,
5162: 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,
5163: MSC_CL_COLLECTION.v_coll_prec.osp_supply
5164: FROM msc_coll_parameters
5165: WHERE instance_id = p_instance_id;
5158: ,MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag
5159: ,MSC_CL_COLLECTION.v_coll_prec.currency_conversion_flag
5160: ,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,
5161: MSC_CL_COLLECTION.v_coll_prec.ibuc_history_flag,MSC_CL_COLLECTION.v_coll_prec.notes_attach_flag,
5162: 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,
5163: MSC_CL_COLLECTION.v_coll_prec.osp_supply
5164: FROM msc_coll_parameters
5165: WHERE instance_id = p_instance_id;
5166: MSC_CL_COLLECTION.v_prec_defined := TRUE;
5159: ,MSC_CL_COLLECTION.v_coll_prec.currency_conversion_flag
5160: ,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,
5161: MSC_CL_COLLECTION.v_coll_prec.ibuc_history_flag,MSC_CL_COLLECTION.v_coll_prec.notes_attach_flag,
5162: 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,
5163: MSC_CL_COLLECTION.v_coll_prec.osp_supply
5164: FROM msc_coll_parameters
5165: WHERE instance_id = p_instance_id;
5166: MSC_CL_COLLECTION.v_prec_defined := TRUE;
5167: END IF;
5162: 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,
5163: MSC_CL_COLLECTION.v_coll_prec.osp_supply
5164: FROM msc_coll_parameters
5165: WHERE instance_id = p_instance_id;
5166: MSC_CL_COLLECTION.v_prec_defined := TRUE;
5167: END IF;
5168:
5169: END GET_COLL_PARAM;
5170:
5177: /* Initialize the global prec record variable */
5178:
5179: SELECT delete_ods_data,org_group, supplier_capacity, atp_rules,
5180: bom, bor, calendar_check, demand_class,ITEM_SUBSTITUTES, forecast, item,
5181: decode(MSC_CL_COLLECTION.v_apps_ver, MSC_UTIL.G_APPS122, MSC_UTIL.SYS_NO, kpi_targets_bis), mds, mps, oh, parameter, planners,
5182: projects, po, reservations, nra, safety_stock,
5183: sales_order, sourcing_history, sourcing, sub_inventories,
5184: customer, supplier, unit_numbers, uom, user_supply_demand, wip, user_comp_association,
5185: po_receipts,