[Home] [Help]
1011: decode (tp.transaction_id2, NULL, ol.ROWID, ol2.ROWID) c_rowid
1012: FROM gmf_batch_vib_details v,
1013: gmf_incoming_material_layers il,
1014: gmf_outgoing_material_layers ol,
1015: mtl_material_transactions t,
1016: gme_transaction_pairs tp,
1017: gmf_outgoing_material_layers ol2,
1018: mtl_material_transactions t2
1019: WHERE
1014: gmf_outgoing_material_layers ol,
1015: mtl_material_transactions t,
1016: gme_transaction_pairs tp,
1017: gmf_outgoing_material_layers ol2,
1018: mtl_material_transactions t2
1019: WHERE
1020: il.mmt_transaction_id = p_tran_rec.reverse_id AND -- incoming layer of reversed prod yield
1021: -- Bug 6312166. il.mmt_transaction_id = p_tran_rec.transaction_id AND -- incoming layer of reversed prod yield
1022: nvl(il.lot_number, 'x') = nvl(p_tran_rec.lot_number, 'x') AND
1063: CURSOR c_ing_layers (p_ing_material_detail_id NUMBER) IS
1064: SELECT mmt.inventory_item_id, mmt.organization_id, /* mtln.lot_number, */ mmt.primary_quantity, msi.primary_uom_code,
1065: mmt.transaction_date, md.line_type, tp.transaction_id2 as reverse_id, l.ROWID, l.*
1066: FROM gmf_outgoing_material_layers l,
1067: mtl_material_transactions mmt,
1068: mtl_transaction_lot_numbers mtln,
1069: mtl_system_items_b msi,
1070: gme_material_details md,
1071: gme_transaction_pairs tp
1826: CURSOR c_last_prod_yield (p_prod_material_detail_id NUMBER) IS
1827: SELECT l.*, t.primary_quantity,
1828: t.inventory_item_id -- Bug 5607069
1829: FROM gmf_incoming_material_layers l,
1830: mtl_material_transactions t,
1831: mtl_transaction_lot_numbers mtln
1832: WHERE
1833: t.trx_source_line_id = p_prod_material_detail_id AND
1834: t.transaction_source_id = p_batch_id AND
1844: CURSOR c_remaining_ing_layers (p_ing_material_detail_id NUMBER) IS
1845: SELECT l.layer_id, l.layer_doc_um, l.remaining_ib_doc_qty, t.inventory_item_id, t.transaction_date,
1846: mtln.lot_number, md.line_type, t.primary_quantity, msi.primary_uom_code, l.ROWID, tp.transaction_id2 as reverse_id,
1847: l.layer_doc_qty
1848: FROM gmf_outgoing_material_layers l, mtl_material_transactions t,
1849: mtl_transaction_lot_numbers mtln, gme_material_details md,
1850: mtl_system_items_b msi, gme_transaction_pairs tp
1851: WHERE
1852: t.trx_source_line_id = p_ing_material_detail_id AND
2109: SELECT nvl(sum (consume_ib_doc_qty), 0)
2110: INTO l_prev_consume_ib_doc_qty
2111: FROM gmf_batch_vib_details v,
2112: gmf_incoming_material_layers il,
2113: mtl_material_transactions mmt
2114: WHERE
2115: v.requirement_id = req.requirement_id AND
2116: v.consume_layer_id = ing.layer_id AND
2117: il.layer_id = v.prod_layer_id AND
2142: INTO l_orig_layer_consumption_qty
2143: FROM gmf_outgoing_material_layers ol,
2144: gmf_batch_vib_details v,
2145: gmf_incoming_material_layers il,
2146: mtl_material_transactions mmt
2147: WHERE
2148: ol.mmt_transaction_id = ing.reverse_id AND
2149: ol.lot_number = ing.lot_number AND
2150: v.consume_layer_id = ol.layer_id AND
2302: SELECT nvl(sum (consume_ib_doc_qty), 0)
2303: INTO l_prev_consume_ib_doc_qty
2304: FROM gmf_batch_vib_details v,
2305: gmf_incoming_material_layers il,
2306: mtl_material_transactions mmt
2307: WHERE
2308: v.requirement_id = req.requirement_id AND
2309: v.consume_layer_id = rsrc.layer_id AND
2310: il.layer_id = v.prod_layer_id AND
2334: INTO l_orig_layer_consumption_qty
2335: FROM gmf_resource_layers rl,
2336: gmf_batch_vib_details v,
2337: gmf_incoming_material_layers il,
2338: mtl_material_transactions mmt
2339: WHERE
2340: rl.poc_trans_id = rsrc.reverse_id AND
2341: v.consume_layer_id = rl.layer_id AND
2342: v.requirement_id = req.requirement_id AND
2738: EXIT WHEN get_latest_yield%NOTFOUND;
2739:
2740: SELECT pnd.*
2741: INTO l_trans_rec
2742: FROM mtl_material_transactions pnd
2743: WHERE transaction_id = l_layer_rec.trans_id
2744: ;
2745:
2746: dbms_output.put_line('processing layer_id: ' || l_layer_rec.layer_id);