[Home] [Help]
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
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
311: r.ing_material_detail_id = p_tran_rec.trx_source_line_id AND
383: BEGIN
384: /* PK Bug 8219507 removed mtln */
385: SELECT -l.layer_doc_qty, l.layer_doc_um
386: INTO l_doc_qty, l_doc_um
387: FROM gmf_outgoing_material_layers l, mtl_material_transactions mmt
388: -- mtl_transaction_lot_numbers mtln
389: WHERE
390: mmt.transaction_id = p_tran_rec.reverse_id AND
391: -- mtln.transaction_id (+) = p_tran_rec.reverse_id AND
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
718: r.delete_mark = 0 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
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,
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
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
1100: AND h.batch_id = t.transaction_source_id
1101: AND t.transaction_source_type_id = 5
1102: );
1104: DELETE
1105: FROM gmf_outgoing_material_layers ol
1106: WHERE (ol.mmt_organization_id, ol.mmt_transaction_id) IN
1107: (SELECT distinct t.organization_id, t.transaction_id
1108: FROM gme_batch_header h, mtl_material_transactions t
1109: WHERE h.batch_id = p_batch_id
1110: AND h.batch_id = t.transaction_source_id
1111: AND t.transaction_source_type_id = 5
1112: );
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
1289: DELETE
1290: FROM gmf_outgoing_material_layers ol
1291: WHERE (ol.mmt_organization_id, ol.mmt_transaction_id) IN
1292: (SELECT distinct t.organization_id, t.transaction_id
1293: FROM gme_batch_header h, mtl_material_transactions t
1294: WHERE h.batch_id = p_batch_id
1295: AND h.batch_id = t.transaction_source_id
1296: AND t.transaction_source_type_id = 5
1297: AND t.transaction_date >= l_start_date
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
1398: (
1399: SELECT mmt.rowid r_id, mmt.transaction_id as trans_id, mmt.transaction_date as trans_date,
1400: md.line_type as line_type, md.dtl_um as doc_um, 'M' as type, mmt.primary_quantity as trans_qty,
1401: tp.transaction_id2 as reverse_id
1402: from mtl_material_transactions mmt,
1403: gme_transaction_pairs tp,
1404: gme_material_details md,
1405: gme_batch_header b
1406: where md.batch_id = b.batch_id
1430: trans_id
1431: );
1432:
1433:
1434: mt mtl_material_transactions%ROWTYPE;
1435: rt gme_resource_txns%ROWTYPE;
1436: l_lot_number VARCHAR2(100);
1437:
1438: x_return_status VARCHAR2(10);
1499: , mmt.trx_source_line_id -- line_id
1500: , mmt.last_updated_by
1501: , mmt.created_by
1502: , mmt.last_update_login
1503: FROM mtl_material_transactions mmt, -- mtl_transaction_lot_numbers mtln,
1504: mtl_system_items_b msi
1505: WHERE
1506: mmt.ROWID = t.r_id
1507: -- AND mtln.transaction_id (+) = mmt.transaction_id
1688: ),
1689: trans_id
1690: );
1691:
1692: mt mtl_material_transactions%ROWTYPE;
1693: rt gme_resource_txns%ROWTYPE;
1694: l_lot_number VARCHAR2(100);
1695:
1696: x_return_status VARCHAR2(10);
1842: (
1843: SELECT mmt.rowid r_id, mmt.transaction_id as trans_id, mmt.transaction_date as trans_date,
1844: md.line_type as line_type, md.dtl_um as doc_um, 'M' as type, mmt.primary_quantity as trans_qty,
1845: tp.transaction_id2 as reverse_id, mmt.trx_source_line_id AS mat_det_id
1846: from mtl_material_transactions mmt,
1847: gme_transaction_pairs tp,
1848: gme_material_details md,
1849: gme_batch_header b
1850: where md.batch_id = b.batch_id
1875: );
1876:
1877:
1878:
1879: mt mtl_material_transactions%ROWTYPE;
1880: rt gme_resource_txns%ROWTYPE;
1881: l_lot_number VARCHAR2(100);
1882:
1883: x_return_status VARCHAR2(10);
1948: , mmt.trx_source_line_id -- line_id
1949: , mmt.last_updated_by
1950: , mmt.created_by
1951: , mmt.last_update_login
1952: FROM mtl_material_transactions mmt, -- mtl_transaction_lot_numbers mtln,
1953: mtl_system_items_b msi
1954: WHERE
1955: mmt.ROWID = t.r_id
1956: -- AND mtln.transaction_id (+) = mmt.transaction_id
2276: /* Bug 13442362 13367279 additional change Not for this bug. We do not need to care about
2277: staging transfers and subinventory transfers for this validation as costing does not cost these */
2278:
2279: CURSOR Cur_OPM_costed IS
2280: select Count(*) from mtl_material_transactions
2281: where transaction_date >= l_startdate
2282: and transaction_date <= l_enddate
2283: and transaction_action_id NOT IN (2, 28)
2284: and opm_costed_flag IS NULL
2382: FROM (
2383: SELECT h.batch_id, h.batch_no, h.batch_status , h.actual_start_date asd,
2384: h.batch_close_date, h.organization_id
2385: FROM gme_batch_header h,
2386: mtl_material_transactions t
2387: WHERE h.batch_status in (2,3,4) -- B9441550
2388: AND h.actual_start_date <= l_enddate
2389: AND h.batch_id = t.transaction_source_id
2390: AND t.transaction_source_type_id = 5
2434: FROM (
2435: SELECT h.batch_id, h.batch_no, h.batch_status , h.actual_start_date asd,
2436: h.batch_close_date, h.organization_id
2437: FROM gme_batch_header h,
2438: mtl_material_transactions t
2439: WHERE h.batch_status in (2,3,4) -- B9441550
2440: AND h.actual_start_date <= l_enddate
2441: AND h.batch_id = t.transaction_source_id
2442: AND t.transaction_source_type_id = 5
2487: FROM (
2488: SELECT h.batch_id, h.batch_no, h.batch_status , h.actual_start_date asd,
2489: h.batch_close_date, h.organization_id
2490: FROM gme_batch_header h,
2491: mtl_material_transactions t
2492: WHERE h.batch_status in (2,3,4) -- B9441550
2493: AND h.actual_start_date <= l_enddate
2494: AND h.batch_id = t.transaction_source_id
2495: AND t.transaction_source_type_id = 5
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,
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
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
2602: AND h.batch_id = t.transaction_source_id
2603: AND t.transaction_source_type_id = 5
2604: );
2606: SELECT count(*) INTO l_count
2607: FROM gmf_outgoing_material_layers ol
2608: WHERE (ol.mmt_organization_id, ol.mmt_transaction_id) IN
2609: (SELECT DISTINCT t.organization_id, t.transaction_id
2610: FROM gme_batch_header h, mtl_material_transactions t
2611: WHERE h.batch_id = rec.batch_id
2612: AND h.batch_id = t.transaction_source_id
2613: AND t.transaction_source_type_id = 5
2614: );