[Home] [Help]
602: mti.program_application_id,
603: mti.program_id,
604: mti.program_update_date
605: FROM mtl_transactions_interface mti,
606: mtl_system_items_kfv msik
607: WHERE mti.transaction_header_id = p_mtl_header_id
608: AND mti.inventory_item_id = msik.inventory_item_id
609: AND mti.organization_id = msik.organization_id
610: AND mti.error_explanation IS NOT NULL;
6057: FROM /* bugfix 5129403: modified the order in the from clause */
6058: wip_move_txn_interface wmti,
6059: wip_operation_resources wor,
6060: bom_resources br,
6061: mtl_system_items msi,
6062: mtl_parameters mp,
6063: -- bom_calendar_dates bcd2, Bug 4398047 join to bcd2 not required
6064: bom_calendar_dates bcd4,
6065: -- bom_calendar_dates bcd1, Bug 4398047 join to bcd1 not required
6273: bom_departments bd,
6274: bom_calendar_dates bcd1,
6275: bom_calendar_dates bcd2,
6276: fnd_user fu,
6277: mtl_system_items msi,
6278: mtl_parameters mp,
6279: hr_organization_information hoi,
6280: wip_entities we,
6281: wip_repetitive_schedules wrs,
6481: FROM /* bugfix 5129403: modified the order in the from clause */
6482: wip_move_txn_interface wmti,
6483: wip_operation_resources wor,
6484: bom_resources br,
6485: mtl_system_items msi,
6486: mtl_parameters mp,
6487: -- bom_calendar_dates bcd2, Bug 4398047 join no longer required
6488: bom_calendar_dates bcd4,
6489: -- bom_calendar_dates bcd1, Bug 4398047 join no longer required
6715: bom_calendar_dates bcd1,
6716: bom_calendar_dates bcd2,
6717: fnd_user fu,
6718: mtl_item_revisions mir,
6719: mtl_system_items msi,
6720: mtl_parameters mp,
6721: hr_organization_information hoi,
6722: wip_operation_resources wor,
6723: wip_repetitive_schedules wrs,
8343: p_gib.mtl_header_id, -- transaction_batch_id
8344: wmti.qa_collection_id, /*Added for Bug 7136450 (FP of 7126588 )*/
8345: WIP_CONSTANTS.ASSY_BATCH_SEQ -- transaction_batch_seq
8346: FROM wip_move_txn_interface wmti,
8347: mtl_system_items msi,
8348: wip_discrete_jobs wdj
8349: WHERE wmti.group_id = p_gib.group_id
8350: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
8351: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
8477: 'Y', -- posting_flag
8478: p_gib.mtl_header_id, -- transaction_batch_id
8479: WIP_CONSTANTS.ASSY_BATCH_SEQ -- transaction_batch_seq
8480: FROM wip_move_txn_interface wmti,
8481: mtl_system_items msi,
8482: wip_repetitive_schedules wrs
8483: WHERE wmti.group_id = p_gib.group_id
8484: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
8485: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
8880: Decode(wmti.entity_type,5,'Y') -- FP bug 7315664 for bug 7281164
8881: FROM wip_move_txn_interface wmti,
8882: mtl_item_locations mil,
8883: wip_operations wop,
8884: mtl_system_items msi,
8885: wip_discrete_jobs wdj
8886: WHERE wmti.group_id = p_gib.group_id
8887: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
8888: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
9019: -- material processor need this value to enable quality
9020: wmti.qa_collection_id qa_collection_id
9021: FROM wip_move_txn_interface wmti,
9022: wip_operations wop,
9023: mtl_system_items msi,
9024: wip_repetitive_schedules wrs,
9025: wip_repetitive_items wri
9026: WHERE wmti.group_id = p_gib.group_id
9027: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
9061:
9062: SELECT COUNT(*)
9063: INTO l_mti_lot_rec
9064: FROM mtl_transactions_interface mti,
9065: mtl_system_items msi
9066: WHERE mti.organization_id = msi.organization_id
9067: AND mti.inventory_item_id = msi.inventory_item_id
9068: AND mti.transaction_header_id = p_gib.assy_header_id
9069: AND mti.transaction_action_id = l_txn_action_id
9130: NULL),
9131: 1, -- Bug 13734082 ,FP Bug for Populating orignation type as 'Production'
9132: mti.transaction_date
9133: FROM mtl_transactions_interface mti,
9134: mtl_system_items msi,
9135: wip_discrete_jobs wdj
9136: WHERE mti.organization_id = msi.organization_id
9137: AND mti.inventory_item_id = msi.inventory_item_id
9138: AND mti.transaction_header_id = p_gib.assy_header_id
9167: UPDATE mtl_transaction_lots_interface mtli
9168: SET lot_expiration_date =
9169: (SELECT MIN(mln.expiration_date)
9170: FROM mtl_transactions_interface mti,
9171: mtl_system_items msi,
9172: mtl_lot_numbers mln
9173: WHERE mti.organization_id = msi.organization_id
9174: AND mti.inventory_item_id = msi.inventory_item_id
9175: AND mti.transaction_header_id = p_gib.assy_header_id
9184: AND mln.organization_id = mti.organization_id)
9185: WHERE mtli.transaction_interface_id IN
9186: (SELECT mti.transaction_interface_id
9187: FROM mtl_transactions_interface mti,
9188: mtl_system_items msi
9189: WHERE mti.organization_id = msi.organization_id
9190: AND mti.inventory_item_id = msi.inventory_item_id
9191: AND mti.transaction_header_id = p_gib.assy_header_id
9192: AND mti.transaction_action_id = l_txn_action_id
9225: UPDATE mtl_transaction_lots_interface mtli
9226: SET lot_expiration_date =
9227: (SELECT mti.transaction_date + NVL(msi.shelf_life_days,0)
9228: FROM mtl_transactions_interface mti,
9229: mtl_system_items msi
9230: WHERE mti.transaction_header_id = p_gib.assy_header_id
9231: AND mti.transaction_action_id = l_txn_action_id
9232: AND mti.transaction_type_id = l_txn_type_id
9233: AND mti.transaction_source_type_id = TPS_INV_JOB_OR_SCHED
9242: WHERE mtli.lot_expiration_date IS NULL
9243: AND mtli.transaction_interface_id IN
9244: (SELECT mti.transaction_interface_id
9245: FROM mtl_transactions_interface mti,
9246: mtl_system_items msi
9247: WHERE mti.organization_id = msi.organization_id
9248: AND mti.inventory_item_id = msi.inventory_item_id
9249: AND mti.transaction_header_id = p_gib.assy_header_id
9250: AND mti.transaction_action_id = l_txn_action_id
9269:
9270: SELECT COUNT(*)
9271: INTO l_mti_ser_rec
9272: FROM mtl_transactions_interface mti,
9273: mtl_system_items msi
9274: WHERE mti.organization_id = msi.organization_id
9275: AND mti.inventory_item_id = msi.inventory_item_id
9276: AND mti.transaction_header_id = p_gib.assy_header_id
9277: AND mti.transaction_action_id = l_txn_action_id
9314: mti.last_update_login,
9315: wsmi.assembly_serial_number,
9316: wsmi.assembly_serial_number
9317: FROM mtl_transactions_interface mti,
9318: mtl_system_items msi,
9319: mtl_transaction_lots_interface mtli,
9320: wip_serial_move_interface wsmi
9321: WHERE mti.organization_id = msi.organization_id
9322: AND mti.inventory_item_id = msi.inventory_item_id
11405: UPDATE mtl_transaction_lots_interface mtli
11406: SET lot_expiration_date =
11407: (SELECT MIN(mln.expiration_date)
11408: FROM mtl_transactions_interface mti,
11409: mtl_system_items msi,
11410: mtl_lot_numbers mln
11411: WHERE mti.organization_id = msi.organization_id
11412: AND mti.inventory_item_id = msi.inventory_item_id
11413: AND mti.transaction_header_id = p_mtl_header_id
11421: AND mln.organization_id = mti.organization_id)
11422: WHERE mtli.transaction_interface_id IN
11423: (SELECT mti.transaction_interface_id
11424: FROM mtl_transactions_interface mti,
11425: mtl_system_items msi
11426: WHERE mti.organization_id = msi.organization_id
11427: AND mti.inventory_item_id = msi.inventory_item_id
11428: AND mti.transaction_header_id = p_mtl_header_id
11429: AND mti.transaction_action_id in (WIP_CONSTANTS.RETASSY_ACTION,WIP_CONSTANTS.CPLASSY_ACTION)
11451: UPDATE mtl_transaction_lots_interface mtli
11452: SET lot_expiration_date =
11453: (SELECT mti.transaction_date + NVL(msi.shelf_life_days,0)
11454: FROM mtl_transactions_interface mti,
11455: mtl_system_items msi
11456: WHERE mti.transaction_header_id =p_mtl_header_id
11457: AND mti.transaction_action_id in (WIP_CONSTANTS.RETASSY_ACTION,WIP_CONSTANTS.CPLASSY_ACTION)
11458: AND mti.transaction_type_id in (WIP_CONSTANTS.RETASSY_TYPE,WIP_CONSTANTS.CPLASSY_TYPE)
11459: AND mti.transaction_source_type_id = TPS_INV_JOB_OR_SCHED
11467: WHERE mtli.lot_expiration_date IS NULL
11468: AND mtli.transaction_interface_id IN
11469: (SELECT mti.transaction_interface_id
11470: FROM mtl_transactions_interface mti,
11471: mtl_system_items msi
11472: WHERE mti.organization_id = msi.organization_id
11473: AND mti.inventory_item_id = msi.inventory_item_id
11474: AND mti.transaction_header_id = p_mtl_header_id
11475: AND mti.transaction_action_id in (WIP_CONSTANTS.RETASSY_ACTION,WIP_CONSTANTS.CPLASSY_ACTION)