[Home] [Help]
312: --* 21-May-2009 Bug 7249505 HARI LUTHRA
313: --* HALUTHRA BUG 7249505 In case of return to vendor process the transaction as an adjustment
314: --* 21-MAY-2009 Bug 6165255 and BUG 8330088 HARI LUTHRA
315: --* Modified the cursor unassociated_ings_cursor and unassociated_prds_cursor so as to include ingrediant transactions
316: --* which are non lot controlled and also to take the primary qty and primary UOM of item from mtl_material_transactions
317: --* and mtl_system_items_b instead of transaction_qty and transaction_uom to avoid the dual_uom issues.
318: --* 27-MAY-2009 Hari Luthra Bug 5473138/8533290
319: --* Modified item_cost_cursor , item_cost_detail_cursor to avoid issues with cost warehouse assosciation
320: --* Also modified query which picks up all the transactions on the basis of the parameters fed while running Lot Cost Process
997: ), -- Bug 7317270,
998: gmd.cost_alloc
999: )
1000: FROM mtl_system_items_b iimb,
1001: mtl_material_transactions mmt,
1002: gme_batch_step_items gbsi,
1003: gme_material_details gmd,
1004: mtl_transaction_lot_numbers mtln,
1005: mtl_parameters mp,
1094: gmd.plan_qty,
1095: gmd.actual_qty,
1096: gmd.dtl_um, gmd.cost_alloc
1097: FROM mtl_system_items_b iimb,
1098: mtl_material_transactions mmt,
1099: gme_material_details gmd,
1100: mtl_transaction_lot_numbers mtln,
1101: mtl_parameters mp,
1102: hr_organization_information hoi
1172: ), -- Bug 7317270
1173: gmd.cost_alloc
1174: )
1175: FROM mtl_system_items_b iimb,
1176: mtl_material_transactions mmt,
1177: gme_material_details gmd,
1178: mtl_transaction_lot_numbers mtln,
1179: hr_organization_information hoi
1180: WHERE mmt.transaction_source_type_id = 5
1217: ), -- Bug 7317270
1218: gme.cost_alloc
1219: )
1220: FROM mtl_system_items_b iimb,
1221: mtl_material_transactions mmt,
1222: gme_material_details gme,
1223: mtl_transaction_lot_numbers mtln,
1224: gme_transaction_pairs gtp
1225: WHERE mmt.trx_source_line_id = gme.material_detail_id
2676: FROM rcv_transactions t,
2677: po_rcv_charges rc,
2678: po_rcv_charge_allocations rca,
2679: mtl_units_of_measure uom,
2680: mtl_material_transactions mmt, /* ANTHIYAG Bug#5463200 14-Aug-2006 */
2681: po_line_locations_all pll -- AF
2682: WHERE mmt.transaction_id = transaction_row.transaction_id /* ANTHIYAG Bug#5463200 14-Aug-2006 */
2683: AND t.transaction_id = mmt.rcv_transaction_id
2684: AND t.shipment_header_id = rc.shipment_header_id
2696: ,glat. primary_uom_code
2697: ,1 lcm_flag
2698: FROM
2699: gmf_lc_adj_transactions glat,
2700: mtl_material_transactions mmt,
2701: rcv_transactions rt ,
2702: po_line_locations_all pll
2703: WHERE
2704: mmt.transaction_id = transaction_row.transaction_id
3266: CURSOR get_last_wipcompletion
3267: IS
3268: SELECT mmt.transaction_id
3269: FROM gmf_lot_costs glc, gmf_material_lot_cost_txns gmt,
3270: mtl_material_transactions mmt
3271: WHERE glc.lot_number = transaction_row.lot_number
3272: AND glc.inventory_item_id = transaction_row.inventory_item_id
3273: AND glc.organization_id = transaction_row.orgn_id
3274: AND glc.cost_type_id = l_cost_type_id
3570:
3571: CURSOR get_src_qty_uom IS
3572: select NVL(mtln.primary_quantity, mmt.primary_quantity), -- B9131983 used NVL
3573: lcig.primary_uom_code
3574: from mtl_material_transactions mmt,
3575: mtl_transaction_lot_numbers mtln,
3576: gmf_lot_costed_items_gt lcig
3577: where mmt.transaction_id = transaction_row.transfer_transaction_id
3578: AND mmt.transaction_id = mtln.transaction_id
3653: IF ( (p_source_le <> p_target_le) OR (l_source_ou <> l_target_ou) ) THEN
3654:
3655: -- Bug Bug 9356358 / 12.1 Bug 9400419 Query transfer price and create cost with default material component
3656: SELECT transfer_price INTO l_unit_cost
3657: FROM mtl_material_transactions
3658: WHERE transaction_id = transaction_row.transaction_id;
3659:
3660: -- Bug 14392602 Use cost component set up at Item level before Fiscal policy
3661: /* SELECT mtl_cmpntcls_id, mtl_analysis_code INTO l_ccc_id, l_a_code
4246: receipt_uom,
4247: document_code,
4248: l_exchange_rate,
4249: l_rcv_transaction_id
4250: FROM rcv_transactions t, mtl_units_of_measure u, mtl_material_transactions mmt -- jboppana
4251: , po_distributions_all pda
4252: WHERE t.source_doc_unit_of_measure = u.unit_of_measure(+)
4253: AND t.transaction_id = mmt.rcv_transaction_id
4254: -- AND mmt.transaction_source_id = transaction_row.doc_id
4301: INTO
4302: target_orgn_id,source_orgn_id, source_le_id, target_le_id, l_shipped_date
4303: FROM
4304: rcv_transactions t,
4305: mtl_material_transactions mmt,
4306: rcv_shipment_headers r,
4307: rcv_shipment_lines rsl,
4308: po_headers_all poh,
4309: hr_organization_information hoi1,
4339: INTO
4340: target_orgn_id,source_orgn_id, source_le_id, target_le_id, l_shipped_date
4341: FROM
4342: rcv_transactions t,
4343: mtl_material_transactions mmt,
4344: rcv_shipment_headers r,
4345: rcv_shipment_lines rsl,
4346: po_headers_all poh,
4347: hr_organization_information hoi1,
5451:
5452:
5453: SELECT COUNT (DISTINCT mmt.inventory_item_id)
5454: INTO l_unassociated_prds
5455: FROM mtl_material_transactions mmt, gme_material_details gmd
5456: WHERE mmt.transaction_source_type_id = 5
5457: AND mmt.transaction_source_id = gmd.batch_id
5458: AND gmd.line_type = 1
5459: AND mmt.transaction_quantity <> 0
7791: mtln.primary_quantity as received_qty -- Bug 13906192
7792: FROM
7793: gmf_lc_adj_transactions glat,
7794: rcv_transactions rt,
7795: mtl_material_transactions mmt, -- Bug 13906192
7796: mtl_transaction_lot_numbers mtln -- Bug 13906192
7797: WHERE
7798: glat.adj_transaction_id = transaction_row.transaction_id
7799: AND glat.rcv_transaction_id = transaction_row.doc_id
9066: --check if there is cost from parent lot At this point
9067: SELECT mln.lot_number
9068: INTO l_parent_lot_number
9069: FROM mtl_transaction_lot_numbers mln,
9070: mtl_material_transactions mmt
9071: WHERE mmt.transaction_id = transaction_row.transfer_transaction_id
9072: AND mmt.transaction_id = mln.transaction_id;
9073:
9074: OPEN lot_cost_cursor(transaction_row.orgn_id,
9189: CURSOR child_lots_cursor IS
9190: SELECT mtln.lot_number,
9191: ABS(mtln.primary_quantity),
9192: mtln.transaction_date
9193: FROM mtl_transaction_lot_numbers mtln, mtl_material_transactions mmt
9194: WHERE mmt.transfer_transaction_id = transaction_row.transfer_transaction_id
9195: AND mmt.transaction_id <> transaction_row.transfer_transaction_id
9196: AND mmt.transaction_id = mtln.transaction_id ;
9197:
9859: -- logical intransit receipt.
9860:
9861: SELECT transaction_id
9862: INTO l_trans_id
9863: FROM mtl_material_transactions
9864: WHERE transfer_transaction_id = transaction_row.transfer_transaction_id
9865: AND transaction_action_id = 15;
9866:
9867: SELECT cost_header_id
10420: DECODE(gme.line_type,1, DECODE((ABS(DECODE(mtln.primary_quantity, 0, 1,mtln.primary_quantity))/DECODE(mtln.primary_quantity, 0, 1, mtln.primary_quantity)),
10421: 1, mmt.transaction_id ,
10422: DECODE(Decode(nvl(gtp.pair_type,0),1,gtp.transaction_id2,NULL), NULL, mmt.transaction_id, (Decode(nvl(gtp.pair_type,0),1,gtp.transaction_id2,NULL)+.5))),mmt.transaction_id) as oc3,
10423: NVL(gbh.routing_id, 0) as routing_id
10424: FROM mtl_material_transactions mmt,
10425: gme_material_details gme,
10426: mtl_transaction_lot_numbers mtln,
10427: (Select pair_type, transaction_id1, transaction_id2 From gme_transaction_pairs where pair_type = 1) gtp, -- PK Bug 9069363
10428: gmf_process_organizations_gt gpo,
10479: mmt.transaction_date as oc1,
10480: decode(mmt.transaction_action_id,27,-1,mmt.transaction_source_type_id) as oc2,
10481: mmt.transaction_id as oc3,
10482: 0 as routing_id
10483: FROM mtl_material_transactions mmt,
10484: mtl_transaction_lot_numbers mtln,
10485: gmf_process_organizations_gt gpo,
10486: gmf_lot_costed_items_gt lcig
10487: WHERE
10533: mmt.transaction_date as oc1,
10534: decode(mmt.transaction_action_id,27,-1,mmt.transaction_source_type_id) as oc2,
10535: mmt.transaction_id as oc3,
10536: 0 as routing_id
10537: FROM mtl_material_transactions mmt,
10538: mtl_transaction_lot_numbers mtln,
10539: gmf_process_organizations_gt gpo,
10540: gmf_lot_costed_items_gt lcig
10541: WHERE
10586: mmt.transaction_date as oc1,
10587: decode(mmt.transaction_action_id,27,-1,mmt.transaction_source_type_id) as oc2,
10588: mmt.transaction_id as oc3,
10589: 0 as routing_id
10590: FROM mtl_material_transactions mmt,
10591: mtl_transaction_lot_numbers mtln,
10592: gmf_process_organizations_gt gpo,
10593: gmf_lot_costed_items_gt lcig
10594: WHERE
10692: 0 as routing_id
10693: FROM
10694: gmf_lc_adj_transactions glat, -- from import process of LCM
10695: rcv_transactions rt,
10696: mtl_material_transactions mmt, -- Bug 13906192
10697: mtl_transaction_lot_numbers mtln, -- Bug 13906192
10698: gmf_process_organizations_gt gpo,
10699: gmf_lot_costed_items_gt glci
10700: WHERE glat.transaction_date >= l_trans_start_date
10795: DECODE(gme.line_type,1, DECODE((ABS(DECODE(mtln.primary_quantity, 0, 1,mtln.primary_quantity))/DECODE(mtln.primary_quantity, 0, 1, mtln.primary_quantity)),
10796: 1, mmt.transaction_id ,
10797: DECODE(Decode(nvl(gtp.pair_type,0),1,gtp.transaction_id2,NULL), NULL, mmt.transaction_id, (Decode(nvl(gtp.pair_type,0),1,gtp.transaction_id2,NULL)+.5))),mmt.transaction_id) as oc3,
10798: NVL(gbh.routing_id, 0) as routing_id
10799: FROM mtl_material_transactions mmt,
10800: gme_material_details gme,
10801: mtl_transaction_lot_numbers mtln,
10802: (Select pair_type, transaction_id1, transaction_id2 From gme_transaction_pairs where pair_type = 1) gtp, -- PK Bug 9069363
10803: gmf_process_organizations_gt gpo,
10855: mmt.transaction_date as oc1,
10856: decode(mmt.transaction_action_id,27,-1,mmt.transaction_source_type_id) as oc2,
10857: mmt.transaction_id as oc3,
10858: 0 as routing_id
10859: FROM mtl_material_transactions mmt,
10860: mtl_transaction_lot_numbers mtln,
10861: gmf_process_organizations_gt gpo,
10862: gmf_lot_costed_items_gt lcig
10863: WHERE
10910: mmt.transaction_date as oc1,
10911: decode(mmt.transaction_action_id,27,-1,mmt.transaction_source_type_id) as oc2,
10912: mmt.transaction_id as oc3,
10913: 0 as routing_id
10914: FROM mtl_material_transactions mmt,
10915: mtl_transaction_lot_numbers mtln,
10916: gmf_process_organizations_gt gpo,
10917: gmf_lot_costed_items_gt lcig
10918: WHERE
10964: mmt.transaction_date as oc1,
10965: decode(mmt.transaction_action_id,27,-1,mmt.transaction_source_type_id) as oc2,
10966: mmt.transaction_id as oc3,
10967: 0 as routing_id
10968: FROM mtl_material_transactions mmt,
10969: mtl_transaction_lot_numbers mtln,
10970: gmf_process_organizations_gt gpo,
10971: gmf_lot_costed_items_gt lcig
10972: WHERE
11072: 0 as routing_id
11073: FROM
11074: gmf_lc_adj_transactions glat, -- from import process of LCM
11075: rcv_transactions rt,
11076: mtl_material_transactions mmt, -- Bug 13906192
11077: mtl_transaction_lot_numbers mtln, -- Bug 13906192
11078: gmf_process_organizations_gt gpo,
11079: gmf_lot_costed_items_gt glci
11080: WHERE glat.transaction_date >= l_trans_start_date
11550: SELECT transfer_organization_id,
11551: hoi.org_information2
11552: INTO l_source_orgn_id,
11553: l_source_le_id
11554: FROM mtl_material_transactions mmt,
11555: hr_organization_information hoi
11556: WHERE mmt.transaction_id = transaction_row.transaction_id
11557: AND hoi.organization_id = mmt.transfer_organization_id
11558: AND hoi.org_information_context = 'Accounting Information';