[Home] [Help]
92: -- rj.primary_item_id item_id,
93: -- rj.completion_subinventory subinv_name,
94: -- rj.rowid rj_rowid --SpUA Add
95: -- FROM wsm_resulting_jobs_interface rj,
96: -- wsm_split_merge_txn_interface txn
97: -- WHERE rj.header_id = p_header_id -- WLTEnh Add
98: -- AND txn.header_id = rj.header_id
99: -- AND (txn.transaction_type_id in
100: -- (WSMPCNST.BONUS))
118: rj.primary_item_id item_id,
119: rj.completion_subinventory subinv_name,
120: rj.rowid rj_rowid --SpUA Add
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
132: rj.job_type job_type,
133: txn.organization_id org_id,
134: rj.header_id header_id
135: FROM wsm_resulting_jobs_interface rj,
136: wsm_split_merge_txn_interface txn
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
147: rj.primary_item_id item_id,
148: rj.completion_subinventory subinv_name,
149: txn.transaction_type_id txn_type_id
150: FROM wsm_resulting_jobs_interface rj,
151: wsm_split_merge_txn_interface txn
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
166: rj.wip_entity_name,
167: /* ST : fix bug 3766859 : added job type */
168: rj.job_type
169: FROM wsm_resulting_jobs_interface rj,
170: wsm_split_merge_txn_interface txn
171: WHERE rj.net_quantity is NULL
172: AND rj.header_id = p_header_id
173: AND txn.header_id = rj.header_id
174: AND txn.transaction_type_id in
183: CURSOR C_COPRODUCTS_SUPPLY(p_header_id NUMBER) is
184: SELECT distinct (rj.header_id) header_id,
185: txn.organization_id org_id
186: FROM wsm_resulting_jobs_interface rj,
187: wsm_split_merge_txn_interface txn
188: WHERE ( rj.coproducts_supply is NULL OR
189: rj.coproducts_supply NOT IN (1,2))
190: AND txn.header_id = rj.header_id
191: AND rj.header_id = p_header_id
294: -- SET class_code = l_class_code
295: -- WHERE rj.class_code is null
296: -- AND rj.header_id = c_no_class_code_rj_rec.header_id
297: -- AND EXISTS ( SELECT null
298: -- FROM wsm_split_merge_txn_interface txn
299: -- WHERE txn.header_id = rj.header_id
300: -- AND (txn.transaction_type_id in
301: -- (WSMPCNST.BONUS)))
302: -- (WSMPCNST.UPDATE_ASSEMBLY,
358: AND wrji.header_id = c_no_class_code_rj_rec.header_id
359:
360: /* ST bug fix 3150692 review comments : Start : We check for the txn type outside.. hence Commenting out the below check
361: AND EXISTS ( SELECT null
362: FROM wsm_split_merge_txn_interface txn
363: WHERE txn.header_id = wrji.header_id
364: AND (txn.transaction_type_id in
365: -- Start : Fix for bug #3150692 --
366: (WSMPCNST.BONUS)))
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
470: AND (txn2.transaction_type_id not in
471: -- Start : Fix for bug #3150692 --
472: (WSMPCNST.BONUS)));
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
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:
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
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
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,
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
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
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
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 ))
1372: TXN.TRANSACTION_DATE,
1373: TXN.TRANSACTION_ID,
1374: TXN.HEADER_ID
1375: , TXN.ORGANIZATION_ID -- 2804945
1376: FROM WSM_SPLIT_MERGE_TXN_INTERFACE TXN,
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
1387: TXN.TRANSACTION_DATE,
1388: TXN.TRANSACTION_ID,
1389: TXN.HEADER_ID
1390: , TXN.ORGANIZATION_ID -- 2804945
1391: FROM WSM_SPLIT_MERGE_TXN_INTERFACE TXN,
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
1405: , transaction_date
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--
1426: , wsji.intraoperation_step
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--
1464: --VJ: CodeRVW: End Additions for NSLBJ--
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
1559:
1560: l_stmt_num := 2;
1561:
1562: /*-----------------------------------------------------------------+
1563: | validate transaction_type in wsm_split_merge_txn_interface table |
1564: +-----------------------------------------------------------------*/
1565:
1566: Begin
1567: l_stmt_num := 3;
1602:
1603: End;
1604:
1605: /*-----------------------------------------------------------------+
1606: | validate organization_id in wsm_split_merge_txn_interface table |
1607: +-----------------------------------------------------------------*/
1608:
1609: l_stmt_num := 4;
1610:
1649:
1650: End;
1651:
1652: /*-----------------------------------------------------------------+
1653: | validate acct_period in wsm_split_merge_txn_interface table |
1654: +-----------------------------------------------------------------*/
1655:
1656: l_stmt_num := 6;
1657:
1679:
1680: --End NL Bugfix 3126650
1681:
1682: /*-----------------------------------------------------------------+
1683: | validate reason_id in wsm_split_merge_txn_interface table |
1684: +-----------------------------------------------------------------*/
1685:
1686: l_stmt_num := 7;
1687:
1812: If l_wsmti_error_flag = 1 Then -- Only a Single UPDATE on WSMTI
1813:
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:
1920: Begin
1921: l_stmt_num := 20;
1922:
1923: select null into l_dummy
1924: from wsm_split_merge_txn_interface txn
1925: where txn.header_id = l_cur_wsji_rec.wsji_header_id
1926: and txn.internal_group_id = WSMPLOAD.G_GROUP_ID;
1927:
1928: Exception
1967: Begin
1968: l_stmt_num := 22;
1969:
1970: SELECT organization_id into l_cur_wsji_rec.organization_id
1971: FROM wsm_split_merge_txn_interface
1972: WHERE header_id = l_cur_wsji_rec.wsji_header_id
1973: AND internal_group_id = WSMPLOAD.G_GROUP_ID;
1974:
1975: Exception
2013: Begin
2014: l_stmt_num := 24;
2015:
2016: select null into l_dummy
2017: from wsm_split_merge_txn_interface txn
2018: where txn.header_id = l_cur_wsji_rec.wsji_header_id
2019: and txn.organization_id = l_cur_wsji_rec.organization_id
2020: and txn.internal_group_id = WSMPLOAD.G_GROUP_ID;
2021:
2062: From wsm_starting_jobs_interface wsji
2063: Where wsji.header_id = l_cur_wsji_rec.wsji_header_id
2064: and wsji.representative_flag = 'Y'
2065: and exists (select 1
2066: from wsm_split_merge_txn_interface wsmti
2067: where wsmti.header_id = l_cur_wsji_rec.wsji_header_id
2068: and wsmti.transaction_type_id = WSMPCNST.MERGE);
2069:
2070: If l_dummy > 1 Then
2126: l_stmt_num := 28;
2127:
2128: SELECT 'Y' into
2129: l_cur_wsji_rec.representative_flag
2130: FROM wsm_split_merge_txn_interface wsmti
2131: WHERE wsmti.header_id = l_cur_wsji_rec.wsji_header_id
2132: AND wsmti.transaction_type_id IN
2133: (WSMPCNST.SPLIT,
2134: WSMPCNST.UPDATE_ASSEMBLY,
2536: Begin
2537: l_stmt_num := 38;
2538:
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:
2598:
2599: l_stmt_num := 41;
2600:
2601: SELECT null into l_dummy
2602: FROM wsm_split_merge_txn_interface txn
2603: WHERE txn.header_id = l_cur_wsji_rec.wsji_header_id
2604: AND txn.transaction_type_id = WSMPCNST.BONUS;
2605:
2606: If SQL%ROWCOUNT > 0 Then
3104: ||l_cur_wsji_rec.wsji_header_id
3105: ||' set to ERROR in WRJI.');
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:
3462:
3463: l_stmt_num := 70;
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
3498: update wsm_resulting_jobs_interface wrji
3499: SET wrji.error_message = l_fnd_generic_err_msg
3500: WHERE wrji.header_id = l_header_id
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);
4475: UPDATE wsm_resulting_jobs_interface wrji
4476: SET wrji.error_message = l_fnd_generic_err_msg
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
4608: SET wrji.error_message = l_fnd_generic_err_msg
4609: WHERE wrji.header_id = l_header_id
4610: and not exists ((select null
4611: from wsm_starting_jobs_interface sj,
4612: wsm_split_merge_txn_interface txn,
4613: wip_entities we
4614: where txn.header_id = wrji.header_id
4615: and sj.header_id = txn.header_id
4616: and wrji.wip_entity_name = we.wip_entity_name
4621: WSMPCNST.UPDATE_QUANTITY)
4622: and we.wip_entity_id = sj.wip_entity_id)
4623: union
4624: select null
4625: from wsm_split_merge_txn_interface txn
4626: where txn.header_id = wrji.header_id
4627: and txn.transaction_type_id IN
4628: (WSMPCNST.SPLIT,
4629: WSMPCNST.MERGE,
4732: SET wrji.error_message = l_fnd_generic_err_msg
4733: WHERE wrji.header_id = l_header_id
4734: and not exists (select null
4735: from mtl_system_items mtl,
4736: wsm_split_merge_txn_interface txn
4737: where txn.header_id = wrji.header_id
4738: and txn.transaction_type_id in
4739: (WSMPCNST.SPLIT,
4740: WSMPCNST.MERGE,
4746: and mtl.pick_components_flag = 'N'
4747: and mtl.eng_item_flag = 'N'
4748: union
4749: select null
4750: from wsm_split_merge_txn_interface txn1
4751: where txn1.header_id = wrji.header_id
4752: and txn1.transaction_type_id in
4753: (WSMPCNST.UPDATE_ROUTING,
4754: WSMPCNST.UPDATE_QUANTITY,
4788: SET wrji.error_message = l_fnd_generic_err_msg
4789: WHERE wrji.header_id = l_header_id
4790: and not exists (select null
4791: from mtl_system_items mtl,
4792: wsm_split_merge_txn_interface txn
4793: where txn.header_id = wrji.header_id
4794: and txn.transaction_type_id in
4795: (WSMPCNST.SPLIT,
4796: WSMPCNST.MERGE,
4800: and mtl.inventory_item_id = wrji.primary_item_id
4801: and mtl.lot_control_code = 2
4802: union
4803: select null
4804: from wsm_split_merge_txn_interface txn1
4805: where txn1.header_id = wrji.header_id
4806: and txn1.transaction_type_id in
4807: (WSMPCNST.UPDATE_ROUTING,
4808: WSMPCNST.UPDATE_QUANTITY,
4840: SET wrji.error_message = l_fnd_generic_err_msg
4841: WHERE wrji.header_id = l_header_id
4842: and not exists (select null
4843: from mtl_system_items mtl,
4844: wsm_split_merge_txn_interface txn
4845: where txn.header_id = wrji.header_id
4846: and txn.transaction_type_id in
4847: (WSMPCNST.SPLIT,
4848: WSMPCNST.MERGE,
4852: and mtl.inventory_item_id = wrji.primary_item_id
4853: and mtl.serial_number_control_code = 1
4854: union
4855: select null
4856: from wsm_split_merge_txn_interface txn1
4857: where txn1.header_id = wrji.header_id
4858: and txn1.transaction_type_id in
4859: (WSMPCNST.UPDATE_ROUTING,
4860: WSMPCNST.UPDATE_QUANTITY,
4895: SET wrji.error_message = l_fnd_generic_err_msg
4896: WHERE wrji.header_id = l_header_id
4897: and exists (select null
4898: from wsm_starting_jobs_interface sj,
4899: wsm_split_merge_txn_interface txn,
4900: wip_discrete_jobs dj
4901: where sj.wip_entity_id = dj.wip_entity_id
4902: and dj.primary_item_id = nvl(wrji.primary_item_id,dj.primary_item_id)
4903: and txn.header_id = wrji.header_id
4935: WHERE wrji.header_id = l_header_id
4936: and not exists (select 1
4937: from gl_code_combinations gcc,
4938: org_organization_definitions ood,
4939: wsm_split_merge_txn_interface txn
4940: where txn.organization_id = ood.organization_id
4941: and txn.header_id = wrji.header_id
4942: and txn.transaction_type_id IN
4943: (WSMPCNST.BONUS,
4949: nvl(gcc.start_date_active, txn.transaction_date)
4950: and nvl(gcc.end_date_active, txn.transaction_date)
4951: union
4952: select 1
4953: from wsm_split_merge_txn_interface txn1
4954: where txn1.transaction_type_id NOT IN
4955: (WSMPCNST.BONUS,
4956: WSMPCNST.UPDATE_QUANTITY)
4957: and txn1.header_id = wrji.header_id);
4987: SET wrji.error_message = l_fnd_generic_err_msg
4988: WHERE wrji.header_id = l_header_id
4989: and ( (wrji.start_quantity < 0 OR nvl(wrji.start_quantity,0) = 0)
4990: or (exists (select null
4991: from wsm_split_merge_txn_interface txn,
4992: wsm_starting_jobs_interface sj,
4993: wip_discrete_jobs dj
4994: where txn.header_id = wrji.header_id
4995: and txn.header_id = sj.header_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
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,
6005: l_stmt_num := 148;
6006: SELECT wsji.intraoperation_step
6007: INTO l_st_iop
6008: FROM wsm_starting_jobs_interface wsji,
6009: wsm_split_merge_txn_interface wsmti
6010: WHERE wsji.header_id = l_header_id
6011: AND wsmti.header_id = l_header_id
6012: AND (wsmti.transaction_type_id in
6013: (WSMPCNST.UPDATE_ASSEMBLY, WSMPCNST.UPDATE_ROUTING )
6090: and wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
6091: and exists (
6092: select 1
6093: from wsm_starting_jobs_interface sj,
6094: wsm_split_merge_txn_interface txn
6095: where txn.header_id = wrji.header_id
6096: and txn.header_id = sj.header_id
6097: and (txn.transaction_type_id in
6098: (WSMPCNST.UPDATE_ASSEMBLY, WSMPCNST.UPDATE_ROUTING )
6161: and wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
6162: and exists (
6163: select 1
6164: from wsm_starting_jobs_interface sj,
6165: wsm_split_merge_txn_interface txn
6166: where txn.header_id = wrji.header_id
6167: and txn.header_id = sj.header_id
6168: and (txn.transaction_type_id in
6169: (WSMPCNST.UPDATE_ASSEMBLY, WSMPCNST.UPDATE_ROUTING )
7433: UPDATE wsm_resulting_jobs_interface wrji
7434: SET wrji.error_message = l_fnd_generic_err_msg
7435: WHERE wrji.header_id = l_header_id
7436: AND exists (SELECT null
7437: FROM wsm_split_merge_txn_interface txn
7438: WHERE txn.header_id = wrji.header_id
7439: AND txn.transaction_type_id = WSMPCNST.BONUS)
7440: AND wrji.starting_std_op_id is not null
7441: AND wrji.starting_operation_code is not null
7500: INTO l_count
7501: FROM wsm_resulting_jobs_interface wrji
7502: WHERE wrji.header_id = l_header_id
7503: AND exists (SELECT null
7504: FROM wsm_split_merge_txn_interface txn
7505: WHERE txn.header_id = wrji.header_id
7506: AND txn.transaction_type_id = WSMPCNST.BONUS);
7507:
7508: IF (l_debug = 'Y') THEN
7521: l_start_op_seq_num
7522: FROM wsm_resulting_jobs_interface wrji
7523: WHERE wrji.header_id = l_header_id
7524: AND exists (SELECT null
7525: FROM wsm_split_merge_txn_interface txn
7526: WHERE txn.header_id = wrji.header_id
7527: AND txn.transaction_type_id = WSMPCNST.BONUS);
7528:
7529: IF (l_debug = 'Y') THEN
7722: bom_operation_sequences bos,
7723: wsm_resulting_jobs_interface wrji
7724: WHERE wrji.header_id = l_header_id
7725: AND exists (SELECT null
7726: FROM wsm_split_merge_txn_interface txn
7727: WHERE txn.header_id = wrji.header_id
7728: AND txn.transaction_type_id = WSMPCNST.BONUS)
7729: AND bos.routing_sequence_id = wrji.common_routing_sequence_id
7730: AND nvl(bos.standard_operation_id, -9999) = nvl(bso.standard_operation_id, -9999)
7757: bom_operation_sequences bos,
7758: wsm_resulting_jobs_interface wrji
7759: WHERE wrji.header_id = l_header_id
7760: AND exists (SELECT null
7761: FROM wsm_split_merge_txn_interface txn
7762: WHERE txn.header_id = wrji.header_id
7763: AND txn.transaction_type_id = WSMPCNST.BONUS)
7764: AND bos.routing_sequence_id = wrji.common_routing_sequence_id
7765: AND nvl(bos.standard_operation_id, -9999) = nvl(bso.standard_operation_id, -9999)
7837: UPDATE wsm_resulting_jobs_interface wrji
7838: SET wrji.starting_operation_seq_num = l_bos_op_seq_num
7839: WHERE wrji.header_id = l_header_id
7840: AND exists (SELECT null
7841: FROM wsm_split_merge_txn_interface txn
7842: WHERE txn.header_id = wrji.header_id
7843: AND txn.transaction_type_id = WSMPCNST.BONUS);
7844:
7845: IF (SQL%ROWCOUNT > 0) THEN
7889: FROM bom_operation_sequences bos,
7890: wsm_resulting_jobs_interface wrji
7891: where wrji.header_id = l_header_id
7892: AND exists (SELECT null
7893: FROM wsm_split_merge_txn_interface txn
7894: WHERE txn.header_id = wrji.header_id
7895: AND txn.transaction_type_id = WSMPCNST.BONUS)
7896: AND bos.routing_sequence_id = wrji.common_routing_sequence_id
7897: and bos.operation_seq_num = l_start_op_seq_num
7953: UPDATE wsm_resulting_jobs_interface wrji
7954: SET wrji.starting_operation_code = l_bso_op_code
7955: WHERE wrji.header_id = l_header_id
7956: AND exists (SELECT null
7957: FROM wsm_split_merge_txn_interface txn
7958: WHERE txn.header_id = wrji.header_id
7959: AND txn.transaction_type_id = WSMPCNST.BONUS);
7960:
7961: IF (SQL%ROWCOUNT > 0) THEN
7992: UPDATE wsm_resulting_jobs_interface wrji
7993: SET wrji.starting_std_op_id = l_bos_std_op_id
7994: WHERE wrji.header_id = l_header_id
7995: AND exists (SELECT null
7996: FROM wsm_split_merge_txn_interface txn
7997: WHERE txn.header_id = wrji.header_id
7998: AND txn.transaction_type_id = WSMPCNST.BONUS);
7999:
8000: IF (SQL%ROWCOUNT > 0) THEN
8033: UPDATE wsm_resulting_jobs_interface wrji
8034: SET wrji.error_message = l_fnd_generic_err_msg
8035: WHERE wrji.header_id = l_header_id
8036: AND EXISTS (SELECT null
8037: FROM wsm_split_merge_txn_interface txn
8038: WHERE txn.header_id = wrji.header_id
8039: AND txn.transaction_type_id = WSMPCNST.BONUS -- BA NSO-WLT
8040: AND wrji.starting_operation_seq_num is null); -- EA NSO-WLT
8041:
8073: UPDATE wsm_resulting_jobs_interface wrji
8074: SET wrji.error_message = l_fnd_generic_err_msg
8075: WHERE wrji.header_id = l_header_id
8076: AND exists (SELECT null
8077: FROM wsm_split_merge_txn_interface txn
8078: WHERE txn.header_id = wrji.header_id
8079: AND txn.transaction_type_id = WSMPCNST.BONUS)
8080: AND nvl(wrji.starting_std_op_id, -9999) = nvl(l_bos_std_op_id, -9999)
8081: AND not exists
8158: SET wrji.error_message = l_fnd_generic_err_msg
8159: WHERE wrji.header_id = l_header_id
8160: and wrji.starting_intraoperation_step is not null
8161: and NOT EXISTS (select null
8162: from wsm_split_merge_txn_interface txn
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
8201:
8202: l_stmt_num := 217;
8203: SELECT transaction_type_id
8204: INTO l_txn_type
8205: FROM wsm_split_merge_txn_interface wsmti
8206: WHERE wsmti.header_id = l_header_id;
8207:
8208: IF (l_txn_type = WSMPCNST.UPDATE_ROUTING) THEN
8209: -- need to make sure that completion date
8535: -- earlier
8536:
8537: -- SELECT transaction_type_id
8538: -- INTO l_txn_type
8539: -- FROM wsm_split_merge_txn_interface wsmti
8540: -- WHERE wsmti.header_id = l_header_id;
8541:
8542: IF (l_txn_type in ( WSMPCNST.BONUS, WSMPCNST.UPDATE_ROUTING, WSMPCNST.UPDATE_ASSEMBLY)
8543: --SpUA begin
8690: End If;
8691:
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:
8758: ||l_header_id
8759: ||' set to ERROR in WRJI ');
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:
8820: ||l_header_id
8821: ||' set to ERROR in WRJI ');
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:
8884: ||l_header_id
8885: ||' set to ERROR in WRJI ');
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:
8957: ||l_header_id
8958: ||' set to ERROR in WSJI ');
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:
9021: ||l_header_id
9022: ||' set to ERROR in WSJI ');
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:
9085: ||l_header_id
9086: ||' set to ERROR in WSJI ');
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:
9182:
9183: SELECT nvl(wrji.starting_operation_seq_num, -1), wrji.starting_operation_code
9184: INTO l_wrji_starting_op_seq_num, l_wrji_starting_op_code
9185: FROM wsm_resulting_jobs_interface wrji,
9186: wsm_split_merge_txn_interface wsmti
9187: WHERE wrji.header_id = l_header_id
9188: AND wsmti.header_id = wrji.header_id
9189: AND (wsmti.transaction_type_id in (3,5) -- Upd rtg/assy
9190: OR (wsmti.transaction_type_id = 1
9226: FND_MESSAGE.SET_NAME('WSM', 'WSM_ERR_IN_START_LOT');
9227: l_fnd_err_msg := FND_MESSAGE.GET;
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
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
9260: Begin
9261: l_stmt_num := 266;
9262:
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: