[Home] [Help]
10: -- NEED_BY_DATE_PREF NUMBER := MSC_CL_COLLECTION.PROMISED_DATE_PREF;
11: -- G_ALL_ORGANIZATIONS VARCHAR2(6):= MSC_CL_COLLECTION.G_ALL_ORGANIZATIONS;
12: -- SYS_INCR Number:=MSC_CL_COLLECTION.SYS_INCR;
13: -- SYS_TGT Number:=MSC_CL_COLLECTION.SYS_TGT;
14: -- NULL_VALUE NUMBER:= MSC_UTIL.NULL_VALUE;
15: -- NULL_CHAR VARCHAR2(6):=MSC_UTIL.NULL_CHAR;
16: -- G_CONF_APS_SCE NUMBER :=MSC_CL_COLLECTION.G_CONF_APS_SCE;
17: -- G_CONF_SCE NUMBER :=MSC_CL_COLLECTION.G_CONF_SCE;
18:
11: -- G_ALL_ORGANIZATIONS VARCHAR2(6):= MSC_CL_COLLECTION.G_ALL_ORGANIZATIONS;
12: -- SYS_INCR Number:=MSC_CL_COLLECTION.SYS_INCR;
13: -- SYS_TGT Number:=MSC_CL_COLLECTION.SYS_TGT;
14: -- NULL_VALUE NUMBER:= MSC_UTIL.NULL_VALUE;
15: -- NULL_CHAR VARCHAR2(6):=MSC_UTIL.NULL_CHAR;
16: -- G_CONF_APS_SCE NUMBER :=MSC_CL_COLLECTION.G_CONF_APS_SCE;
17: -- G_CONF_SCE NUMBER :=MSC_CL_COLLECTION.G_CONF_SCE;
18:
19: FUNCTION IS_SUPPLIES_LOAD_DONE
51: RETURN TRUE;
52:
53: EXCEPTION
54: WHEN OTHERS THEN
55: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
56: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Failure to get the Status of Supplies Load .');
57: RETURN FALSE;
58:
59: END IS_SUPPLIES_LOAD_DONE;
52:
53: EXCEPTION
54: WHEN OTHERS THEN
55: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
56: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Failure to get the Status of Supplies Load .');
57: RETURN FALSE;
58:
59: END IS_SUPPLIES_LOAD_DONE;
60:
79: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
80: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
81: object_name => 'supplies_nx_'||MSC_CL_COLLECTION.v_instance_code);
82:
83: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Index SUPPLIES_NX_'||MSC_CL_COLLECTION.v_instance_code||' created.');
84:
85: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
86: application_short_name => 'MSC',
87: statement_type => AD_DDL.CREATE_INDEX,
92: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
93: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
94: object_name => 'supplies_nx1_'||MSC_CL_COLLECTION.v_instance_code);
95:
96: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Index SUPPLIES_NX1_'||MSC_CL_COLLECTION.v_instance_code||' created.');
97:
98: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
99: application_short_name => 'MSC',
100: statement_type => AD_DDL.CREATE_INDEX,
104: ||'(plan_id, sr_instance_id, disposition_id, po_line_id ) '
105: ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
106: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
107: object_name => 'supplies_nx2_'||MSC_CL_COLLECTION.v_instance_code);
108: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Index SUPPLIES_NX2_'||MSC_CL_COLLECTION.v_instance_code||' created.');
109:
110: RETURN TRUE;
111:
112: EXCEPTION
110: RETURN TRUE;
111:
112: EXCEPTION
113: WHEN OTHERS THEN
114: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
115: update msc_apps_instances
116: set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
117: where instance_id = MSC_CL_COLLECTION.v_instance_id;
118: commit;
152: USING lv_msc_schema,lv_msc_schema,lv_ind_name;
153:
154: IF (lv_drop_index = 1) THEN
155:
156: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropping the index :'||lv_ind_name);
157: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
158: application_short_name => 'MSC',
159: statement_type => AD_DDL.DROP_INDEX,
160: statement =>
159: statement_type => AD_DDL.DROP_INDEX,
160: statement =>
161: 'drop index supplies_nx_'||MSC_CL_COLLECTION.v_instance_code,
162: object_name => 'supplies_nx_'||MSC_CL_COLLECTION.v_instance_code);
163: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropped the index :'||lv_ind_name);
164:
165: lv_drop_index := 2;
166: END IF;
167:
171: INTO lv_drop_index
172: USING lv_msc_schema,lv_msc_schema,lv_ind_name;
173:
174: IF (lv_drop_index = 1) THEN
175: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropping the index :'||lv_ind_name);
176: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
177: application_short_name => 'MSC',
178: statement_type => AD_DDL.DROP_INDEX,
179: statement =>
179: statement =>
180: 'drop index supplies_nx1_'||MSC_CL_COLLECTION.v_instance_code,
181: object_name => 'supplies_nx1_'||MSC_CL_COLLECTION.v_instance_code);
182:
183: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropped the index :'||lv_ind_name);
184: lv_drop_index :=2 ;
185: END IF;
186:
187: lv_ind_name := 'SUPPLIES_NX2_'||MSC_CL_COLLECTION.v_instance_code;
190: INTO lv_drop_index
191: USING lv_msc_schema,lv_msc_schema,lv_ind_name;
192:
193: IF (lv_drop_index = 1) THEN
194: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropping the index :'||lv_ind_name);
195: ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
196: application_short_name => 'MSC',
197: statement_type => AD_DDL.DROP_INDEX,
198: statement =>
197: statement_type => AD_DDL.DROP_INDEX,
198: statement =>
199: 'drop index supplies_nx2_'||MSC_CL_COLLECTION.v_instance_code,
200: object_name => 'supplies_nx2_'||MSC_CL_COLLECTION.v_instance_code);
201: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropped the index :'||lv_ind_name);
202: lv_drop_index := 2;
203: END IF;
204:
205: RETURN true;
207: WHEN NO_DATA_FOUND THEN
208: RETURN true;
209:
210: WHEN OTHERS THEN
211: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
212: RETURN FALSE;
213: END drop_supplies_tmp_ind;
214:
215: --==================================================================
443: AND tps2.SR_TP_SITE_ID(+)= x.OWNING_PARTNER_SITE_ID
444: AND tps2.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
445: AND tps2.PARTNER_TYPE(+)= 1
446: AND x.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
447: AND x.DELETED_FLAG= MSC_UTIL.SYS_NO
448: AND md.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
449: AND md.DESIGNATOR(+)= x.SCHEDULE_DESIGNATOR
450: AND md.Organization_ID(+)= x.Organization_ID
451: --comment for bug13979785
492: FROM MSC_ITEM_ID_LID t1,
493: MSC_ST_SUPPLIES x,
494: MSC_TP_SITE_ID_LID tps1,
495: MSC_TP_SITE_ID_LID tps2
496: WHERE x.DELETED_FLAG= MSC_UTIL.SYS_YES
497: AND x.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
498: AND t1.SR_INVENTORY_ITEM_ID(+)= x.INVENTORY_ITEM_ID
499: AND t1.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
500: AND tps1.SR_TP_SITE_ID(+)= x.PLANNING_PARTNER_SITE_ID
524: /* CP-ACK starts */
525: lv_po_dock_date_ref := nvl(fnd_profile.value('MSC_PO_DOCK_DATE_CALC_PREF'), MSC_CL_COLLECTION.PROMISED_DATE_PREF);
526: /* CP-ACk ends */
527:
528: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
529: lv_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
530: ELSE
531: lv_tbl:= 'MSC_SUPPLIES';
532: END IF;
816: ||' )';
817:
818:
819:
820: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply');
821:
822: IF MSC_CL_COLLECTION.v_is_complete_refresh AND MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
823:
824: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1);
818:
819:
820: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply');
821:
822: IF MSC_CL_COLLECTION.v_is_complete_refresh AND MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
823:
824: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1);
825:
826: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
822: IF MSC_CL_COLLECTION.v_is_complete_refresh AND MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
823:
824: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1);
825:
826: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
827: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1);
828: ELSE
829: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
830: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
825:
826: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
827: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1);
828: ELSE
829: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
830: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
831: END IF;
832:
833: END IF;
863: ||' AND SR_INSTANCE_ID= :v_instance_id '
864: ||' AND ORDER_TYPE= 11 ' --Intransit shipment
865: ||' AND SR_MTL_SUPPLY_ID= -1 ';
866:
867: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
868: EXECUTE IMMEDIATE lv_sql_stmt1 USING MSC_CL_COLLECTION.v_last_collection_id,
869: MSC_CL_COLLECTION.v_current_date,
870: MSC_CL_COLLECTION.v_current_user,
871: MSC_CL_COLLECTION.v_instance_id;
869: MSC_CL_COLLECTION.v_current_date,
870: MSC_CL_COLLECTION.v_current_user,
871: MSC_CL_COLLECTION.v_instance_id;
872: ELSE
873: lv_sql_stmt1:=lv_sql_stmt1||' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
874: EXECUTE IMMEDIATE lv_sql_stmt1 USING MSC_CL_COLLECTION.v_last_collection_id,
875: MSC_CL_COLLECTION.v_current_date,
876: MSC_CL_COLLECTION.v_current_user,
877: MSC_CL_COLLECTION.v_instance_id;
885: FOR c_rec IN c1_d LOOP
886: --1 =PO, 2 = PO REQ, 8 = PO receiving, 12= Intrasit Receipt
887: IF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) or
888: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) and
889: (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- PO
890:
891: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd PO-0');
892:
893:
887: IF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) or
888: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) and
889: (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- PO
890:
891: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd PO-0');
892:
893:
894: UPDATE MSC_SUPPLIES ms
895: SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
905:
906: /*3 Discret Job, 7 Non STandard Job, 27 Flow schedule, 70 Eam supply: ds change change,86 External Repair Order */
907: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (3,7,27, 70,86)) or
908: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (3,7,27,70)) and
909: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- WIP_JOB
910:
911: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP-0');
912: if c_rec.ORDER_TYPE = 70 then
913: MSC_CL_COLLECTION.link_top_transaction_id_req := TRUE;
907: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (3,7,27, 70,86)) or
908: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (3,7,27,70)) and
909: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- WIP_JOB
910:
911: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP-0');
912: if c_rec.ORDER_TYPE = 70 then
913: MSC_CL_COLLECTION.link_top_transaction_id_req := TRUE;
914: end if;
915:
928:
929: -- 14=discrete job co product, 15 non stanstard job co-product
930: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (14,15)) or
931: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (14,15)) and
932: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- DISCRETE JOB COMPONENT
933:
934: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP DSC JOB-0');
935:
936:
930: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (14,15)) or
931: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (14,15)) and
932: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- DISCRETE JOB COMPONENT
933:
934: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP DSC JOB-0');
935:
936:
937: UPDATE MSC_SUPPLIES ms
938: SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
949: AND OPERATION_SEQ_NUM= NVL(c_rec.OPERATION_SEQ_NUM,OPERATION_SEQ_NUM);
950:
951: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 30) or
952: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 30) and
953: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- REPT ITEM
954:
955: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP REPT ITEM-0');
956:
957:
951: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 30) or
952: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 30) and
953: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- REPT ITEM
954:
955: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP REPT ITEM-0');
956:
957:
958: UPDATE MSC_SUPPLIES
959: SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
973: AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
974:
975: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 18) or
976: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 18) and
977: (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- ONHAND
978:
979: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd OH-0');
980:
981:
975: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 18) or
976: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 18) and
977: (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- ONHAND
978:
979: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd OH-0');
980:
981:
982: UPDATE MSC_SUPPLIES
983: --SET NEW_ORDER_QUANTITY= 0.0,
991: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
992: AND ORDER_TYPE= 18
993: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
994: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
995: AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
996: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
997: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
998: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
999: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
992: AND ORDER_TYPE= 18
993: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
994: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
995: AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
996: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
997: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
998: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
999: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1000: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
993: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
994: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
995: AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
996: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
997: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
998: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
999: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1000: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1001: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
994: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
995: AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
996: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
997: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
998: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
999: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1000: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1001: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1002: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
995: AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
996: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
997: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
998: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
999: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1000: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1001: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1002: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1003: AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
996: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
997: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
998: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
999: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1000: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1001: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1002: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1003: AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
1004:
997: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
998: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
999: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1000: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1001: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1002: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1003: AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
1004:
1005: /* planned order */
998: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
999: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1000: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1001: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1002: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1003: AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
1004:
1005: /* planned order */
1006: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (5,81)) or
999: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1000: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1001: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1002: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1003: AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
1004:
1005: /* planned order */
1006: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (5,81)) or
1007: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 5) and
1004:
1005: /* planned order */
1006: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (5,81)) or
1007: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 5) and
1008: (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- MPS
1009:
1010:
1011:
1012:
1025: AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
1026:
1027: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 41) or
1028: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 41) and
1029: (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- USER DEFINED
1030:
1031: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd USUP-0');
1032:
1033:
1027: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 41) or
1028: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 41) and
1029: (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- USER DEFINED
1030:
1031: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd USUP-0');
1032:
1033:
1034: UPDATE MSC_SUPPLIES
1035: SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
1082:
1083: BEGIN
1084:
1085: -- SRP enhancement
1086: IF MSC_UTIL.g_collect_srp_data = 'Y' THEN
1087: IF (c_rec.ORDER_TYPE = 1)
1088: OR (c_rec.ORDER_TYPE = 5 AND c_rec.FIRM_PLANNED_TYPE = 1)
1089: OR (c_rec.ORDER_TYPE = 75) OR (c_rec.ORDER_TYPE = 74)
1090: OR (c_rec.ORDER_TYPE = 86) THEN
1087: IF (c_rec.ORDER_TYPE = 1)
1088: OR (c_rec.ORDER_TYPE = 5 AND c_rec.FIRM_PLANNED_TYPE = 1)
1089: OR (c_rec.ORDER_TYPE = 75) OR (c_rec.ORDER_TYPE = 74)
1090: OR (c_rec.ORDER_TYPE = 86) THEN
1091: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
1092: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
1093: ELSIF (c_rec.ORDER_TYPE = 81) THEN--bug 13839374 returns forecast legacy
1094: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
1095: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_BAD;
1088: OR (c_rec.ORDER_TYPE = 5 AND c_rec.FIRM_PLANNED_TYPE = 1)
1089: OR (c_rec.ORDER_TYPE = 75) OR (c_rec.ORDER_TYPE = 74)
1090: OR (c_rec.ORDER_TYPE = 86) THEN
1091: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
1092: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
1093: ELSIF (c_rec.ORDER_TYPE = 81) THEN--bug 13839374 returns forecast legacy
1094: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
1095: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_BAD;
1096: ELSE
1090: OR (c_rec.ORDER_TYPE = 86) THEN
1091: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
1092: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
1093: ELSIF (c_rec.ORDER_TYPE = 81) THEN--bug 13839374 returns forecast legacy
1094: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
1095: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_BAD;
1096: ELSE
1097: lv_ITEM_TYPE_ID := c_rec.ITEM_TYPE_ID;
1098: lv_ITEM_TYPE_VALUE := c_rec.ITEM_TYPE_VALUE;
1091: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
1092: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
1093: ELSIF (c_rec.ORDER_TYPE = 81) THEN--bug 13839374 returns forecast legacy
1094: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
1095: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_BAD;
1096: ELSE
1097: lv_ITEM_TYPE_ID := c_rec.ITEM_TYPE_ID;
1098: lv_ITEM_TYPE_VALUE := c_rec.ITEM_TYPE_VALUE;
1099: END IF;
1100: END IF;
1101:
1102: -- bug 13839374 returns forecast updating part condition to bad
1103: IF (c_rec.order_type = 81) THEN
1104: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
1105: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_BAD;
1106: END IF;
1107:
1108:
1101:
1102: -- bug 13839374 returns forecast updating part condition to bad
1103: IF (c_rec.order_type = 81) THEN
1104: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
1105: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_BAD;
1106: END IF;
1107:
1108:
1109: --logic for calculating dock date and schedule date
1170: lv_schedule_date :=c_rec.NEW_SCHEDULE_DATE;
1171:
1172: END IF;
1173: /* bug 5937871 */
1174: IF MSC_UTIL.g_collect_srp_data = 'Y' THEN
1175: IF c_rec.ORDER_TYPE=75 THEN
1176: if (c_rec.NEW_SCHEDULE_DATE is null ) then
1177: lv_cal_code_omc:=msc_calendar.GET_CALENDAR_CODE(c_rec.SR_INSTANCE_ID, null, null, null, null, null, c_rec.ORGANIZATION_ID, null, MSC_CALENDAR.OMC);
1178: lv_schedule_date :=MSC_CALENDAR.DATE_OFFSET(lv_cal_code_omc,c_rec.SR_INSTANCE_ID,c_rec.RO_CREATION_DATE,nvl(c_rec.REPAIR_LEAD_TIME,0),1);
1186: --=================== PO SUPPLIES =====================
1187:
1188: IF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) or
1189: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) and
1190: (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- PO
1191:
1192: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd PO');
1193:
1194:
1188: IF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) or
1189: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) and
1190: (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- PO
1191:
1192: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd PO');
1193:
1194:
1195: IF c_rec.SR_MTL_SUPPLY_ID<> -1 THEN
1196:
1305:
1306: --=================== WIP JOB SUPPLIES =====================
1307: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (3,7,27,70,75,86)) or /* 70 eam supply*/
1308: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (3,7,27,70)) and
1309: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- WIP_JOB
1310:
1311:
1312: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP JOB');
1313:
1308: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (3,7,27,70)) and
1309: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- WIP_JOB
1310:
1311:
1312: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP JOB');
1313:
1314:
1315: /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1316: UPDATE MSC_SUPPLIES
1431:
1432: --=================== WIP DISCRETE JOB COMPONENT SUPPLIES =====================
1433: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (14,15)) or
1434: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (14,15)) and
1435: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- DISCRETE JOB COMPONENT
1436:
1437: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP DSC JOB');
1438:
1439:
1433: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (14,15)) or
1434: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (14,15)) and
1435: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- DISCRETE JOB COMPONENT
1436:
1437: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP DSC JOB');
1438:
1439:
1440: /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1441: UPDATE MSC_SUPPLIES
1545:
1546: --=================== REPETITIVE ITEM SUPPLIES =====================
1547: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 30) or
1548: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 30) and
1549: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- REPT ITEM
1550:
1551: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd REPT ITEMS');
1552:
1553: /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1547: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 30) or
1548: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 30) and
1549: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- REPT ITEM
1550:
1551: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd REPT ITEMS');
1552:
1553: /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1554: UPDATE MSC_SUPPLIES
1555: SET
1645:
1646: --=================== ONHAND SUPPLIES =====================
1647: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 18) or
1648: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 18) and
1649: (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- ONHAND
1650:
1651:
1652: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd OH');
1653:
1648: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 18) and
1649: (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- ONHAND
1650:
1651:
1652: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd OH');
1653:
1654:
1655: /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1656: UPDATE MSC_SUPPLIES
1687: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1688: AND ORDER_TYPE= c_rec.ORDER_TYPE
1689: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
1690: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1691: AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
1692: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
1693: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
1694: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
1695: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1688: AND ORDER_TYPE= c_rec.ORDER_TYPE
1689: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
1690: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1691: AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
1692: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
1693: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
1694: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
1695: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1696: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1689: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
1690: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1691: AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
1692: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
1693: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
1694: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
1695: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1696: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1697: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1690: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1691: AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
1692: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
1693: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
1694: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
1695: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1696: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1697: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1698: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1691: AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
1692: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
1693: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
1694: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
1695: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1696: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1697: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1698: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1699: AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
1692: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
1693: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
1694: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
1695: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1696: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1697: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1698: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1699: AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
1700:
1693: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
1694: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
1695: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1696: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1697: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1698: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1699: AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
1700:
1701: --=================== MPS SUPPLIES =====================
1694: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
1695: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1696: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1697: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1698: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1699: AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
1700:
1701: --=================== MPS SUPPLIES =====================
1702: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (5,81)) or
1695: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1696: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1697: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1698: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1699: AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
1700:
1701: --=================== MPS SUPPLIES =====================
1702: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (5,81)) or
1703: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 5) and
1700:
1701: --=================== MPS SUPPLIES =====================
1702: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (5,81)) or
1703: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 5) and
1704: (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- MPS
1705:
1706: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd MPS');
1707:
1708:
1702: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (5,81)) or
1703: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 5) and
1704: (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- MPS
1705:
1706: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd MPS');
1707:
1708:
1709: /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1710: UPDATE MSC_SUPPLIES
1812:
1813: --=================== USER DEFINED SUPPLIES =====================
1814: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 41) or
1815: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 41) and
1816: (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- USER DEFINED
1817:
1818: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd USUP');
1819:
1820:
1814: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 41) or
1815: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 41) and
1816: (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- USER DEFINED
1817:
1818: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd USUP');
1819:
1820:
1821: /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1822: UPDATE MSC_SUPPLIES
1918: IF MSC_CL_COLLECTION.v_is_complete_refresh OR
1919: SQL%NOTFOUND OR
1920: c_rec.SR_MTL_SUPPLY_ID= -1 THEN
1921: if(SQL%NOTFOUND) Then
1922: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Load supply: not found:' || c_rec.SR_MTL_SUPPLY_ID);
1923: end if;
1924:
1925: EXECUTE IMMEDIATE lv_sql_stmt
1926: USING c_rec.INVENTORY_ITEM_ID,
2088: WHEN OTHERS THEN
2089:
2090: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2091:
2092: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQLCODE========================================');
2093: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2094: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
2095: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
2096: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2092: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQLCODE========================================');
2093: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2094: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
2095: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
2096: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2097:
2098: update msc_apps_instances
2099: set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
2100: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2099: set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
2100: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2101: commit;
2102:
2103: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2104: RAISE;
2105:
2106: ELSE
2107: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2103: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2104: RAISE;
2105:
2106: ELSE
2107: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2108:
2109: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'OTHER========================================');
2110: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2111: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
2105:
2106: ELSE
2107: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2108:
2109: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'OTHER========================================');
2110: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2111: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
2112: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
2113: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2109: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'OTHER========================================');
2110: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2111: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
2112: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
2113: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2114:
2115: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2116: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
2117: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
2114:
2115: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2116: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
2117: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
2118: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2119:
2120: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2121: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2122: FND_MESSAGE.SET_TOKEN('VALUE',
2121: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2122: FND_MESSAGE.SET_TOKEN('VALUE',
2123: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
2124: MSC_CL_COLLECTION.v_instance_id));
2125: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2126:
2127: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2128: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORDER_TYPE');
2129: FND_MESSAGE.SET_TOKEN('VALUE',
2127: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2128: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORDER_TYPE');
2129: FND_MESSAGE.SET_TOKEN('VALUE',
2130: MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE',c_rec.ORDER_TYPE));
2131: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2132:
2133: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2134: END IF;
2135:
2129: FND_MESSAGE.SET_TOKEN('VALUE',
2130: MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE',c_rec.ORDER_TYPE));
2131: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2132:
2133: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2134: END IF;
2135:
2136: END;
2137:
2143: /* Load the PO Acknowledgment Records if MSC:Configuration is set to
2144: CP or APS + CP and Supplier Responses parameter is set to Yes in
2145: collection parameters.*/
2146:
2147: IF (MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE
2148: OR MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE) THEN
2149:
2150: IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_incremental_refresh) THEN
2151:
2144: CP or APS + CP and Supplier Responses parameter is set to Yes in
2145: collection parameters.*/
2146:
2147: IF (MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE
2148: OR MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE) THEN
2149:
2150: IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_incremental_refresh) THEN
2151:
2152: MSC_CL_SUPPLIER_RESP.LOAD_SUPPLIER_RESPONSE
2159: MSC_CL_COLLECTION.v_last_collection_id);
2160:
2161: ELSIF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2162:
2163: IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.suprep_sn_flag = MSC_UTIL.SYS_INCR) THEN
2164:
2165: MSC_CL_SUPPLIER_RESP.LOAD_SUPPLIER_RESPONSE
2166: ( MSC_CL_COLLECTION.v_instance_id,
2167: FALSE, --MSC_CL_COLLECTION.v_is_complete_refresh,
2176: END IF; -- IF (v_is_complete_....
2177:
2178: END IF; --IF (G_MSC_CONFIGURA....
2179:
2180: IF(MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y'
2181: -- and MSC_CL_COLLECTION.v_coll_prec.eam_info_flag = MSC_UTIL.SYS_YES
2182: and MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES)
2183: THEN
2184:
2177:
2178: END IF; --IF (G_MSC_CONFIGURA....
2179:
2180: IF(MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y'
2181: -- and MSC_CL_COLLECTION.v_coll_prec.eam_info_flag = MSC_UTIL.SYS_YES
2182: and MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES)
2183: THEN
2184:
2185: /*We will not pass the type of refresh here, it'll be determined in
2178: END IF; --IF (G_MSC_CONFIGURA....
2179:
2180: IF(MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y'
2181: -- and MSC_CL_COLLECTION.v_coll_prec.eam_info_flag = MSC_UTIL.SYS_YES
2182: and MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES)
2183: THEN
2184:
2185: /*We will not pass the type of refresh here, it'll be determined in
2186: * package: MSC_CL_AHL_ODS_LOAD itself */
2189:
2190: /*Call to procedure in MSCLAHLB.pls */
2191:
2192:
2193: IF(MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y'
2194: -- and MSC_CL_COLLECTION.v_coll_prec.cmro_flag = MSC_UTIL.SYS_YES
2195: and MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES)
2196: THEN
2197:
2190: /*Call to procedure in MSCLAHLB.pls */
2191:
2192:
2193: IF(MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y'
2194: -- and MSC_CL_COLLECTION.v_coll_prec.cmro_flag = MSC_UTIL.SYS_YES
2195: and MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES)
2196: THEN
2197:
2198: /*We will not pass the type of refresh here, it'll be determined in
2191:
2192:
2193: IF(MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y'
2194: -- and MSC_CL_COLLECTION.v_coll_prec.cmro_flag = MSC_UTIL.SYS_YES
2195: and MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES)
2196: THEN
2197:
2198: /*We will not pass the type of refresh here, it'll be determined in
2199: * package: MSC_CL_AHL_ODS_LOAD itself */
2200: /* MSC_CL_AHL_ODS_LOAD.LOAD_CMRO_FORECASTS(MSC_CL_COLLECTION.v_instance_id,
2201: MSC_CL_COLLECTION.v_current_user,
2202: MSC_CL_COLLECTION.v_last_collection_id);
2203: */
2204: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'In MSCLSUPB.pls before call to LOAD_CMRO_FORECAST_SUPPLIES');
2205: MSC_CL_AHL_ODS_LOAD.LOAD_CMRO_FORECAST_SUPPLIES;
2206: /* MSC_CL_AHL_ODS_LOAD.LOAD_CMRO_FORECAST_DEMANDS;
2207: MSC_CL_AHL_ODS_LOAD.LOAD_CMRO_FORECAST_RR; */
2208:
2207: MSC_CL_AHL_ODS_LOAD.LOAD_CMRO_FORECAST_RR; */
2208:
2209: END IF;
2210:
2211: IF(MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y'
2212: and MSC_CL_COLLECTION.v_coll_prec.osp_supply = MSC_UTIL.SYS_YES)
2213: THEN
2214: MSC_CL_AHL_ODS_LOAD.LOAD_OSP_SUPPLY;
2215: END IF;
2208:
2209: END IF;
2210:
2211: IF(MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y'
2212: and MSC_CL_COLLECTION.v_coll_prec.osp_supply = MSC_UTIL.SYS_YES)
2213: THEN
2214: MSC_CL_AHL_ODS_LOAD.LOAD_OSP_SUPPLY;
2215: END IF;
2216:
2223:
2224:
2225: /* analyze msc_supplies here */
2226: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
2227: IF MSC_CL_COLLECTION.v_exchange_mode= MSC_UTIL.SYS_YES THEN
2228: /* create temporay index */
2229: IF MSC_CL_SUPPLY_ODS_LOAD.create_supplies_tmp_ind THEN
2230: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Index creation on Temp Supplies table successful.');
2231: ELSE
2226: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
2227: IF MSC_CL_COLLECTION.v_exchange_mode= MSC_UTIL.SYS_YES THEN
2228: /* create temporay index */
2229: IF MSC_CL_SUPPLY_ODS_LOAD.create_supplies_tmp_ind THEN
2230: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Index creation on Temp Supplies table successful.');
2231: ELSE
2232: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2233: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Index creation on Temp Supplies table failed.');
2234: RAISE MSC_CL_COLLECTION.SUPPLIES_INDEX_FAIL;
2228: /* create temporay index */
2229: IF MSC_CL_SUPPLY_ODS_LOAD.create_supplies_tmp_ind THEN
2230: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Index creation on Temp Supplies table successful.');
2231: ELSE
2232: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2233: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Index creation on Temp Supplies table failed.');
2234: RAISE MSC_CL_COLLECTION.SUPPLIES_INDEX_FAIL;
2235: END IF;
2236: END IF;
2229: IF MSC_CL_SUPPLY_ODS_LOAD.create_supplies_tmp_ind THEN
2230: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Index creation on Temp Supplies table successful.');
2231: ELSE
2232: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2233: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Index creation on Temp Supplies table failed.');
2234: RAISE MSC_CL_COLLECTION.SUPPLIES_INDEX_FAIL;
2235: END IF;
2236: END IF;
2237: msc_analyse_tables_pk.analyse_table( lv_tbl, MSC_CL_COLLECTION.v_instance_id, -1);
2244: update msc_apps_instances
2245: set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
2246: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2247: commit;
2248: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'MSC_CL_COLLECTION.SUPPLIES_INDEX_FAIL failed');
2249: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2250: RAISE;
2251:
2252: WHEN OTHERS THEN
2245: set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
2246: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2247: commit;
2248: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'MSC_CL_COLLECTION.SUPPLIES_INDEX_FAIL failed');
2249: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2250: RAISE;
2251:
2252: WHEN OTHERS THEN
2253:
2255: set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
2256: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2257: commit;
2258:
2259: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load supply other exception');
2260: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2261: RAISE;
2262: END LOAD_SUPPLY;
2263:
2256: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2257: commit;
2258:
2259: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load supply other exception');
2260: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2261: RAISE;
2262: END LOAD_SUPPLY;
2263:
2264: PROCEDURE LOAD_STAGING_SUPPLY IS
2605: AND tps2.SR_TP_SITE_ID(+)= x.OWNING_PARTNER_SITE_ID
2606: AND tps2.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
2607: AND tps2.PARTNER_TYPE(+)= 1
2608: AND x.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2609: AND x.DELETED_FLAG= MSC_UTIL.SYS_NO
2610: AND md.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
2611: AND md.DESIGNATOR(+)= x.SCHEDULE_DESIGNATOR
2612: AND md.Organization_ID(+)= x.Organization_ID
2613: AND md.Designator_Type(+)= 2 -- MPS
2640: /* CP-ACK starts */
2641: lv_po_dock_date_ref := nvl(fnd_profile.value('MSC_PO_DOCK_DATE_CALC_PREF'), MSC_CL_COLLECTION.PROMISED_DATE_PREF);
2642: /* CP-ACk ends */
2643:
2644: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_staging_supply');
2645:
2646:
2647: lv_temp_supply_tbl := 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2648: c_count := 0;
2925: ||' :ACTIVITY_ITEM_ID'
2926: ||' )';
2927:
2928:
2929: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
2930:
2931: --agmcont:
2932: if (MSC_CL_COLLECTION.v_is_cont_refresh = FALSE) then
2933:
2942: IF(lv_org_id <> c_rec.ORGANIZATION_ID or lv_org_id=0) THEN
2943:
2944: --GET_CALENDAR_CODE to be called only once for the same org
2945:
2946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 c_rec.SR_INSTANCE_ID='||to_char(c_rec.SR_INSTANCE_ID));
2947: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 org_id='||to_char(c_rec.ORGANIZATION_ID));
2948: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 orc='||to_char(MSC_CALENDAR.ORC));
2949: lv_cal_code:=msc_calendar.GET_CALENDAR_CODE(c_rec.SR_INSTANCE_ID, null, null, null, null, null, c_rec.ORGANIZATION_ID, null, MSC_CALENDAR.ORC);
2950:
2943:
2944: --GET_CALENDAR_CODE to be called only once for the same org
2945:
2946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 c_rec.SR_INSTANCE_ID='||to_char(c_rec.SR_INSTANCE_ID));
2947: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 org_id='||to_char(c_rec.ORGANIZATION_ID));
2948: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 orc='||to_char(MSC_CALENDAR.ORC));
2949: lv_cal_code:=msc_calendar.GET_CALENDAR_CODE(c_rec.SR_INSTANCE_ID, null, null, null, null, null, c_rec.ORGANIZATION_ID, null, MSC_CALENDAR.ORC);
2950:
2951: lv_cal_code_omc:=msc_calendar.GET_CALENDAR_CODE(c_rec.SR_INSTANCE_ID, null, null, null, null, null, c_rec.ORGANIZATION_ID, null, MSC_CALENDAR.OMC);
2944: --GET_CALENDAR_CODE to be called only once for the same org
2945:
2946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 c_rec.SR_INSTANCE_ID='||to_char(c_rec.SR_INSTANCE_ID));
2947: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 org_id='||to_char(c_rec.ORGANIZATION_ID));
2948: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 orc='||to_char(MSC_CALENDAR.ORC));
2949: lv_cal_code:=msc_calendar.GET_CALENDAR_CODE(c_rec.SR_INSTANCE_ID, null, null, null, null, null, c_rec.ORGANIZATION_ID, null, MSC_CALENDAR.ORC);
2950:
2951: lv_cal_code_omc:=msc_calendar.GET_CALENDAR_CODE(c_rec.SR_INSTANCE_ID, null, null, null, null, null, c_rec.ORGANIZATION_ID, null, MSC_CALENDAR.OMC);
2952:
3007: END IF;
3008: -- SRP enhancement
3009:
3010: /* bug 5937871 */
3011: IF MSC_UTIL.g_collect_srp_data = 'Y' THEN
3012: IF c_rec.ORDER_TYPE=75 THEN
3013: if (c_rec.NEW_SCHEDULE_DATE is null ) then
3014: lv_cal_code_omc:=msc_calendar.GET_CALENDAR_CODE(c_rec.SR_INSTANCE_ID, null, null, null, null, null, c_rec.ORGANIZATION_ID, null, MSC_CALENDAR.OMC);
3015: lv_schedule_date :=MSC_CALENDAR.DATE_OFFSET(lv_cal_code_omc,c_rec.SR_INSTANCE_ID,c_rec.RO_CREATION_DATE,nvl(c_rec.REPAIR_LEAD_TIME,0),1);
3017: END IF ;
3018: END IF ;
3019: /* end bug 5937871*/
3020:
3021: IF MSC_UTIL.g_collect_srp_data = 'Y' THEN
3022:
3023: IF (c_rec.ORDER_TYPE = 1)
3024: OR (c_rec.ORDER_TYPE = 5 AND c_rec.FIRM_PLANNED_TYPE = 1)
3025: OR (c_rec.ORDER_TYPE = 75) OR (c_rec.ORDER_TYPE = 74)
3023: IF (c_rec.ORDER_TYPE = 1)
3024: OR (c_rec.ORDER_TYPE = 5 AND c_rec.FIRM_PLANNED_TYPE = 1)
3025: OR (c_rec.ORDER_TYPE = 75) OR (c_rec.ORDER_TYPE = 74)
3026: THEN -- For Bug 5909379
3027: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
3028: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
3029: ELSE
3030: lv_ITEM_TYPE_ID := c_rec.ITEM_TYPE_ID;
3031: lv_ITEM_TYPE_VALUE := c_rec.ITEM_TYPE_VALUE;
3024: OR (c_rec.ORDER_TYPE = 5 AND c_rec.FIRM_PLANNED_TYPE = 1)
3025: OR (c_rec.ORDER_TYPE = 75) OR (c_rec.ORDER_TYPE = 74)
3026: THEN -- For Bug 5909379
3027: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
3028: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
3029: ELSE
3030: lv_ITEM_TYPE_ID := c_rec.ITEM_TYPE_ID;
3031: lv_ITEM_TYPE_VALUE := c_rec.ITEM_TYPE_VALUE;
3032:
3032:
3033: END IF;
3034:
3035: IF (c_rec.ORDER_TYPE = 86) THEN
3036: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
3037: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
3038: END IF;
3039:
3040: END IF; --IF ((c_rec.SR_MTL_SUPPLY_ID = -1) or
3033: END IF;
3034:
3035: IF (c_rec.ORDER_TYPE = 86) THEN
3036: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
3037: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
3038: END IF;
3039:
3040: END IF; --IF ((c_rec.SR_MTL_SUPPLY_ID = -1) or
3041: -- ( SQL%NOTFOUND )) THEN
3191: c_count:= c_count+1;
3192:
3193: IF c_count> MSC_CL_COLLECTION.PBS THEN
3194: COMMIT;
3195: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The total record count inserted is '||TO_CHAR(c_count));
3196: c_count:= 0;
3197: END IF;
3198:
3199: EXCEPTION
3200: WHEN OTHERS THEN
3201:
3202: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3203:
3204: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'P========================================');
3205: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3206: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_STAGING_SUPPLY');
3207: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3208: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3204: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'P========================================');
3205: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3206: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_STAGING_SUPPLY');
3207: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3208: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3209:
3210: update msc_apps_instances
3211: set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
3212: where instance_id = MSC_CL_COLLECTION.v_instance_id;
3211: set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
3212: where instance_id = MSC_CL_COLLECTION.v_instance_id;
3213: commit;
3214:
3215: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3216: RAISE;
3217:
3218: ELSE
3219: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3215: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3216: RAISE;
3217:
3218: ELSE
3219: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3220:
3221: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'E========================================');
3222: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3223: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_STAGING_SUPPLY');
3217:
3218: ELSE
3219: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3220:
3221: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'E========================================');
3222: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3223: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_STAGING_SUPPLY');
3224: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3225: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3221: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'E========================================');
3222: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3223: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_STAGING_SUPPLY');
3224: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3225: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3226:
3227: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3228: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
3229: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
3226:
3227: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3228: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
3229: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
3230: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3231:
3232: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3233: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3234: FND_MESSAGE.SET_TOKEN('VALUE',
3233: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3234: FND_MESSAGE.SET_TOKEN('VALUE',
3235: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3236: MSC_CL_COLLECTION.v_instance_id));
3237: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3238:
3239: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3240: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORDER_TYPE');
3241: FND_MESSAGE.SET_TOKEN('VALUE',
3239: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3240: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORDER_TYPE');
3241: FND_MESSAGE.SET_TOKEN('VALUE',
3242: MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE',c_rec.ORDER_TYPE));
3243: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3244:
3245: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3246: END IF;
3247:
3241: FND_MESSAGE.SET_TOKEN('VALUE',
3242: MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE',c_rec.ORDER_TYPE));
3243: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3244:
3245: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3246: END IF;
3247:
3248: END;
3249:
3248: END;
3249:
3250: END LOOP;
3251:
3252: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The total record count inserted is '||TO_CHAR(c_count));
3253:
3254: IF(MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
3255: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3256: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_ATTRIBUTES', MSC_CL_COLLECTION.v_instance_id,NULL);
3250: END LOOP;
3251:
3252: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The total record count inserted is '||TO_CHAR(c_count));
3253:
3254: IF(MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
3255: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3256: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_ATTRIBUTES', MSC_CL_COLLECTION.v_instance_id,NULL);
3257: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_OPERATION_REL',MSC_CL_COLLECTION.v_instance_id,NULL );
3258: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_TASK_HIERARCHY',MSC_CL_COLLECTION.v_instance_id, NULL );
3251:
3252: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The total record count inserted is '||TO_CHAR(c_count));
3253:
3254: IF(MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
3255: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3256: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_ATTRIBUTES', MSC_CL_COLLECTION.v_instance_id,NULL);
3257: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_OPERATION_REL',MSC_CL_COLLECTION.v_instance_id,NULL );
3258: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_TASK_HIERARCHY',MSC_CL_COLLECTION.v_instance_id, NULL );
3259:
3258: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_TASK_HIERARCHY',MSC_CL_COLLECTION.v_instance_id, NULL );
3259:
3260: commit;
3261: ELSE
3262: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3263: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_ATTRIBUTES', MSC_CL_COLLECTION.v_instance_id, NULL ,MSC_CL_COLLECTION.v_sub_str);
3264: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_OPERATION_REL',MSC_CL_COLLECTION.v_instance_id, NULL ,MSC_CL_COLLECTION.v_sub_str);
3265: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_WO_TASK_HIERARCHY', MSC_CL_COLLECTION.v_instance_id, NULL ,MSC_CL_COLLECTION.v_sub_str);
3266: commit;
3270: COMMIT;
3271:
3272: FOR extra_rec in c2 LOOP
3273:
3274: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'DUE to PARTIAL REFRESH supplies not loaded related to '||
3275: ' ITEMS with IDs '|| TO_CHAR(extra_rec.inventory_item_id));
3276:
3277: END LOOP;
3278:
3282: -- msc_supplies. The same needs to be called in
3283: -- "else" section these code lines if contineous
3284: -- collections is enabled for this entity.
3285: --==============================================
3286: IF ( MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE
3287: OR MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE
3288: ) THEN
3289:
3290: IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag = MSC_UTIL.SYS_YES) THEN
3283: -- "else" section these code lines if contineous
3284: -- collections is enabled for this entity.
3285: --==============================================
3286: IF ( MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE
3287: OR MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE
3288: ) THEN
3289:
3290: IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag = MSC_UTIL.SYS_YES) THEN
3291:
3286: IF ( MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE
3287: OR MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE
3288: ) THEN
3289:
3290: IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag = MSC_UTIL.SYS_YES) THEN
3291:
3292: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Supplier Responses from iSupplier Portal');
3293:
3294: MSC_CL_SUPPLIER_RESP.LOAD_SUPPLIER_RESPONSE
3288: ) THEN
3289:
3290: IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag = MSC_UTIL.SYS_YES) THEN
3291:
3292: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Supplier Responses from iSupplier Portal');
3293:
3294: MSC_CL_SUPPLIER_RESP.LOAD_SUPPLIER_RESPONSE
3295: ( MSC_CL_COLLECTION.v_instance_id,
3296: MSC_CL_COLLECTION.v_is_complete_refresh,
3304:
3305: END IF;
3306:
3307: /* CP-ACK ends */
3308: IF(MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y'
3309: -- AND MSC_CL_COLLECTION.v_coll_prec.cmro_flag = MSC_UTIL.SYS_YES
3310: AND MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES)
3311: THEN
3312:
3305: END IF;
3306:
3307: /* CP-ACK ends */
3308: IF(MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y'
3309: -- AND MSC_CL_COLLECTION.v_coll_prec.cmro_flag = MSC_UTIL.SYS_YES
3310: AND MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES)
3311: THEN
3312:
3313: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
3306:
3307: /* CP-ACK ends */
3308: IF(MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y'
3309: -- AND MSC_CL_COLLECTION.v_coll_prec.cmro_flag = MSC_UTIL.SYS_YES
3310: AND MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES)
3311: THEN
3312:
3313: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
3314: 'In MSCLSUPB.pls before call to
3309: -- AND MSC_CL_COLLECTION.v_coll_prec.cmro_flag = MSC_UTIL.SYS_YES
3310: AND MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES)
3311: THEN
3312:
3313: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
3314: 'In MSCLSUPB.pls before call to
3315: LOAD_CMRO_FORECAST_SUPPLIES');
3316:
3317: MSC_CL_AHL_ODS_LOAD.LOAD_CMRO_FORECAST_SUPPLIES;
3321: else
3322: -- For continuous refresh
3323:
3324:
3325: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_staging_supply: cont');
3326:
3327: /* CP-AUTO */
3328: /* Following code lines will get executed if any of the Supply type of entity
3329: is getting collected on targeted basis.
3331: targeted basis then we need not to execute lv_cur_sql_stmt because there
3332: is seperate API to take care of Supplier Responses. */
3333:
3334:
3335: IF ((MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
3336: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
3337: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
3338: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
3339: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT)
3332: is seperate API to take care of Supplier Responses. */
3333:
3334:
3335: IF ((MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
3336: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
3337: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
3338: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
3339: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT)
3340: ) THEN
3333:
3334:
3335: IF ((MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
3336: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
3337: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
3338: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
3339: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT)
3340: ) THEN
3341:
3334:
3335: IF ((MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
3336: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
3337: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
3338: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
3339: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT)
3340: ) THEN
3341:
3342: lv_cur_sql_stmt :=
3335: IF ((MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
3336: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
3337: (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
3338: (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
3339: (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT)
3340: ) THEN
3341:
3342: lv_cur_sql_stmt :=
3343: 'SELECT'
3523: || ' AND tps2.SR_TP_SITE_ID(+)= x.OWNING_PARTNER_SITE_ID'
3524: || ' AND tps2.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID'
3525: || ' AND tps2.PARTNER_TYPE(+)= 1'
3526: || ' AND x.SR_INSTANCE_ID=' || MSC_CL_COLLECTION.v_instance_id
3527: || ' AND x.DELETED_FLAG=' || MSC_UTIL.SYS_NO
3528: || ' AND md.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID'
3529: || ' AND md.DESIGNATOR(+)= x.SCHEDULE_DESIGNATOR'
3530: || ' AND md.Organization_ID(+)= x.Organization_ID'
3531: || ' AND md.Designator_Type(+)= 2'
3537: || ' AND x.order_type in (';
3538:
3539:
3540:
3541: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_staging_supply: 5');
3542:
3543: if (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES) and
3544: (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) then
3545: lv_where_clause := '5';
3539:
3540:
3541: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_staging_supply: 5');
3542:
3543: if (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES) and
3544: (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) then
3545: lv_where_clause := '5';
3546: end if;
3547:
3540:
3541: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_staging_supply: 5');
3542:
3543: if (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES) and
3544: (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) then
3545: lv_where_clause := '5';
3546: end if;
3547:
3548: if (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) and
3544: (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) then
3545: lv_where_clause := '5';
3546: end if;
3547:
3548: if (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) and
3549: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
3550: if (lv_where_clause is null) then
3551: lv_where_clause := '3,7,14,15,16,27,30,70';
3552: else
3545: lv_where_clause := '5';
3546: end if;
3547:
3548: if (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) and
3549: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
3550: if (lv_where_clause is null) then
3551: lv_where_clause := '3,7,14,15,16,27,30,70';
3552: else
3553: lv_where_clause := lv_where_clause||',3,7,14,15,16,27,30,70';
3553: lv_where_clause := lv_where_clause||',3,7,14,15,16,27,30,70';
3554: end if;
3555: end if;
3556:
3557: if (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES) and
3558: (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) then
3559: if (lv_where_clause is null) then
3560: lv_where_clause := '1,2,8,11,12,73,74,87';
3561: else
3554: end if;
3555: end if;
3556:
3557: if (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES) and
3558: (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) then
3559: if (lv_where_clause is null) then
3560: lv_where_clause := '1,2,8,11,12,73,74,87';
3561: else
3562: lv_where_clause := lv_where_clause||',1,2,8,11,12,73,74,87';
3562: lv_where_clause := lv_where_clause||',1,2,8,11,12,73,74,87';
3563: end if;
3564: end if;
3565:
3566: if (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES) and
3567: (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) then
3568: if (lv_where_clause is null) then
3569: lv_where_clause := '18';
3570: else
3563: end if;
3564: end if;
3565:
3566: if (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES) and
3567: (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) then
3568: if (lv_where_clause is null) then
3569: lv_where_clause := '18';
3570: else
3571: lv_where_clause := lv_where_clause||',18';
3571: lv_where_clause := lv_where_clause||',18';
3572: end if;
3573: end if;
3574:
3575: if (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES) and
3576: (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) then
3577: if (lv_where_clause is null) then
3578: lv_where_clause := '41';
3579: else
3572: end if;
3573: end if;
3574:
3575: if (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES) and
3576: (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) then
3577: if (lv_where_clause is null) then
3578: lv_where_clause := '41';
3579: else
3580: lv_where_clause := lv_where_clause||',41';
3582: end if;
3583:
3584: lv_cur_sql_stmt := lv_cur_sql_stmt||lv_where_clause ||' )';
3585:
3586: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_staging_supply: 6');
3587:
3588: lv_cur_sql_stmt:=lv_cur_sql_stmt|| ' order by x.ORGANIZATION_ID ';
3589:
3590: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_cur_sql_stmt=' || lv_cur_sql_stmt);
3586: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_staging_supply: 6');
3587:
3588: lv_cur_sql_stmt:=lv_cur_sql_stmt|| ' order by x.ORGANIZATION_ID ';
3589:
3590: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_cur_sql_stmt=' || lv_cur_sql_stmt);
3591:
3592: open cur for lv_cur_sql_stmt USING MSC_CL_COLLECTION.v_chr10, MSC_CL_COLLECTION.v_chr13, MSC_CL_COLLECTION.v_chr10, MSC_CL_COLLECTION.v_chr13;
3593:
3594: LOOP
3783: IF (lv_order_type = 1) OR (lv_order_type = 74) THEN
3784: lv_OLD_SCHEDULE_DATE := lv_NEW_SCHEDULE_DATE;
3785: END IF;
3786: -- SRP enhancement
3787: IF MSC_UTIL.g_collect_srp_data = 'Y' THEN
3788: IF (lv_order_type = 1)
3789: OR (lv_order_type = 5 AND lv_FIRM_PLANNED_TYPE = 1)
3790: OR (lv_order_type = 75) OR (lv_order_type = 74)
3791: OR (lv_order_type = 86) THEN
3788: IF (lv_order_type = 1)
3789: OR (lv_order_type = 5 AND lv_FIRM_PLANNED_TYPE = 1)
3790: OR (lv_order_type = 75) OR (lv_order_type = 74)
3791: OR (lv_order_type = 86) THEN
3792: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
3793: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
3794: END IF;
3795: END IF;
3796:
3789: OR (lv_order_type = 5 AND lv_FIRM_PLANNED_TYPE = 1)
3790: OR (lv_order_type = 75) OR (lv_order_type = 74)
3791: OR (lv_order_type = 86) THEN
3792: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
3793: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
3794: END IF;
3795: END IF;
3796:
3797: --IF ((c_rec.SR_MTL_SUPPLY_ID = -1) or
3944: c_count:= c_count+1;
3945:
3946: IF c_count> MSC_CL_COLLECTION.PBS THEN
3947: COMMIT;
3948: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The total record count inserted is '||TO_CHAR(c_count));
3949: c_count:= 0;
3950: END IF;
3951:
3952:
3954: WHEN OTHERS THEN
3955:
3956: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3957:
3958: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3959: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3960: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3961: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3962: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3958: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3959: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3960: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3961: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3962: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3963:
3964: update msc_apps_instances
3965: set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
3966: where instance_id = MSC_CL_COLLECTION.v_instance_id;
3965: set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
3966: where instance_id = MSC_CL_COLLECTION.v_instance_id;
3967: commit;
3968:
3969: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3970: RAISE;
3971:
3972: ELSE
3973: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3969: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3970: RAISE;
3971:
3972: ELSE
3973: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3974:
3975: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3976: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3977: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3971:
3972: ELSE
3973: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3974:
3975: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3976: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3977: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3978: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3979: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3975: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3976: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3977: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3978: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3979: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3980:
3981: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3982: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_COLLECTION.ITEM_NAME');
3983: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( lv_INVENTORY_ITEM_ID));
3980:
3981: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3982: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_COLLECTION.ITEM_NAME');
3983: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( lv_INVENTORY_ITEM_ID));
3984: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3985:
3986: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3987: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3988: FND_MESSAGE.SET_TOKEN('VALUE',
3987: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3988: FND_MESSAGE.SET_TOKEN('VALUE',
3989: MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
3990: MSC_CL_COLLECTION.v_instance_id));
3991: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3992:
3993: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3994: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORDER_TYPE');
3995: FND_MESSAGE.SET_TOKEN('VALUE',
3993: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3994: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORDER_TYPE');
3995: FND_MESSAGE.SET_TOKEN('VALUE',
3996: MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE',lv_ORDER_TYPE));
3997: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3998:
3999: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4000: END IF;
4001:
3995: FND_MESSAGE.SET_TOKEN('VALUE',
3996: MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE',lv_ORDER_TYPE));
3997: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3998:
3999: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4000: END IF;
4001:
4002: END;
4003:
4002: END;
4003:
4004: END LOOP;
4005:
4006: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The total record count inserted is '||TO_CHAR(c_count));
4007: COMMIT;
4008:
4009: /*
4010: FOR extra_rec in c2 LOOP
4008:
4009: /*
4010: FOR extra_rec in c2 LOOP
4011:
4012: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'DUE to PARTIAL REFRESH supplies not loaded related to '||
4013: ' ITEMS with IDs '|| TO_CHAR(extra_rec.inventory_item_id));
4014:
4015: END LOOP;
4016: */
4027:
4028: -- We will call this API only if "Supplier Responses"
4029: -- entity needs to be collected as Targeted Refresh
4030: --===================================================
4031: IF ( MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE
4032: OR MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE
4033: ) THEN
4034:
4035: IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.suprep_sn_flag = MSC_UTIL.SYS_TGT) THEN
4028: -- We will call this API only if "Supplier Responses"
4029: -- entity needs to be collected as Targeted Refresh
4030: --===================================================
4031: IF ( MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE
4032: OR MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE
4033: ) THEN
4034:
4035: IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.suprep_sn_flag = MSC_UTIL.SYS_TGT) THEN
4036:
4031: IF ( MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE
4032: OR MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE
4033: ) THEN
4034:
4035: IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.suprep_sn_flag = MSC_UTIL.SYS_TGT) THEN
4036:
4037: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Supplier Responses from iSupplier Portal');
4038:
4039: MSC_CL_SUPPLIER_RESP.LOAD_SUPPLIER_RESPONSE
4033: ) THEN
4034:
4035: IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.suprep_sn_flag = MSC_UTIL.SYS_TGT) THEN
4036:
4037: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Supplier Responses from iSupplier Portal');
4038:
4039: MSC_CL_SUPPLIER_RESP.LOAD_SUPPLIER_RESPONSE
4040: ( MSC_CL_COLLECTION.v_instance_id,
4041: FALSE, --MSC_CL_COLLECTION.v_is_complete_refresh,
4049:
4050: END IF;
4051: end if;
4052:
4053: IF(MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y'
4054: -- and MSC_CL_COLLECTION.v_coll_prec.eam_info_flag = MSC_UTIL.SYS_YES
4055: and MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES)
4056: THEN
4057:
4050: END IF;
4051: end if;
4052:
4053: IF(MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y'
4054: -- and MSC_CL_COLLECTION.v_coll_prec.eam_info_flag = MSC_UTIL.SYS_YES
4055: and MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES)
4056: THEN
4057:
4058: /*We will not pass the type of refresh here, it'll be determined in
4051: end if;
4052:
4053: IF(MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y'
4054: -- and MSC_CL_COLLECTION.v_coll_prec.eam_info_flag = MSC_UTIL.SYS_YES
4055: and MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES)
4056: THEN
4057:
4058: /*We will not pass the type of refresh here, it'll be determined in
4059: * package: MSC_CL_AHL_ODS_LOAD itself */
4059: * package: MSC_CL_AHL_ODS_LOAD itself */
4060: MSC_CL_EAM_ODS_LOAD.LOAD_EAM_FORECASTS;
4061: END IF;
4062:
4063: IF(MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y'
4064: and MSC_CL_COLLECTION.v_coll_prec.osp_supply = MSC_UTIL.SYS_YES)
4065: THEN
4066: MSC_CL_AHL_ODS_LOAD.LOAD_OSP_SUPPLY;
4067: END IF;
4060: MSC_CL_EAM_ODS_LOAD.LOAD_EAM_FORECASTS;
4061: END IF;
4062:
4063: IF(MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y'
4064: and MSC_CL_COLLECTION.v_coll_prec.osp_supply = MSC_UTIL.SYS_YES)
4065: THEN
4066: MSC_CL_AHL_ODS_LOAD.LOAD_OSP_SUPPLY;
4067: END IF;
4068:
4079: BEGIN
4080:
4081: lv_temp_supply_tbl := 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
4082: lv_sql_stmt:=
4083: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_temp_supply_tbl
4084: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_SUPPLIES '
4085: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
4086: ||' AND plan_id = -1 AND ( ' ;
4087:
4080:
4081: lv_temp_supply_tbl := 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
4082: lv_sql_stmt:=
4083: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_temp_supply_tbl
4084: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_SUPPLIES '
4085: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
4086: ||' AND plan_id = -1 AND ( ' ;
4087:
4088: -- agmcont:
4086: ||' AND plan_id = -1 AND ( ' ;
4087:
4088: -- agmcont:
4089:
4090: IF MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES THEN
4091: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
4092: if (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) then
4093: lv_where_clause := '5';
4094: end if;
4088: -- agmcont:
4089:
4090: IF MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES THEN
4091: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
4092: if (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) then
4093: lv_where_clause := '5';
4094: end if;
4095: else
4096: lv_where_clause := '5';
4095: else
4096: lv_where_clause := '5';
4097: end if;
4098: END IF;
4099: IF (MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag = MSC_UTIL.SYS_YES) THEN
4100: IF (lv_where_clause IS NULL) THEN
4101: lv_where_clause := '29';
4102: ELSE
4103: lv_where_clause := lv_where_clause||', 29';
4102: ELSE
4103: lv_where_clause := lv_where_clause||', 29';
4104: END IF;
4105: END IF;
4106: IF (MSC_CL_COLLECTION.v_coll_prec.cmro_closed_wo = MSC_UTIL.SYS_YES) THEN
4107: IF (lv_where_clause IS NULL) THEN
4108: lv_where_clause := '90';
4109: ELSE
4110: lv_where_clause := lv_where_clause||', 90';
4109: ELSE
4110: lv_where_clause := lv_where_clause||', 90';
4111: END IF;
4112: END IF;
4113: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
4114:
4115: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
4116: if (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
4117: IF (lv_where_clause IS NULL) THEN
4112: END IF;
4113: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
4114:
4115: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
4116: if (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
4117: IF (lv_where_clause IS NULL) THEN
4118: lv_where_clause := '3,7,14,15,16,27,30,70';
4119: ELSE
4120: lv_where_clause := lv_where_clause||', 3,7,14,15,16,27,30,70';
4131:
4132: -- Question what does REPT item - 16 represent?
4133: -- It is assumed that it should be grouped with WIP flag.
4134:
4135: IF (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES) THEN
4136: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
4137: if (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) then
4138: IF (lv_where_clause IS NULL) THEN
4139: lv_where_clause := '1,2,8,11,12,73,74,87';
4133: -- It is assumed that it should be grouped with WIP flag.
4134:
4135: IF (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES) THEN
4136: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
4137: if (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) then
4138: IF (lv_where_clause IS NULL) THEN
4139: lv_where_clause := '1,2,8,11,12,73,74,87';
4140: ELSE
4141: lv_where_clause := lv_where_clause||', 1,2,8,11,12,73,74,87';
4149: END IF;
4150: end if;
4151: END IF;
4152:
4153: IF (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES) THEN
4154: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
4155: if (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) then
4156: IF (lv_where_clause IS NULL) THEN
4157: lv_where_clause := '18';
4151: END IF;
4152:
4153: IF (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES) THEN
4154: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
4155: if (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) then
4156: IF (lv_where_clause IS NULL) THEN
4157: lv_where_clause := '18';
4158: ELSE
4159: lv_where_clause := lv_where_clause||', 18';
4167: END IF;
4168: end if;
4169: END IF;
4170:
4171: IF (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag = MSC_UTIL.SYS_YES) THEN
4172: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
4173: NULL;
4174: Else
4175:
4181: end if;
4182:
4183: END IF; -- Added for 5909379 SRP Additions
4184:
4185: IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag = MSC_UTIL.SYS_YES) THEN
4186: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
4187: NULL;
4188: Else
4189:
4196:
4197: END IF; -- Added for 5935273 SRP Additions
4198:
4199:
4200: IF (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES) THEN
4201: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
4202: if (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) then
4203: IF (lv_where_clause IS NULL) THEN
4204: lv_where_clause := '41';
4198:
4199:
4200: IF (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES) THEN
4201: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
4202: if (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) then
4203: IF (lv_where_clause IS NULL) THEN
4204: lv_where_clause := '41';
4205: ELSE
4206: lv_where_clause := lv_where_clause||', 41';
4216: END IF;
4217:
4218: -- CP-ACK starts
4219:
4220: IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag = MSC_UTIL.SYS_YES) THEN
4221:
4222: -- CP-AUTO
4223: IF (MSC_CL_COLLECTION.v_is_cont_refresh) then
4224:
4221:
4222: -- CP-AUTO
4223: IF (MSC_CL_COLLECTION.v_is_cont_refresh) then
4224:
4225: IF (MSC_CL_COLLECTION.v_coll_prec.suprep_sn_flag = MSC_UTIL.SYS_TGT) THEN
4226:
4227: IF (lv_where_clause IS NULL) THEN
4228: lv_where_clause := MSC_CL_COLLECTION.G_MRP_PO_ACK;
4229: ELSE
4250: -- loaded during partial replacement since
4251: -- User Defined supply load is parameter independent.
4252: -- For that purpose complete refresh needs to be run.
4253:
4254: IF (MSC_CL_COLLECTION.v_coll_prec.osp_supply = MSC_UTIL.SYS_YES) THEN
4255: IF (lv_where_clause IS NULL) THEN
4256: lv_where_clause := '974';
4257: ELSE
4258: lv_where_clause := lv_where_clause||', 974';
4258: lv_where_clause := lv_where_clause||', 974';
4259: END IF;
4260: END IF;
4261:
4262: IF (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES) THEN
4263: IF (lv_where_clause IS NULL) THEN
4264: lv_where_clause := '992';
4265: ELSE
4266: lv_where_clause := lv_where_clause||', 992';
4266: lv_where_clause := lv_where_clause||', 992';
4267: END IF;
4268: END IF;
4269:
4270: IF (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES) THEN
4271: IF (lv_where_clause IS NULL) THEN
4272: lv_where_clause := '92';
4273: ELSE
4274: lv_where_clause := lv_where_clause||', 92';
4275: END IF;
4276: END IF;
4277:
4278:
4279: IF NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
4280: lv_sql_stmt := lv_sql_stmt||' organization_id NOT '||MSC_UTIL.v_in_org_str;
4281: END IF;
4282:
4283: IF NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS
4276: END IF;
4277:
4278:
4279: IF NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
4280: lv_sql_stmt := lv_sql_stmt||' organization_id NOT '||MSC_UTIL.v_in_org_str;
4281: END IF;
4282:
4283: IF NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS
4284: AND NOT (MSC_CL_COLLECTION.v_is_complete_refresh) THEN
4279: IF NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
4280: lv_sql_stmt := lv_sql_stmt||' organization_id NOT '||MSC_UTIL.v_in_org_str;
4281: END IF;
4282:
4283: IF NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS
4284: AND NOT (MSC_CL_COLLECTION.v_is_complete_refresh) THEN
4285: lv_sql_stmt := lv_sql_stmt||' OR ';
4286: END IF;
4287:
4291: END IF;
4292:
4293: lv_sql_stmt := lv_sql_stmt ||' )';
4294:
4295: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Load ODS supply :sql stmt being executed: '||lv_sql_stmt);
4296: EXECUTE IMMEDIATE lv_sql_stmt;
4297:
4298: COMMIT;
4299:
4304: set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
4305: where instance_id = MSC_CL_COLLECTION.v_instance_id;
4306: commit;
4307:
4308: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4309: RAISE;
4310:
4311: END LOAD_ODS_SUPPLY;
4312: --start of change as per the aggregation review comments.
4337:
4338: /* copy sql structure:
4339: lv_copySQL_fixedpart
4340: ||' AND ('
4341: ||' (ORGANIZATION_ID NOT '||MSC_UTIL.v_in_org_str) --if not all orgs
4342: ||' OR order_type NOT IN ('||lv_collected_ordertypes||')' --ifnot compl coll
4343: ||' AND (NOT '||lv_collected_entites_filter2 ) --ifnot compl coll
4344: || ')'
4345:
4345:
4346:
4347: -- agmcont:
4348:
4349: IF MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES
4350: THEN
4351: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
4352: THEN
4353: IF (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT)
4349: IF MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES
4350: THEN
4351: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
4352: THEN
4353: IF (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT)
4354: THEN
4355: lv_collected_ordertypes := '5';
4356: END IF;
4357: ELSE
4359: END IF;
4360: END IF;
4361:
4362: IF (MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag =
4363: MSC_UTIL.SYS_YES)
4364: THEN
4365: IF (lv_collected_ordertypes IS NULL)
4366: THEN
4367: lv_collected_ordertypes := '29';
4369: lv_collected_ordertypes := lv_collected_ordertypes || ', 29';
4370: END IF;
4371: END IF;
4372:
4373: IF (MSC_CL_COLLECTION.v_coll_prec.cmro_closed_wo = MSC_UTIL.SYS_YES)
4374: THEN
4375: IF (lv_collected_ordertypes IS NULL)
4376: THEN
4377: lv_collected_ordertypes := '90';
4379: lv_collected_ordertypes := lv_collected_ordertypes || ', 90';
4380: END IF;
4381: END IF;
4382:
4383: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES)
4384: THEN
4385: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
4386: THEN
4387: IF (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT)
4383: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES)
4384: THEN
4385: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
4386: THEN
4387: IF (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT)
4388: THEN
4389: IF (lv_collected_ordertypes IS NULL)
4390: THEN
4391: lv_collected_ordertypes := '3,7,14,15,16,27,30,70';
4407:
4408: -- Question what does REPT item - 16 represent?
4409: -- It is assumed that it should be grouped with WIP flag.
4410:
4411: IF (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES)
4412: THEN
4413: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
4414: THEN
4415: IF (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT)
4411: IF (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES)
4412: THEN
4413: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
4414: THEN
4415: IF (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT)
4416: THEN
4417: IF (lv_collected_ordertypes IS NULL)
4418: THEN
4419: lv_collected_ordertypes := '1,2,8,11,12,73,74,87';
4432: END IF;
4433: END IF;
4434: END IF;
4435:
4436: IF (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES)
4437: THEN
4438: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
4439: THEN
4440: IF (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT)
4436: IF (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES)
4437: THEN
4438: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
4439: THEN
4440: IF (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT)
4441: THEN
4442: IF (lv_collected_ordertypes IS NULL)
4443: THEN
4444: lv_collected_ordertypes := '18';
4456: END IF;
4457: END IF;
4458:
4459: IF (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag =
4460: MSC_UTIL.SYS_YES)
4461: THEN
4462: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
4463: THEN
4464: NULL;
4472: END IF;
4473: END IF; -- Added for 5909379 SRP Additions
4474:
4475: IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag =
4476: MSC_UTIL.SYS_YES)
4477: THEN
4478: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
4479: THEN
4480: NULL;
4489: END IF; -- Added for 5935273 SRP Additions
4490:
4491:
4492: IF (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag =
4493: MSC_UTIL.SYS_YES)
4494: THEN
4495: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
4496: THEN
4497: IF (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT)
4493: MSC_UTIL.SYS_YES)
4494: THEN
4495: IF (MSC_CL_COLLECTION.v_is_cont_refresh)
4496: THEN
4497: IF (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT)
4498: THEN
4499: IF (lv_collected_ordertypes IS NULL)
4500: THEN
4501: lv_collected_ordertypes := '41';
4515:
4516: /* CP-ACK starts */
4517:
4518: /* IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag =
4519: MSC_UTIL.SYS_YES)
4520: THEN
4521: /* CP-AUTO */
4522: /*IF (MSC_CL_COLLECTION.v_is_cont_refresh)
4523: THEN
4521: /* CP-AUTO */
4522: /*IF (MSC_CL_COLLECTION.v_is_cont_refresh)
4523: THEN
4524: IF (MSC_CL_COLLECTION.v_coll_prec.suprep_sn_flag =
4525: MSC_UTIL.SYS_TGT)
4526: THEN
4527: IF (lv_collected_ordertypes IS NULL)
4528: THEN
4529: lv_collected_ordertypes := MSC_CL_COLLECTION.G_MRP_PO_ACK;
4555:
4556: /* start of code to prepare lv_collected_entites_filter2*/
4557:
4558: /* IF ( (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4559: MSC_UTIL.SYS_YES)
4560: AND (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4561: MSC_UTIL.SYS_YES))
4562: THEN
4563: lv_collected_entites_filter2 := '(ORDER_TYPE =92)';
4557:
4558: /* IF ( (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4559: MSC_UTIL.SYS_YES)
4560: AND (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4561: MSC_UTIL.SYS_YES))
4562: THEN
4563: lv_collected_entites_filter2 := '(ORDER_TYPE =92)';
4564: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4565: MSC_UTIL.SYS_NO)
4561: MSC_UTIL.SYS_YES))
4562: THEN
4563: lv_collected_entites_filter2 := '(ORDER_TYPE =92)';
4564: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4565: MSC_UTIL.SYS_NO)
4566: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4567: MSC_UTIL.SYS_YES))
4568: THEN
4569: Lv_collected_entites_filter2 :=
4563: lv_collected_entites_filter2 := '(ORDER_TYPE =92)';
4564: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4565: MSC_UTIL.SYS_NO)
4566: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4567: MSC_UTIL.SYS_YES))
4568: THEN
4569: Lv_collected_entites_filter2 :=
4570: '( ORDER_TYPE =92 AND MAINTENANCE_OBJECT_SOURCE =2 ) ';
4571: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4568: THEN
4569: Lv_collected_entites_filter2 :=
4570: '( ORDER_TYPE =92 AND MAINTENANCE_OBJECT_SOURCE =2 ) ';
4571: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4572: MSC_UTIL.SYS_YES)
4573: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4574: MSC_UTIL.SYS_NO))
4575: THEN
4576: lv_collected_entites_filter2 :=
4570: '( ORDER_TYPE =92 AND MAINTENANCE_OBJECT_SOURCE =2 ) ';
4571: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4572: MSC_UTIL.SYS_YES)
4573: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4574: MSC_UTIL.SYS_NO))
4575: THEN
4576: lv_collected_entites_filter2 :=
4577: '( ORDER_TYPE =92 AND MAINTENANCE_OBJECT_SOURCE =1 ) ';
4578: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4575: THEN
4576: lv_collected_entites_filter2 :=
4577: '( ORDER_TYPE =92 AND MAINTENANCE_OBJECT_SOURCE =1 ) ';
4578: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4579: MSC_UTIL.SYS_NO)
4580: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4581: MSC_UTIL.SYS_NO))
4582: THEN
4583: lv_collected_entites_filter2 := NULL;
4577: '( ORDER_TYPE =92 AND MAINTENANCE_OBJECT_SOURCE =1 ) ';
4578: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4579: MSC_UTIL.SYS_NO)
4580: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4581: MSC_UTIL.SYS_NO))
4582: THEN
4583: lv_collected_entites_filter2 := NULL;
4584: END IF;
4585:
4587: /* start of code to prepare lv_copySQL_variablepart*/
4588: /*firstcondn := TRUE;
4589:
4590: IF (NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag =
4591: MSC_UTIL.G_ALL_ORGANIZATIONS)
4592: THEN
4593: IF (NOT firstcondn)
4594: THEN
4595: lv_copySQL_variablepart := lv_copySQL_variablepart || ' AND ';
4595: lv_copySQL_variablepart := lv_copySQL_variablepart || ' AND ';
4596: END IF;
4597:
4598: lv_copySQL_variablepart :=
4599: '(ORGANIZATION_ID NOT ' || MSC_UTIL.v_in_org_str || ')';
4600: firstcondn := FALSE;
4601: END IF;
4602:
4603: IF NOT ( MSC_CL_COLLECTION.v_is_complete_refresh
4645: ELSE
4646: lv_sql_stmt := lv_copySQL_fixedpart|| ' AND ('|| lv_copySQL_variablepart|| ')';
4647: END IF;
4648:
4649: MSC_UTIL.LOG_MSG (MSC_UTIL.G_D_STATUS,'Load ODS supply : lv_collected_entites_filter2 -' || lv_collected_entites_filter2);
4650: MSC_UTIL.LOG_MSG (MSC_UTIL.G_D_STATUS,'Load ODS supply : lv_copySQL_variablepart -' || lv_copySQL_variablepart);
4651: MSC_UTIL.LOG_MSG (MSC_UTIL.G_D_STATUS, 'Load ODS supply :sql stmt being executed: ' || lv_sql_stmt);
4652:
4653: EXECUTE IMMEDIATE lv_sql_stmt;
4646: lv_sql_stmt := lv_copySQL_fixedpart|| ' AND ('|| lv_copySQL_variablepart|| ')';
4647: END IF;
4648:
4649: MSC_UTIL.LOG_MSG (MSC_UTIL.G_D_STATUS,'Load ODS supply : lv_collected_entites_filter2 -' || lv_collected_entites_filter2);
4650: MSC_UTIL.LOG_MSG (MSC_UTIL.G_D_STATUS,'Load ODS supply : lv_copySQL_variablepart -' || lv_copySQL_variablepart);
4651: MSC_UTIL.LOG_MSG (MSC_UTIL.G_D_STATUS, 'Load ODS supply :sql stmt being executed: ' || lv_sql_stmt);
4652:
4653: EXECUTE IMMEDIATE lv_sql_stmt;
4654:
4647: END IF;
4648:
4649: MSC_UTIL.LOG_MSG (MSC_UTIL.G_D_STATUS,'Load ODS supply : lv_collected_entites_filter2 -' || lv_collected_entites_filter2);
4650: MSC_UTIL.LOG_MSG (MSC_UTIL.G_D_STATUS,'Load ODS supply : lv_copySQL_variablepart -' || lv_copySQL_variablepart);
4651: MSC_UTIL.LOG_MSG (MSC_UTIL.G_D_STATUS, 'Load ODS supply :sql stmt being executed: ' || lv_sql_stmt);
4652:
4653: EXECUTE IMMEDIATE lv_sql_stmt;
4654:
4655: COMMIT;
4661: WHERE instance_id = MSC_CL_COLLECTION.v_instance_id;
4662:
4663: COMMIT;
4664:
4665: MSC_UTIL.LOG_MSG (MSC_UTIL.G_D_STATUS, SQLERRM);
4666: RAISE;
4667: END LOAD_ODS_SUPPLY;*/
4668:
4669: --end of change as per the aggregation review comments.
4674: lv_sql_stmt VARCHAR2(32767);
4675:
4676: BEGIN
4677:
4678: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
4679: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
4680: ELSE
4681: lv_supply_tbl:= 'MSC_SUPPLIES';
4682: END IF;