[Home] [Help]
103: -- WSMPCNST.UPDATE_ROUTING)
104: -- --SpUA begin
105: -- OR (txn.transaction_type_id in (WSMPCNST.SPLIT) AND rj.split_has_update_assy = 1))
106: -- --SpUA end
107: -- AND rj.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
108: -- AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
109: -- AND rj.process_status = WIP_CONSTANTS.RUNNING
110: -- ORDER BY TXN.TRANSACTION_DATE;
111:
105: -- OR (txn.transaction_type_id in (WSMPCNST.SPLIT) AND rj.split_has_update_assy = 1))
106: -- --SpUA end
107: -- AND rj.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
108: -- AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
109: -- AND rj.process_status = WIP_CONSTANTS.RUNNING
110: -- ORDER BY TXN.TRANSACTION_DATE;
111:
112: -- Changed the cursor to fix bug #3150692--
113: CURSOR C_NO_CLASS_CODE_RJ(p_header_id NUMBER) is
121: FROM wsm_resulting_jobs_interface rj,
122: wsm_split_merge_txn_interface txn
123: WHERE rj.header_id = p_header_id
124: AND txn.header_id = rj.header_id
125: AND rj.job_type = WIP_CONSTANTS.STANDARD
126: AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
127: AND rj.process_status = WIP_CONSTANTS.RUNNING
128: ORDER BY TXN.TRANSACTION_DATE;
129:
123: WHERE rj.header_id = p_header_id
124: AND txn.header_id = rj.header_id
125: AND rj.job_type = WIP_CONSTANTS.STANDARD
126: AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
127: AND rj.process_status = WIP_CONSTANTS.RUNNING
128: ORDER BY TXN.TRANSACTION_DATE;
129:
130: CURSOR C_VLDT_CLASS_CODE_RJ(p_header_id NUMBER) is
131: SELECT rj.class_code class_code,
137: WHERE txn.header_id = rj.header_id
138: AND rj.header_id = p_header_id -- WLTEnh Add
139: AND txn.transaction_type_id = WSMPCNST.BONUS
140: AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
141: AND rj.process_status = WIP_CONSTANTS.RUNNING
142: ORDER BY TXN.TRANSACTION_DATE;
143:
144: CURSOR C_NONSTD_NO_CLASS_CODE_RJ(p_header_id NUMBER) is
145: SELECT rj.header_id header_id,
152: -- WHERE rj.class_code is null -- Commented to fix bug #3150692--
153: -- AND rj.header_id = p_header_id -- Commented to fix bug #3150692--
154: WHERE rj.header_id = p_header_id
155: AND txn.header_id = rj.header_id
156: AND rj.job_type = WIP_CONSTANTS.NONSTANDARD
157: AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
158: AND rj.process_status = WIP_CONSTANTS.RUNNING
159: ORDER BY TXN.TRANSACTION_DATE;
160:
154: WHERE rj.header_id = p_header_id
155: AND txn.header_id = rj.header_id
156: AND rj.job_type = WIP_CONSTANTS.NONSTANDARD
157: AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
158: AND rj.process_status = WIP_CONSTANTS.RUNNING
159: ORDER BY TXN.TRANSACTION_DATE;
160:
161: CURSOR C_NET_QUANTITY(p_header_id NUMBER) is
162: SELECT distinct (rj.header_id) header_id, txn.organization_id org_id,
176: WSMPCNST.MERGE,
177: WSMPCNST.BONUS,
178: WSMPCNST.UPDATE_QUANTITY)
179: AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
180: AND rj.process_status = WIP_CONSTANTS.RUNNING;
181:
182:
183: CURSOR C_COPRODUCTS_SUPPLY(p_header_id NUMBER) is
184: SELECT distinct (rj.header_id) header_id,
193: (WSMPCNST.SPLIT,
194: WSMPCNST.MERGE,
195: WSMPCNST.BONUS)
196: AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
197: AND rj.process_status = WIP_CONSTANTS.RUNNING;
198:
199: /*BA#1732786*/
200: CURSOR C_NET_QUANTITY_MERGE(hdr_id wsm_starting_jobs_interface.header_id%TYPE) is
201: select (wdj.net_quantity - wdj.quantity_scrapped)
307: -- rj.split_has_update_assy = 1)))
308: -- --SpUA end
309: -- AND rj.rowid = c_no_class_code_rj_rec.rj_rowid --SpUA Add
310: -- AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
311: -- AND rj.process_status = WIP_CONSTANTS.RUNNING;
312:
313: -- Changed to fix bug #3150692 --
314: l_stmt_num := 3;
315: /* ST bug fix 3150692 review comments : Move the class code check to the IF condition */
321: -- AND rj.class_code IS NULL /* ST bug fix 3150692 Added check for class_code to prevent overwrite of user entered value for Std Bonus txn*/
322: /* ST bug fix 3150692 review comments : Commented the class code check */
323: AND rj.rowid = c_no_class_code_rj_rec.rj_rowid --SpUA Add
324: AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
325: AND rj.process_status = WIP_CONSTANTS.RUNNING;
326:
327:
328: IF (SQL%ROWCOUNT > 0) and l_debug = 'Y' THEN
329: FND_MESSAGE.SET_NAME('WSM', 'WSM_MODIFIED_FIELD');
375:
376: ST bug fix 3150692 review comments end */
377: AND wrji.rowid = c_no_class_code_rj_rec.rj_rowid --SpUA add
378: AND wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
379: AND wrji.process_status = WIP_CONSTANTS.RUNNING;
380:
381: FND_MESSAGE.SET_NAME('WSM', 'WSM_NULL_FIELD');
382: FND_MESSAGE.SET_TOKEN('FLD_NAME', 'class_code');
383: l_fnd_err_msg := FND_MESSAGE.GET;
458: where sj.header_id = rj.header_id
459: and sj.wip_entity_id = dis.wip_entity_id
460: and sj.representative_flag = 'Y')
461: /* where rj.class_code is null -- ST bug fix 3150692 Overwrite the class code in the resulting jobs with the parent jobs's value
462: and rj.process_status = WIP_CONSTANTS.RUNNING */ /* ST bug fix 3150692 : Commented the where condition */
463: where rj.process_status = WIP_CONSTANTS.RUNNING
464: and rj.internal_group_id = WSMPLOAD.G_GROUP_ID
465: and rj.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
466: and exists (
459: and sj.wip_entity_id = dis.wip_entity_id
460: and sj.representative_flag = 'Y')
461: /* where rj.class_code is null -- ST bug fix 3150692 Overwrite the class code in the resulting jobs with the parent jobs's value
462: and rj.process_status = WIP_CONSTANTS.RUNNING */ /* ST bug fix 3150692 : Commented the where condition */
463: where rj.process_status = WIP_CONSTANTS.RUNNING
464: and rj.internal_group_id = WSMPLOAD.G_GROUP_ID
465: and rj.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
466: and exists (
467: SELECT null
461: /* where rj.class_code is null -- ST bug fix 3150692 Overwrite the class code in the resulting jobs with the parent jobs's value
462: and rj.process_status = WIP_CONSTANTS.RUNNING */ /* ST bug fix 3150692 : Commented the where condition */
463: where rj.process_status = WIP_CONSTANTS.RUNNING
464: and rj.internal_group_id = WSMPLOAD.G_GROUP_ID
465: and rj.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
466: and exists (
467: SELECT null
468: FROM wsm_split_merge_txn_interface txn2
469: WHERE txn2.header_id = rj.header_id
505: SET wrji.error_message = l_fnd_generic_err_msg
506: WHERE wrji.class_code is null
507: AND wrji.header_id = C_NONSTD_NO_CLASS_CODE_RJ_rec.header_id
508: AND wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
509: AND wrji.process_status = WIP_CONSTANTS.RUNNING;
510:
511: IF (SQL%ROWCOUNT > 0) THEN
512: /* Added if stmt to fix bug #3722383 */
513:
545: AND wsji.header_id = wrji.header_id)
546: -- WHERE wrji.class_code is null -- Commented to fix bug #3150692
547: WHERE wrji.header_id = C_NONSTD_NO_CLASS_CODE_RJ_rec.header_id
548: AND wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
549: AND wrji.process_status = WIP_CONSTANTS.RUNNING;
550:
551: FND_MESSAGE.SET_NAME('WSM', 'WSM_MODIFIED_FIELD');
552: FND_MESSAGE.SET_TOKEN('FLD_NAME', 'class_code in Resulting Jobs');
553: l_fnd_err_msg := FND_MESSAGE.GET;
806: IF ( c_net_quantity_rec.transaction_type_id = WSMPCNST.BONUS) THEN
807: l_stmt_num := 18;
808: UPDATE wsm_resulting_jobs_interface wrji
809: /* ST : fix for bug 3766859 : added decode : in case of non-std bonus default to 0 */
810: SET wrji.net_quantity = decode ( c_net_quantity_rec.job_type, WIP_CONSTANTS.STANDARD,wrji.start_quantity
811: ,0)
812: WHERE wrji.header_id = c_net_quantity_rec.header_id
813: AND wrji.net_quantity is NULL ;
814:
899: (select sj.intraoperation_step
900: from wsm_starting_jobs_interface sj
901: where sj.header_id = rj.header_id
902: and sj.representative_flag = 'Y')
903: WHERE rj.process_status = WIP_CONSTANTS.RUNNING
904: AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
905: AND rj.header_id = p_header_id -- WLTEnh
906: AND rj.starting_intraoperation_step is null;
907:
919: | Default starting_intraoperation_step for Bonus to 'Queue' |
920: +----------------------------------------------------------*/
921: l_stmt_num := 21;
922: UPDATE wsm_resulting_jobs_interface wrji
923: SET wrji.starting_intraoperation_step = WIP_CONSTANTS.QUEUE
924: WHERE wrji.starting_intraoperation_step IS NULL
925: AND wrji.process_status = WIP_CONSTANTS.RUNNING
926: AND wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
927: AND wrji.header_id = p_header_id -- WLTEnh
921: l_stmt_num := 21;
922: UPDATE wsm_resulting_jobs_interface wrji
923: SET wrji.starting_intraoperation_step = WIP_CONSTANTS.QUEUE
924: WHERE wrji.starting_intraoperation_step IS NULL
925: AND wrji.process_status = WIP_CONSTANTS.RUNNING
926: AND wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
927: AND wrji.header_id = p_header_id -- WLTEnh
928: AND EXISTS(SELECT 1
929: FROM wsm_split_merge_txn_interface sji
928: AND EXISTS(SELECT 1
929: FROM wsm_split_merge_txn_interface sji
930: WHERE sji.header_id = wrji.header_id
931: AND sji.transaction_type_id = WSMPCNST.BONUS
932: AND sji.process_status = WIP_CONSTANTS.RUNNING);
933:
934: IF (SQL%ROWCOUNT > 0) and l_debug = 'Y' THEN
935: FND_MESSAGE.SET_NAME('WSM', 'WSM_MODIFIED_FIELD');
936: FND_MESSAGE.SET_TOKEN('FLD_NAME', 'starting_intraoperation_step in Resulting Jobs');
948:
949: l_stmt_num := 22;
950: UPDATE wsm_resulting_jobs_interface rj
951: SET rj.forward_op_option = 4
952: WHERE rj.process_status = WIP_CONSTANTS.RUNNING
953: AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
954: AND rj.forward_op_option <> 4
955: AND rj.header_id = p_header_id -- WLTEnh
956: AND rj.header_id IN (SELECT header_id
955: AND rj.header_id = p_header_id -- WLTEnh
956: AND rj.header_id IN (SELECT header_id
957: FROM wsm_split_merge_txn_interface
958: WHERE internal_group_id = WSMPLOAD.G_GROUP_ID
959: AND process_status = WIP_CONSTANTS.RUNNING
960: AND (transaction_type_id IN
961: --SpUA begin
962: ( --WSMPCNST.SPLIT,
963: WSMPCNST.BONUS)
1001: WHERE bor.common_routing_sequence_id = rj.common_routing_sequence_id
1002: AND bor.organization_id = rj.organization_id
1003: --VJ: Start Changes for NSLBJ--
1004: AND bor.assembly_item_id = decode(rj.job_type,
1005: WIP_CONSTANTS.STANDARD, rj.primary_item_id,
1006: WIP_CONSTANTS.NONSTANDARD, rj.routing_reference_id,
1007: rj.primary_item_id)
1008: AND msi.secondary_inventory_name = bor.completion_subinventory -- CZH.BUG2398718
1009: AND msi.organization_id = bor.organization_id -- CZH.BUG2398718
1002: AND bor.organization_id = rj.organization_id
1003: --VJ: Start Changes for NSLBJ--
1004: AND bor.assembly_item_id = decode(rj.job_type,
1005: WIP_CONSTANTS.STANDARD, rj.primary_item_id,
1006: WIP_CONSTANTS.NONSTANDARD, rj.routing_reference_id,
1007: rj.primary_item_id)
1008: AND msi.secondary_inventory_name = bor.completion_subinventory -- CZH.BUG2398718
1009: AND msi.organization_id = bor.organization_id -- CZH.BUG2398718
1010: )
1008: AND msi.secondary_inventory_name = bor.completion_subinventory -- CZH.BUG2398718
1009: AND msi.organization_id = bor.organization_id -- CZH.BUG2398718
1010: )
1011: --VJ: End Changes for NSLBJ--
1012: WHERE rj.process_status = WIP_CONSTANTS.RUNNING
1013: AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
1014: AND rj.rowid = l_wrji_rowid --SpUA Add
1015: AND rj.completion_subinventory is null
1016: AND rj.common_routing_sequence_id is not null -- CZH.BUG2398718
1017: AND rj.header_id IN (SELECT header_id
1018: FROM wsm_split_merge_txn_interface
1019: WHERE internal_group_id = WSMPLOAD.G_GROUP_ID
1020: and header_id = p_header_id -- WLTEnh
1021: AND process_status = WIP_CONSTANTS.RUNNING
1022: AND (transaction_type_id IN (WSMPCNST.UPDATE_ASSEMBLY,
1023: WSMPCNST.BONUS,
1024: WSMPCNST.UPDATE_ROUTING)
1025: --SpUA begin
1063: AND sj.internal_group_id = WSMPLOAD.G_GROUP_ID
1064: AND msi.secondary_inventory_name = wdj.completion_subinventory -- CZH.BUG2398718
1065: AND msi.organization_id = wdj.organization_id -- CZH.BUG2398718
1066: )
1067: WHERE rj.process_status = WIP_CONSTANTS.RUNNING
1068: AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
1069: AND rj.header_id = p_header_id -- WLTEnh
1070: AND rj.completion_subinventory is null
1071: AND rj.header_id IN (SELECT header_id
1071: AND rj.header_id IN (SELECT header_id
1072: FROM wsm_split_merge_txn_interface
1073: WHERE internal_group_id = WSMPLOAD.G_GROUP_ID
1074: and header_id = p_header_id -- WLTEnh
1075: AND process_status = WIP_CONSTANTS.RUNNING
1076: --SpUA begin
1077: AND (transaction_type_id IN ( --WSMPCNST.SPLIT,
1078: WSMPCNST.UPDATE_LOT_NAME,
1079: WSMPCNST.UPDATE_QUANTITY)
1115: AND sj.representative_flag = 'Y'
1116: AND msi.secondary_inventory_name = wdj.completion_subinventory -- CZH.BUG2398718
1117: AND msi.organization_id = wdj.organization_id -- CZH.BUG2398718
1118: )
1119: WHERE rj.process_status = WIP_CONSTANTS.RUNNING
1120: AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
1121: AND rj.completion_subinventory is null
1122: AND rj.header_id IN (SELECT header_id
1123: FROM wsm_split_merge_txn_interface
1122: AND rj.header_id IN (SELECT header_id
1123: FROM wsm_split_merge_txn_interface
1124: WHERE internal_group_id = WSMPLOAD.G_GROUP_ID
1125: and header_id = p_header_id -- WLTEnh
1126: AND process_status = WIP_CONSTANTS.RUNNING
1127: AND transaction_type_id IN ( WSMPCNST.MERGE ))
1128: RETURNING rj.completion_subinventory, -- WLTEnh
1129: rj.completion_locator_id
1130: INTO l_temp_csi,
1299: rj.scheduled_completion_date scheduled_completion_date,rj.start_quantity,
1300: rj.organization_id org_id,
1301: rj.primary_item_id item_id
1302: FROM wsm_resulting_jobs_interface rj
1303: WHERE rj.process_status = WIP_CONSTANTS.RUNNING
1304: AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
1305: AND rj.header_id = l_header_id
1306: AND (rj.scheduled_start_date is null
1307: or rj.scheduled_completion_date is null)
1319: , wrji.routing_revision_date
1320: , wrji.routing_revision
1321: /*EA WLTEnh */
1322: FROM wsm_resulting_jobs_interface wrji
1323: WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
1324: AND wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
1325: AND wrji.header_id = l_header_id
1326: AND wrji.wip_entity_name = l_we_name; --VJ: Added condition to fix bug #2315397--
1327:
1358: SELECT ROWIDTOCHAR(RJ.ROWID) "X_ROWID", RJ.ORGANIZATION_ID, RJ.HEADER_ID
1359: FROM WSM_RESULTING_JOBS_INTERFACE RJ
1360: WHERE RJ.HEADER_ID = p_header_id
1361: AND RJ.GROUP_ID = WSMPLOAD.G_GROUP_ID
1362: AND RJ.PROCESS_STATUS = WIP_CONSTANTS.RUNNING
1363: AND (RJ.BOM_REVISION IS NOT NULL OR RJ.BOM_REVISION_DATE IS NOT NULL)
1364: AND RJ.job_type = WIP_CONSTANTS.STANDARD; --VJ: Added for NSLBJ--
1365:
1366: --VJ: End Changes for NSLBJ--
1360: WHERE RJ.HEADER_ID = p_header_id
1361: AND RJ.GROUP_ID = WSMPLOAD.G_GROUP_ID
1362: AND RJ.PROCESS_STATUS = WIP_CONSTANTS.RUNNING
1363: AND (RJ.BOM_REVISION IS NOT NULL OR RJ.BOM_REVISION_DATE IS NOT NULL)
1364: AND RJ.job_type = WIP_CONSTANTS.STANDARD; --VJ: Added for NSLBJ--
1365:
1366: --VJ: End Changes for NSLBJ--
1367:
1368: -- Start : Comments to fix bug #3453139 --
1377: WSM_STARTING_JOBS_INTERFACE SJ
1378: WHERE TXN.INTERNAL_GROUP_ID = WSMPLOAD.G_GROUP_ID
1379: AND SJ.HEADER_ID = TXN.HEADER_ID
1380: AND TXN.header_id = WSMPLOAD.G_HEADER_ID -- XleSplits
1381: AND TXN.PROCESS_STATUS = WIP_CONSTANTS.RUNNING
1382: AND SJ.PROCESS_STATUS = WIP_CONSTANTS.RUNNING
1383: ORDER BY TXN.TRANSACTION_DATE;
1384:
1385: CURSOR C_RESULTING_JOB IS
1378: WHERE TXN.INTERNAL_GROUP_ID = WSMPLOAD.G_GROUP_ID
1379: AND SJ.HEADER_ID = TXN.HEADER_ID
1380: AND TXN.header_id = WSMPLOAD.G_HEADER_ID -- XleSplits
1381: AND TXN.PROCESS_STATUS = WIP_CONSTANTS.RUNNING
1382: AND SJ.PROCESS_STATUS = WIP_CONSTANTS.RUNNING
1383: ORDER BY TXN.TRANSACTION_DATE;
1384:
1385: CURSOR C_RESULTING_JOB IS
1386: SELECT RJ.WIP_ENTITY_NAME,
1392: WSM_RESULTING_JOBS_INTERFACE RJ
1393: WHERE TXN.INTERNAL_GROUP_ID = WSMPLOAD.G_GROUP_ID
1394: AND RJ.HEADER_ID = TXN.HEADER_ID
1395: AND TXN.header_id = WSMPLOAD.G_HEADER_ID -- XleSplits
1396: AND TXN.PROCESS_STATUS = WIP_CONSTANTS.RUNNING
1397: AND RJ.PROCESS_STATUS = WIP_CONSTANTS.RUNNING
1398: ORDER BY TXN.TRANSACTION_DATE;
1399: ********************************************/
1400: -- End : Comments to fix bug #3453139 --
1393: WHERE TXN.INTERNAL_GROUP_ID = WSMPLOAD.G_GROUP_ID
1394: AND RJ.HEADER_ID = TXN.HEADER_ID
1395: AND TXN.header_id = WSMPLOAD.G_HEADER_ID -- XleSplits
1396: AND TXN.PROCESS_STATUS = WIP_CONSTANTS.RUNNING
1397: AND RJ.PROCESS_STATUS = WIP_CONSTANTS.RUNNING
1398: ORDER BY TXN.TRANSACTION_DATE;
1399: ********************************************/
1400: -- End : Comments to fix bug #3453139 --
1401:
1406: , reason_id
1407: , organization_id
1408: , rowid /*EA WLTEnh */
1409: FROM wsm_split_merge_txn_interface
1410: WHERE process_status = WIP_CONSTANTS.RUNNING
1411: AND internal_group_id = WSMPLOAD.G_GROUP_ID
1412: AND header_id = WSMPLOAD.G_HEADER_ID -- XleSplits
1413: ORDER BY TRANSACTION_DATE; --VJ: Added for NSLBJ--
1414:
1427: , wsji.operation_seq_num
1428: /*EA WLTEnh*/
1429: FROM wsm_starting_jobs_interface wsji,
1430: wsm_split_merge_txn_interface wsmti --VJ: CodeRVW: Added for NSLBJ--
1431: WHERE wsji.process_status = WIP_CONSTANTS.RUNNING
1432: AND wsji.internal_group_id = WSMPLOAD.G_GROUP_ID
1433: AND wsmti.internal_group_id = WSMPLOAD.G_GROUP_ID --VJ: CodeRVW: Added for NSLBJ--
1434: AND wsji.header_id = wsmti.header_id --VJ: CodeRVW: Added for NSLBJ--
1435: AND wsmti.header_id = WSMPLOAD.G_HEADER_ID
1432: AND wsji.internal_group_id = WSMPLOAD.G_GROUP_ID
1433: AND wsmti.internal_group_id = WSMPLOAD.G_GROUP_ID --VJ: CodeRVW: Added for NSLBJ--
1434: AND wsji.header_id = wsmti.header_id --VJ: CodeRVW: Added for NSLBJ--
1435: AND wsmti.header_id = WSMPLOAD.G_HEADER_ID
1436: AND wsmti.process_status = WIP_CONSTANTS.RUNNING;
1437:
1438: CURSOR C_WRJI IS
1439: SELECT wsmti.header_id ,
1440: nvl(common_routing_sequence_id, -1) common_routing_sequence_id,
1465: , wrji.rowid wrji_rowid
1466: , nvl(wrji.split_has_update_assy, 0) --SpUA add
1467: FROM wsm_resulting_jobs_interface wrji,
1468: wsm_split_merge_txn_interface wsmti
1469: WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
1470: AND wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
1471: AND wsmti.internal_group_id = WSMPLOAD.G_GROUP_ID
1472: AND wrji.header_id=wsmti.header_id
1473: AND wsmti.header_id = WSMPLOAD.G_HEADER_ID
1470: AND wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
1471: AND wsmti.internal_group_id = WSMPLOAD.G_GROUP_ID
1472: AND wrji.header_id=wsmti.header_id
1473: AND wsmti.header_id = WSMPLOAD.G_HEADER_ID
1474: AND wsmti.process_status = WIP_CONSTANTS.RUNNING;
1475: --end modification
1476:
1477: l_error_num NUMBER ;
1478:
1814: l_stmt_num := 13;
1815:
1816: UPDATE WSM_SPLIT_MERGE_TXN_INTERFACE wsmti
1817: SET wsmti.error_message = l_fnd_generic_err_msg
1818: , wsmti.process_status = WIP_CONSTANTS.ERROR
1819: WHERE wsmti.rowid = l_cur_wsmti_rec.rowid;
1820:
1821: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
1822: ||l_stmt_num||'): '
1829: l_stmt_num := 15;
1830:
1831: Update wsm_starting_jobs_interface wsji
1832: SET wsji.error_message = l_fnd_generic_err_msg
1833: , wsji.process_status = WIP_CONSTANTS.ERROR
1834: Where wsji.header_id = l_cur_wsmti_rec.header_id;
1835:
1836: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
1837: ||l_stmt_num||'): '
1844: l_stmt_num := 16;
1845:
1846: Update wsm_resulting_jobs_interface wrji
1847: Set wrji.error_message = l_fnd_generic_err_msg
1848: , wrji.process_status = WIP_CONSTANTS.ERROR
1849: Where wrji.header_id = l_cur_wsmti_rec.header_id;
1850:
1851: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
1852: ||l_stmt_num||'): '
1853: ||'Record for Header Id '
1854: ||l_cur_wsmti_rec.header_id
1855: ||' set to ERROR in WRJI.');
1856:
1857: p_err_num := WIP_CONSTANTS.ERROR;
1858: p_err_msg := l_fnd_err_msg;
1859: raise proc_error;
1860:
1861: End If;
2199: where dis.wip_entity_id = l_cur_wsji_rec.wip_entity_id
2200: and dis.wip_entity_id = we.wip_entity_id
2201: and dis.organization_id = l_cur_wsji_rec.organization_id
2202: and dis.status_type = 3
2203: and dis.job_type in (WIP_CONSTANTS.STANDARD,
2204: WIP_CONSTANTS.NONSTANDARD) --VJ: Added for NSLBJ--
2205: and we.entity_type = 5;
2206:
2207: IF (SQL%ROWCOUNT > 0) THEN
2200: and dis.wip_entity_id = we.wip_entity_id
2201: and dis.organization_id = l_cur_wsji_rec.organization_id
2202: and dis.status_type = 3
2203: and dis.job_type in (WIP_CONSTANTS.STANDARD,
2204: WIP_CONSTANTS.NONSTANDARD) --VJ: Added for NSLBJ--
2205: and we.entity_type = 5;
2206:
2207: IF (SQL%ROWCOUNT > 0) THEN
2208: FND_MESSAGE.SET_NAME('WSM', 'WSM_MODIFIED_FIELD');
2271: and dis.wip_entity_id = we.wip_entity_id
2272: and dis.wip_entity_id = nvl(l_cur_wsji_rec.wip_entity_id, dis.wip_entity_id)
2273: and dis.organization_id = we.organization_id
2274: and dis.status_type = 3
2275: and dis.job_type in (WIP_CONSTANTS.STANDARD, WIP_CONSTANTS.NONSTANDARD)
2276: and we.entity_type = 5;
2277:
2278: IF (SQL%ROWCOUNT > 0) THEN
2279: FND_MESSAGE.SET_NAME('WSM', 'WSM_MODIFIED_FIELD');
2539: select null into l_dummy
2540: from wsm_split_merge_txn_interface txn
2541: where txn.header_id = l_cur_wsji_rec.wsji_header_id
2542: and txn.transaction_type_id <> WSMPCNST.BONUS
2543: AND l_cur_wsji_rec.intraoperation_step in (WIP_CONSTANTS.QUEUE, WIP_CONSTANTS.TOMOVE);
2544:
2545: Exception
2546:
2547: When NO_DATA_FOUND Then
2878: and NOT EXISTS (select 1
2879: from bom_operational_routings bor
2880: where bor.routing_sequence_id = l_cur_wsji_rec.routing_seq_id
2881: and ((bor.assembly_item_id = l_cur_wsji_rec.primary_item_id
2882: and l_wsji_job_type = WIP_CONSTANTS.STANDARD) -- Bug#2679705
2883: OR
2884: (bor.assembly_item_id = l_wsji_routing_reference_id
2885: and l_wsji_job_type = WIP_CONSTANTS.NONSTANDARD) -- Bug#2679705
2886: )
2881: and ((bor.assembly_item_id = l_cur_wsji_rec.primary_item_id
2882: and l_wsji_job_type = WIP_CONSTANTS.STANDARD) -- Bug#2679705
2883: OR
2884: (bor.assembly_item_id = l_wsji_routing_reference_id
2885: and l_wsji_job_type = WIP_CONSTANTS.NONSTANDARD) -- Bug#2679705
2886: )
2887: and bor.organization_id = l_cur_wsji_rec.organization_id
2888: and bor.cfm_routing_flag=3);
2889:
3082: l_stmt_num := 55;
3083:
3084: Update wsm_starting_jobs_interface wsji
3085: SET wsji.error_message = l_fnd_generic_err_msg
3086: , wsji.process_status = WIP_CONSTANTS.ERROR
3087: WHERE wsji.rowid = l_cur_wsji_rec.wsji_rowid;
3088:
3089: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
3090: ||l_stmt_num||'): '
3094:
3095: l_stmt_num := 56; -- Bug#2682603
3096: Update wsm_resulting_jobs_interface wrji
3097: Set wrji.error_message = l_fnd_generic_err_msg
3098: , wrji.process_status = WIP_CONSTANTS.ERROR
3099: Where wrji.header_id = l_cur_wsji_rec.wsji_header_id;
3100:
3101: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
3102: ||l_stmt_num||'): '
3106:
3107: l_stmt_num := 57;
3108: Update wsm_split_merge_txn_interface wsmti
3109: SET wsmti.error_message = l_fnd_generic_err_msg
3110: , wsmti.process_status = WIP_CONSTANTS.ERROR
3111: Where wsmti.header_id = l_cur_wsji_rec.wsji_header_id;
3112:
3113: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
3114: ||l_stmt_num||'): '
3115: ||'Record for Header Id '
3116: ||l_cur_wsji_rec.wsji_header_id
3117: ||' set to ERROR in WSMTI.');
3118:
3119: p_err_num := WIP_CONSTANTS.ERROR;
3120: p_err_msg := l_fnd_err_msg;
3121: raise proc_error;
3122:
3123: End If;
3250: l_stmt_num := 63;
3251:
3252: update wsm_resulting_jobs_interface wrji
3253: SET wrji.error_message = l_fnd_generic_err_msg
3254: , wrji.process_status = WIP_CONSTANTS.ERROR -- WLTEnh
3255: WHERE wrji.header_id = l_header_id;
3256:
3257:
3258: set_wrji_error(); -- WLTEnh
3289:
3290: l_stmt_num := 64;
3291: update wsm_resulting_jobs_interface wrji
3292: SET wrji.error_message = l_fnd_generic_err_msg
3293: , wrji.process_status = WIP_CONSTANTS.ERROR -- WLTEnh
3294: WHERE wrji.header_id = l_header_id
3295: AND l_txn_type = WSMPCNST.BONUS --VJ: CodeRVW: Added for NSLBJ--
3296: AND wrji.job_type IS NOT NULL
3297: AND wrji.job_type NOT IN (WIP_CONSTANTS.STANDARD, WIP_CONSTANTS.NONSTANDARD); --Fixed bug #2341915
3293: , wrji.process_status = WIP_CONSTANTS.ERROR -- WLTEnh
3294: WHERE wrji.header_id = l_header_id
3295: AND l_txn_type = WSMPCNST.BONUS --VJ: CodeRVW: Added for NSLBJ--
3296: AND wrji.job_type IS NOT NULL
3297: AND wrji.job_type NOT IN (WIP_CONSTANTS.STANDARD, WIP_CONSTANTS.NONSTANDARD); --Fixed bug #2341915
3298:
3299: IF (SQL%ROWCOUNT > 0) THEN
3300:
3301: set_wrji_error(); -- WLTEnh
3320:
3321: l_stmt_num := 65;
3322: --VJ: Start additions to fix bug #2341915--
3323: update wsm_resulting_jobs_interface wrji
3324: SET wrji.job_type = WIP_CONSTANTS.STANDARD
3325: WHERE wrji.header_id = l_header_id
3326: AND l_txn_type = WSMPCNST.BONUS
3327: AND wrji.job_type IS NULL;
3328:
3408: l_stmt_num := 69;
3409:
3410: update wsm_resulting_jobs_interface wrji
3411: SET wrji.error_message = l_fnd_generic_err_msg
3412: , wrji.process_status = WIP_CONSTANTS.ERROR -- WLTEnh
3413: WHERE wrji.header_id = l_header_id
3414: and wrji.job_type IS NOT NULL
3415: AND wrji.job_type <> l_min_job_type;
3416:
3464: update wsm_resulting_jobs_interface wrji
3465: SET organization_id = (SELECT organization_id
3466: FROM wsm_split_merge_txn_interface
3467: WHERE header_id = wrji.header_id
3468: AND process_status = WIP_CONSTANTS.RUNNING
3469: AND internal_group_id = WSMPLOAD.G_GROUP_ID)
3470: WHERE wrji.header_id = l_header_id
3471: AND wrji.organization_id IS NULL;
3472:
3501: and not exists (select null
3502: from wsm_split_merge_txn_interface txn
3503: where txn.header_id = wrji.header_id
3504: and txn.organization_id = wrji.organization_id
3505: and txn.process_status = WIP_CONSTANTS.RUNNING
3506: AND txn.internal_group_id = WSMPLOAD.G_GROUP_ID);
3507:
3508:
3509: IF (SQL%ROWCOUNT > 0) THEN
3560:
3561: update wsm_resulting_jobs_interface rj
3562: set scheduled_start_date = l_scheduled_start_date,
3563: scheduled_completion_date = l_scheduled_completion_date
3564: where rj.process_status = WIP_CONSTANTS.RUNNING
3565: and rj.internal_group_id = WSMPLOAD.G_GROUP_ID
3566: and rj.header_id = l_header_id;
3567:
3568: IF (SQL%ROWCOUNT > 0) THEN
3630: l_class_code,
3631: l_completion_subinventory,
3632: l_completion_locator_id
3633: FROM wsm_resulting_jobs_interface wrji
3634: WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
3635: AND wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
3636: AND wrji.header_id = l_header_id
3637: AND wrji.wip_entity_name = l_we_name;
3638:
3648: --VJ: End Additions to fix bug #2315397--
3649:
3650: --VJ: Start Additions for NSLBJ--
3651:
3652: IF (l_job_type = WIP_CONSTANTS.NONSTANDARD) THEN --End beforel_stmt_num := 94;--
3653:
3654: l_stmt_num := 74;
3655: --VJ: 0313: Start Additions for NSLBJ--
3656: IF (l_wrji_alt_rtg_desig = '-1') THEN -- it is NULL
4194: END IF;
4195:
4196: END IF; --IF (l_txn_type IN (WSMPCNST.BONUS, WSMPCNST.UPDATE_ASSEMBLY, WSMPCNST.UPDATE_ROUTING))
4197:
4198: END IF; --(l_job_type = WIP_CONSTANTS.NONSTANDARD) Begin @ _stmt_num := 166
4199:
4200: -- BA: CZH.BUG2398718
4201: IF (l_job_type = WIP_CONSTANTS.STANDARD) THEN
4202: -- We should have the same code for Standard LBJ !!!!!!!!!
4197:
4198: END IF; --(l_job_type = WIP_CONSTANTS.NONSTANDARD) Begin @ _stmt_num := 166
4199:
4200: -- BA: CZH.BUG2398718
4201: IF (l_job_type = WIP_CONSTANTS.STANDARD) THEN
4202: -- We should have the same code for Standard LBJ !!!!!!!!!
4203: NULL;
4204: END IF; -- (l_job_type = WIP_CONSTANTS.STANDARD)
4205: -- EA: CZH.BUG2398718
4200: -- BA: CZH.BUG2398718
4201: IF (l_job_type = WIP_CONSTANTS.STANDARD) THEN
4202: -- We should have the same code for Standard LBJ !!!!!!!!!
4203: NULL;
4204: END IF; -- (l_job_type = WIP_CONSTANTS.STANDARD)
4205: -- EA: CZH.BUG2398718
4206:
4207: --VJ: End Additions for NSLBJ--
4208:
4240: l_class_code,
4241: l_completion_subinventory,
4242: l_completion_locator_id
4243: FROM wsm_resulting_jobs_interface wrji
4244: WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
4245: AND wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
4246: AND wrji.header_id = l_header_id
4247: AND wrji.wip_entity_name = l_we_name;
4248:
4477: WHERE wrji.header_id = l_header_id
4478: and not exists (select null
4479: from wsm_split_merge_txn_interface txn
4480: where txn.header_id = wrji.header_id
4481: and txn.process_status = WIP_CONSTANTS.RUNNING);
4482:
4483: IF (SQL%ROWCOUNT > 0) THEN
4484: set_wrji_error(); -- WLTEnh
4485:
5069: | for a STANDARD Job |
5070: +---------------------------------------------------------*/
5071:
5072: l_stmt_num := 119;
5073: IF (l_job_type = WIP_CONSTANTS.STANDARD) THEN
5074: l_stmt_num := 120;
5075: UPDATE wsm_resulting_jobs_interface wrji
5076: SET routing_reference_id = NULL,
5077: bom_reference_id = NULL
5075: UPDATE wsm_resulting_jobs_interface wrji
5076: SET routing_reference_id = NULL,
5077: bom_reference_id = NULL
5078: WHERE wrji.header_id = l_header_id
5079: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
5080: and wrji.process_status = WIP_CONSTANTS.RUNNING
5081: and wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
5082: and (wrji.routing_reference_id IS NOT NULL
5083: OR wrji.bom_reference_id IS NOT NULL);
5076: SET routing_reference_id = NULL,
5077: bom_reference_id = NULL
5078: WHERE wrji.header_id = l_header_id
5079: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
5080: and wrji.process_status = WIP_CONSTANTS.RUNNING
5081: and wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
5082: and (wrji.routing_reference_id IS NOT NULL
5083: OR wrji.bom_reference_id IS NOT NULL);
5084:
5208: if (l_wrji_rtg_seq_id = -1) then
5209: l_stmt_num := 125;
5210: UPDATE wsm_resulting_jobs_interface wrji
5211: SET common_routing_sequence_id=l_rtg_seq_id
5212: WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
5213: AND wrji.header_id=l_header_id
5214: AND wrji.rowid = l_wrji_rowid;
5215:
5216: l_wrji_rtg_seq_id := l_rtg_seq_id;
5240: then
5241: l_stmt_num := 126;
5242: UPDATE wsm_resulting_jobs_interface wrji
5243: SET alternate_routing_designator=l_alt_rtg_desig
5244: WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
5245: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
5246: AND wrji.header_id=l_header_id
5247: AND wrji.rowid = l_wrji_rowid;
5248:
5241: l_stmt_num := 126;
5242: UPDATE wsm_resulting_jobs_interface wrji
5243: SET alternate_routing_designator=l_alt_rtg_desig
5244: WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
5245: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
5246: AND wrji.header_id=l_header_id
5247: AND wrji.rowid = l_wrji_rowid;
5248:
5249: FND_MESSAGE.SET_NAME('WSM', 'WSM_MODIFIED_FIELD');
5273: then
5274: l_stmt_num := 127;
5275: UPDATE wsm_resulting_jobs_interface wrji
5276: SET alternate_routing_designator=NULL
5277: WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
5278: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
5279: AND wrji.header_id=l_header_id
5280: AND wrji.rowid = l_wrji_rowid;
5281:
5274: l_stmt_num := 127;
5275: UPDATE wsm_resulting_jobs_interface wrji
5276: SET alternate_routing_designator=NULL
5277: WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
5278: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
5279: AND wrji.header_id=l_header_id
5280: AND wrji.rowid = l_wrji_rowid;
5281:
5282: FND_MESSAGE.SET_NAME('WSM', 'WSM_MODIFIED_FIELD');
5311: SET common_routing_sequence_id=
5312: (select bor.common_routing_sequence_id
5313: from bom_operational_routings bor
5314: where bor.assembly_item_id = decode(wrji.job_type,
5315: WIP_CONSTANTS.STANDARD, wrji.primary_item_id,
5316: wrji.routing_reference_id) -- Fix for bug #2369822
5317: and bor.alternate_routing_designator is null
5318: and bor.organization_id = l_organization_id --bugfix 1922146: added orgn_id condn
5319: and bor.cfm_routing_flag = 3)
5345: UPDATE wsm_resulting_jobs_interface wrji
5346: SET wrji.error_message = l_fnd_generic_err_msg
5347: WHERE wrji.header_id = l_header_id
5348: AND wrji.rowid = l_wrji_rowid --SpUA add
5349: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
5350: AND NOT EXISTS (select 1
5351: from bom_operational_routings bor
5352: where bor.routing_sequence_id = wrji.common_routing_sequence_id
5353: and bor.alternate_routing_designator = wrji.alternate_routing_designator
5383: UPDATE wsm_resulting_jobs_interface wrji
5384: SET wrji.error_message = l_fnd_generic_err_msg
5385: WHERE wrji.header_id = l_header_id
5386: AND wrji.rowid = l_wrji_rowid --SpUA add
5387: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
5388: AND NOT EXISTS (select 1
5389: from bom_operational_routings bor
5390: where bor.routing_sequence_id = wrji.common_routing_sequence_id
5391: --and bor.assembly_item_id=wrji.primary_item_id --bugfix 2077930
5403: and bor.organization_id = l_organization_id --bugfix 1922146: added orgn_id condn
5404: and bor.cfm_routing_flag=3)
5405: WHERE wrji.header_id = l_header_id
5406: AND wrji.rowid = l_wrji_rowid --SpUA add
5407: AND wrji.job_type = WIP_CONSTANTS.STANDARD; --VJ: Added for NSLBJ--
5408:
5409: FND_MESSAGE.SET_NAME('WSM', 'WSM_MODIFIED_FIELD');
5410: FND_MESSAGE.SET_TOKEN('FLD_NAME', 'alternate_routing_designator in Resulting Jobs');
5411: l_fnd_err_msg := FND_MESSAGE.GET;
5453: UPDATE wsm_resulting_jobs_interface wrji
5454: SET wrji.error_message = l_fnd_generic_err_msg
5455: WHERE wrji.header_id = l_header_id
5456: AND wrji.rowid = l_wrji_rowid --SpUA add
5457: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
5458: AND NOT EXISTS (select 1
5459: from bom_operational_routings bor
5460: where bor.alternate_routing_designator = wrji.alternate_routing_designator
5461: and bor.assembly_item_id = wrji.primary_item_id
5473: and bor.organization_id = l_organization_id --bugfix 1922146: added orgn_id condn
5474: and bor.cfm_routing_flag=3)
5475: WHERE wrji.header_id = l_header_id
5476: AND wrji.rowid = l_wrji_rowid --SpUA add
5477: AND wrji.job_type = WIP_CONSTANTS.STANDARD; --VJ: Added for NSLBJ--
5478:
5479: FND_MESSAGE.SET_NAME('WSM', 'WSM_MODIFIED_FIELD');
5480: FND_MESSAGE.SET_TOKEN('FLD_NAME', 'common_routing_sequence_id in Resulting Jobs');
5481: l_fnd_err_msg := FND_MESSAGE.GET;
5622: mtl_secondary_inventories msi -- CZH.BUG2398718
5623: WHERE bor.common_routing_sequence_id = rj.common_routing_sequence_id
5624: AND bor.organization_id = rj.organization_id
5625: AND bor.assembly_item_id = decode(rj.job_type,
5626: WIP_CONSTANTS.STANDARD, rj.primary_item_id,
5627: WIP_CONSTANTS.NONSTANDARD, rj.routing_reference_id,
5628: rj.primary_item_id)
5629: AND msi.secondary_inventory_name = bor.completion_subinventory -- CZH.BUG2398718
5630: AND msi.organization_id = bor.organization_id -- CZH.BUG2398718
5623: WHERE bor.common_routing_sequence_id = rj.common_routing_sequence_id
5624: AND bor.organization_id = rj.organization_id
5625: AND bor.assembly_item_id = decode(rj.job_type,
5626: WIP_CONSTANTS.STANDARD, rj.primary_item_id,
5627: WIP_CONSTANTS.NONSTANDARD, rj.routing_reference_id,
5628: rj.primary_item_id)
5629: AND msi.secondary_inventory_name = bor.completion_subinventory -- CZH.BUG2398718
5630: AND msi.organization_id = bor.organization_id -- CZH.BUG2398718
5631: )
5628: rj.primary_item_id)
5629: AND msi.secondary_inventory_name = bor.completion_subinventory -- CZH.BUG2398718
5630: AND msi.organization_id = bor.organization_id -- CZH.BUG2398718
5631: )
5632: WHERE rj.process_status = WIP_CONSTANTS.RUNNING
5633: AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
5634: AND rj.completion_subinventory is null
5635: AND rj.common_routing_sequence_id is not null -- CZH.BUG2398718
5636: AND rj.rowid = l_wrji_rowid --SpUA add
5636: AND rj.rowid = l_wrji_rowid --SpUA add
5637: AND rj.header_id IN (SELECT header_id
5638: FROM wsm_split_merge_txn_interface
5639: WHERE internal_group_id = WSMPLOAD.G_GROUP_ID
5640: AND process_status = WIP_CONSTANTS.RUNNING
5641: AND (transaction_type_id IN (WSMPCNST.UPDATE_ASSEMBLY,
5642: WSMPCNST.BONUS,
5643: WSMPCNST.UPDATE_ROUTING)
5644: --SpUA begin
5694: and wsji.header_id=l_header_id;
5695:
5696: ELSE -- updAsm, updRtg, bonus,
5697: /* ST bug fix 3327459 also jobs in split with change of assembly */
5698: IF (l_job_type = WIP_CONSTANTS.STANDARD) THEN
5699: l_item_id := l_primary_item_id;
5700: ELSIF (l_job_type = WIP_CONSTANTS.NONSTANDARD) THEN
5701: l_item_id := l_routing_reference_id;
5702: END IF;
5696: ELSE -- updAsm, updRtg, bonus,
5697: /* ST bug fix 3327459 also jobs in split with change of assembly */
5698: IF (l_job_type = WIP_CONSTANTS.STANDARD) THEN
5699: l_item_id := l_primary_item_id;
5700: ELSIF (l_job_type = WIP_CONSTANTS.NONSTANDARD) THEN
5701: l_item_id := l_routing_reference_id;
5702: END IF;
5703:
5704: If (l_debug = 'Y') Then
6014: OR (wsmti.transaction_type_id in (WSMPCNST.SPLIT)
6015: AND l_split_has_update_assy = 1));
6016:
6017: IF (l_profile_value = 2) THEN -- Option A (Old Behavior)
6018: IF (l_st_iop = WIP_CONSTANTS.QUEUE) THEN -- Res St Op is mandatory.
6019: l_execute_this := 1;
6020: ELSE -- Job is at TM
6021: -- honor the user specified op seq - it may/maynot be NULL
6022: l_execute_this := 0;
6023: END IF;
6024: ELSE -- Option C (New Behavior)
6025: -- Start : Fix for bug #3154541 --
6026: -- l_execute_this := 1;
6027: IF (l_st_iop = WIP_CONSTANTS.QUEUE) THEN -- Res St Op is mandatory.
6028: l_execute_this := 1;
6029: ELSE -- Job is at TM
6030: -- User has to manually specify a starting operation
6031: l_execute_this := 2;
6085: SET starting_operation_seq_num = l_bos_opseq_num,
6086: starting_operation_code = l_bso_stdop_code
6087: WHERE wrji.header_id = l_header_id
6088: and wrji.rowid = l_wrji_rowid --SpUA add
6089: and wrji.process_status = WIP_CONSTANTS.RUNNING
6090: and wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
6091: and exists (
6092: select 1
6093: from wsm_starting_jobs_interface sj,
6100: OR (txn.transaction_type_id in (WSMPCNST.SPLIT)
6101: AND l_split_has_update_assy = 1))
6102: --SpUA end
6103: --Start Changes to fix bug #2352369--
6104: --and sj.intraoperation_step = WIP_CONSTANTS.TOMOVE
6105: and (nvl(wrji.starting_std_op_id, l_bos_stdop_id) = l_bos_stdop_id OR
6106: nvl(wrji.starting_operation_code, l_bso_stdop_code) = l_bso_stdop_code)
6107: and wrji.starting_operation_seq_num IS NULL -- ADD: bugfix 2672003, 2682611
6108: and wrji.starting_operation_code IS NULL -- ADD: bugfix 2672003, 2682611
6144: -- BA NSO:WLT when starting Job is at TO_MOVE nullify the starting opnum/code details.
6145: -- Bug#2095535
6146:
6147:
6148: ElsIf (l_sj_intraoperation_step = WIP_CONSTANTS.TOMOVE
6149: --SpUA bugfix 3161493. We should not null out starting_op for APS Option C
6150: and l_profile_value = 2) Then
6151: --End SpUA bugfix 3161493
6152:
6155: SET starting_operation_seq_num = NULL,
6156: starting_operation_code = NULL
6157: WHERE wrji.header_id = l_header_id
6158: AND wrji.rowid = l_wrji_rowid --SpUA add
6159: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
6160: and wrji.process_status = WIP_CONSTANTS.RUNNING
6161: and wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
6162: and exists (
6163: select 1
6156: starting_operation_code = NULL
6157: WHERE wrji.header_id = l_header_id
6158: AND wrji.rowid = l_wrji_rowid --SpUA add
6159: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
6160: and wrji.process_status = WIP_CONSTANTS.RUNNING
6161: and wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
6162: and exists (
6163: select 1
6164: from wsm_starting_jobs_interface sj,
6170: --SpUA begin
6171: OR (txn.transaction_type_id in (WSMPCNST.SPLIT)
6172: AND l_split_has_update_assy = 1))
6173: --SpUA end
6174: and sj.intraoperation_step = WIP_CONSTANTS.TOMOVE
6175: );
6176:
6177: FND_MESSAGE.SET_NAME('WSM', 'WSM_MODIFIED_FIELD');
6178: FND_MESSAGE.SET_TOKEN('FLD_NAME',
6198: END IF;
6199:
6200: -- EA BBK:NSO:WLT when starting Job is at TO_MOVE nullify the starting opnum/code details.
6201:
6202: END IF; /* ( l_op_seq_id <> -99 )- ElsIf l_sj_intraoperation_step = WIP_CONSTANTS.TOMOVE */
6203:
6204: END; --Disallow Upd txn at TM if that op is last op of new routing
6205: -- end bugfix 1776002
6206:
6245: l_stmt_num := 155;
6246: UPDATE wsm_resulting_jobs_interface wrji
6247: SET wrji.error_message = l_fnd_generic_err_msg
6248: WHERE wrji.header_id = l_header_id
6249: AND wrji.job_type = WIP_CONSTANTS.STANDARD
6250: AND wrji.rowid = l_wrji_rowid;
6251: --VJ: End Additions for NSLBJ--
6252:
6253: FND_MESSAGE.SET_NAME('WSM', 'WSM_INVALID_FIELD');
6280: l_stmt_num := 156;
6281: UPDATE wsm_resulting_jobs_interface wrji
6282: SET wrji.error_message = l_fnd_generic_err_msg
6283: WHERE wrji.header_id = l_header_id
6284: AND wrji.job_type = WIP_CONSTANTS.STANDARD
6285: AND wrji.rowid = l_wrji_rowid;
6286: --VJ: End Additions for NSLBJ--
6287:
6288: FND_MESSAGE.SET_NAME('WSM', 'WSM_INVALID_FIELD');
6308: if (l_wrji_bom_seq_id = -1) then
6309: l_stmt_num := 157;
6310: UPDATE wsm_resulting_jobs_interface wrji
6311: SET common_bom_sequence_id=l_bom_seq_id
6312: WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
6313: AND wrji.header_id=l_header_id
6314: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
6315: AND wrji.rowid = l_wrji_rowid;
6316:
6310: UPDATE wsm_resulting_jobs_interface wrji
6311: SET common_bom_sequence_id=l_bom_seq_id
6312: WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
6313: AND wrji.header_id=l_header_id
6314: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
6315: AND wrji.rowid = l_wrji_rowid;
6316:
6317: FND_MESSAGE.SET_NAME('WSM', 'WSM_MODIFIED_FIELD');
6318: FND_MESSAGE.SET_TOKEN('FLD_NAME', 'common_bom_sequence_id in Resulting Jobs');
6339:
6340: l_stmt_num := 158;
6341: UPDATE wsm_resulting_jobs_interface wrji
6342: SET alternate_bom_designator=l_alt_bom_desig
6343: WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
6344: AND wrji.header_id=l_header_id
6345: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
6346: AND wrji.rowid = l_wrji_rowid;
6347:
6341: UPDATE wsm_resulting_jobs_interface wrji
6342: SET alternate_bom_designator=l_alt_bom_desig
6343: WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
6344: AND wrji.header_id=l_header_id
6345: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
6346: AND wrji.rowid = l_wrji_rowid;
6347:
6348: FND_MESSAGE.SET_NAME('WSM', 'WSM_MODIFIED_FIELD');
6349: FND_MESSAGE.SET_TOKEN('FLD_NAME', 'alternate_bom_designator in Resulting Jobs');
6372:
6373: l_stmt_num := 159;
6374: UPDATE wsm_resulting_jobs_interface wrji
6375: SET alternate_bom_designator=NULL
6376: WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
6377: AND wrji.header_id=l_header_id
6378: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
6379: AND wrji.rowid = l_wrji_rowid;
6380:
6374: UPDATE wsm_resulting_jobs_interface wrji
6375: SET alternate_bom_designator=NULL
6376: WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
6377: AND wrji.header_id=l_header_id
6378: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
6379: AND wrji.rowid = l_wrji_rowid;
6380:
6381: FND_MESSAGE.SET_NAME('WSM', 'WSM_MODIFIED_FIELD');
6382: FND_MESSAGE.SET_TOKEN('FLD_NAME', 'alternate_bom_designator in Resulting Jobs');
6432: or (bom.assembly_type = 2)))
6433: -- and bom.assembly_type = 1) --endfix
6434: WHERE wrji.header_id = l_header_id
6435: AND wrji.rowid = l_wrji_rowid --SpUA add
6436: AND wrji.job_type = WIP_CONSTANTS.STANDARD; --VJ: Added for NSLBJ--
6437:
6438: FND_MESSAGE.SET_NAME('WSM', 'WSM_MODIFIED_FIELD');
6439: FND_MESSAGE.SET_TOKEN('FLD_NAME', 'common_bom_sequence_id in Resulting Jobs');
6440: l_fnd_err_msg := FND_MESSAGE.GET;
6467: UPDATE wsm_resulting_jobs_interface wrji
6468: SET wrji.error_message = l_fnd_generic_err_msg
6469: WHERE wrji.header_id = l_header_id
6470: AND wrji.rowid = l_wrji_rowid --SpUA add
6471: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
6472: AND NOT EXISTS (select 1
6473: from bom_bill_of_materials bom,
6474: mtl_system_items msi --bugfix 1828859
6475: where bom.bill_sequence_id = wrji.common_bom_sequence_id
6509: UPDATE wsm_resulting_jobs_interface wrji
6510: SET wrji.error_message = l_fnd_generic_err_msg
6511: WHERE wrji.header_id = l_header_id
6512: AND wrji.rowid = l_wrji_rowid --SpUA add
6513: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
6514: AND NOT EXISTS (select 1
6515: from bom_bill_of_materials bom,
6516: mtl_system_items msi --bugfix 1828859
6517: where bom.bill_sequence_id = wrji.common_bom_sequence_id
6539: or (bom.assembly_type = 2)))
6540: -- and bom.assembly_type=1) --endfix
6541: WHERE wrji.header_id = l_header_id
6542: AND wrji.rowid = l_wrji_rowid --SpUA add
6543: AND wrji.job_type = WIP_CONSTANTS.STANDARD; --VJ: Added for NSLBJ--
6544:
6545: FND_MESSAGE.SET_NAME('WSM', 'WSM_MODIFIED_FIELD');
6546: FND_MESSAGE.SET_TOKEN('FLD_NAME', 'alternate_bom_designator in Resulting Jobs');
6547: l_fnd_err_msg := FND_MESSAGE.GET;
6588: UPDATE wsm_resulting_jobs_interface wrji
6589: SET wrji.error_message = l_fnd_generic_err_msg
6590: WHERE wrji.header_id = l_header_id
6591: AND wrji.rowid = l_wrji_rowid --SpUA add
6592: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
6593: and NOT EXISTS (select 1
6594: from bom_bill_of_materials bom,
6595: mtl_system_items msi --bugfix 1828859
6596: where bom.alternate_bom_designator = wrji.alternate_bom_designator
6618: or (bom.assembly_type = 2)))
6619: -- and bom.assembly_type=1) --endfix
6620: WHERE wrji.header_id = l_header_id
6621: AND wrji.rowid = l_wrji_rowid
6622: AND wrji.job_type = WIP_CONSTANTS.STANDARD; --VJ: Added for NSLBJ--
6623:
6624: FND_MESSAGE.SET_NAME('WSM', 'WSM_MODIFIED_FIELD');
6625: FND_MESSAGE.SET_TOKEN('FLD_NAME', 'common_bom_sequence_id in Resulting Jobs');
6626: l_fnd_err_msg := FND_MESSAGE.GET;
6729:
6730: UPDATE wsm_resulting_jobs_interface wrji
6731: SET wrji.error_message = l_fnd_generic_err_msg
6732: WHERE wrji.header_id = l_header_id
6733: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
6734: and not exists (select null
6735: from bom_routing_alternates_v bor
6736: where bor.assembly_item_id = wrji.primary_item_id
6737: and bor.organization_id = wrji.organization_id
6774: --Changed the sql since org_id has been added to WRJI now--
6775: UPDATE wsm_resulting_jobs_interface wrji
6776: SET wrji.error_message = l_fnd_generic_err_msg
6777: WHERE wrji.header_id = l_header_id
6778: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
6779: and not exists
6780: (select null
6781: from bom_bill_alternates_v bba,
6782: mtl_system_items msi --bugfix 1828859
6826: FOR c_bom_rtg_revision_rec IN bom_rtg_revision(l_header_id, l_we_name) LOOP
6827: --VJ: Added param to fix bug #2315397--
6828: l_stmt_num := 172;
6829: -- Get the Routing Revision and Routing Revision Date first --
6830: IF (c_bom_rtg_revision_rec.job_type = WIP_CONSTANTS.STANDARD) THEN
6831: l_temp := c_bom_rtg_revision_rec.item_id;
6832: ELSIF (c_bom_rtg_revision_rec.job_type = WIP_CONSTANTS.NONSTANDARD) THEN
6833: l_temp := c_bom_rtg_revision_rec.rtg_ref_id;
6834: END IF;
6828: l_stmt_num := 172;
6829: -- Get the Routing Revision and Routing Revision Date first --
6830: IF (c_bom_rtg_revision_rec.job_type = WIP_CONSTANTS.STANDARD) THEN
6831: l_temp := c_bom_rtg_revision_rec.item_id;
6832: ELSIF (c_bom_rtg_revision_rec.job_type = WIP_CONSTANTS.NONSTANDARD) THEN
6833: l_temp := c_bom_rtg_revision_rec.rtg_ref_id;
6834: END IF;
6835:
6836: -- BA: CZH.I_OED-1: the following logic is not right,
6904: END IF;
6905: END IF;
6906:
6907: l_stmt_num := 175;
6908: IF (c_bom_rtg_revision_rec.job_type = WIP_CONSTANTS.STANDARD) THEN
6909: l_stmt_num := 176;
6910: l_temp := c_bom_rtg_revision_rec.item_id;
6911: ELSIF (c_bom_rtg_revision_rec.job_type = WIP_CONSTANTS.NONSTANDARD) THEN
6912: l_stmt_num := 177;
6907: l_stmt_num := 175;
6908: IF (c_bom_rtg_revision_rec.job_type = WIP_CONSTANTS.STANDARD) THEN
6909: l_stmt_num := 176;
6910: l_temp := c_bom_rtg_revision_rec.item_id;
6911: ELSIF (c_bom_rtg_revision_rec.job_type = WIP_CONSTANTS.NONSTANDARD) THEN
6912: l_stmt_num := 177;
6913: l_temp := c_bom_rtg_revision_rec.bom_ref_id;
6914: END IF;
6915:
7004: --Changed the sql since org_id has been added to WRJI now--
7005: UPDATE wsm_resulting_jobs_interface wrji
7006: SET wrji.error_message = l_fnd_generic_err_msg
7007: WHERE wrji.header_id = l_header_id
7008: AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
7009: and not exists (select null
7010: from mtl_routing_rev_highdate_v rou
7011: where wrji.organization_id = rou.organization_id
7012: --VJ: Start Changes for NSLBJ--
7010: from mtl_routing_rev_highdate_v rou
7011: where wrji.organization_id = rou.organization_id
7012: --VJ: Start Changes for NSLBJ--
7013: and rou.inventory_item_id = decode(l_job_type,
7014: WIP_CONSTANTS.STANDARD,
7015: wrji.primary_item_id,
7016: WIP_CONSTANTS.NONSTANDARD,
7017: wrji.routing_reference_id)
7018: --VJ: End Changes for NSLBJ--
7012: --VJ: Start Changes for NSLBJ--
7013: and rou.inventory_item_id = decode(l_job_type,
7014: WIP_CONSTANTS.STANDARD,
7015: wrji.primary_item_id,
7016: WIP_CONSTANTS.NONSTANDARD,
7017: wrji.routing_reference_id)
7018: --VJ: End Changes for NSLBJ--
7019: and rou.process_revision = nvl(wrji.routing_revision,
7020: rou.process_revision)
7145:
7146: l_upd_rtg_asly_err := 0;
7147:
7148: l_stmt_num := 182;
7149: IF(l_job_type = WIP_CONSTANTS.STANDARD) THEN --VJ: Added for NSLBJ--
7150: l_stmt_num := 183;
7151: select
7152: bor.common_routing_sequence_id,
7153: wrji.primary_item_id,
7175: and wrji.rowid = l_wrji_rowid --SpUA add
7176: and wsji.header_id = wrji.header_id
7177: and bor.organization_id = wrji.organization_id --VJ: Added for NSLBJ--
7178: --Above condition also a fix for bug #2269765--
7179: and wrji.job_type = WIP_CONSTANTS.STANDARD; --VJ: Added for NSLBJ--
7180:
7181: --VJ: Start Additions for NSLBJ--
7182: ELSIF(l_job_type = WIP_CONSTANTS.NONSTANDARD) THEN
7183: l_stmt_num := 184;
7178: --Above condition also a fix for bug #2269765--
7179: and wrji.job_type = WIP_CONSTANTS.STANDARD; --VJ: Added for NSLBJ--
7180:
7181: --VJ: Start Additions for NSLBJ--
7182: ELSIF(l_job_type = WIP_CONSTANTS.NONSTANDARD) THEN
7183: l_stmt_num := 184;
7184: select
7185: wrji.common_routing_sequence_id,
7186: wrji.primary_item_id,
7203: where
7204: wrji.header_id = l_header_id
7205: and wrji.rowid = l_wrji_rowid --SpUA add
7206: and wsji.header_id = wrji.header_id
7207: and wrji.job_type = WIP_CONSTANTS.NONSTANDARD; --VJ: Added for NSLBJ--
7208:
7209: END IF;
7210: --VJ: End Additions for NSLBJ--
7211:
7258:
7259: End If;
7260:
7261:
7262: if (l_op_is_std = 0 and l_sj_intraoperation_step <> WIP_CONSTANTS.TOMOVE ) then
7263: l_stmt_num := 187;
7264: -- op is non-std
7265: FND_MESSAGE.SET_NAME('WSM', 'WSM_JOB_AT_NSO'); --BBK
7266: l_fnd_err_msg := FND_MESSAGE.GET;
7266: l_fnd_err_msg := FND_MESSAGE.GET;
7267: -- l_fnd_err_msg := 'Please specify the starting operation, since current operation does not exist in the target routing.';
7268: raise l_processing_err_excp;
7269:
7270: elsif (l_op_rptd_times = 0 and l_sj_intraoperation_step <> WIP_CONSTANTS.TOMOVE) then
7271: l_stmt_num := 188;
7272: -- this std op does not exist in the target asly/routing
7273: fnd_message.set_name('WSM', 'WSM_NO_CURRENT_STDOP_TGTRTG'); --BBK
7274: l_fnd_err_msg := FND_MESSAGE.GET;
7275: raise l_processing_err_excp;
7276:
7277: -- BA#Bug#2095535 BBK:NSO:WLT
7278:
7279: --elsif (l_op_rptd_times = 1 and l_sj_intraoperation_step = WIP_CONSTANTS.TOMOVE) then
7280: elsif (l_op_rptd_times = 1) then -- CHG: BUGFIX 2686319
7281:
7282: -- this std op does exist only once in the target asly/routing
7283:
7307: starting_operation_seq_num = l_start_op_seq_num, -- ADD: BUGFIX 2686319
7308: starting_operation_code = l_start_op_code
7309: WHERE wrji.header_id = l_header_id
7310: and wrji.rowid = l_wrji_rowid --SpUA add
7311: and wrji.process_status = WIP_CONSTANTS.RUNNING
7312: and wrji.internal_group_id = WSMPLOAD.G_GROUP_ID;
7313:
7314:
7315: FND_MESSAGE.SET_NAME('WSM', 'WSM_MODIFIED_FIELD');
7335: raise proc_error;
7336: END IF;
7337: -- EA#Bug#2095535 BBK:NSO:WLT
7338:
7339: elsif (l_op_rptd_times > 1 and l_sj_intraoperation_step <> WIP_CONSTANTS.TOMOVE ) then
7340: -- this std op is repeated more than once in the routing.
7341: l_stmt_num := 192;
7342: FND_MESSAGE.SET_NAME('WSM', 'WSM_JOB_AT_REPEATED_OP'); --BBK
7343: l_fnd_err_msg := FND_MESSAGE.GET;
7343: l_fnd_err_msg := FND_MESSAGE.GET;
7344: raise l_processing_err_excp;
7345:
7346: elsif (
7347: (l_op_is_std = 0 and l_sj_intraoperation_step = WIP_CONSTANTS.TOMOVE)
7348: OR (l_op_rptd_times <> 1 and l_sj_intraoperation_step = WIP_CONSTANTS.TOMOVE)
7349: ) then
7350: -- At TO_MOVE, if current operation is
7351: -- A Nonstandard operation OR
7344: raise l_processing_err_excp;
7345:
7346: elsif (
7347: (l_op_is_std = 0 and l_sj_intraoperation_step = WIP_CONSTANTS.TOMOVE)
7348: OR (l_op_rptd_times <> 1 and l_sj_intraoperation_step = WIP_CONSTANTS.TOMOVE)
7349: ) then
7350: -- At TO_MOVE, if current operation is
7351: -- A Nonstandard operation OR
7352: -- A standard operation but is not repeated in tgrtouting
7777: l_stmt_num := 207;
7778: UPDATE wsm_resulting_jobs_interface wrji
7779: SET starting_operation_seq_num = l_bos_op_seq_num
7780: WHERE wrji.header_id = l_header_id
7781: and wrji.process_status = WIP_CONSTANTS.RUNNING
7782: and wrji.internal_group_id = WSMPLOAD.G_GROUP_ID;
7783: -- EA: BUG 2686319
7784:
7785: IF (l_debug = 'Y') THEN
8163: where txn.header_id = wrji.header_id
8164: and wrji.starting_intraoperation_step =
8165: decode(txn.transaction_type_id,WSMPCNST.BONUS,1,wrji.starting_intraoperation_step)
8166: and wrji.starting_intraoperation_step in
8167: (WIP_CONSTANTS.QUEUE,
8168: WIP_CONSTANTS.TOMOVE));
8169:
8170:
8171: IF (SQL%ROWCOUNT > 0) THEN
8164: and wrji.starting_intraoperation_step =
8165: decode(txn.transaction_type_id,WSMPCNST.BONUS,1,wrji.starting_intraoperation_step)
8166: and wrji.starting_intraoperation_step in
8167: (WIP_CONSTANTS.QUEUE,
8168: WIP_CONSTANTS.TOMOVE));
8169:
8170:
8171: IF (SQL%ROWCOUNT > 0) THEN
8172:
8555:
8556: begin
8557:
8558: IF (l_txn_type = WSMPCNST.BONUS) THEN
8559: l_sj_intraoperation_step := WIP_CONSTANTS.QUEUE;
8560: ELSE
8561: l_stmt_num := 230;
8562: select intraoperation_step
8563: into l_sj_intraoperation_step
8564: from wsm_starting_jobs_interface
8565: where header_id = l_header_id;
8566: END IF;
8567: --For Option C, Queue or ToMove, execute this. For Option A, only if it's Queue.
8568: if (l_sj_intraoperation_step <> WIP_CONSTANTS.TOMOVE OR l_profile_value = 1)
8569: then
8570:
8571: --End SpUA bugfix
8572:
8661:
8662: l_stmt_num := 234;
8663: UPDATE wsm_resulting_jobs_interface wrji
8664: SET wrji.error_message = l_fnd_generic_err_msg
8665: , wrji.process_status = WIP_CONSTANTS.ERROR
8666: where wrji.header_id = l_header_id
8667: and wrji.wip_entity_name = NVL(l_job_name, wrji.wip_entity_name);
8668:
8669: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
8677: l_stmt_num := 235;
8678:
8679: Update wsm_starting_jobs_interface wsji
8680: SET wsji.error_message = l_fnd_generic_err_msg
8681: , wsji.process_status = WIP_CONSTANTS.ERROR
8682: Where wsji.header_id = l_header_id;
8683:
8684: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
8685: ||l_stmt_num||'): '
8692:
8693: l_stmt_num := 236;
8694: Update wsm_split_merge_txn_interface wsmti
8695: SET wsmti.error_message = l_fnd_generic_err_msg
8696: , wsmti.process_status = WIP_CONSTANTS.ERROR
8697: Where wsmti.header_id = l_header_id;
8698:
8699: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
8700: ||l_stmt_num||'): '
8700: ||l_stmt_num||'): '
8701: ||'Record for Header Id '
8702: ||l_header_id
8703: ||' set to ERROR in WSMTI.');
8704: p_err_num := WIP_CONSTANTS.ERROR;
8705: p_err_msg := l_fnd_err_msg;
8706: raise proc_error;
8707:
8708: End If;
8735:
8736: l_stmt_num := 238;
8737: UPDATE WSM_STARTING_JOBS_INTERFACE wsji
8738: SET wsji.error_message = l_fnd_generic_err_msg
8739: , wsji.process_status = WIP_CONSTANTS.ERROR
8740: WHERE wsji.header_id = l_header_id
8741: AND wsji.WIP_ENTITY_ID = l_wip_id;
8742:
8743: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
8748:
8749: l_stmt_num := 239;
8750: UPDATE WSM_RESULTING_JOBS_INTERFACE wrji
8751: SET wrji.error_message = l_fnd_generic_err_msg
8752: , wrji.process_status = WIP_CONSTANTS.ERROR
8753: WHERE wrji.header_id = l_header_id;
8754:
8755: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
8756: ||l_stmt_num||'): '
8760:
8761: l_stmt_num := 240;
8762: UPDATE WSM_SPLIT_MERGE_TXN_INTERFACE wsmti
8763: SET wsmti.error_message = l_fnd_generic_err_msg
8764: , wsmti.process_status = WIP_CONSTANTS.ERROR
8765: WHERE wsmti.header_id = l_header_id;
8766:
8767: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
8768: ||l_stmt_num||'): '
8797:
8798: l_stmt_num := 242;
8799: UPDATE WSM_STARTING_JOBS_INTERFACE wsji
8800: SET wsji.error_message = l_fnd_generic_err_msg
8801: , wsji.process_status = WIP_CONSTANTS.ERROR
8802: WHERE wsji.header_id = l_header_id
8803: AND wsji.WIP_ENTITY_ID = l_wip_id;
8804:
8805: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
8810:
8811: l_stmt_num := 243;
8812: UPDATE WSM_RESULTING_JOBS_INTERFACE wrji
8813: SET wrji.error_message = l_fnd_generic_err_msg
8814: , wrji.process_status = WIP_CONSTANTS.ERROR
8815: WHERE wrji.header_id = l_header_id;
8816:
8817: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
8818: ||l_stmt_num||'): '
8822:
8823: l_stmt_num := 244;
8824: UPDATE WSM_SPLIT_MERGE_TXN_INTERFACE wsmti
8825: SET wsmti.error_message = l_fnd_generic_err_msg
8826: , wsmti.process_status = WIP_CONSTANTS.ERROR
8827: WHERE wsmti.header_id = l_header_id;
8828:
8829: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
8830: ||l_stmt_num||'): '
8861:
8862: l_stmt_num := 246;
8863: UPDATE WSM_STARTING_JOBS_INTERFACE wsji
8864: SET wsji.error_message = l_fnd_generic_err_msg
8865: , wsji.process_status = WIP_CONSTANTS.ERROR
8866: WHERE wsji.header_id = l_header_id
8867: AND wsji.WIP_ENTITY_ID = l_wip_id;
8868:
8869: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
8874:
8875: l_stmt_num := 247;
8876: UPDATE WSM_RESULTING_JOBS_INTERFACE wrji
8877: SET wrji.error_message = l_fnd_generic_err_msg
8878: , wrji.process_status = WIP_CONSTANTS.ERROR
8879: WHERE wrji.header_id = l_header_id;
8880:
8881: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
8882: ||l_stmt_num||'): '
8886:
8887: l_stmt_num := 248;
8888: UPDATE WSM_SPLIT_MERGE_TXN_INTERFACE wsmti
8889: SET wsmti.error_message = l_fnd_generic_err_msg
8890: , wsmti.process_status = WIP_CONSTANTS.ERROR
8891: WHERE wsmti.header_id = l_header_id;
8892:
8893: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
8894: ||l_stmt_num||'): '
8935:
8936: l_stmt_num := 250;
8937: UPDATE WSM_RESULTING_JOBS_INTERFACE wrji
8938: SET wrji.error_message = l_fnd_generic_err_msg
8939: , wrji.process_status = WIP_CONSTANTS.ERROR
8940: WHERE wrji.header_id = l_header_id
8941: AND wrji.WIP_ENTITY_NAME = l_job_name;
8942:
8943: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
8947: ||' set to ERROR in WRJI for wip_entity_name='||l_job_name);
8948: l_stmt_num := 251;
8949: UPDATE WSM_STARTING_JOBS_INTERFACE wsji
8950: SET wsji.error_message = l_fnd_generic_err_msg
8951: , wsji.process_status = WIP_CONSTANTS.ERROR
8952: WHERE wsji.header_id = l_header_id;
8953:
8954: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
8955: ||l_stmt_num||'): '
8959:
8960: l_stmt_num := 252;
8961: UPDATE WSM_SPLIT_MERGE_TXN_INTERFACE wsmti
8962: SET wsmti.error_message = l_fnd_generic_err_msg
8963: , wsmti.process_status = WIP_CONSTANTS.ERROR
8964: WHERE wsmti.header_id = l_header_id;
8965:
8966: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
8967: ||l_stmt_num||'): '
8998:
8999: l_stmt_num := 254;
9000: UPDATE WSM_RESULTING_JOBS_INTERFACE wrji
9001: SET wrji.error_message = l_fnd_generic_err_msg
9002: , wrji.process_status = WIP_CONSTANTS.ERROR
9003: WHERE wrji.header_id = l_header_id
9004: AND wrji.WIP_ENTITY_NAME = l_job_name;
9005:
9006: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
9011:
9012: l_stmt_num := 255;
9013: UPDATE WSM_STARTING_JOBS_INTERFACE wsji
9014: SET wsji.error_message = l_fnd_generic_err_msg
9015: , wsji.process_status = WIP_CONSTANTS.ERROR
9016: WHERE wsji.header_id = l_header_id;
9017:
9018: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
9019: ||l_stmt_num||'): '
9023:
9024: l_stmt_num := 256;
9025: UPDATE WSM_SPLIT_MERGE_TXN_INTERFACE wsmti
9026: SET wsmti.error_message = l_fnd_generic_err_msg
9027: , wsmti.process_status = WIP_CONSTANTS.ERROR
9028: WHERE wsmti.header_id = l_header_id;
9029:
9030: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
9031: ||l_stmt_num||'): '
9062:
9063: l_stmt_num := 258;
9064: UPDATE WSM_RESULTING_JOBS_INTERFACE wrji
9065: SET wrji.error_message = l_fnd_generic_err_msg
9066: , wrji.process_status = WIP_CONSTANTS.ERROR
9067: WHERE wrji.header_id = l_header_id
9068: AND wrji.WIP_ENTITY_NAME = l_job_name;
9069:
9070: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
9075:
9076: l_stmt_num := 259;
9077: UPDATE WSM_STARTING_JOBS_INTERFACE wsji
9078: SET wsji.error_message = l_fnd_generic_err_msg
9079: , wsji.process_status = WIP_CONSTANTS.ERROR
9080: WHERE wsji.header_id = l_header_id;
9081:
9082: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
9083: ||l_stmt_num||'): '
9087:
9088: l_stmt_num := 260;
9089: UPDATE WSM_SPLIT_MERGE_TXN_INTERFACE wsmti
9090: SET wsmti.error_message = l_fnd_generic_err_msg
9091: , wsmti.process_status = WIP_CONSTANTS.ERROR
9092: WHERE wsmti.header_id = l_header_id;
9093:
9094: FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPVLDT.VALIDATE_RECORDS('
9095: ||l_stmt_num||'): '
9228:
9229: l_stmt_num := 263;
9230: UPDATE wsm_split_merge_txn_interface
9231: SET error_message = l_fnd_err_msg -- error_message = error_message || l_fnd_err_msg
9232: , process_status = WIP_CONSTANTS.ERROR -- WLTEnh
9233: WHERE header_id IN ( SELECT distinct header_id
9234: FROM wsm_starting_jobs_interface
9235: WHERE error_message IS NOT NULL)
9236: AND process_status = WIP_CONSTANTS.RUNNING;
9232: , process_status = WIP_CONSTANTS.ERROR -- WLTEnh
9233: WHERE header_id IN ( SELECT distinct header_id
9234: FROM wsm_starting_jobs_interface
9235: WHERE error_message IS NOT NULL)
9236: AND process_status = WIP_CONSTANTS.RUNNING;
9237:
9238:
9239: FND_MESSAGE.SET_NAME('WSM', 'WSM_ERR_IN_RESULT_LOT');
9240: l_fnd_err_msg := FND_MESSAGE.GET;
9241:
9242: l_stmt_num := 264;
9243: UPDATE wsm_split_merge_txn_interface
9244: SET error_message = l_fnd_err_msg -- error_message = error_message || l_fnd_err_msg
9245: , process_status = WIP_CONSTANTS.ERROR -- WLTEnh
9246: WHERE header_id IN ( SELECT distinct header_id
9247: FROM wsm_resulting_jobs_interface
9248: WHERE error_message IS NOT NULL)
9249: AND process_status = WIP_CONSTANTS.RUNNING;
9245: , process_status = WIP_CONSTANTS.ERROR -- WLTEnh
9246: WHERE header_id IN ( SELECT distinct header_id
9247: FROM wsm_resulting_jobs_interface
9248: WHERE error_message IS NOT NULL)
9249: AND process_status = WIP_CONSTANTS.RUNNING;
9250:
9251:
9252: l_stmt_num := 265;
9253:
9263: select 1 into l_errored_rows
9264: from wsm_split_merge_txn_interface
9265: Where internal_group_id = WSMPLOAD.G_GROUP_ID
9266: and header_id = WSMPLOAD.G_HEADER_ID
9267: and process_status = WIP_CONSTANTS.ERROR;
9268:
9269: Exception
9270: When no_data_found Then
9271: l_errored_rows := 0;