[Home] [Help]
80: where transaction_header_id = p_txnHeaderID
81: and transaction_source_type_id = 5
82: and process_flag = 1
83: and upper(nvl(flow_schedule, 'N')) = 'Y'
84: and transaction_action_id in (WIP_CONSTANTS.SCRASSY_ACTION,
85: WIP_CONSTANTS.CPLASSY_ACTION,
86: WIP_CONSTANTS.RETASSY_ACTION);
87: l_returnStatus varchar2(1);
88: l_validationException exception;
81: and transaction_source_type_id = 5
82: and process_flag = 1
83: and upper(nvl(flow_schedule, 'N')) = 'Y'
84: and transaction_action_id in (WIP_CONSTANTS.SCRASSY_ACTION,
85: WIP_CONSTANTS.CPLASSY_ACTION,
86: WIP_CONSTANTS.RETASSY_ACTION);
87: l_returnStatus varchar2(1);
88: l_validationException exception;
89:
82: and process_flag = 1
83: and upper(nvl(flow_schedule, 'N')) = 'Y'
84: and transaction_action_id in (WIP_CONSTANTS.SCRASSY_ACTION,
85: WIP_CONSTANTS.CPLASSY_ACTION,
86: WIP_CONSTANTS.RETASSY_ACTION);
87: l_returnStatus varchar2(1);
88: l_validationException exception;
89:
90: l_params wip_logger.param_tbl_t;
100: l_nontxn_excluded VARCHAR2(1); --added for fix 5630078
101: begin
102: l_lot_entry_type := 0 ; /* Fix for Bug#5187500 */
103:
104: if (l_logLevel <= wip_constants.trace_logging) then
105: l_params(1).paramName := 'p_txnHeaderID';
106: l_params(1).paramValue := p_txnHeaderID;
107: wip_logger.entryPoint(p_procName => 'wip_flowutil_priv.processFlowInterfaceRecords',
108: p_params => l_params,
233: p_taskID => flow_rec.task_id,
234: p_toOpSeqNum => flow_rec.operation_seq_num,
235: p_altRoutDesig => flow_rec.alternate_routing_designator,
236: p_acctPeriodID => flow_rec.acct_period_id,
237: p_txnMode => wip_constants.background,
238: p_lockFlag => wip_constants.yes,
239: p_txnSourceID => flow_rec.transaction_source_id,
240: p_cplTxnID => flow_rec.completion_transaction_id,
241: p_txnBatchID => flow_rec.transaction_batch_id,
234: p_toOpSeqNum => flow_rec.operation_seq_num,
235: p_altRoutDesig => flow_rec.alternate_routing_designator,
236: p_acctPeriodID => flow_rec.acct_period_id,
237: p_txnMode => wip_constants.background,
238: p_lockFlag => wip_constants.yes,
239: p_txnSourceID => flow_rec.transaction_source_id,
240: p_cplTxnID => flow_rec.completion_transaction_id,
241: p_txnBatchID => flow_rec.transaction_batch_id,
242: p_txnBatchSeq => flow_rec.transaction_batch_seq + 1,
264: INTO l_bf_count
265: FROM mtl_transactions_interface
266: WHERE transaction_header_id = p_txnHeaderID
267: AND completion_transaction_id = flow_rec.completion_transaction_id
268: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
269: WIP_CONSTANTS.RETCOMP_ACTION,
270: WIP_CONSTANTS.ISSNEGC_ACTION,
271: WIP_CONSTANTS.RETNEGC_ACTION);
272:
265: FROM mtl_transactions_interface
266: WHERE transaction_header_id = p_txnHeaderID
267: AND completion_transaction_id = flow_rec.completion_transaction_id
268: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
269: WIP_CONSTANTS.RETCOMP_ACTION,
270: WIP_CONSTANTS.ISSNEGC_ACTION,
271: WIP_CONSTANTS.RETNEGC_ACTION);
272:
273: /* Bug 9907143: modified for serial tagging enhancement for SUN
266: WHERE transaction_header_id = p_txnHeaderID
267: AND completion_transaction_id = flow_rec.completion_transaction_id
268: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
269: WIP_CONSTANTS.RETCOMP_ACTION,
270: WIP_CONSTANTS.ISSNEGC_ACTION,
271: WIP_CONSTANTS.RETNEGC_ACTION);
272:
273: /* Bug 9907143: modified for serial tagging enhancement for SUN
274:
267: AND completion_transaction_id = flow_rec.completion_transaction_id
268: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
269: WIP_CONSTANTS.RETCOMP_ACTION,
270: WIP_CONSTANTS.ISSNEGC_ACTION,
271: WIP_CONSTANTS.RETNEGC_ACTION);
272:
273: /* Bug 9907143: modified for serial tagging enhancement for SUN
274:
275: SELECT COUNT(*)
277: FROM mtl_transactions_interface mti,
278: mtl_system_items msi
279: WHERE mti.organization_id = msi.organization_id
280: AND mti.inventory_item_id = msi.inventory_item_id
281: AND (msi.lot_control_code = WIP_CONSTANTS.LOT
282: OR
283: msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
284: WIP_CONSTANTS.DYN_RCV_SN))
285: AND transaction_header_id = p_txnHeaderID
279: WHERE mti.organization_id = msi.organization_id
280: AND mti.inventory_item_id = msi.inventory_item_id
281: AND (msi.lot_control_code = WIP_CONSTANTS.LOT
282: OR
283: msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
284: WIP_CONSTANTS.DYN_RCV_SN))
285: AND transaction_header_id = p_txnHeaderID
286: AND completion_transaction_id = flow_rec.completion_transaction_id
287: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
280: AND mti.inventory_item_id = msi.inventory_item_id
281: AND (msi.lot_control_code = WIP_CONSTANTS.LOT
282: OR
283: msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
284: WIP_CONSTANTS.DYN_RCV_SN))
285: AND transaction_header_id = p_txnHeaderID
286: AND completion_transaction_id = flow_rec.completion_transaction_id
287: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
288: WIP_CONSTANTS.RETCOMP_ACTION,
283: msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
284: WIP_CONSTANTS.DYN_RCV_SN))
285: AND transaction_header_id = p_txnHeaderID
286: AND completion_transaction_id = flow_rec.completion_transaction_id
287: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
288: WIP_CONSTANTS.RETCOMP_ACTION,
289: WIP_CONSTANTS.ISSNEGC_ACTION,
290: WIP_CONSTANTS.RETNEGC_ACTION);
291: */
284: WIP_CONSTANTS.DYN_RCV_SN))
285: AND transaction_header_id = p_txnHeaderID
286: AND completion_transaction_id = flow_rec.completion_transaction_id
287: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
288: WIP_CONSTANTS.RETCOMP_ACTION,
289: WIP_CONSTANTS.ISSNEGC_ACTION,
290: WIP_CONSTANTS.RETNEGC_ACTION);
291: */
292: SELECT COUNT(*)
285: AND transaction_header_id = p_txnHeaderID
286: AND completion_transaction_id = flow_rec.completion_transaction_id
287: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
288: WIP_CONSTANTS.RETCOMP_ACTION,
289: WIP_CONSTANTS.ISSNEGC_ACTION,
290: WIP_CONSTANTS.RETNEGC_ACTION);
291: */
292: SELECT COUNT(*)
293: INTO l_lot_ser_count
286: AND completion_transaction_id = flow_rec.completion_transaction_id
287: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
288: WIP_CONSTANTS.RETCOMP_ACTION,
289: WIP_CONSTANTS.ISSNEGC_ACTION,
290: WIP_CONSTANTS.RETNEGC_ACTION);
291: */
292: SELECT COUNT(*)
293: INTO l_lot_ser_count
294: FROM mtl_transactions_interface mti,
294: FROM mtl_transactions_interface mti,
295: mtl_system_items msi
296: WHERE mti.organization_id = msi.organization_id
297: AND mti.inventory_item_id = msi.inventory_item_id
298: AND (msi.lot_control_code = WIP_CONSTANTS.LOT
299: OR
300: msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
301: WIP_CONSTANTS.DYN_RCV_SN)
302: OR
296: WHERE mti.organization_id = msi.organization_id
297: AND mti.inventory_item_id = msi.inventory_item_id
298: AND (msi.lot_control_code = WIP_CONSTANTS.LOT
299: OR
300: msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
301: WIP_CONSTANTS.DYN_RCV_SN)
302: OR
303: inv_cache.get_serial_tagged(mti.organization_id, mti.inventory_item_id, mti.transaction_type_id ) = WIP_CONSTANTS.SER_TAGGED)
304: AND transaction_header_id = p_txnHeaderID
297: AND mti.inventory_item_id = msi.inventory_item_id
298: AND (msi.lot_control_code = WIP_CONSTANTS.LOT
299: OR
300: msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
301: WIP_CONSTANTS.DYN_RCV_SN)
302: OR
303: inv_cache.get_serial_tagged(mti.organization_id, mti.inventory_item_id, mti.transaction_type_id ) = WIP_CONSTANTS.SER_TAGGED)
304: AND transaction_header_id = p_txnHeaderID
305: AND completion_transaction_id = flow_rec.completion_transaction_id
299: OR
300: msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
301: WIP_CONSTANTS.DYN_RCV_SN)
302: OR
303: inv_cache.get_serial_tagged(mti.organization_id, mti.inventory_item_id, mti.transaction_type_id ) = WIP_CONSTANTS.SER_TAGGED)
304: AND transaction_header_id = p_txnHeaderID
305: AND completion_transaction_id = flow_rec.completion_transaction_id
306: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
307: WIP_CONSTANTS.RETCOMP_ACTION,
302: OR
303: inv_cache.get_serial_tagged(mti.organization_id, mti.inventory_item_id, mti.transaction_type_id ) = WIP_CONSTANTS.SER_TAGGED)
304: AND transaction_header_id = p_txnHeaderID
305: AND completion_transaction_id = flow_rec.completion_transaction_id
306: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
307: WIP_CONSTANTS.RETCOMP_ACTION,
308: WIP_CONSTANTS.ISSNEGC_ACTION,
309: WIP_CONSTANTS.RETNEGC_ACTION);
310:
303: inv_cache.get_serial_tagged(mti.organization_id, mti.inventory_item_id, mti.transaction_type_id ) = WIP_CONSTANTS.SER_TAGGED)
304: AND transaction_header_id = p_txnHeaderID
305: AND completion_transaction_id = flow_rec.completion_transaction_id
306: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
307: WIP_CONSTANTS.RETCOMP_ACTION,
308: WIP_CONSTANTS.ISSNEGC_ACTION,
309: WIP_CONSTANTS.RETNEGC_ACTION);
310:
311: if (l_lot_entry_type = 0 ) then -- Check only once
304: AND transaction_header_id = p_txnHeaderID
305: AND completion_transaction_id = flow_rec.completion_transaction_id
306: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
307: WIP_CONSTANTS.RETCOMP_ACTION,
308: WIP_CONSTANTS.ISSNEGC_ACTION,
309: WIP_CONSTANTS.RETNEGC_ACTION);
310:
311: if (l_lot_entry_type = 0 ) then -- Check only once
312: SELECT backflush_lot_entry_type
305: AND completion_transaction_id = flow_rec.completion_transaction_id
306: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
307: WIP_CONSTANTS.RETCOMP_ACTION,
308: WIP_CONSTANTS.ISSNEGC_ACTION,
309: WIP_CONSTANTS.RETNEGC_ACTION);
310:
311: if (l_lot_entry_type = 0 ) then -- Check only once
312: SELECT backflush_lot_entry_type
313: INTO l_lot_entry_type
315: WHERE organization_id = flow_rec.organization_id ;
316: end if ;
317:
318: IF ((l_bf_count <> 0) and (l_lot_ser_count <> 0) and
319: (l_lot_entry_type <> WIP_CONSTANTS.MAN_ENTRY)) THEN
320: -- derive lot for Components
321: wip_autoLotProc_priv.deriveLotsFromMTI
322: (p_orgID => flow_rec.organization_id,
323: p_wipEntityID => flow_rec.transaction_source_id,
356: end if;
357: end if;
358: exception
359: when l_validationException then
360: if (l_logLevel <= wip_constants.full_logging) then
361: wip_logger.log(p_msg => 'Error defaulting/validating interface ' ||
362: to_char(flow_rec.transaction_interface_id) || ':' || l_errMsg,
363: x_returnStatus => l_returnStatus);
364: end if;
365: -- skip this one, validate next record. pl/sql doesn't have continue;
366: end;
367: end loop;
368:
369: if (l_logLevel <= wip_constants.trace_logging) then
370: wip_logger.exitPoint(p_procName => 'wip_flowutil_priv.processFlowInterfaceRecords',
371: p_procReturnStatus => fnd_api.g_ret_sts_success,
372: p_msg => 'Finished processFlowInterfaceRecords',
373: x_returnStatus => l_returnStatus); --discard logging return status
428: p_rout_rev_date => p_routRevDate,
429: p_item_id => p_itemID,
430: p_start_date => p_txnDate,
431: p_org_id => p_orgID) = 0 )
432: or (p_txnActionID <> WIP_CONSTANTS.SCRASSY_ACTION and
433: wip_flow_derive.completion_sub(
434: p_comp_sub => p_cplSubinv,
435: p_item_id => p_itemID,
436: p_org_id => p_orgID,
434: p_comp_sub => p_cplSubinv,
435: p_item_id => p_itemID,
436: p_org_id => p_orgID,
437: p_alt_rtg_des => p_altRtgDes) = 0 )
438: or (p_txnActionID <> WIP_CONSTANTS.SCRASSY_ACTION and
439: wip_flow_derive.completion_locator_id(
440: p_comp_loc => p_cplLocID,
441: p_item_id => p_itemID,
442: p_org_id => p_orgID,
738: l_compTbl system.wip_component_tbl_t;
739: l_count number;
740: l_childTxnTypeID number;
741: l_childTxnActionID number;
742: l_insertPhantom number := WIP_CONSTANTS.NO;
743:
744: l_insert varchar2(1) ; /*BUG 6134576*/
745: l_service_item_flag varchar2(1) ; /*BUG 6134576*/
746:
769: l_wfs_info wfs_info_cursor%ROWTYPE := null;
770: l_wip_entity_id number := null; -- use it to retrieve wip_flow_schedules info
771: begin
772:
773: if (l_logLevel <= wip_constants.trace_logging) then
774: l_params(1).paramName := 'p_txnHeaderID';
775: l_params(1).paramValue := p_txnHeaderID;
776: l_params(2).paramName := 'p_parentID';
777: l_params(2).paramValue := p_parentID;
884:
885: end if;
886:
887: -- bug 5605598: filter out lot-based components appropriately
888: if (nvl(l_compTbl(l_count).basis_type,WIP_CONSTANTS.ITEM_BASED_MTL) = WIP_CONSTANTS.LOT_BASED_MTL) then
889: if (
890: not(
891: (l_wfs_info.quantity_completed = 0 and l_wfs_info.quantity_scrapped <= 0 and p_qty > 0) or
892: (l_wfs_info.quantity_completed + l_wfs_info.quantity_scrapped > 0 and
899: end if;
900:
901: -- we don't insert phantom comp(for phantom routing resource charging) into mti if
902: -- the bom parameter is set to NO.
903: if (( l_insertPhantom = WIP_CONSTANTS.YES or
904: nvl(l_compTbl(l_count).wip_supply_type, 1) <> 6) and l_insert = 'Y' ) then /*Bug 6134576*/
905: -- derive the txn type and action id
906: l_childTxnActionID := l_compTbl(l_count).transaction_action_id;
907: l_childTxnTypeID := getTypeFromAction(l_childTxnActionID);
979: 1, -- process flag
980: p_lockFlag,
981: 1, -- validation required
982: p_txnDate,
983: ROUND(l_compTbl(l_count).primary_quantity * -1, WIP_CONSTANTS.INV_MAX_PRECISION),
984: l_compTbl(l_count).primary_uom_code,
985: ROUND(l_compTbl(l_count).primary_quantity * -1, WIP_CONSTANTS.INV_MAX_PRECISION),
986: 5,
987: 'Y',
981: 1, -- validation required
982: p_txnDate,
983: ROUND(l_compTbl(l_count).primary_quantity * -1, WIP_CONSTANTS.INV_MAX_PRECISION),
984: l_compTbl(l_count).primary_uom_code,
985: ROUND(l_compTbl(l_count).primary_quantity * -1, WIP_CONSTANTS.INV_MAX_PRECISION),
986: 5,
987: 'Y',
988: l_childTxnActionID,
989: l_childTxnTypeID,
987: 'Y',
988: l_childTxnActionID,
989: l_childTxnTypeID,
990: l_compTbl(l_count).wip_supply_type,
991: wip_constants.flow,
992: l_compTbl(l_count).supply_subinventory,
993: l_compTbl(l_count).supply_locator_id,
994: p_acctPeriodID,
995: p_cplTxnID,
999: l_compTbl(l_count).task_id,
1000: p_projectID,
1001: p_taskID);
1002:
1003: if (l_logLevel <= wip_constants.full_logging) then
1004: wip_logger.log(p_msg => 'Insert item ' || l_compTbl(l_count).inventory_item_id ||
1005: ' under op ' || l_compTbl(l_count).operation_seq_num,
1006: x_returnStatus => l_returnStatus);
1007: end if;
1010: <
1011: l_count := l_compTbl.next(l_count);
1012: end loop;
1013:
1014: if (l_logLevel <= wip_constants.trace_logging) then
1015: wip_logger.exitPoint(p_procName => 'wip_flowutil_priv.explodeRequirementsToMTI',
1016: p_procReturnStatus => x_returnStatus,
1017: p_msg => 'Explode BOM to MTI successfully for interface ' ||
1018: to_char(p_parentID) || ' successfully!',
1024:
1025: wip_utilities.get_message_stack(p_msg => l_errMsg,
1026: p_delete_stack => fnd_api.g_false);
1027:
1028: if (l_logLevel <= wip_constants.trace_logging) then
1029: wip_logger.exitPoint(p_procName => 'wip_flowutil_priv.explodeRequirementsToMTI',
1030: p_procReturnStatus => x_returnStatus,
1031: p_msg => 'explosion error: ' || l_errMsg || ' for exploding interface '
1032: || to_char(p_parentID),
1040: substrb(fnd_message.get, 1, 240));
1041:
1042: when others then
1043: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1044: if (l_logLevel <= wip_constants.trace_logging) then
1045:
1046: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_flowutil_priv',
1047: p_procedure_name => 'explodeRequirementsToMTI',
1048: p_error_text => SQLERRM);
1253: source_project_id = l_srcProjID,
1254: source_task_id = l_srcTaskID,
1255: transaction_source_id = l_wipEntityID,
1256: wip_entity_type = decode(l_wipEntityID, null, null, 4),
1257: transaction_date = to_date(to_char(l_txnDate, WIP_CONSTANTS.DT_NOSEC_FMT), WIP_CONSTANTS.DT_NOSEC_FMT)
1258: where parent_id = p_parentID
1259: and process_flag = 1
1260: and substitution_type_id is null;
1261:
1396: and transaction_source_id is null
1397: and flow_schedule = 'Y'
1398: and transaction_action_id in (31, 32, 30) -- CFM Scrap
1399: and scheduled_flag = 2
1400: and process_flag = wip_constants.mti_inventory;
1401:
1402: cursor c_MMTTflowCompletion return flow_rec_t is
1403: select transaction_action_id, -- CFM Scrap
1404: last_update_date,
1498: begin
1499:
1500: x_returnStatus := fnd_api.g_ret_sts_success;
1501:
1502: if (l_logLevel <= wip_constants.trace_logging) then
1503: l_params(1).paramName := 'p_txnInterfaceID';
1504: l_params(1).paramValue := p_txnInterfaceID;
1505: wip_logger.entryPoint(p_procName => 'wip_flowUtil_priv.createFlowSchedule',
1506: p_params => l_params,
1509: raise fnd_api.g_exc_unexpected_error;
1510: end if;
1511: end if;
1512:
1513: if(l_logLevel <= wip_constants.full_logging) then
1514: declare
1515: l_count NUMBER;
1516: begin
1517: if(p_txnInterfaceID is not null) then
1556: close c_MMTTflowCompletion;
1557: end if;
1558:
1559: if(l_flowRec.organization_id is null) then --org id column is not null in both MTI and MMTT
1560: if(l_logLevel <= wip_constants.trace_logging) then
1561: wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.createFlowSchedule',
1562: p_procReturnStatus => x_returnStatus,
1563: p_msg => 'Flow schedule creation not necessary.',
1564: x_returnStatus => l_returnStatus); --discard logging return status
1744: ) returning project_id, task_id into l_prjID, l_tskID;
1745:
1746: x_wipEntityID := l_wipEntityID;
1747:
1748: if (l_logLevel <= wip_constants.full_logging) then
1749: wip_logger.log(p_msg => 'wip entity id: ' || x_wipEntityID,
1750: x_returnStatus => l_returnStatus);
1751: wip_logger.log(p_msg => 'src prjID: ' || l_flowRec.source_project_id,
1752: x_returnStatus => l_returnStatus);
1774: wip_entity_type = l_flowRec.wip_entity_type
1775: where transaction_header_id = l_flowRec.transaction_header_id
1776: and completion_transaction_id = l_flowRec.completion_transaction_id;
1777:
1778: if (l_logLevel <= wip_constants.full_logging) then
1779: wip_logger.log(p_msg => SQL%ROWCOUNT || 'MTI rows updated!',
1780: x_returnStatus => l_returnStatus);
1781: end if;
1782:
1794: wip_entity_type = l_flowRec.wip_entity_type
1795: where transaction_temp_id = p_txnTmpID;
1796: end if;
1797:
1798: if (l_logLevel <= wip_constants.full_logging) then
1799: wip_logger.log(p_msg => SQL%ROWCOUNT || 'MMTT rows updated!',
1800: x_returnStatus => l_returnStatus);
1801: end if;
1802:
1799: wip_logger.log(p_msg => SQL%ROWCOUNT || 'MMTT rows updated!',
1800: x_returnStatus => l_returnStatus);
1801: end if;
1802:
1803: if (l_logLevel <= wip_constants.trace_logging) then
1804: wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.createFlowSchedule',
1805: p_procReturnStatus => x_returnStatus,
1806: p_msg => 'Flow schedules created successfully!',
1807: x_returnStatus => l_returnStatus); --discard logging return status
1808: end if;
1809: exception
1810: when others then
1811: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1812: if (l_logLevel <= wip_constants.trace_logging) then
1813: wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.createFlowSchedule',
1814: p_procReturnStatus => x_returnStatus,
1815: p_msg => 'unexpected error: ' || SQLERRM,
1816: x_returnStatus => l_returnStatus); --discard logging return status
1841: l_returnStatus varchar2(1);
1842: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1843: begin
1844:
1845: if (l_logLevel <= wip_constants.trace_logging) then
1846: l_params(1).paramName := 'p_txnTempID';
1847: l_params(1).paramValue := p_txnTempID;
1848: wip_logger.entryPoint(p_procName => 'wip_flowUtil_priv.updateFlowSchedule',
1849: p_params => l_params,
1924: last_updated_by = FND_GLOBAL.user_id,--bugfix 7379879
1925: last_update_date = sysdate
1926: where wip_entity_id = l_wipEntityID;
1927:
1928: if (l_logLevel <= wip_constants.trace_logging) then
1929: wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.updateFlowSchedule',
1930: p_procReturnStatus => x_returnStatus,
1931: p_msg => 'Flow schedules updated successfully!',
1932: x_returnStatus => l_returnStatus); --discard logging return status
1933: end if;
1934: exception
1935: when others then
1936: x_returnStatus := fnd_api.g_ret_sts_error;
1937: if (l_logLevel <= wip_constants.trace_logging) then
1938: wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.updateFlowSchedule',
1939: p_procReturnStatus => x_returnStatus,
1940: p_msg => 'unexpected error: ' || SQLERRM,
1941: x_returnStatus => l_returnStatus); --discard logging return status
2042: if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
2043: return;
2044: end if;
2045:
2046: if (l_logLevel <= wip_constants.full_logging) then
2047: wip_logger.log(p_msg => x_compTbl.count || ' components exploded!',
2048: x_returnStatus => l_returnStatus);
2049: end if;
2050:
2077:
2078: -- ER 4369064: For work orderless txn, we have to include yield factor
2079: -- if Include Yield in WIP Parameters is checked.
2080: -- Rounding this to 5 decimals since this will go into MTI
2081: /* Bug fix 4728358: Number of rounding decimal places should be obtained from WIP_CONSTANTS.
2082: In particular we want 6 places instead of 5. */
2083: if (p_txnFlag AND l_includeYield = 1) then
2084: x_compTbl(l_count).primary_quantity := round(x_compTbl(l_count).primary_quantity
2085: / x_compTbl(l_count).component_yield_factor, WIP_CONSTANTS.INV_MAX_PRECISION);
2081: /* Bug fix 4728358: Number of rounding decimal places should be obtained from WIP_CONSTANTS.
2082: In particular we want 6 places instead of 5. */
2083: if (p_txnFlag AND l_includeYield = 1) then
2084: x_compTbl(l_count).primary_quantity := round(x_compTbl(l_count).primary_quantity
2085: / x_compTbl(l_count).component_yield_factor, WIP_CONSTANTS.INV_MAX_PRECISION);
2086: /* End of bug fix 4728358 */
2087: end if;
2088:
2089: if (round(abs(x_compTbl(l_count).primary_quantity),
2086: /* End of bug fix 4728358 */
2087: end if;
2088:
2089: if (round(abs(x_compTbl(l_count).primary_quantity),
2090: WIP_CONSTANTS.INV_MAX_PRECISION) = 0) then
2091: if(p_txnFlag) then
2092: if (l_logLevel <= wip_constants.full_logging) then
2093: wip_logger.log(p_msg => 'Qty too small, component ' ||
2094: x_compTbl(l_count).inventory_item_id || ' stripped!',
2088:
2089: if (round(abs(x_compTbl(l_count).primary_quantity),
2090: WIP_CONSTANTS.INV_MAX_PRECISION) = 0) then
2091: if(p_txnFlag) then
2092: if (l_logLevel <= wip_constants.full_logging) then
2093: wip_logger.log(p_msg => 'Qty too small, component ' ||
2094: x_compTbl(l_count).inventory_item_id || ' stripped!',
2095: x_returnStatus => l_returnStatus);
2096: end if;
2116: l_checkPass := true;
2117: elsif ( l_cfmRouting <> 1 ) then
2118: if ( x_compTbl(l_count).operation_seq_num > p_toOpSeqNum ) then
2119: -- delete the comp that has op seq greater than to op seq num
2120: if (l_logLevel <= wip_constants.full_logging) then
2121: wip_logger.log(p_msg => 'Op ' || x_compTbl(l_count).operation_seq_num ||
2122: ' after terminal op, item ' ||
2123: x_compTbl(l_count).inventory_item_id || ' stripped!',
2124: x_returnStatus => l_returnStatus);
2146: p_toOpSeqNum,
2147: l_lineOpTbl) ) then
2148: l_checkPass := true;
2149: else
2150: if (l_logLevel <= wip_constants.full_logging) then
2151: wip_logger.log(p_msg => 'Event ' || x_compTbl(l_count).operation_seq_num ||
2152: ' belongs to line op that after terminal op, item ' ||
2153: x_compTbl(l_count).inventory_item_id || ' stripped!',
2154: x_returnStatus => l_returnStatus);
2165: -- ER 4369064: This code is called for discrete jobs too. Bulk and Supplier
2166: -- type components should be exploded for discrete jobs.
2167: if ( l_checkPass and p_txnFlag and
2168: nvl(x_compTbl(l_count).wip_supply_type, 1) not in (1, 2, 3, 6) ) then
2169: if (l_logLevel <= wip_constants.full_logging) then
2170: wip_logger.log(p_msg => 'Nonrelated supply type item ' ||
2171: x_compTbl(l_count).inventory_item_id ||
2172: ' at op ' || x_compTbl(l_count).operation_seq_num ||
2173: ' stripped!',
2200: x_returnStatus := fnd_api.g_ret_sts_error;
2201: fnd_message.set_name('WIP', 'WIP_COMP_NOT_TRANSACTABLE');
2202: fnd_message.set_token('ENTITY1', x_compTbl(l_count).item_name);
2203: fnd_msg_pub.add;
2204: if (l_logLevel <= wip_constants.full_logging) then
2205: wip_logger.log(p_msg => 'Item ' || x_compTbl(l_count).inventory_item_id ||
2206: ' not transactable, failed explosion!',
2207: x_returnStatus => l_returnStatus);
2208: end if;
2236: if(nvl(p_defaultPushSubinv, 'N') = 'Y') then --This is wol/flow txn
2237: x_compTbl(l_count).supply_subinventory := l_wpSubinv;
2238: l_locatorID := l_wpLocatorID;
2239: else -- This is discrete txn
2240: if(x_compTbl(l_count).wip_supply_type in(wip_constants.op_pull, wip_constants.assy_pull)) then
2241: x_compTbl(l_count).supply_subinventory := l_wpSubinv;
2242: l_locatorID := l_wpLocatorID;
2243: end if;
2244: end if;
2317: l_returnStatus varchar2(1);
2318: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2319: begin
2320:
2321: if (l_logLevel <= wip_constants.trace_logging) then
2322: l_params(1).paramName := 'p_txnTempID';
2323: l_params(1).paramValue := p_txnTempID;
2324: l_params(2).paramName := 'p_assyID';
2325: l_params(2).paramValue := p_assyID;
2382: l_count := l_compTbl.first;
2383: while ( l_count is not null ) loop
2384: -- we don't insert phantom comp(for phantom routing resource charging) into mmtt if
2385: -- the bom parameter is set to NO.
2386: if ( l_insertPhantom = WIP_CONSTANTS.YES or
2387: nvl(l_compTbl(l_count).wip_supply_type, 1) <> 6 ) then
2388: -- derive the txn action and type id
2389: l_childTxnActionID := l_compTbl(l_count).transaction_action_id;
2390: l_childTxnTypeID := getTypeFromAction(l_childTxnActionID);
2469: 'Y'
2470: from mtl_material_transactions_temp mmtt
2471: where mmtt.transaction_temp_id = p_txnTempID;
2472:
2473: if (l_logLevel <= wip_constants.full_logging) then
2474: wip_logger.log(p_msg => 'Insert item ' || l_compTbl(l_count).inventory_item_id ||
2475: ' under op ' || l_compTbl(l_count).operation_seq_num,
2476: x_returnStatus => l_returnStatus);
2477: end if;
2480: l_count := l_compTbl.next(l_count);
2481: end loop;
2482:
2483: l_compTbl.delete;
2484: if (l_logLevel <= wip_constants.trace_logging) then
2485: wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.explodeRequirementsToMMTT',
2486: p_procReturnStatus => x_returnStatus,
2487: p_msg => 'Exploded BOM to MMTT successfully!',
2488: x_returnStatus => l_returnStatus); --discard logging return status
2489: end if;
2490: exception
2491: when others then
2492: x_returnStatus := fnd_api.g_ret_sts_error;
2493: if (l_logLevel <= wip_constants.trace_logging) then
2494: wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.explodeRequirementsToMMTT',
2495: p_procReturnStatus => x_returnStatus,
2496: p_msg => 'unexpected error: ' || SQLERRM,
2497: x_returnStatus => l_returnStatus); --discard logging return status
2639: * transaction type id
2640: */
2641: function getTypeFromAction(p_txnActionID in number) return number is
2642: begin
2643: if ( p_txnActionID = WIP_CONSTANTS.ISSCOMP_ACTION ) then
2644: return WIP_CONSTANTS.ISSCOMP_TYPE;
2645: end if;
2646:
2647: if ( p_txnActionID = WIP_CONSTANTS.ISSNEGC_ACTION ) then
2640: */
2641: function getTypeFromAction(p_txnActionID in number) return number is
2642: begin
2643: if ( p_txnActionID = WIP_CONSTANTS.ISSCOMP_ACTION ) then
2644: return WIP_CONSTANTS.ISSCOMP_TYPE;
2645: end if;
2646:
2647: if ( p_txnActionID = WIP_CONSTANTS.ISSNEGC_ACTION ) then
2648: return WIP_CONSTANTS.ISSNEGC_TYPE;
2643: if ( p_txnActionID = WIP_CONSTANTS.ISSCOMP_ACTION ) then
2644: return WIP_CONSTANTS.ISSCOMP_TYPE;
2645: end if;
2646:
2647: if ( p_txnActionID = WIP_CONSTANTS.ISSNEGC_ACTION ) then
2648: return WIP_CONSTANTS.ISSNEGC_TYPE;
2649: end if;
2650:
2651: if ( p_txnActionID = WIP_CONSTANTS.RETCOMP_ACTION ) then
2644: return WIP_CONSTANTS.ISSCOMP_TYPE;
2645: end if;
2646:
2647: if ( p_txnActionID = WIP_CONSTANTS.ISSNEGC_ACTION ) then
2648: return WIP_CONSTANTS.ISSNEGC_TYPE;
2649: end if;
2650:
2651: if ( p_txnActionID = WIP_CONSTANTS.RETCOMP_ACTION ) then
2652: return WIP_CONSTANTS.RETCOMP_TYPE;
2647: if ( p_txnActionID = WIP_CONSTANTS.ISSNEGC_ACTION ) then
2648: return WIP_CONSTANTS.ISSNEGC_TYPE;
2649: end if;
2650:
2651: if ( p_txnActionID = WIP_CONSTANTS.RETCOMP_ACTION ) then
2652: return WIP_CONSTANTS.RETCOMP_TYPE;
2653: end if;
2654:
2655: if ( p_txnActionID = WIP_CONSTANTS.RETNEGC_ACTION ) then
2648: return WIP_CONSTANTS.ISSNEGC_TYPE;
2649: end if;
2650:
2651: if ( p_txnActionID = WIP_CONSTANTS.RETCOMP_ACTION ) then
2652: return WIP_CONSTANTS.RETCOMP_TYPE;
2653: end if;
2654:
2655: if ( p_txnActionID = WIP_CONSTANTS.RETNEGC_ACTION ) then
2656: return WIP_CONSTANTS.RETNEGC_TYPE;
2651: if ( p_txnActionID = WIP_CONSTANTS.RETCOMP_ACTION ) then
2652: return WIP_CONSTANTS.RETCOMP_TYPE;
2653: end if;
2654:
2655: if ( p_txnActionID = WIP_CONSTANTS.RETNEGC_ACTION ) then
2656: return WIP_CONSTANTS.RETNEGC_TYPE;
2657: end if;
2658:
2659: return null;
2652: return WIP_CONSTANTS.RETCOMP_TYPE;
2653: end if;
2654:
2655: if ( p_txnActionID = WIP_CONSTANTS.RETNEGC_ACTION ) then
2656: return WIP_CONSTANTS.RETNEGC_TYPE;
2657: end if;
2658:
2659: return null;
2660: end getTypeFromAction;
2667: p_signOfPer in number,
2668: x_childTxnTypeID out nocopy number,
2669: x_childTxnActionID out nocopy number) is
2670: begin
2671: if ( p_parentTxnTypeID in (WIP_CONSTANTS.CPLASSY_TYPE,
2672: WIP_CONSTANTS.SCRASSY_TYPE) ) then
2673: if ( p_signOfPer > 0 ) then
2674: x_childTxnTypeID := WIP_CONSTANTS.ISSCOMP_TYPE;
2675: x_childTxnActionID := WIP_CONSTANTS.ISSCOMP_ACTION;
2668: x_childTxnTypeID out nocopy number,
2669: x_childTxnActionID out nocopy number) is
2670: begin
2671: if ( p_parentTxnTypeID in (WIP_CONSTANTS.CPLASSY_TYPE,
2672: WIP_CONSTANTS.SCRASSY_TYPE) ) then
2673: if ( p_signOfPer > 0 ) then
2674: x_childTxnTypeID := WIP_CONSTANTS.ISSCOMP_TYPE;
2675: x_childTxnActionID := WIP_CONSTANTS.ISSCOMP_ACTION;
2676: else
2670: begin
2671: if ( p_parentTxnTypeID in (WIP_CONSTANTS.CPLASSY_TYPE,
2672: WIP_CONSTANTS.SCRASSY_TYPE) ) then
2673: if ( p_signOfPer > 0 ) then
2674: x_childTxnTypeID := WIP_CONSTANTS.ISSCOMP_TYPE;
2675: x_childTxnActionID := WIP_CONSTANTS.ISSCOMP_ACTION;
2676: else
2677: x_childTxnTypeID := WIP_CONSTANTS.ISSNEGC_TYPE;
2678: x_childTxnActionID := WIP_CONSTANTS.ISSNEGC_ACTION;
2671: if ( p_parentTxnTypeID in (WIP_CONSTANTS.CPLASSY_TYPE,
2672: WIP_CONSTANTS.SCRASSY_TYPE) ) then
2673: if ( p_signOfPer > 0 ) then
2674: x_childTxnTypeID := WIP_CONSTANTS.ISSCOMP_TYPE;
2675: x_childTxnActionID := WIP_CONSTANTS.ISSCOMP_ACTION;
2676: else
2677: x_childTxnTypeID := WIP_CONSTANTS.ISSNEGC_TYPE;
2678: x_childTxnActionID := WIP_CONSTANTS.ISSNEGC_ACTION;
2679: end if;
2673: if ( p_signOfPer > 0 ) then
2674: x_childTxnTypeID := WIP_CONSTANTS.ISSCOMP_TYPE;
2675: x_childTxnActionID := WIP_CONSTANTS.ISSCOMP_ACTION;
2676: else
2677: x_childTxnTypeID := WIP_CONSTANTS.ISSNEGC_TYPE;
2678: x_childTxnActionID := WIP_CONSTANTS.ISSNEGC_ACTION;
2679: end if;
2680: else -- return or return from scrap
2681: if ( p_signOfPer > 0 ) then
2674: x_childTxnTypeID := WIP_CONSTANTS.ISSCOMP_TYPE;
2675: x_childTxnActionID := WIP_CONSTANTS.ISSCOMP_ACTION;
2676: else
2677: x_childTxnTypeID := WIP_CONSTANTS.ISSNEGC_TYPE;
2678: x_childTxnActionID := WIP_CONSTANTS.ISSNEGC_ACTION;
2679: end if;
2680: else -- return or return from scrap
2681: if ( p_signOfPer > 0 ) then
2682: x_childTxnTypeID := WIP_CONSTANTS.RETCOMP_TYPE;
2678: x_childTxnActionID := WIP_CONSTANTS.ISSNEGC_ACTION;
2679: end if;
2680: else -- return or return from scrap
2681: if ( p_signOfPer > 0 ) then
2682: x_childTxnTypeID := WIP_CONSTANTS.RETCOMP_TYPE;
2683: x_childTxnActionID := WIP_CONSTANTS.RETCOMP_ACTION;
2684: else
2685: x_childTxnTypeID := WIP_CONSTANTS.RETNEGC_TYPE;
2686: x_childTxnActionID := WIP_CONSTANTS.RETNEGC_ACTION;
2679: end if;
2680: else -- return or return from scrap
2681: if ( p_signOfPer > 0 ) then
2682: x_childTxnTypeID := WIP_CONSTANTS.RETCOMP_TYPE;
2683: x_childTxnActionID := WIP_CONSTANTS.RETCOMP_ACTION;
2684: else
2685: x_childTxnTypeID := WIP_CONSTANTS.RETNEGC_TYPE;
2686: x_childTxnActionID := WIP_CONSTANTS.RETNEGC_ACTION;
2687: end if;
2681: if ( p_signOfPer > 0 ) then
2682: x_childTxnTypeID := WIP_CONSTANTS.RETCOMP_TYPE;
2683: x_childTxnActionID := WIP_CONSTANTS.RETCOMP_ACTION;
2684: else
2685: x_childTxnTypeID := WIP_CONSTANTS.RETNEGC_TYPE;
2686: x_childTxnActionID := WIP_CONSTANTS.RETNEGC_ACTION;
2687: end if;
2688: end if;
2689: end getChildTxn;
2682: x_childTxnTypeID := WIP_CONSTANTS.RETCOMP_TYPE;
2683: x_childTxnActionID := WIP_CONSTANTS.RETCOMP_ACTION;
2684: else
2685: x_childTxnTypeID := WIP_CONSTANTS.RETNEGC_TYPE;
2686: x_childTxnActionID := WIP_CONSTANTS.RETNEGC_ACTION;
2687: end if;
2688: end if;
2689: end getChildTxn;
2690:
2705: and transaction_source_type_id = 5
2706: and flow_schedule = 'Y'
2707: and process_flag = 'Y'
2708: and locator_id is not null
2709: and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
2710: WIP_CONSTANTS.RETCOMP_ACTION,
2711: WIP_CONSTANTS.ISSNEGC_ACTION,
2712: WIP_CONSTANTS.RETNEGC_ACTION)
2713: order by operation_seq_num;
2706: and flow_schedule = 'Y'
2707: and process_flag = 'Y'
2708: and locator_id is not null
2709: and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
2710: WIP_CONSTANTS.RETCOMP_ACTION,
2711: WIP_CONSTANTS.ISSNEGC_ACTION,
2712: WIP_CONSTANTS.RETNEGC_ACTION)
2713: order by operation_seq_num;
2714:
2707: and process_flag = 'Y'
2708: and locator_id is not null
2709: and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
2710: WIP_CONSTANTS.RETCOMP_ACTION,
2711: WIP_CONSTANTS.ISSNEGC_ACTION,
2712: WIP_CONSTANTS.RETNEGC_ACTION)
2713: order by operation_seq_num;
2714:
2715: l_params wip_logger.param_tbl_t;
2708: and locator_id is not null
2709: and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
2710: WIP_CONSTANTS.RETCOMP_ACTION,
2711: WIP_CONSTANTS.ISSNEGC_ACTION,
2712: WIP_CONSTANTS.RETNEGC_ACTION)
2713: order by operation_seq_num;
2714:
2715: l_params wip_logger.param_tbl_t;
2716: l_returnStatus varchar2(1);
2724: l_projRefEnabled number;
2725: l_orgLocControl number := 0;
2726: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2727: begin
2728: if (l_logLevel <= wip_constants.trace_logging) then
2729: l_params(1).paramName := 'p_parentID';
2730: l_params(1).paramValue := p_parentID;
2731: wip_logger.entryPoint(p_procName => 'wip_flowUtil_priv.generateCompLocator',
2732: p_params => l_params,
2756: from mtl_parameters
2757: where organization_id = l_orgID;
2758:
2759: if ( (l_srcProjectID is null) or (l_projRefEnabled <> 1) ) then
2760: if (l_logLevel <= wip_constants.trace_logging) then
2761: wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.generateCompLocator',
2762: p_procReturnStatus => x_returnStatus,
2763: p_msg => 'Source project id is null or the org parameter ' ||
2764: 'does not has project reference enabled',
2778: p_item_id => comp_rec.inventory_item_id,
2779: p_org_loc_control => l_orgLocControl);
2780: if ( l_success = false ) then
2781: x_returnStatus := fnd_api.g_ret_sts_error;
2782: if (l_logLevel <= wip_constants.trace_logging) then
2783: wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.generateCompLocator',
2784: p_procReturnStatus => x_returnStatus,
2785: p_msg => 'Error in calling '||
2786: 'pjm_project_locator.get_component_projectsupply!',
2801: where rowid = comp_rec.rowid;
2802: end if;
2803: end loop;
2804:
2805: if (l_logLevel <= wip_constants.trace_logging) then
2806: wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.generateCompLocator',
2807: p_procReturnStatus => x_returnStatus,
2808: p_msg => 'Finished!',
2809: x_returnStatus => l_returnStatus); --discard logging return status