[Home] [Help]
63: x_msg_data OUT NOCOPY VARCHAR2,
64: p_Create_vib IN VARCHAR2 DEFAULT 'Y') IS
65:
66: l_layer_id NUMBER;
67: l_layer_rec gmf_incoming_material_layers%ROWTYPE;
68: l_doc_qty NUMBER;
69: l_doc_um VARCHAR2(4);
70: l_req_count PLS_INTEGER;
71:
115: BEGIN
116: /* PK Bug 8219507 removed mtln */
117: SELECT -l.layer_doc_qty, l.layer_doc_um
118: INTO l_doc_qty, l_doc_um
119: FROM gmf_incoming_material_layers l, mtl_material_transactions mmt
120: -- mtl_transaction_lot_numbers mtln
121: WHERE
122: mmt.transaction_id = p_tran_rec.reverse_id AND
123: -- mtln.transaction_id (+) = p_tran_rec.reverse_id AND
152: IF g_debug <= gme_debug.g_log_statement THEN
153: gme_debug.put_line ('creating incoming layer: ' || l_layer_id);
154: END IF;
155:
156: INSERT INTO gmf_incoming_material_layers(
157: layer_id,
158: mmt_transaction_id,
159: mmt_organization_id,
160: lot_number,
302: CURSOR c_null_vib_rows IS
303: SELECT v.rowid, v.*, mmt.organization_id, l.layer_date
304: FROM gmf_batch_vib_details v,
305: gmf_batch_requirements r,
306: gmf_incoming_material_layers l,
307: mtl_material_transactions mmt
308: -- mtl_transaction_lot_numbers mtln
309: WHERE
310: r.batch_id = p_tran_rec.transaction_source_id AND
709: -- VKINDURI Added hint as per bug # 13424903
710: SELECT /*+ LEADING(r) */ v.rowid, v.*, t.organization_id, l.layer_date
711: FROM gmf_batch_requirements r,
712: gmf_batch_vib_details v,
713: gmf_incoming_material_layers l,
714: mtl_material_transactions t
715: WHERE
716: r.batch_id = p_rsrc_rec.doc_id AND
717: r.batchstep_resource_id = p_rsrc_rec.line_id AND
1060: FROM gmf_layer_cost_details c
1061: WHERE
1062: c.layer_id IN
1063: (SELECT il.layer_id
1064: FROM gme_batch_header h, mtl_material_transactions t, gmf_incoming_material_layers il
1065: WHERE h.batch_id = p_batch_id
1066: AND h.batch_id = t.transaction_source_id
1067: AND t.transaction_source_type_id = 5
1068: AND il.mmt_transaction_id = t.transaction_id
1068: AND il.mmt_transaction_id = t.transaction_id
1069: AND il.mmt_organization_id = t.organization_id
1070: );
1071:
1072: /* Bug 13442362 13367279. This query does a full table scan of gmf_incoming_material_layers
1073: This is also not required since PSEUDO_LAYER record now has transaction_id of last yield.
1074: Thus next query is still deleting it using the index
1075:
1076: DELETE
1073: This is also not required since PSEUDO_LAYER record now has transaction_id of last yield.
1074: Thus next query is still deleting it using the index
1075:
1076: DELETE
1077: FROM gmf_incoming_material_layers il
1078: WHERE il.PSEUDO_LAYER_ID IS NOT NULL
1079: AND EXISTS
1080: (SELECT 1
1081: FROM gme_batch_header h, mtl_material_transactions t,
1078: WHERE il.PSEUDO_LAYER_ID IS NOT NULL
1079: AND EXISTS
1080: (SELECT 1
1081: FROM gme_batch_header h, mtl_material_transactions t,
1082: gmf_incoming_material_layers im
1083: WHERE h.batch_id = p_batch_id
1084: AND h.batch_id = t.transaction_source_id
1085: AND t.transaction_source_type_id = 5
1086: AND im.mmt_transaction_id = t.transaction_id
1091: Bug 13442362 13367279. End */
1092:
1093:
1094: DELETE
1095: FROM gmf_incoming_material_layers il
1096: WHERE (il.mmt_organization_id, il.mmt_transaction_id) IN
1097: (SELECT distinct t.organization_id, t.transaction_id
1098: FROM gme_batch_header h, mtl_material_transactions t
1099: WHERE h.batch_id = p_batch_id
1188: e_baddata EXCEPTION;
1189:
1190: CURSOR cur_incoming_layers IS
1191: select count(*)
1192: FROM gme_batch_header h, mtl_material_transactions t, gmf_incoming_material_layers il
1193: WHERE h.batch_id = p_batch_id
1194: AND h.batch_id = t.transaction_source_id
1195: AND t.transaction_source_type_id = 5
1196: AND il.mmt_transaction_id = t.transaction_id
1202: FROM gmf_batch_vib_details bvd
1203: WHERE bvd.CONSUME_LAYER_DATE < l_start_date
1204: AND bvd.prod_layer_id IN
1205: (SELECT il.layer_id
1206: FROM gme_batch_header h, mtl_material_transactions t, gmf_incoming_material_layers il
1207: WHERE h.batch_id = p_batch_id
1208: AND h.batch_id = t.transaction_source_id
1209: AND t.transaction_source_type_id = 5
1210: AND il.mmt_transaction_id = t.transaction_id
1224: DELETE
1225: FROM gmf_layer_cost_details c
1226: WHERE c.layer_id IN
1227: (SELECT il.layer_id
1228: FROM gme_batch_header h, mtl_material_transactions t, gmf_incoming_material_layers il
1229: WHERE h.batch_id = p_batch_id
1230: AND h.batch_id = t.transaction_source_id
1231: AND t.transaction_source_type_id = 5
1232: AND il.mmt_transaction_id = t.transaction_id
1276: DELETE
1277: FROM gmf_batch_vib_details bvd
1278: WHERE bvd.prod_layer_id IN
1279: (SELECT il.layer_id
1280: FROM gme_batch_header h, mtl_material_transactions t, gmf_incoming_material_layers il
1281: WHERE h.batch_id = p_batch_id
1282: AND h.batch_id = t.transaction_source_id
1283: AND t.transaction_source_type_id = 5
1284: AND il.mmt_transaction_id = t.transaction_id
1308: AND t.trans_date >= l_start_date
1309: );
1310:
1311: DELETE
1312: FROM gmf_incoming_material_layers il
1313: WHERE il.layer_id IN
1314: (SELECT il1.layer_id
1315: FROM gme_batch_header h, mtl_material_transactions t, gmf_incoming_material_layers il1
1316: WHERE h.batch_id = p_batch_id
1311: DELETE
1312: FROM gmf_incoming_material_layers il
1313: WHERE il.layer_id IN
1314: (SELECT il1.layer_id
1315: FROM gme_batch_header h, mtl_material_transactions t, gmf_incoming_material_layers il1
1316: WHERE h.batch_id = p_batch_id
1317: AND h.batch_id = t.transaction_source_id
1318: AND t.transaction_source_type_id = 5
1319: AND il1.mmt_transaction_id = t.transaction_id
1980:
1981: IF t.line_type = 1 THEN
1982: SELECT count(*)
1983: INTO l_count
1984: FROM gmf_incoming_material_layers
1985: WHERE
1986: mmt_transaction_id = trans_rec.transaction_id
1987: AND ((lot_number is not null and lot_number = trans_rec.lot_number)
1988: OR
2578:
2579: fnd_file.put_line(fnd_file.log, ' After rebuild record count for Batch_id '|| rec.batch_id||' Batch No '||rec.batch_no);
2580:
2581: SELECT count(*) INTO l_count
2582: FROM gmf_incoming_material_layers il
2583: WHERE il.PSEUDO_LAYER_ID IS NOT NULL
2584: AND EXISTS
2585: (SELECT 1
2586: FROM gme_batch_header h, mtl_material_transactions t,
2583: WHERE il.PSEUDO_LAYER_ID IS NOT NULL
2584: AND EXISTS
2585: (SELECT 1
2586: FROM gme_batch_header h, mtl_material_transactions t,
2587: gmf_incoming_material_layers im
2588: WHERE h.batch_id = rec.batch_id
2589: AND h.batch_id = t.transaction_source_id
2590: AND t.transaction_source_type_id = 5
2591: AND im.mmt_transaction_id = t.transaction_id
2593: AND im.layer_id = il.PSEUDO_LAYER_ID
2594: );
2595: fnd_file.put_line(fnd_file.log, ' Psuedo Incoming layers count = '|| l_count);
2596: SELECT count(*) INTO l_count
2597: FROM gmf_incoming_material_layers il
2598: WHERE (il.mmt_organization_id, il.mmt_transaction_id) IN
2599: (SELECT DISTINCT t.organization_id, t.transaction_id
2600: FROM gme_batch_header h, mtl_material_transactions t
2601: WHERE h.batch_id = rec.batch_id