[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: --==================================================================
431: AND tps2.SR_TP_SITE_ID(+)= x.OWNING_PARTNER_SITE_ID
432: AND tps2.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
433: AND tps2.PARTNER_TYPE(+)= 1
434: AND x.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
435: AND x.DELETED_FLAG= MSC_UTIL.SYS_NO
436: AND md.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
437: AND md.DESIGNATOR(+)= x.SCHEDULE_DESIGNATOR
438: AND md.Organization_ID(+)= x.Organization_ID
439: AND md.Designator_Type(+)= 2 -- MPS
466: FROM MSC_ITEM_ID_LID t1,
467: MSC_ST_SUPPLIES x,
468: MSC_TP_SITE_ID_LID tps1,
469: MSC_TP_SITE_ID_LID tps2
470: WHERE x.DELETED_FLAG= MSC_UTIL.SYS_YES
471: AND x.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
472: AND t1.SR_INVENTORY_ITEM_ID(+)= x.INVENTORY_ITEM_ID
473: AND t1.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
474: AND tps1.SR_TP_SITE_ID(+)= x.PLANNING_PARTNER_SITE_ID
498: /* CP-ACK starts */
499: lv_po_dock_date_ref := nvl(fnd_profile.value('MSC_PO_DOCK_DATE_CALC_PREF'), MSC_CL_COLLECTION.PROMISED_DATE_PREF);
500: /* CP-ACk ends */
501:
502: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
503: lv_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
504: ELSE
505: lv_tbl:= 'MSC_SUPPLIES';
506: END IF;
772: ||' )';
773:
774:
775:
776: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply');
777:
778: IF MSC_CL_COLLECTION.v_is_complete_refresh AND MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
779:
780: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1);
774:
775:
776: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply');
777:
778: IF MSC_CL_COLLECTION.v_is_complete_refresh AND MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
779:
780: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1);
781:
782: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
778: IF MSC_CL_COLLECTION.v_is_complete_refresh AND MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
779:
780: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1);
781:
782: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
783: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1);
784: ELSE
785: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
786: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
781:
782: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
783: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1);
784: ELSE
785: MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
786: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIES', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
787: END IF;
788:
789: END IF;
819: ||' AND SR_INSTANCE_ID= :v_instance_id '
820: ||' AND ORDER_TYPE= 11 ' --Intransit shipment
821: ||' AND SR_MTL_SUPPLY_ID= -1 ';
822:
823: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
824: EXECUTE IMMEDIATE lv_sql_stmt1 USING MSC_CL_COLLECTION.v_last_collection_id,
825: MSC_CL_COLLECTION.v_current_date,
826: MSC_CL_COLLECTION.v_current_user,
827: MSC_CL_COLLECTION.v_instance_id;
825: MSC_CL_COLLECTION.v_current_date,
826: MSC_CL_COLLECTION.v_current_user,
827: MSC_CL_COLLECTION.v_instance_id;
828: ELSE
829: lv_sql_stmt1:=lv_sql_stmt1||' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
830: EXECUTE IMMEDIATE lv_sql_stmt1 USING MSC_CL_COLLECTION.v_last_collection_id,
831: MSC_CL_COLLECTION.v_current_date,
832: MSC_CL_COLLECTION.v_current_user,
833: MSC_CL_COLLECTION.v_instance_id;
841: FOR c_rec IN c1_d LOOP
842: --1 =PO, 2 = PO REQ, 8 = PO receiving, 12= Intrasit Receipt
843: IF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) or
844: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) and
845: (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- PO
846:
847: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd PO-0');
848:
849:
843: IF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) or
844: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) and
845: (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- PO
846:
847: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd PO-0');
848:
849:
850: UPDATE MSC_SUPPLIES ms
851: SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
861:
862: /*3 Discret Job, 7 Non STandard Job, 27 Flow schedule, 70 Eam supply: ds change change,86 External Repair Order */
863: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (3,7,27, 70,86)) or
864: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (3,7,27,70)) and
865: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- WIP_JOB
866:
867: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP-0');
868: if c_rec.ORDER_TYPE = 70 then
869: MSC_CL_COLLECTION.link_top_transaction_id_req := TRUE;
863: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (3,7,27, 70,86)) or
864: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (3,7,27,70)) and
865: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- WIP_JOB
866:
867: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP-0');
868: if c_rec.ORDER_TYPE = 70 then
869: MSC_CL_COLLECTION.link_top_transaction_id_req := TRUE;
870: end if;
871:
884:
885: -- 14=discrete job co product, 15 non stanstard job co-product
886: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (14,15)) or
887: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (14,15)) and
888: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- DISCRETE JOB COMPONENT
889:
890: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP DSC JOB-0');
891:
892:
886: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (14,15)) or
887: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (14,15)) and
888: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- DISCRETE JOB COMPONENT
889:
890: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP DSC JOB-0');
891:
892:
893: UPDATE MSC_SUPPLIES ms
894: SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
905: AND OPERATION_SEQ_NUM= NVL(c_rec.OPERATION_SEQ_NUM,OPERATION_SEQ_NUM);
906:
907: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 30) or
908: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 30) and
909: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- REPT ITEM
910:
911: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP REPT ITEM-0');
912:
913:
907: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 30) or
908: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 30) and
909: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- REPT ITEM
910:
911: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP REPT ITEM-0');
912:
913:
914: UPDATE MSC_SUPPLIES
915: SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
929: AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
930:
931: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 18) or
932: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 18) and
933: (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- ONHAND
934:
935: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd OH-0');
936:
937:
931: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 18) or
932: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 18) and
933: (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- ONHAND
934:
935: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd OH-0');
936:
937:
938: UPDATE MSC_SUPPLIES
939: --SET NEW_ORDER_QUANTITY= 0.0,
947: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
948: AND ORDER_TYPE= 18
949: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
950: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
951: AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
952: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
953: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
954: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
955: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
948: AND ORDER_TYPE= 18
949: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
950: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
951: AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
952: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
953: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
954: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
955: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
956: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
949: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
950: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
951: AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
952: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
953: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
954: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
955: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
956: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
957: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
950: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
951: AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
952: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
953: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
954: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
955: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
956: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
957: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
958: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
951: AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
952: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
953: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
954: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
955: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
956: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
957: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
958: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
959: AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
952: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
953: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
954: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
955: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
956: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
957: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
958: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
959: AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
960:
953: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
954: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
955: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
956: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
957: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
958: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
959: AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
960:
961: /* planned order */
954: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
955: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
956: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
957: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
958: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
959: AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
960:
961: /* planned order */
962: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 5) or
955: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
956: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
957: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
958: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
959: AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
960:
961: /* planned order */
962: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 5) or
963: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 5) and
960:
961: /* planned order */
962: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 5) or
963: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 5) and
964: (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- MPS
965:
966:
967: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd MPS-0');
968:
963: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 5) and
964: (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- MPS
965:
966:
967: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd MPS-0');
968:
969:
970: UPDATE MSC_SUPPLIES
971: SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
982: AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
983:
984: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 41) or
985: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 41) and
986: (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- USER DEFINED
987:
988: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd USUP-0');
989:
990:
984: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 41) or
985: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 41) and
986: (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- USER DEFINED
987:
988: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd USUP-0');
989:
990:
991: UPDATE MSC_SUPPLIES
992: SET OLD_NEW_SCHEDULE_DATE = NEW_SCHEDULE_DATE,
1039:
1040: BEGIN
1041:
1042: -- SRP enhancement
1043: IF MSC_UTIL.g_collect_srp_data = 'Y' THEN
1044: IF (c_rec.ORDER_TYPE = 1)
1045: OR (c_rec.ORDER_TYPE = 5 AND c_rec.FIRM_PLANNED_TYPE = 1)
1046: OR (c_rec.ORDER_TYPE = 75) OR (c_rec.ORDER_TYPE = 74)
1047: OR (c_rec.ORDER_TYPE = 86) THEN
1044: IF (c_rec.ORDER_TYPE = 1)
1045: OR (c_rec.ORDER_TYPE = 5 AND c_rec.FIRM_PLANNED_TYPE = 1)
1046: OR (c_rec.ORDER_TYPE = 75) OR (c_rec.ORDER_TYPE = 74)
1047: OR (c_rec.ORDER_TYPE = 86) THEN
1048: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
1049: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
1050: ELSE
1051: lv_ITEM_TYPE_ID := c_rec.ITEM_TYPE_ID;
1052: lv_ITEM_TYPE_VALUE := c_rec.ITEM_TYPE_VALUE;
1045: OR (c_rec.ORDER_TYPE = 5 AND c_rec.FIRM_PLANNED_TYPE = 1)
1046: OR (c_rec.ORDER_TYPE = 75) OR (c_rec.ORDER_TYPE = 74)
1047: OR (c_rec.ORDER_TYPE = 86) THEN
1048: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
1049: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
1050: ELSE
1051: lv_ITEM_TYPE_ID := c_rec.ITEM_TYPE_ID;
1052: lv_ITEM_TYPE_VALUE := c_rec.ITEM_TYPE_VALUE;
1053: END IF;
1116: lv_schedule_date :=c_rec.NEW_SCHEDULE_DATE;
1117:
1118: END IF;
1119: /* bug 5937871 */
1120: IF MSC_UTIL.g_collect_srp_data = 'Y' THEN
1121: IF c_rec.ORDER_TYPE=75 THEN
1122: if (c_rec.NEW_SCHEDULE_DATE is null ) then
1123: 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);
1124: 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);
1132: --=================== PO SUPPLIES =====================
1133:
1134: IF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) or
1135: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) and
1136: (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- PO
1137:
1138: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd PO');
1139:
1140:
1134: IF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) or
1135: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (1,2,8,11,12,73,74,87)) and
1136: (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- PO
1137:
1138: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd PO');
1139:
1140:
1141: IF c_rec.SR_MTL_SUPPLY_ID<> -1 THEN
1142:
1251:
1252: --=================== WIP JOB SUPPLIES =====================
1253: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (3,7,27,70,75,86)) or /* 70 eam supply*/
1254: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (3,7,27,70)) and
1255: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- WIP_JOB
1256:
1257:
1258: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP JOB');
1259:
1254: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (3,7,27,70)) and
1255: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- WIP_JOB
1256:
1257:
1258: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP JOB');
1259:
1260:
1261: /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1262: UPDATE MSC_SUPPLIES
1368:
1369: --=================== WIP DISCRETE JOB COMPONENT SUPPLIES =====================
1370: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (14,15)) or
1371: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (14,15)) and
1372: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- DISCRETE JOB COMPONENT
1373:
1374: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP DSC JOB');
1375:
1376:
1370: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE IN (14,15)) or
1371: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE IN (14,15)) and
1372: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- DISCRETE JOB COMPONENT
1373:
1374: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd WIP DSC JOB');
1375:
1376:
1377: /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1378: UPDATE MSC_SUPPLIES
1482:
1483: --=================== REPETITIVE ITEM SUPPLIES =====================
1484: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 30) or
1485: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 30) and
1486: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- REPT ITEM
1487:
1488: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd REPT ITEMS');
1489:
1490: /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1484: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 30) or
1485: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 30) and
1486: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- REPT ITEM
1487:
1488: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd REPT ITEMS');
1489:
1490: /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1491: UPDATE MSC_SUPPLIES
1492: SET
1582:
1583: --=================== ONHAND SUPPLIES =====================
1584: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 18) or
1585: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 18) and
1586: (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- ONHAND
1587:
1588:
1589: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd OH');
1590:
1585: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 18) and
1586: (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- ONHAND
1587:
1588:
1589: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd OH');
1590:
1591:
1592: /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1593: UPDATE MSC_SUPPLIES
1624: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1625: AND ORDER_TYPE= c_rec.ORDER_TYPE
1626: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
1627: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1628: AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
1629: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
1630: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
1631: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
1632: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1625: AND ORDER_TYPE= c_rec.ORDER_TYPE
1626: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
1627: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1628: AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
1629: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
1630: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
1631: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
1632: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1633: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1626: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
1627: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1628: AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
1629: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
1630: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
1631: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
1632: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1633: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1634: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1627: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1628: AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
1629: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
1630: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
1631: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
1632: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1633: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1634: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1635: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1628: AND NVL(SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)= NVL( c_rec.SUBINVENTORY_CODE, MSC_UTIL.NULL_CHAR)
1629: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
1630: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
1631: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
1632: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1633: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1634: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1635: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1636: AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
1629: AND NVL(LOT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.LOT_NUMBER, MSC_UTIL.NULL_CHAR)
1630: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
1631: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
1632: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1633: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1634: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1635: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1636: AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
1637:
1630: AND NVL(PROJECT_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.PROJECT_ID, MSC_UTIL.NULL_VALUE)
1631: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
1632: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1633: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1634: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1635: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1636: AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
1637:
1638: --=================== MPS SUPPLIES =====================
1631: AND NVL(TASK_ID, MSC_UTIL.NULL_VALUE)= NVL( c_rec.TASK_ID, MSC_UTIL.NULL_VALUE)
1632: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1633: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1634: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1635: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1636: AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
1637:
1638: --=================== MPS SUPPLIES =====================
1639: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 5) or
1632: AND NVL(UNIT_NUMBER, MSC_UTIL.NULL_CHAR)= NVL( c_rec.UNIT_NUMBER,MSC_UTIL.NULL_CHAR)
1633: AND NVL(OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1634: AND NVL(OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.OWNING_TP_TYPE,MSC_UTIL.NULL_VALUE)
1635: AND NVL(PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_PARTNER_SITE_ID,MSC_UTIL.NULL_VALUE)
1636: AND NVL(PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE)= NVL(c_rec.PLANNING_TP_TYPE,MSC_UTIL.NULL_VALUE);
1637:
1638: --=================== MPS SUPPLIES =====================
1639: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 5) or
1640: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 5) and
1637:
1638: --=================== MPS SUPPLIES =====================
1639: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 5) or
1640: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 5) and
1641: (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- MPS
1642:
1643: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd MPS');
1644:
1645:
1639: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 5) or
1640: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 5) and
1641: (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- MPS
1642:
1643: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd MPS');
1644:
1645:
1646: /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1647: UPDATE MSC_SUPPLIES
1749:
1750: --=================== USER DEFINED SUPPLIES =====================
1751: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 41) or
1752: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 41) and
1753: (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- USER DEFINED
1754:
1755: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd USUP');
1756:
1757:
1751: ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh and c_rec.ORDER_TYPE= 41) or
1752: (MSC_CL_COLLECTION.v_is_cont_refresh and (c_rec.ORDER_TYPE = 41) and
1753: (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_INCR)) THEN -- USER DEFINED
1754:
1755: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_supply: upd USUP');
1756:
1757:
1758: /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1759: UPDATE MSC_SUPPLIES
1855: IF MSC_CL_COLLECTION.v_is_complete_refresh OR
1856: SQL%NOTFOUND OR
1857: c_rec.SR_MTL_SUPPLY_ID= -1 THEN
1858: if(SQL%NOTFOUND) Then
1859: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Load supply: not found:' || c_rec.SR_MTL_SUPPLY_ID);
1860: end if;
1861:
1862: EXECUTE IMMEDIATE lv_sql_stmt
1863: USING c_rec.INVENTORY_ITEM_ID,
2016: WHEN OTHERS THEN
2017:
2018: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2019:
2020: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQLCODE========================================');
2021: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2022: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
2023: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
2024: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2020: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQLCODE========================================');
2021: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2022: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
2023: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
2024: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2025:
2026: update msc_apps_instances
2027: set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
2028: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2027: set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
2028: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2029: commit;
2030:
2031: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2032: RAISE;
2033:
2034: ELSE
2035: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2031: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2032: RAISE;
2033:
2034: ELSE
2035: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2036:
2037: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'OTHER========================================');
2038: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2039: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
2033:
2034: ELSE
2035: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2036:
2037: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'OTHER========================================');
2038: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2039: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
2040: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
2041: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2037: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'OTHER========================================');
2038: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2039: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
2040: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
2041: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2042:
2043: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2044: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
2045: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
2042:
2043: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2044: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
2045: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
2046: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2047:
2048: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2049: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2050: FND_MESSAGE.SET_TOKEN('VALUE',
2049: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2050: FND_MESSAGE.SET_TOKEN('VALUE',
2051: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
2052: MSC_CL_COLLECTION.v_instance_id));
2053: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2054:
2055: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2056: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORDER_TYPE');
2057: FND_MESSAGE.SET_TOKEN('VALUE',
2055: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2056: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORDER_TYPE');
2057: FND_MESSAGE.SET_TOKEN('VALUE',
2058: MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE',c_rec.ORDER_TYPE));
2059: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2060:
2061: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2062: END IF;
2063:
2057: FND_MESSAGE.SET_TOKEN('VALUE',
2058: MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE',c_rec.ORDER_TYPE));
2059: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2060:
2061: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2062: END IF;
2063:
2064: END;
2065:
2071: /* Load the PO Acknowledgment Records if MSC:Configuration is set to
2072: CP or APS + CP and Supplier Responses parameter is set to Yes in
2073: collection parameters.*/
2074:
2075: IF (MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE
2076: OR MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE) THEN
2077:
2078: IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_incremental_refresh) THEN
2079:
2072: CP or APS + CP and Supplier Responses parameter is set to Yes in
2073: collection parameters.*/
2074:
2075: IF (MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE
2076: OR MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE) THEN
2077:
2078: IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_incremental_refresh) THEN
2079:
2080: MSC_CL_SUPPLIER_RESP.LOAD_SUPPLIER_RESPONSE
2087: MSC_CL_COLLECTION.v_last_collection_id);
2088:
2089: ELSIF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2090:
2091: 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
2092:
2093: MSC_CL_SUPPLIER_RESP.LOAD_SUPPLIER_RESPONSE
2094: ( MSC_CL_COLLECTION.v_instance_id,
2095: FALSE, --MSC_CL_COLLECTION.v_is_complete_refresh,
2115:
2116:
2117: /* analyze msc_supplies here */
2118: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
2119: IF MSC_CL_COLLECTION.v_exchange_mode= MSC_UTIL.SYS_YES THEN
2120: /* create temporay index */
2121: IF MSC_CL_SUPPLY_ODS_LOAD.create_supplies_tmp_ind THEN
2122: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Index creation on Temp Supplies table successful.');
2123: ELSE
2118: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
2119: IF MSC_CL_COLLECTION.v_exchange_mode= MSC_UTIL.SYS_YES THEN
2120: /* create temporay index */
2121: IF MSC_CL_SUPPLY_ODS_LOAD.create_supplies_tmp_ind THEN
2122: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Index creation on Temp Supplies table successful.');
2123: ELSE
2124: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2125: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Index creation on Temp Supplies table failed.');
2126: RAISE MSC_CL_COLLECTION.SUPPLIES_INDEX_FAIL;
2120: /* create temporay index */
2121: IF MSC_CL_SUPPLY_ODS_LOAD.create_supplies_tmp_ind THEN
2122: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Index creation on Temp Supplies table successful.');
2123: ELSE
2124: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2125: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Index creation on Temp Supplies table failed.');
2126: RAISE MSC_CL_COLLECTION.SUPPLIES_INDEX_FAIL;
2127: END IF;
2128: END IF;
2121: IF MSC_CL_SUPPLY_ODS_LOAD.create_supplies_tmp_ind THEN
2122: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Index creation on Temp Supplies table successful.');
2123: ELSE
2124: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2125: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Index creation on Temp Supplies table failed.');
2126: RAISE MSC_CL_COLLECTION.SUPPLIES_INDEX_FAIL;
2127: END IF;
2128: END IF;
2129: msc_analyse_tables_pk.analyse_table( lv_tbl, MSC_CL_COLLECTION.v_instance_id, -1);
2136: update msc_apps_instances
2137: set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
2138: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2139: commit;
2140: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'MSC_CL_COLLECTION.SUPPLIES_INDEX_FAIL failed');
2141: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2142: RAISE;
2143:
2144: WHEN OTHERS THEN
2137: set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
2138: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2139: commit;
2140: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'MSC_CL_COLLECTION.SUPPLIES_INDEX_FAIL failed');
2141: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2142: RAISE;
2143:
2144: WHEN OTHERS THEN
2145:
2147: set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
2148: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2149: commit;
2150:
2151: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load supply other exception');
2152: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2153: RAISE;
2154: END LOAD_SUPPLY;
2155:
2148: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2149: commit;
2150:
2151: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load supply other exception');
2152: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2153: RAISE;
2154: END LOAD_SUPPLY;
2155:
2156: PROCEDURE LOAD_STAGING_SUPPLY IS
2477: AND tps2.SR_TP_SITE_ID(+)= x.OWNING_PARTNER_SITE_ID
2478: AND tps2.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
2479: AND tps2.PARTNER_TYPE(+)= 1
2480: AND x.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2481: AND x.DELETED_FLAG= MSC_UTIL.SYS_NO
2482: AND md.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID
2483: AND md.DESIGNATOR(+)= x.SCHEDULE_DESIGNATOR
2484: AND md.Organization_ID(+)= x.Organization_ID
2485: AND md.Designator_Type(+)= 2 -- MPS
2507: /* CP-ACK starts */
2508: lv_po_dock_date_ref := nvl(fnd_profile.value('MSC_PO_DOCK_DATE_CALC_PREF'), MSC_CL_COLLECTION.PROMISED_DATE_PREF);
2509: /* CP-ACk ends */
2510:
2511: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_staging_supply');
2512:
2513:
2514: lv_temp_supply_tbl := 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2515: c_count := 0;
2774: ||' :REQ_LINE_ID'
2775: ||' )';
2776:
2777:
2778: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
2779:
2780: --agmcont:
2781: if (MSC_CL_COLLECTION.v_is_cont_refresh = FALSE) then
2782:
2791: IF(lv_org_id <> c_rec.ORGANIZATION_ID or lv_org_id=0) THEN
2792:
2793: --GET_CALENDAR_CODE to be called only once for the same org
2794:
2795: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 c_rec.SR_INSTANCE_ID='||to_char(c_rec.SR_INSTANCE_ID));
2796: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 org_id='||to_char(c_rec.ORGANIZATION_ID));
2797: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 orc='||to_char(MSC_CALENDAR.ORC));
2798: 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);
2799:
2792:
2793: --GET_CALENDAR_CODE to be called only once for the same org
2794:
2795: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 c_rec.SR_INSTANCE_ID='||to_char(c_rec.SR_INSTANCE_ID));
2796: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 org_id='||to_char(c_rec.ORGANIZATION_ID));
2797: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 orc='||to_char(MSC_CALENDAR.ORC));
2798: 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);
2799:
2800: 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);
2793: --GET_CALENDAR_CODE to be called only once for the same org
2794:
2795: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 c_rec.SR_INSTANCE_ID='||to_char(c_rec.SR_INSTANCE_ID));
2796: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 org_id='||to_char(c_rec.ORGANIZATION_ID));
2797: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'debug2 orc='||to_char(MSC_CALENDAR.ORC));
2798: 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);
2799:
2800: 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);
2801:
2856: END IF;
2857: -- SRP enhancement
2858:
2859: /* bug 5937871 */
2860: IF MSC_UTIL.g_collect_srp_data = 'Y' THEN
2861: IF c_rec.ORDER_TYPE=75 THEN
2862: if (c_rec.NEW_SCHEDULE_DATE is null ) then
2863: 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);
2864: 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);
2866: END IF ;
2867: END IF ;
2868: /* end bug 5937871*/
2869:
2870: IF MSC_UTIL.g_collect_srp_data = 'Y' THEN
2871:
2872: IF (c_rec.ORDER_TYPE = 1)
2873: OR (c_rec.ORDER_TYPE = 5 AND c_rec.FIRM_PLANNED_TYPE = 1)
2874: OR (c_rec.ORDER_TYPE = 75) OR (c_rec.ORDER_TYPE = 74)
2872: IF (c_rec.ORDER_TYPE = 1)
2873: OR (c_rec.ORDER_TYPE = 5 AND c_rec.FIRM_PLANNED_TYPE = 1)
2874: OR (c_rec.ORDER_TYPE = 75) OR (c_rec.ORDER_TYPE = 74)
2875: THEN -- For Bug 5909379
2876: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
2877: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
2878: ELSE
2879: lv_ITEM_TYPE_ID := c_rec.ITEM_TYPE_ID;
2880: lv_ITEM_TYPE_VALUE := c_rec.ITEM_TYPE_VALUE;
2873: OR (c_rec.ORDER_TYPE = 5 AND c_rec.FIRM_PLANNED_TYPE = 1)
2874: OR (c_rec.ORDER_TYPE = 75) OR (c_rec.ORDER_TYPE = 74)
2875: THEN -- For Bug 5909379
2876: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
2877: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
2878: ELSE
2879: lv_ITEM_TYPE_ID := c_rec.ITEM_TYPE_ID;
2880: lv_ITEM_TYPE_VALUE := c_rec.ITEM_TYPE_VALUE;
2881:
2881:
2882: END IF;
2883:
2884: IF (c_rec.ORDER_TYPE = 86) THEN
2885: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
2886: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
2887: END IF;
2888:
2889: END IF; --IF ((c_rec.SR_MTL_SUPPLY_ID = -1) or
2882: END IF;
2883:
2884: IF (c_rec.ORDER_TYPE = 86) THEN
2885: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
2886: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
2887: END IF;
2888:
2889: END IF; --IF ((c_rec.SR_MTL_SUPPLY_ID = -1) or
2890: -- ( SQL%NOTFOUND )) THEN
3031: c_count:= c_count+1;
3032:
3033: IF c_count> MSC_CL_COLLECTION.PBS THEN
3034: COMMIT;
3035: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The total record count inserted is '||TO_CHAR(c_count));
3036: c_count:= 0;
3037: END IF;
3038:
3039: EXCEPTION
3040: WHEN OTHERS THEN
3041:
3042: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3043:
3044: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'P========================================');
3045: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3046: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_STAGING_SUPPLY');
3047: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3048: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3044: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'P========================================');
3045: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3046: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_STAGING_SUPPLY');
3047: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3048: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3049:
3050: update msc_apps_instances
3051: set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
3052: where instance_id = MSC_CL_COLLECTION.v_instance_id;
3051: set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
3052: where instance_id = MSC_CL_COLLECTION.v_instance_id;
3053: commit;
3054:
3055: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3056: RAISE;
3057:
3058: ELSE
3059: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3055: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3056: RAISE;
3057:
3058: ELSE
3059: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3060:
3061: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'E========================================');
3062: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3063: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_STAGING_SUPPLY');
3057:
3058: ELSE
3059: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3060:
3061: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'E========================================');
3062: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3063: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_STAGING_SUPPLY');
3064: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3065: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3061: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'E========================================');
3062: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3063: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_STAGING_SUPPLY');
3064: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3065: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3066:
3067: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3068: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
3069: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
3066:
3067: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3068: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
3069: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
3070: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3071:
3072: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3073: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3074: FND_MESSAGE.SET_TOKEN('VALUE',
3073: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3074: FND_MESSAGE.SET_TOKEN('VALUE',
3075: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3076: MSC_CL_COLLECTION.v_instance_id));
3077: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3078:
3079: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3080: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORDER_TYPE');
3081: FND_MESSAGE.SET_TOKEN('VALUE',
3079: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3080: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORDER_TYPE');
3081: FND_MESSAGE.SET_TOKEN('VALUE',
3082: MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE',c_rec.ORDER_TYPE));
3083: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3084:
3085: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3086: END IF;
3087:
3081: FND_MESSAGE.SET_TOKEN('VALUE',
3082: MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE',c_rec.ORDER_TYPE));
3083: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3084:
3085: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3086: END IF;
3087:
3088: END;
3089:
3088: END;
3089:
3090: END LOOP;
3091:
3092: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The total record count inserted is '||TO_CHAR(c_count));
3093: COMMIT;
3094:
3095: FOR extra_rec in c2 LOOP
3096:
3093: COMMIT;
3094:
3095: FOR extra_rec in c2 LOOP
3096:
3097: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'DUE to PARTIAL REFRESH supplies not loaded related to '||
3098: ' ITEMS with IDs '|| TO_CHAR(extra_rec.inventory_item_id));
3099:
3100: END LOOP;
3101:
3105: -- msc_supplies. The same needs to be called in
3106: -- "else" section these code lines if contineous
3107: -- collections is enabled for this entity.
3108: --==============================================
3109: IF ( MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE
3110: OR MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE
3111: ) THEN
3112:
3113: IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag = MSC_UTIL.SYS_YES) THEN
3106: -- "else" section these code lines if contineous
3107: -- collections is enabled for this entity.
3108: --==============================================
3109: IF ( MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE
3110: OR MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE
3111: ) THEN
3112:
3113: IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag = MSC_UTIL.SYS_YES) THEN
3114:
3109: IF ( MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE
3110: OR MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE
3111: ) THEN
3112:
3113: IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag = MSC_UTIL.SYS_YES) THEN
3114:
3115: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Supplier Responses from iSupplier Portal');
3116:
3117: MSC_CL_SUPPLIER_RESP.LOAD_SUPPLIER_RESPONSE
3111: ) THEN
3112:
3113: IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag = MSC_UTIL.SYS_YES) THEN
3114:
3115: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Supplier Responses from iSupplier Portal');
3116:
3117: MSC_CL_SUPPLIER_RESP.LOAD_SUPPLIER_RESPONSE
3118: ( MSC_CL_COLLECTION.v_instance_id,
3119: MSC_CL_COLLECTION.v_is_complete_refresh,
3133: else
3134: -- For continuous refresh
3135:
3136:
3137: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_staging_supply: cont');
3138:
3139: /* CP-AUTO */
3140: /* Following code lines will get executed if any of the Supply type of entity
3141: is getting collected on targeted basis.
3143: targeted basis then we need not to execute lv_cur_sql_stmt because there
3144: is seperate API to take care of Supplier Responses. */
3145:
3146:
3147: 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
3148: (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
3149: (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
3150: (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
3151: (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)
3144: is seperate API to take care of Supplier Responses. */
3145:
3146:
3147: 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
3148: (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
3149: (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
3150: (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
3151: (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)
3152: ) THEN
3145:
3146:
3147: 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
3148: (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
3149: (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
3150: (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
3151: (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)
3152: ) THEN
3153:
3146:
3147: 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
3148: (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
3149: (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
3150: (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
3151: (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)
3152: ) THEN
3153:
3154: lv_cur_sql_stmt :=
3147: 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
3148: (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
3149: (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
3150: (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
3151: (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)
3152: ) THEN
3153:
3154: lv_cur_sql_stmt :=
3155: 'SELECT'
3323: || ' AND tps2.SR_TP_SITE_ID(+)= x.OWNING_PARTNER_SITE_ID'
3324: || ' AND tps2.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID'
3325: || ' AND tps2.PARTNER_TYPE(+)= 1'
3326: || ' AND x.SR_INSTANCE_ID=' || MSC_CL_COLLECTION.v_instance_id
3327: || ' AND x.DELETED_FLAG=' || MSC_UTIL.SYS_NO
3328: || ' AND md.SR_INSTANCE_ID(+)= x.SR_INSTANCE_ID'
3329: || ' AND md.DESIGNATOR(+)= x.SCHEDULE_DESIGNATOR'
3330: || ' AND md.Organization_ID(+)= x.Organization_ID'
3331: || ' AND md.Designator_Type(+)= 2'
3332: || ' AND x.order_type in (';
3333:
3334:
3335:
3336: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_staging_supply: 5');
3337:
3338: if (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES) and
3339: (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) then
3340: lv_where_clause := '5';
3334:
3335:
3336: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_staging_supply: 5');
3337:
3338: if (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES) and
3339: (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) then
3340: lv_where_clause := '5';
3341: end if;
3342:
3335:
3336: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_staging_supply: 5');
3337:
3338: if (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES) and
3339: (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) then
3340: lv_where_clause := '5';
3341: end if;
3342:
3343: if (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) and
3339: (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) then
3340: lv_where_clause := '5';
3341: end if;
3342:
3343: if (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) and
3344: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
3345: if (lv_where_clause is null) then
3346: lv_where_clause := '3,7,14,15,16,27,30,70';
3347: else
3340: lv_where_clause := '5';
3341: end if;
3342:
3343: if (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) and
3344: (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
3345: if (lv_where_clause is null) then
3346: lv_where_clause := '3,7,14,15,16,27,30,70';
3347: else
3348: lv_where_clause := lv_where_clause||',3,7,14,15,16,27,30,70';
3348: lv_where_clause := lv_where_clause||',3,7,14,15,16,27,30,70';
3349: end if;
3350: end if;
3351:
3352: if (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES) and
3353: (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) then
3354: if (lv_where_clause is null) then
3355: lv_where_clause := '1,2,8,11,12,73,74,87';
3356: else
3349: end if;
3350: end if;
3351:
3352: if (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES) and
3353: (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) then
3354: if (lv_where_clause is null) then
3355: lv_where_clause := '1,2,8,11,12,73,74,87';
3356: else
3357: lv_where_clause := lv_where_clause||',1,2,8,11,12,73,74,87';
3357: lv_where_clause := lv_where_clause||',1,2,8,11,12,73,74,87';
3358: end if;
3359: end if;
3360:
3361: if (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES) and
3362: (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) then
3363: if (lv_where_clause is null) then
3364: lv_where_clause := '18';
3365: else
3358: end if;
3359: end if;
3360:
3361: if (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES) and
3362: (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) then
3363: if (lv_where_clause is null) then
3364: lv_where_clause := '18';
3365: else
3366: lv_where_clause := lv_where_clause||',18';
3366: lv_where_clause := lv_where_clause||',18';
3367: end if;
3368: end if;
3369:
3370: if (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES) and
3371: (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) then
3372: if (lv_where_clause is null) then
3373: lv_where_clause := '41';
3374: else
3367: end if;
3368: end if;
3369:
3370: if (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES) and
3371: (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) then
3372: if (lv_where_clause is null) then
3373: lv_where_clause := '41';
3374: else
3375: lv_where_clause := lv_where_clause||',41';
3377: end if;
3378:
3379: lv_cur_sql_stmt := lv_cur_sql_stmt||lv_where_clause ||' )';
3380:
3381: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_staging_supply: 6');
3382:
3383: lv_cur_sql_stmt:=lv_cur_sql_stmt|| ' order by x.ORGANIZATION_ID ';
3384:
3385: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_cur_sql_stmt=' || lv_cur_sql_stmt);
3381: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'load_staging_supply: 6');
3382:
3383: lv_cur_sql_stmt:=lv_cur_sql_stmt|| ' order by x.ORGANIZATION_ID ';
3384:
3385: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_cur_sql_stmt=' || lv_cur_sql_stmt);
3386:
3387: 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;
3388:
3389: LOOP
3569: IF (lv_order_type = 1) OR (lv_order_type = 74) THEN
3570: lv_OLD_SCHEDULE_DATE := lv_NEW_SCHEDULE_DATE;
3571: END IF;
3572: -- SRP enhancement
3573: IF MSC_UTIL.g_collect_srp_data = 'Y' THEN
3574: IF (lv_order_type = 1)
3575: OR (lv_order_type = 5 AND lv_FIRM_PLANNED_TYPE = 1)
3576: OR (lv_order_type = 75) OR (lv_order_type = 74)
3577: OR (lv_order_type = 86) THEN
3574: IF (lv_order_type = 1)
3575: OR (lv_order_type = 5 AND lv_FIRM_PLANNED_TYPE = 1)
3576: OR (lv_order_type = 75) OR (lv_order_type = 74)
3577: OR (lv_order_type = 86) THEN
3578: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
3579: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
3580: END IF;
3581: END IF;
3582:
3575: OR (lv_order_type = 5 AND lv_FIRM_PLANNED_TYPE = 1)
3576: OR (lv_order_type = 75) OR (lv_order_type = 74)
3577: OR (lv_order_type = 86) THEN
3578: lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
3579: lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
3580: END IF;
3581: END IF;
3582:
3583: --IF ((c_rec.SR_MTL_SUPPLY_ID = -1) or
3721: c_count:= c_count+1;
3722:
3723: IF c_count> MSC_CL_COLLECTION.PBS THEN
3724: COMMIT;
3725: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The total record count inserted is '||TO_CHAR(c_count));
3726: c_count:= 0;
3727: END IF;
3728:
3729:
3731: WHEN OTHERS THEN
3732:
3733: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3734:
3735: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3736: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3737: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3738: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3739: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3735: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3736: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3737: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3738: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3739: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3740:
3741: update msc_apps_instances
3742: set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
3743: where instance_id = MSC_CL_COLLECTION.v_instance_id;
3742: set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
3743: where instance_id = MSC_CL_COLLECTION.v_instance_id;
3744: commit;
3745:
3746: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3747: RAISE;
3748:
3749: ELSE
3750: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3746: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3747: RAISE;
3748:
3749: ELSE
3750: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3751:
3752: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3753: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3754: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3748:
3749: ELSE
3750: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3751:
3752: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3753: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3754: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3755: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3756: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3752: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3753: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3754: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3755: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
3756: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3757:
3758: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3759: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_COLLECTION.ITEM_NAME');
3760: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( lv_INVENTORY_ITEM_ID));
3757:
3758: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3759: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_COLLECTION.ITEM_NAME');
3760: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( lv_INVENTORY_ITEM_ID));
3761: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3762:
3763: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3764: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3765: FND_MESSAGE.SET_TOKEN('VALUE',
3764: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3765: FND_MESSAGE.SET_TOKEN('VALUE',
3766: MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
3767: MSC_CL_COLLECTION.v_instance_id));
3768: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3769:
3770: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3771: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORDER_TYPE');
3772: FND_MESSAGE.SET_TOKEN('VALUE',
3770: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3771: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORDER_TYPE');
3772: FND_MESSAGE.SET_TOKEN('VALUE',
3773: MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE',lv_ORDER_TYPE));
3774: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3775:
3776: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3777: END IF;
3778:
3772: FND_MESSAGE.SET_TOKEN('VALUE',
3773: MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE',lv_ORDER_TYPE));
3774: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3775:
3776: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3777: END IF;
3778:
3779: END;
3780:
3779: END;
3780:
3781: END LOOP;
3782:
3783: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The total record count inserted is '||TO_CHAR(c_count));
3784: COMMIT;
3785:
3786: /*
3787: FOR extra_rec in c2 LOOP
3785:
3786: /*
3787: FOR extra_rec in c2 LOOP
3788:
3789: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'DUE to PARTIAL REFRESH supplies not loaded related to '||
3790: ' ITEMS with IDs '|| TO_CHAR(extra_rec.inventory_item_id));
3791:
3792: END LOOP;
3793: */
3804:
3805: -- We will call this API only if "Supplier Responses"
3806: -- entity needs to be collected as Targeted Refresh
3807: --===================================================
3808: IF ( MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE
3809: OR MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE
3810: ) THEN
3811:
3812: 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
3805: -- We will call this API only if "Supplier Responses"
3806: -- entity needs to be collected as Targeted Refresh
3807: --===================================================
3808: IF ( MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE
3809: OR MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE
3810: ) THEN
3811:
3812: 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
3813:
3808: IF ( MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE
3809: OR MSC_UTIL.G_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE
3810: ) THEN
3811:
3812: 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
3813:
3814: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Supplier Responses from iSupplier Portal');
3815:
3816: MSC_CL_SUPPLIER_RESP.LOAD_SUPPLIER_RESPONSE
3810: ) THEN
3811:
3812: 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
3813:
3814: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Supplier Responses from iSupplier Portal');
3815:
3816: MSC_CL_SUPPLIER_RESP.LOAD_SUPPLIER_RESPONSE
3817: ( MSC_CL_COLLECTION.v_instance_id,
3818: FALSE, --MSC_CL_COLLECTION.v_is_complete_refresh,
3849: ||' AND order_type NOT IN (';
3850:
3851: -- agmcont:
3852:
3853: IF MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES THEN
3854: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3855: if (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) then
3856: lv_where_clause := '5';
3857: end if;
3851: -- agmcont:
3852:
3853: IF MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES THEN
3854: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3855: if (MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) then
3856: lv_where_clause := '5';
3857: end if;
3858: else
3859: lv_where_clause := '5';
3858: else
3859: lv_where_clause := '5';
3860: end if;
3861: END IF;
3862: IF (MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag = MSC_UTIL.SYS_YES) THEN
3863: IF (lv_where_clause IS NULL) THEN
3864: lv_where_clause := '29';
3865: ELSE
3866: lv_where_clause := lv_where_clause||', 29';
3865: ELSE
3866: lv_where_clause := lv_where_clause||', 29';
3867: END IF;
3868: END IF;
3869: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
3870:
3871: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3872: if (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
3873: IF (lv_where_clause IS NULL) THEN
3868: END IF;
3869: IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
3870:
3871: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3872: if (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
3873: IF (lv_where_clause IS NULL) THEN
3874: lv_where_clause := '3,7,14,15,16,27,30,70';
3875: ELSE
3876: lv_where_clause := lv_where_clause||', 3,7,14,15,16,27,30,70';
3887:
3888: -- Question what does REPT item - 16 represent?
3889: -- It is assumed that it should be grouped with WIP flag.
3890:
3891: IF (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES) THEN
3892: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3893: if (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) then
3894: IF (lv_where_clause IS NULL) THEN
3895: lv_where_clause := '1,2,8,11,12,73,74,87';
3889: -- It is assumed that it should be grouped with WIP flag.
3890:
3891: IF (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES) THEN
3892: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3893: if (MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) then
3894: IF (lv_where_clause IS NULL) THEN
3895: lv_where_clause := '1,2,8,11,12,73,74,87';
3896: ELSE
3897: lv_where_clause := lv_where_clause||', 1,2,8,11,12,73,74,87';
3905: END IF;
3906: end if;
3907: END IF;
3908:
3909: IF (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES) THEN
3910: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3911: if (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) then
3912: IF (lv_where_clause IS NULL) THEN
3913: lv_where_clause := '18';
3907: END IF;
3908:
3909: IF (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES) THEN
3910: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3911: if (MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) then
3912: IF (lv_where_clause IS NULL) THEN
3913: lv_where_clause := '18';
3914: ELSE
3915: lv_where_clause := lv_where_clause||', 18';
3923: END IF;
3924: end if;
3925: END IF;
3926:
3927: IF (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag = MSC_UTIL.SYS_YES) THEN
3928: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3929: NULL;
3930: Else
3931:
3937: end if;
3938:
3939: END IF; -- Added for 5909379 SRP Additions
3940:
3941: IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag = MSC_UTIL.SYS_YES) THEN
3942: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3943: NULL;
3944: Else
3945:
3952:
3953: END IF; -- Added for 5935273 SRP Additions
3954:
3955:
3956: IF (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES) THEN
3957: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3958: if (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) then
3959: IF (lv_where_clause IS NULL) THEN
3960: lv_where_clause := '41';
3954:
3955:
3956: IF (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES) THEN
3957: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3958: if (MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) then
3959: IF (lv_where_clause IS NULL) THEN
3960: lv_where_clause := '41';
3961: ELSE
3962: lv_where_clause := lv_where_clause||', 41';
3972: END IF;
3973:
3974: /* CP-ACK starts */
3975:
3976: IF (MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag = MSC_UTIL.SYS_YES) THEN
3977:
3978: /* CP-AUTO */
3979: IF (MSC_CL_COLLECTION.v_is_cont_refresh) then
3980:
3977:
3978: /* CP-AUTO */
3979: IF (MSC_CL_COLLECTION.v_is_cont_refresh) then
3980:
3981: IF (MSC_CL_COLLECTION.v_coll_prec.suprep_sn_flag = MSC_UTIL.SYS_TGT) THEN
3982:
3983: IF (lv_where_clause IS NULL) THEN
3984: lv_where_clause := MSC_CL_COLLECTION.G_MRP_PO_ACK;
3985: ELSE
4008: -- For that purpose complete refresh needs to be run.
4009:
4010: lv_sql_stmt := lv_sql_stmt||lv_where_clause ||' )';
4011:
4012: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
4013: null;
4014: ELSE
4015: lv_sql_stmt1:= ' UNION ALL '
4016: ||' SELECT * from MSC_SUPPLIES '
4015: lv_sql_stmt1:= ' UNION ALL '
4016: ||' SELECT * from MSC_SUPPLIES '
4017: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
4018: ||' AND plan_id = -1 '
4019: ||' AND organization_id not '||MSC_UTIL.v_in_org_str
4020: ||' AND order_type IN (';
4021:
4022: lv_sql_stmt1 := lv_sql_stmt1||lv_where_clause ||' )';
4023:
4024:
4025: if NOT (MSC_CL_COLLECTION.v_is_complete_refresh) then
4026: lv_sql_stmt :=lv_sql_stmt||lv_sql_stmt1;
4027: else
4028: lv_sql_stmt := lv_sql_stmt||' AND organization_id NOT '||MSC_UTIL.v_in_org_str;
4029:
4030: lv_sql_stmt :=lv_sql_stmt||lv_sql_stmt1;
4031: end if;
4032:
4043: set SUPPLIES_LOAD_FLAG = MSC_CL_COLLECTION.G_JOB_ERROR
4044: where instance_id = MSC_CL_COLLECTION.v_instance_id;
4045: commit;
4046:
4047: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4048: RAISE;
4049:
4050: END LOAD_ODS_SUPPLY;
4051:
4055: lv_sql_stmt VARCHAR2(32767);
4056:
4057: BEGIN
4058:
4059: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
4060: lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
4061: ELSE
4062: lv_supply_tbl:= 'MSC_SUPPLIES';
4063: END IF;