[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,
224: p_taskID => flow_rec.task_id,
225: p_toOpSeqNum => flow_rec.operation_seq_num,
226: p_altRoutDesig => flow_rec.alternate_routing_designator,
227: p_acctPeriodID => flow_rec.acct_period_id,
228: p_txnMode => wip_constants.background,
229: p_lockFlag => wip_constants.yes,
230: p_txnSourceID => flow_rec.transaction_source_id,
231: p_cplTxnID => flow_rec.completion_transaction_id,
232: p_txnBatchID => flow_rec.transaction_batch_id,
225: p_toOpSeqNum => flow_rec.operation_seq_num,
226: p_altRoutDesig => flow_rec.alternate_routing_designator,
227: p_acctPeriodID => flow_rec.acct_period_id,
228: p_txnMode => wip_constants.background,
229: p_lockFlag => wip_constants.yes,
230: p_txnSourceID => flow_rec.transaction_source_id,
231: p_cplTxnID => flow_rec.completion_transaction_id,
232: p_txnBatchID => flow_rec.transaction_batch_id,
233: p_txnBatchSeq => flow_rec.transaction_batch_seq + 1,
255: INTO l_bf_count
256: FROM mtl_transactions_interface
257: WHERE transaction_header_id = p_txnHeaderID
258: AND completion_transaction_id = flow_rec.completion_transaction_id
259: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
260: WIP_CONSTANTS.RETCOMP_ACTION,
261: WIP_CONSTANTS.ISSNEGC_ACTION,
262: WIP_CONSTANTS.RETNEGC_ACTION);
263: SELECT COUNT(*)
256: FROM mtl_transactions_interface
257: WHERE transaction_header_id = p_txnHeaderID
258: AND completion_transaction_id = flow_rec.completion_transaction_id
259: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
260: WIP_CONSTANTS.RETCOMP_ACTION,
261: WIP_CONSTANTS.ISSNEGC_ACTION,
262: WIP_CONSTANTS.RETNEGC_ACTION);
263: SELECT COUNT(*)
264: INTO l_lot_ser_count
257: WHERE transaction_header_id = p_txnHeaderID
258: AND completion_transaction_id = flow_rec.completion_transaction_id
259: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
260: WIP_CONSTANTS.RETCOMP_ACTION,
261: WIP_CONSTANTS.ISSNEGC_ACTION,
262: WIP_CONSTANTS.RETNEGC_ACTION);
263: SELECT COUNT(*)
264: INTO l_lot_ser_count
265: FROM mtl_transactions_interface mti,
258: AND completion_transaction_id = flow_rec.completion_transaction_id
259: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
260: WIP_CONSTANTS.RETCOMP_ACTION,
261: WIP_CONSTANTS.ISSNEGC_ACTION,
262: WIP_CONSTANTS.RETNEGC_ACTION);
263: SELECT COUNT(*)
264: INTO l_lot_ser_count
265: FROM mtl_transactions_interface mti,
266: mtl_system_items msi
265: FROM mtl_transactions_interface mti,
266: mtl_system_items msi
267: WHERE mti.organization_id = msi.organization_id
268: AND mti.inventory_item_id = msi.inventory_item_id
269: AND (msi.lot_control_code = WIP_CONSTANTS.LOT
270: OR
271: msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
272: WIP_CONSTANTS.DYN_RCV_SN))
273: AND transaction_header_id = p_txnHeaderID
267: WHERE mti.organization_id = msi.organization_id
268: AND mti.inventory_item_id = msi.inventory_item_id
269: AND (msi.lot_control_code = WIP_CONSTANTS.LOT
270: OR
271: msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
272: WIP_CONSTANTS.DYN_RCV_SN))
273: AND transaction_header_id = p_txnHeaderID
274: AND completion_transaction_id = flow_rec.completion_transaction_id
275: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
268: AND mti.inventory_item_id = msi.inventory_item_id
269: AND (msi.lot_control_code = WIP_CONSTANTS.LOT
270: OR
271: msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
272: WIP_CONSTANTS.DYN_RCV_SN))
273: AND transaction_header_id = p_txnHeaderID
274: AND completion_transaction_id = flow_rec.completion_transaction_id
275: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
276: WIP_CONSTANTS.RETCOMP_ACTION,
271: msi.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
272: WIP_CONSTANTS.DYN_RCV_SN))
273: AND transaction_header_id = p_txnHeaderID
274: AND completion_transaction_id = flow_rec.completion_transaction_id
275: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
276: WIP_CONSTANTS.RETCOMP_ACTION,
277: WIP_CONSTANTS.ISSNEGC_ACTION,
278: WIP_CONSTANTS.RETNEGC_ACTION);
279:
272: WIP_CONSTANTS.DYN_RCV_SN))
273: AND transaction_header_id = p_txnHeaderID
274: AND completion_transaction_id = flow_rec.completion_transaction_id
275: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
276: WIP_CONSTANTS.RETCOMP_ACTION,
277: WIP_CONSTANTS.ISSNEGC_ACTION,
278: WIP_CONSTANTS.RETNEGC_ACTION);
279:
280: if (l_lot_entry_type = 0 ) then -- Check only once
273: AND transaction_header_id = p_txnHeaderID
274: AND completion_transaction_id = flow_rec.completion_transaction_id
275: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
276: WIP_CONSTANTS.RETCOMP_ACTION,
277: WIP_CONSTANTS.ISSNEGC_ACTION,
278: WIP_CONSTANTS.RETNEGC_ACTION);
279:
280: if (l_lot_entry_type = 0 ) then -- Check only once
281: SELECT backflush_lot_entry_type
274: AND completion_transaction_id = flow_rec.completion_transaction_id
275: AND transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
276: WIP_CONSTANTS.RETCOMP_ACTION,
277: WIP_CONSTANTS.ISSNEGC_ACTION,
278: WIP_CONSTANTS.RETNEGC_ACTION);
279:
280: if (l_lot_entry_type = 0 ) then -- Check only once
281: SELECT backflush_lot_entry_type
282: INTO l_lot_entry_type
284: WHERE organization_id = flow_rec.organization_id ;
285: end if ;
286:
287: IF ((l_bf_count <> 0) and (l_lot_ser_count <> 0) and
288: (l_lot_entry_type <> WIP_CONSTANTS.MAN_ENTRY)) THEN
289: -- derive lot for Components
290: wip_autoLotProc_priv.deriveLotsFromMTI
291: (p_orgID => flow_rec.organization_id,
292: p_wipEntityID => flow_rec.transaction_source_id,
325: end if;
326: end if;
327: exception
328: when l_validationException then
329: if (l_logLevel <= wip_constants.full_logging) then
330: wip_logger.log(p_msg => 'Error defaulting/validating interface ' ||
331: to_char(flow_rec.transaction_interface_id) || ':' || l_errMsg,
332: x_returnStatus => l_returnStatus);
333: end if;
334: -- skip this one, validate next record. pl/sql doesn't have continue;
335: end;
336: end loop;
337:
338: if (l_logLevel <= wip_constants.trace_logging) then
339: wip_logger.exitPoint(p_procName => 'wip_flowutil_priv.processFlowInterfaceRecords',
340: p_procReturnStatus => fnd_api.g_ret_sts_success,
341: p_msg => 'Finished processFlowInterfaceRecords',
342: x_returnStatus => l_returnStatus); --discard logging return status
397: p_rout_rev_date => p_routRevDate,
398: p_item_id => p_itemID,
399: p_start_date => p_txnDate,
400: p_org_id => p_orgID) = 0 )
401: or (p_txnActionID <> WIP_CONSTANTS.SCRASSY_ACTION and
402: wip_flow_derive.completion_sub(
403: p_comp_sub => p_cplSubinv,
404: p_item_id => p_itemID,
405: p_org_id => p_orgID,
403: p_comp_sub => p_cplSubinv,
404: p_item_id => p_itemID,
405: p_org_id => p_orgID,
406: p_alt_rtg_des => p_altRtgDes) = 0 )
407: or (p_txnActionID <> WIP_CONSTANTS.SCRASSY_ACTION and
408: wip_flow_derive.completion_locator_id(
409: p_comp_loc => p_cplLocID,
410: p_item_id => p_itemID,
411: p_org_id => p_orgID,
707: l_compTbl system.wip_component_tbl_t;
708: l_count number;
709: l_childTxnTypeID number;
710: l_childTxnActionID number;
711: l_insertPhantom number := WIP_CONSTANTS.NO;
712:
713: l_insert varchar2(1) ; /*BUG 6134576*/
714: l_service_item_flag varchar2(1) ; /*BUG 6134576*/
715:
738: l_wfs_info wfs_info_cursor%ROWTYPE := null;
739: l_wip_entity_id number := null; -- use it to retrieve wip_flow_schedules info
740: begin
741:
742: if (l_logLevel <= wip_constants.trace_logging) then
743: l_params(1).paramName := 'p_txnHeaderID';
744: l_params(1).paramValue := p_txnHeaderID;
745: l_params(2).paramName := 'p_parentID';
746: l_params(2).paramValue := p_parentID;
853:
854: end if;
855:
856: -- bug 5605598: filter out lot-based components appropriately
857: if (nvl(l_compTbl(l_count).basis_type,WIP_CONSTANTS.ITEM_BASED_MTL) = WIP_CONSTANTS.LOT_BASED_MTL) then
858: if (
859: not(
860: (l_wfs_info.quantity_completed = 0 and l_wfs_info.quantity_scrapped <= 0 and p_qty > 0) or
861: (l_wfs_info.quantity_completed + l_wfs_info.quantity_scrapped > 0 and
868: end if;
869:
870: -- we don't insert phantom comp(for phantom routing resource charging) into mti if
871: -- the bom parameter is set to NO.
872: if (( l_insertPhantom = WIP_CONSTANTS.YES or
873: nvl(l_compTbl(l_count).wip_supply_type, 1) <> 6) and l_insert = 'Y' ) then /*Bug 6134576*/
874: -- derive the txn type and action id
875: l_childTxnActionID := l_compTbl(l_count).transaction_action_id;
876: l_childTxnTypeID := getTypeFromAction(l_childTxnActionID);
948: 1, -- process flag
949: p_lockFlag,
950: 1, -- validation required
951: p_txnDate,
952: ROUND(l_compTbl(l_count).primary_quantity * -1, WIP_CONSTANTS.INV_MAX_PRECISION),
953: l_compTbl(l_count).primary_uom_code,
954: ROUND(l_compTbl(l_count).primary_quantity * -1, WIP_CONSTANTS.INV_MAX_PRECISION),
955: 5,
956: 'Y',
950: 1, -- validation required
951: p_txnDate,
952: ROUND(l_compTbl(l_count).primary_quantity * -1, WIP_CONSTANTS.INV_MAX_PRECISION),
953: l_compTbl(l_count).primary_uom_code,
954: ROUND(l_compTbl(l_count).primary_quantity * -1, WIP_CONSTANTS.INV_MAX_PRECISION),
955: 5,
956: 'Y',
957: l_childTxnActionID,
958: l_childTxnTypeID,
956: 'Y',
957: l_childTxnActionID,
958: l_childTxnTypeID,
959: l_compTbl(l_count).wip_supply_type,
960: wip_constants.flow,
961: l_compTbl(l_count).supply_subinventory,
962: l_compTbl(l_count).supply_locator_id,
963: p_acctPeriodID,
964: p_cplTxnID,
968: l_compTbl(l_count).task_id,
969: p_projectID,
970: p_taskID);
971:
972: if (l_logLevel <= wip_constants.full_logging) then
973: wip_logger.log(p_msg => 'Insert item ' || l_compTbl(l_count).inventory_item_id ||
974: ' under op ' || l_compTbl(l_count).operation_seq_num,
975: x_returnStatus => l_returnStatus);
976: end if;
979: <
980: l_count := l_compTbl.next(l_count);
981: end loop;
982:
983: if (l_logLevel <= wip_constants.trace_logging) then
984: wip_logger.exitPoint(p_procName => 'wip_flowutil_priv.explodeRequirementsToMTI',
985: p_procReturnStatus => x_returnStatus,
986: p_msg => 'Explode BOM to MTI successfully for interface ' ||
987: to_char(p_parentID) || ' successfully!',
993:
994: wip_utilities.get_message_stack(p_msg => l_errMsg,
995: p_delete_stack => fnd_api.g_false);
996:
997: if (l_logLevel <= wip_constants.trace_logging) then
998: wip_logger.exitPoint(p_procName => 'wip_flowutil_priv.explodeRequirementsToMTI',
999: p_procReturnStatus => x_returnStatus,
1000: p_msg => 'explosion error: ' || l_errMsg || ' for exploding interface '
1001: || to_char(p_parentID),
1009: substrb(fnd_message.get, 1, 240));
1010:
1011: when others then
1012: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1013: if (l_logLevel <= wip_constants.trace_logging) then
1014:
1015: fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_flowutil_priv',
1016: p_procedure_name => 'explodeRequirementsToMTI',
1017: p_error_text => SQLERRM);
1222: source_project_id = l_srcProjID,
1223: source_task_id = l_srcTaskID,
1224: transaction_source_id = l_wipEntityID,
1225: wip_entity_type = decode(l_wipEntityID, null, null, 4),
1226: transaction_date = to_date(to_char(l_txnDate, WIP_CONSTANTS.DT_NOSEC_FMT), WIP_CONSTANTS.DT_NOSEC_FMT)
1227: where parent_id = p_parentID
1228: and process_flag = 1
1229: and substitution_type_id is null;
1230:
1365: and transaction_source_id is null
1366: and flow_schedule = 'Y'
1367: and transaction_action_id in (31, 32, 30) -- CFM Scrap
1368: and scheduled_flag = 2
1369: and process_flag = wip_constants.mti_inventory;
1370:
1371: cursor c_MMTTflowCompletion return flow_rec_t is
1372: select transaction_action_id, -- CFM Scrap
1373: last_update_date,
1467: begin
1468:
1469: x_returnStatus := fnd_api.g_ret_sts_success;
1470:
1471: if (l_logLevel <= wip_constants.trace_logging) then
1472: l_params(1).paramName := 'p_txnInterfaceID';
1473: l_params(1).paramValue := p_txnInterfaceID;
1474: wip_logger.entryPoint(p_procName => 'wip_flowUtil_priv.createFlowSchedule',
1475: p_params => l_params,
1478: raise fnd_api.g_exc_unexpected_error;
1479: end if;
1480: end if;
1481:
1482: if(l_logLevel <= wip_constants.full_logging) then
1483: declare
1484: l_count NUMBER;
1485: begin
1486: if(p_txnInterfaceID is not null) then
1525: close c_MMTTflowCompletion;
1526: end if;
1527:
1528: if(l_flowRec.organization_id is null) then --org id column is not null in both MTI and MMTT
1529: if(l_logLevel <= wip_constants.trace_logging) then
1530: wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.createFlowSchedule',
1531: p_procReturnStatus => x_returnStatus,
1532: p_msg => 'Flow schedule creation not necessary.',
1533: x_returnStatus => l_returnStatus); --discard logging return status
1713: ) returning project_id, task_id into l_prjID, l_tskID;
1714:
1715: x_wipEntityID := l_wipEntityID;
1716:
1717: if (l_logLevel <= wip_constants.full_logging) then
1718: wip_logger.log(p_msg => 'wip entity id: ' || x_wipEntityID,
1719: x_returnStatus => l_returnStatus);
1720: wip_logger.log(p_msg => 'src prjID: ' || l_flowRec.source_project_id,
1721: x_returnStatus => l_returnStatus);
1743: wip_entity_type = l_flowRec.wip_entity_type
1744: where transaction_header_id = l_flowRec.transaction_header_id
1745: and completion_transaction_id = l_flowRec.completion_transaction_id;
1746:
1747: if (l_logLevel <= wip_constants.full_logging) then
1748: wip_logger.log(p_msg => SQL%ROWCOUNT || 'MTI rows updated!',
1749: x_returnStatus => l_returnStatus);
1750: end if;
1751:
1763: wip_entity_type = l_flowRec.wip_entity_type
1764: where transaction_temp_id = p_txnTmpID;
1765: end if;
1766:
1767: if (l_logLevel <= wip_constants.full_logging) then
1768: wip_logger.log(p_msg => SQL%ROWCOUNT || 'MMTT rows updated!',
1769: x_returnStatus => l_returnStatus);
1770: end if;
1771:
1768: wip_logger.log(p_msg => SQL%ROWCOUNT || 'MMTT rows updated!',
1769: x_returnStatus => l_returnStatus);
1770: end if;
1771:
1772: if (l_logLevel <= wip_constants.trace_logging) then
1773: wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.createFlowSchedule',
1774: p_procReturnStatus => x_returnStatus,
1775: p_msg => 'Flow schedules created successfully!',
1776: x_returnStatus => l_returnStatus); --discard logging return status
1777: end if;
1778: exception
1779: when others then
1780: x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1781: if (l_logLevel <= wip_constants.trace_logging) then
1782: wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.createFlowSchedule',
1783: p_procReturnStatus => x_returnStatus,
1784: p_msg => 'unexpected error: ' || SQLERRM,
1785: x_returnStatus => l_returnStatus); --discard logging return status
1810: l_returnStatus varchar2(1);
1811: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1812: begin
1813:
1814: if (l_logLevel <= wip_constants.trace_logging) then
1815: l_params(1).paramName := 'p_txnTempID';
1816: l_params(1).paramValue := p_txnTempID;
1817: wip_logger.entryPoint(p_procName => 'wip_flowUtil_priv.updateFlowSchedule',
1818: p_params => l_params,
1891: 2, 2),
1892: status)
1893: where wip_entity_id = l_wipEntityID;
1894:
1895: if (l_logLevel <= wip_constants.trace_logging) then
1896: wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.updateFlowSchedule',
1897: p_procReturnStatus => x_returnStatus,
1898: p_msg => 'Flow schedules updated successfully!',
1899: x_returnStatus => l_returnStatus); --discard logging return status
1900: end if;
1901: exception
1902: when others then
1903: x_returnStatus := fnd_api.g_ret_sts_error;
1904: if (l_logLevel <= wip_constants.trace_logging) then
1905: wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.updateFlowSchedule',
1906: p_procReturnStatus => x_returnStatus,
1907: p_msg => 'unexpected error: ' || SQLERRM,
1908: x_returnStatus => l_returnStatus); --discard logging return status
2009: if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
2010: return;
2011: end if;
2012:
2013: if (l_logLevel <= wip_constants.full_logging) then
2014: wip_logger.log(p_msg => x_compTbl.count || ' components exploded!',
2015: x_returnStatus => l_returnStatus);
2016: end if;
2017:
2044:
2045: -- ER 4369064: For work orderless txn, we have to include yield factor
2046: -- if Include Yield in WIP Parameters is checked.
2047: -- Rounding this to 5 decimals since this will go into MTI
2048: /* Bug fix 4728358: Number of rounding decimal places should be obtained from WIP_CONSTANTS.
2049: In particular we want 6 places instead of 5. */
2050: if (p_txnFlag AND l_includeYield = 1) then
2051: x_compTbl(l_count).primary_quantity := round(x_compTbl(l_count).primary_quantity
2052: / x_compTbl(l_count).component_yield_factor, WIP_CONSTANTS.INV_MAX_PRECISION);
2048: /* Bug fix 4728358: Number of rounding decimal places should be obtained from WIP_CONSTANTS.
2049: In particular we want 6 places instead of 5. */
2050: if (p_txnFlag AND l_includeYield = 1) then
2051: x_compTbl(l_count).primary_quantity := round(x_compTbl(l_count).primary_quantity
2052: / x_compTbl(l_count).component_yield_factor, WIP_CONSTANTS.INV_MAX_PRECISION);
2053: /* End of bug fix 4728358 */
2054: end if;
2055:
2056: if (round(abs(x_compTbl(l_count).primary_quantity),
2053: /* End of bug fix 4728358 */
2054: end if;
2055:
2056: if (round(abs(x_compTbl(l_count).primary_quantity),
2057: WIP_CONSTANTS.INV_MAX_PRECISION) = 0) then
2058: if(p_txnFlag) then
2059: if (l_logLevel <= wip_constants.full_logging) then
2060: wip_logger.log(p_msg => 'Qty too small, component ' ||
2061: x_compTbl(l_count).inventory_item_id || ' stripped!',
2055:
2056: if (round(abs(x_compTbl(l_count).primary_quantity),
2057: WIP_CONSTANTS.INV_MAX_PRECISION) = 0) then
2058: if(p_txnFlag) then
2059: if (l_logLevel <= wip_constants.full_logging) then
2060: wip_logger.log(p_msg => 'Qty too small, component ' ||
2061: x_compTbl(l_count).inventory_item_id || ' stripped!',
2062: x_returnStatus => l_returnStatus);
2063: end if;
2077: l_checkPass := true;
2078: elsif ( l_cfmRouting <> 1 ) then
2079: if ( x_compTbl(l_count).operation_seq_num > p_toOpSeqNum ) then
2080: -- delete the comp that has op seq greater than to op seq num
2081: if (l_logLevel <= wip_constants.full_logging) then
2082: wip_logger.log(p_msg => 'Op ' || x_compTbl(l_count).operation_seq_num ||
2083: ' after terminal op, item ' ||
2084: x_compTbl(l_count).inventory_item_id || ' stripped!',
2085: x_returnStatus => l_returnStatus);
2107: p_toOpSeqNum,
2108: l_lineOpTbl) ) then
2109: l_checkPass := true;
2110: else
2111: if (l_logLevel <= wip_constants.full_logging) then
2112: wip_logger.log(p_msg => 'Event ' || x_compTbl(l_count).operation_seq_num ||
2113: ' belongs to line op that after terminal op, item ' ||
2114: x_compTbl(l_count).inventory_item_id || ' stripped!',
2115: x_returnStatus => l_returnStatus);
2126: -- ER 4369064: This code is called for discrete jobs too. Bulk and Supplier
2127: -- type components should be exploded for discrete jobs.
2128: if ( l_checkPass and p_txnFlag and
2129: nvl(x_compTbl(l_count).wip_supply_type, 1) not in (1, 2, 3, 6) ) then
2130: if (l_logLevel <= wip_constants.full_logging) then
2131: wip_logger.log(p_msg => 'Nonrelated supply type item ' ||
2132: x_compTbl(l_count).inventory_item_id ||
2133: ' at op ' || x_compTbl(l_count).operation_seq_num ||
2134: ' stripped!',
2161: x_returnStatus := fnd_api.g_ret_sts_error;
2162: fnd_message.set_name('WIP', 'WIP_COMP_NOT_TRANSACTABLE');
2163: fnd_message.set_token('ENTITY1', x_compTbl(l_count).item_name);
2164: fnd_msg_pub.add;
2165: if (l_logLevel <= wip_constants.full_logging) then
2166: wip_logger.log(p_msg => 'Item ' || x_compTbl(l_count).inventory_item_id ||
2167: ' not transactable, failed explosion!',
2168: x_returnStatus => l_returnStatus);
2169: end if;
2197: if(nvl(p_defaultPushSubinv, 'N') = 'Y') then --This is wol/flow txn
2198: x_compTbl(l_count).supply_subinventory := l_wpSubinv;
2199: l_locatorID := l_wpLocatorID;
2200: else -- This is discrete txn
2201: if(x_compTbl(l_count).wip_supply_type in(wip_constants.op_pull, wip_constants.assy_pull)) then
2202: x_compTbl(l_count).supply_subinventory := l_wpSubinv;
2203: l_locatorID := l_wpLocatorID;
2204: end if;
2205: end if;
2278: l_returnStatus varchar2(1);
2279: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2280: begin
2281:
2282: if (l_logLevel <= wip_constants.trace_logging) then
2283: l_params(1).paramName := 'p_txnTempID';
2284: l_params(1).paramValue := p_txnTempID;
2285: l_params(2).paramName := 'p_assyID';
2286: l_params(2).paramValue := p_assyID;
2343: l_count := l_compTbl.first;
2344: while ( l_count is not null ) loop
2345: -- we don't insert phantom comp(for phantom routing resource charging) into mmtt if
2346: -- the bom parameter is set to NO.
2347: if ( l_insertPhantom = WIP_CONSTANTS.YES or
2348: nvl(l_compTbl(l_count).wip_supply_type, 1) <> 6 ) then
2349: -- derive the txn action and type id
2350: l_childTxnActionID := l_compTbl(l_count).transaction_action_id;
2351: l_childTxnTypeID := getTypeFromAction(l_childTxnActionID);
2430: 'Y'
2431: from mtl_material_transactions_temp mmtt
2432: where mmtt.transaction_temp_id = p_txnTempID;
2433:
2434: if (l_logLevel <= wip_constants.full_logging) then
2435: wip_logger.log(p_msg => 'Insert item ' || l_compTbl(l_count).inventory_item_id ||
2436: ' under op ' || l_compTbl(l_count).operation_seq_num,
2437: x_returnStatus => l_returnStatus);
2438: end if;
2441: l_count := l_compTbl.next(l_count);
2442: end loop;
2443:
2444: l_compTbl.delete;
2445: if (l_logLevel <= wip_constants.trace_logging) then
2446: wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.explodeRequirementsToMMTT',
2447: p_procReturnStatus => x_returnStatus,
2448: p_msg => 'Exploded BOM to MMTT successfully!',
2449: x_returnStatus => l_returnStatus); --discard logging return status
2450: end if;
2451: exception
2452: when others then
2453: x_returnStatus := fnd_api.g_ret_sts_error;
2454: if (l_logLevel <= wip_constants.trace_logging) then
2455: wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.explodeRequirementsToMMTT',
2456: p_procReturnStatus => x_returnStatus,
2457: p_msg => 'unexpected error: ' || SQLERRM,
2458: x_returnStatus => l_returnStatus); --discard logging return status
2600: * transaction type id
2601: */
2602: function getTypeFromAction(p_txnActionID in number) return number is
2603: begin
2604: if ( p_txnActionID = WIP_CONSTANTS.ISSCOMP_ACTION ) then
2605: return WIP_CONSTANTS.ISSCOMP_TYPE;
2606: end if;
2607:
2608: if ( p_txnActionID = WIP_CONSTANTS.ISSNEGC_ACTION ) then
2601: */
2602: function getTypeFromAction(p_txnActionID in number) return number is
2603: begin
2604: if ( p_txnActionID = WIP_CONSTANTS.ISSCOMP_ACTION ) then
2605: return WIP_CONSTANTS.ISSCOMP_TYPE;
2606: end if;
2607:
2608: if ( p_txnActionID = WIP_CONSTANTS.ISSNEGC_ACTION ) then
2609: return WIP_CONSTANTS.ISSNEGC_TYPE;
2604: if ( p_txnActionID = WIP_CONSTANTS.ISSCOMP_ACTION ) then
2605: return WIP_CONSTANTS.ISSCOMP_TYPE;
2606: end if;
2607:
2608: if ( p_txnActionID = WIP_CONSTANTS.ISSNEGC_ACTION ) then
2609: return WIP_CONSTANTS.ISSNEGC_TYPE;
2610: end if;
2611:
2612: if ( p_txnActionID = WIP_CONSTANTS.RETCOMP_ACTION ) then
2605: return WIP_CONSTANTS.ISSCOMP_TYPE;
2606: end if;
2607:
2608: if ( p_txnActionID = WIP_CONSTANTS.ISSNEGC_ACTION ) then
2609: return WIP_CONSTANTS.ISSNEGC_TYPE;
2610: end if;
2611:
2612: if ( p_txnActionID = WIP_CONSTANTS.RETCOMP_ACTION ) then
2613: return WIP_CONSTANTS.RETCOMP_TYPE;
2608: if ( p_txnActionID = WIP_CONSTANTS.ISSNEGC_ACTION ) then
2609: return WIP_CONSTANTS.ISSNEGC_TYPE;
2610: end if;
2611:
2612: if ( p_txnActionID = WIP_CONSTANTS.RETCOMP_ACTION ) then
2613: return WIP_CONSTANTS.RETCOMP_TYPE;
2614: end if;
2615:
2616: if ( p_txnActionID = WIP_CONSTANTS.RETNEGC_ACTION ) then
2609: return WIP_CONSTANTS.ISSNEGC_TYPE;
2610: end if;
2611:
2612: if ( p_txnActionID = WIP_CONSTANTS.RETCOMP_ACTION ) then
2613: return WIP_CONSTANTS.RETCOMP_TYPE;
2614: end if;
2615:
2616: if ( p_txnActionID = WIP_CONSTANTS.RETNEGC_ACTION ) then
2617: return WIP_CONSTANTS.RETNEGC_TYPE;
2612: if ( p_txnActionID = WIP_CONSTANTS.RETCOMP_ACTION ) then
2613: return WIP_CONSTANTS.RETCOMP_TYPE;
2614: end if;
2615:
2616: if ( p_txnActionID = WIP_CONSTANTS.RETNEGC_ACTION ) then
2617: return WIP_CONSTANTS.RETNEGC_TYPE;
2618: end if;
2619:
2620: return null;
2613: return WIP_CONSTANTS.RETCOMP_TYPE;
2614: end if;
2615:
2616: if ( p_txnActionID = WIP_CONSTANTS.RETNEGC_ACTION ) then
2617: return WIP_CONSTANTS.RETNEGC_TYPE;
2618: end if;
2619:
2620: return null;
2621: end getTypeFromAction;
2628: p_signOfPer in number,
2629: x_childTxnTypeID out nocopy number,
2630: x_childTxnActionID out nocopy number) is
2631: begin
2632: if ( p_parentTxnTypeID in (WIP_CONSTANTS.CPLASSY_TYPE,
2633: WIP_CONSTANTS.SCRASSY_TYPE) ) then
2634: if ( p_signOfPer > 0 ) then
2635: x_childTxnTypeID := WIP_CONSTANTS.ISSCOMP_TYPE;
2636: x_childTxnActionID := WIP_CONSTANTS.ISSCOMP_ACTION;
2629: x_childTxnTypeID out nocopy number,
2630: x_childTxnActionID out nocopy number) is
2631: begin
2632: if ( p_parentTxnTypeID in (WIP_CONSTANTS.CPLASSY_TYPE,
2633: WIP_CONSTANTS.SCRASSY_TYPE) ) then
2634: if ( p_signOfPer > 0 ) then
2635: x_childTxnTypeID := WIP_CONSTANTS.ISSCOMP_TYPE;
2636: x_childTxnActionID := WIP_CONSTANTS.ISSCOMP_ACTION;
2637: else
2631: begin
2632: if ( p_parentTxnTypeID in (WIP_CONSTANTS.CPLASSY_TYPE,
2633: WIP_CONSTANTS.SCRASSY_TYPE) ) then
2634: if ( p_signOfPer > 0 ) then
2635: x_childTxnTypeID := WIP_CONSTANTS.ISSCOMP_TYPE;
2636: x_childTxnActionID := WIP_CONSTANTS.ISSCOMP_ACTION;
2637: else
2638: x_childTxnTypeID := WIP_CONSTANTS.ISSNEGC_TYPE;
2639: x_childTxnActionID := WIP_CONSTANTS.ISSNEGC_ACTION;
2632: if ( p_parentTxnTypeID in (WIP_CONSTANTS.CPLASSY_TYPE,
2633: WIP_CONSTANTS.SCRASSY_TYPE) ) then
2634: if ( p_signOfPer > 0 ) then
2635: x_childTxnTypeID := WIP_CONSTANTS.ISSCOMP_TYPE;
2636: x_childTxnActionID := WIP_CONSTANTS.ISSCOMP_ACTION;
2637: else
2638: x_childTxnTypeID := WIP_CONSTANTS.ISSNEGC_TYPE;
2639: x_childTxnActionID := WIP_CONSTANTS.ISSNEGC_ACTION;
2640: end if;
2634: if ( p_signOfPer > 0 ) then
2635: x_childTxnTypeID := WIP_CONSTANTS.ISSCOMP_TYPE;
2636: x_childTxnActionID := WIP_CONSTANTS.ISSCOMP_ACTION;
2637: else
2638: x_childTxnTypeID := WIP_CONSTANTS.ISSNEGC_TYPE;
2639: x_childTxnActionID := WIP_CONSTANTS.ISSNEGC_ACTION;
2640: end if;
2641: else -- return or return from scrap
2642: if ( p_signOfPer > 0 ) then
2635: x_childTxnTypeID := WIP_CONSTANTS.ISSCOMP_TYPE;
2636: x_childTxnActionID := WIP_CONSTANTS.ISSCOMP_ACTION;
2637: else
2638: x_childTxnTypeID := WIP_CONSTANTS.ISSNEGC_TYPE;
2639: x_childTxnActionID := WIP_CONSTANTS.ISSNEGC_ACTION;
2640: end if;
2641: else -- return or return from scrap
2642: if ( p_signOfPer > 0 ) then
2643: x_childTxnTypeID := WIP_CONSTANTS.RETCOMP_TYPE;
2639: x_childTxnActionID := WIP_CONSTANTS.ISSNEGC_ACTION;
2640: end if;
2641: else -- return or return from scrap
2642: if ( p_signOfPer > 0 ) then
2643: x_childTxnTypeID := WIP_CONSTANTS.RETCOMP_TYPE;
2644: x_childTxnActionID := WIP_CONSTANTS.RETCOMP_ACTION;
2645: else
2646: x_childTxnTypeID := WIP_CONSTANTS.RETNEGC_TYPE;
2647: x_childTxnActionID := WIP_CONSTANTS.RETNEGC_ACTION;
2640: end if;
2641: else -- return or return from scrap
2642: if ( p_signOfPer > 0 ) then
2643: x_childTxnTypeID := WIP_CONSTANTS.RETCOMP_TYPE;
2644: x_childTxnActionID := WIP_CONSTANTS.RETCOMP_ACTION;
2645: else
2646: x_childTxnTypeID := WIP_CONSTANTS.RETNEGC_TYPE;
2647: x_childTxnActionID := WIP_CONSTANTS.RETNEGC_ACTION;
2648: end if;
2642: if ( p_signOfPer > 0 ) then
2643: x_childTxnTypeID := WIP_CONSTANTS.RETCOMP_TYPE;
2644: x_childTxnActionID := WIP_CONSTANTS.RETCOMP_ACTION;
2645: else
2646: x_childTxnTypeID := WIP_CONSTANTS.RETNEGC_TYPE;
2647: x_childTxnActionID := WIP_CONSTANTS.RETNEGC_ACTION;
2648: end if;
2649: end if;
2650: end getChildTxn;
2643: x_childTxnTypeID := WIP_CONSTANTS.RETCOMP_TYPE;
2644: x_childTxnActionID := WIP_CONSTANTS.RETCOMP_ACTION;
2645: else
2646: x_childTxnTypeID := WIP_CONSTANTS.RETNEGC_TYPE;
2647: x_childTxnActionID := WIP_CONSTANTS.RETNEGC_ACTION;
2648: end if;
2649: end if;
2650: end getChildTxn;
2651:
2666: and transaction_source_type_id = 5
2667: and flow_schedule = 'Y'
2668: and process_flag = 'Y'
2669: and locator_id is not null
2670: and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
2671: WIP_CONSTANTS.RETCOMP_ACTION,
2672: WIP_CONSTANTS.ISSNEGC_ACTION,
2673: WIP_CONSTANTS.RETNEGC_ACTION)
2674: order by operation_seq_num;
2667: and flow_schedule = 'Y'
2668: and process_flag = 'Y'
2669: and locator_id is not null
2670: and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
2671: WIP_CONSTANTS.RETCOMP_ACTION,
2672: WIP_CONSTANTS.ISSNEGC_ACTION,
2673: WIP_CONSTANTS.RETNEGC_ACTION)
2674: order by operation_seq_num;
2675:
2668: and process_flag = 'Y'
2669: and locator_id is not null
2670: and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
2671: WIP_CONSTANTS.RETCOMP_ACTION,
2672: WIP_CONSTANTS.ISSNEGC_ACTION,
2673: WIP_CONSTANTS.RETNEGC_ACTION)
2674: order by operation_seq_num;
2675:
2676: l_params wip_logger.param_tbl_t;
2669: and locator_id is not null
2670: and transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
2671: WIP_CONSTANTS.RETCOMP_ACTION,
2672: WIP_CONSTANTS.ISSNEGC_ACTION,
2673: WIP_CONSTANTS.RETNEGC_ACTION)
2674: order by operation_seq_num;
2675:
2676: l_params wip_logger.param_tbl_t;
2677: l_returnStatus varchar2(1);
2685: l_projRefEnabled number;
2686: l_orgLocControl number := 0;
2687: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2688: begin
2689: if (l_logLevel <= wip_constants.trace_logging) then
2690: l_params(1).paramName := 'p_parentID';
2691: l_params(1).paramValue := p_parentID;
2692: wip_logger.entryPoint(p_procName => 'wip_flowUtil_priv.generateCompLocator',
2693: p_params => l_params,
2717: from mtl_parameters
2718: where organization_id = l_orgID;
2719:
2720: if ( (l_srcProjectID is null) or (l_projRefEnabled <> 1) ) then
2721: if (l_logLevel <= wip_constants.trace_logging) then
2722: wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.generateCompLocator',
2723: p_procReturnStatus => x_returnStatus,
2724: p_msg => 'Source project id is null or the org parameter ' ||
2725: 'does not has project reference enabled',
2739: p_item_id => comp_rec.inventory_item_id,
2740: p_org_loc_control => l_orgLocControl);
2741: if ( l_success = false ) then
2742: x_returnStatus := fnd_api.g_ret_sts_error;
2743: if (l_logLevel <= wip_constants.trace_logging) then
2744: wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.generateCompLocator',
2745: p_procReturnStatus => x_returnStatus,
2746: p_msg => 'Error in calling '||
2747: 'pjm_project_locator.get_component_projectsupply!',
2762: where rowid = comp_rec.rowid;
2763: end if;
2764: end loop;
2765:
2766: if (l_logLevel <= wip_constants.trace_logging) then
2767: wip_logger.exitPoint(p_procName => 'wip_flowUtil_priv.generateCompLocator',
2768: p_procReturnStatus => x_returnStatus,
2769: p_msg => 'Finished!',
2770: x_returnStatus => l_returnStatus); --discard logging return status