[Home] [Help]
119: l_stmt_num := 30;
120:
121: SELECT COUNT(wppb.PAC_PERIOD_ID)
122: INTO l_job
123: FROM WIP_PAC_PERIOD_BALANCES wppb
124: WHERE wppb.WIP_ENTITY_ID = p_entity_id
125: AND wppb.PAC_PERIOD_ID = p_prior_period_id
126: AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(p_line_id,-99));
127:
516: p_login_id p_login,
517: p_request_id p_request,
518: p_prog_app_id p_prog_app,
519: p_prog_id p_prog
520: FROM wip_pac_period_balances wppb
521: WHERE wppb.pac_period_id = p_prior_period_id
522: AND wppb.cost_group_id = p_cost_group_id
523: AND wppb.wip_entity_id = p_entity_id
524: AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(p_line_id,-99));
943: p_login_id p_login,
944: p_request_id p_request,
945: p_prog_app_id p_prog_app,
946: p_prog_id p_prog
947: FROM wip_pac_period_balances wppb
948: WHERE wppb.pac_period_id = p_pac_period_id
949: AND wppb.cost_group_id = p_cost_group_id
950: AND wppb.wip_entity_id = p_entity_id
951: AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(p_line_id,-99));
1000: | END of Third cursor |
1001: | END IF; |
1002: | END of Second Cursor |
1003: | End of First Cursor |
1004: | UPDATE table WIP_PAC_PERIOD_BALANCES (wppb) |
1005: | Check the job balance in this period for each cost element. |
1006: | If the value is negative then put these values in VAR columns |
1007: | END; |
1008: *----------------------------------------------------------------------------*/
1035: l_repetitive_schedule_id NUMBER;
1036:
1037: CURSOR c_wip_opseq IS
1038: SELECT wppb.operation_seq_num operation_seq_num
1039: FROM WIP_PAC_PERIOD_BALANCES wppb
1040: WHERE wppb.pac_period_id = p_pac_period_id
1041: AND wppb.cost_group_id = p_cost_group_id
1042: AND wppb.wip_entity_id = p_entity_id
1043: AND NVL(wppb.line_id, -99) = nvl(p_line_id,-99)
1830:
1831: END LOOP; -- End of Component Loop
1832:
1833: -----------------------------------------------------------------------------------
1834: -- Update the wip_pac_period_balances with relieved values
1835: -----------------------------------------------------------------------------------
1836: -- OUT columns will not be updated if p_net_qty = 0 and p_final_completion_flag <>1
1837: -- In this case only TEMP columns are updated
1838: ------------------------------------------------------------------------------------
1836: -- OUT columns will not be updated if p_net_qty = 0 and p_final_completion_flag <>1
1837: -- In this case only TEMP columns are updated
1838: ------------------------------------------------------------------------------------
1839: l_stmt_num := 270;
1840: UPDATE wip_pac_period_balances wppb
1841: SET wppb.pl_material_out = wppb.pl_material_out + decode(p_net_qty,0,
1842: decode(p_final_completion_flag,
1843: 0,0,
1844: NULL,0,
1939: AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
1940: AND cprocd.cost_element_id = 1; -- Material Cost Element
1941:
1942: l_stmt_num := 290;
1943: UPDATE WIP_PAC_PERIOD_BALANCES wppb
1944: -- New column to store the variance in case if total job balance is negative
1945: -- This column is not storing accumlated value
1946: SET wppb.pl_material_temp_var = wppb.pl_material_temp,
1947: -- This column is storing accumlated values of variance
1991: AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
1992: AND cprocd.cost_element_id = 2; -- Material Overhead Cost Element
1993:
1994: l_stmt_num := 310;
1995: UPDATE wip_pac_period_balances wppb
1996: -- New column to store the variance in case total job balance is negative
1997: SET wppb.pl_material_overhead_temp_var = wppb.pl_material_overhead_temp,
1998: -- This column is storing accumlated values of variance
1999: wppb.pl_material_overhead_var = nvl(wppb.pl_material_overhead_var,0) + nvl(wppb.pl_material_overhead_temp,0),
2041: AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
2042: AND cprocd.cost_element_id = 3; -- Resource Cost Element
2043:
2044: l_stmt_num := 330;
2045: UPDATE wip_pac_period_balances wppb
2046: -- New column to store the variance in case if total job balance is negative
2047: SET wppb.pl_resource_temp_var = wppb.pl_resource_temp,
2048: -- This column is storing accumlated values of variance
2049: wppb.pl_resource_var = nvl(wppb.pl_resource_var,0) + nvl(wppb.pl_resource_temp,0),
2091: AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
2092: AND cprocd.cost_element_id = 4; -- Outside Processing Cost Element
2093:
2094: l_stmt_num := 350;
2095: UPDATE wip_pac_period_balances wppb
2096: -- New column to store the varaince in case total job balance is negative
2097: SET wppb.pl_outside_processing_temp_var = wppb.pl_outside_processing_temp,
2098: -- This column is storing accumlated values of variance
2099: wppb.pl_outside_processing_var = nvl(wppb.pl_outside_processing_var,0) + nvl(wppb.pl_outside_processing_temp,0),
2141: AND nvl(cprocd.line_id, -99) = nvl(p_line_id,-99)
2142: AND cprocd.cost_element_id = 5; -- Overhead Processing Cost Element
2143:
2144: l_stmt_num := 370;
2145: UPDATE wip_pac_period_balances wppb
2146: -- New column to store the varaince in case total job balance is negative
2147: SET wppb.pl_overhead_temp_var = wppb.pl_overhead_temp,
2148: -- This column is storing accumlated values of variance
2149: wppb.pl_overhead_var = nvl(wppb.pl_overhead_var,0) + nvl(wppb.pl_overhead_temp,0),
2360: ----------------------------------------------------------------------
2361:
2362: l_stmt_num := 0;
2363:
2364: UPDATE wip_pac_period_balances wppb
2365: SET tl_resource_temp = 0,
2366: tl_overhead_temp = 0,
2367: tl_outside_processing_temp = 0,
2368: pl_material_temp = 0,
2393: IF (p_entity_type = 4) THEN
2394:
2395: l_stmt_num := 10;
2396:
2397: UPDATE wip_pac_period_balances wppb
2398: SET (tl_resource_out,
2399: tl_resource_temp,
2400: tl_outside_processing_out,
2401: tl_outside_processing_temp,
2450: (NVL(wppb.pl_overhead_in,0)-NVL(wppb.pl_overhead_out,0)
2451: - NVL(wppb.pl_overhead_var,0)),
2452: (NVL(wppb.pl_overhead_in,0)-NVL(wppb.pl_overhead_out,0)
2453: - NVL(wppb.pl_overhead_var,0))
2454: FROM wip_pac_period_balances wppb2,
2455: wip_flow_schedules wfs
2456: WHERE wppb2.pac_period_id = p_pac_period_id
2457: AND wppb2.cost_group_id = p_cost_group_id
2458: AND wppb2.wip_entity_id = p_entity_id
2496: l_stmt_num := 25;
2497: /* Final completion exists */
2498: IF p_final_completion_flag = 1 THEN
2499:
2500: UPDATE wip_pac_period_balances wppb
2501: SET (tl_resource_out,
2502: tl_resource_temp,
2503: tl_outside_processing_out,
2504: tl_outside_processing_temp,
2553: (NVL(wppb.tl_overhead_in,0)-NVL(wppb.tl_overhead_out,0)
2554: - NVL(wppb.tl_overhead_var,0)),
2555: 0
2556: )
2557: FROM wip_pac_period_balances wppb2
2558: WHERE wppb2.pac_period_id =p_pac_period_id
2559: AND wppb2.cost_group_id = p_cost_group_id
2560: AND wppb2.wip_entity_id = p_entity_id
2561: AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
2566: AND wppb.wip_entity_id = p_entity_id
2567: AND NVL(wppb.line_id,-99) = NVL(p_line_id,-99)
2568: AND wppb.operation_seq_num <= wppb.operation_seq_num;
2569: ELSE /* No Final completion exists */
2570: UPDATE wip_pac_period_balances wppb
2571: SET (tl_resource_out,
2572: tl_resource_temp,
2573: tl_outside_processing_out,
2574: tl_outside_processing_temp,
2668: NVL(wppb2.relieved_assembly_units,0) - nvl(wppb2.unrelieved_scrap_quantity,0))
2669: ),
2670: 0
2671: )
2672: FROM wip_pac_period_balances wppb2
2673: WHERE wppb2.pac_period_id = p_pac_period_id
2674: AND wppb2.cost_group_id = p_cost_group_id
2675: AND wppb2.wip_entity_id = p_entity_id
2676: AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
2684: decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
2685: END IF;
2686: ELSIF (p_net_qty < 0) THEN
2687: l_stmt_num := 28;
2688: UPDATE wip_pac_period_balances wppb
2689: SET (tl_resource_out,
2690: tl_resource_temp,
2691: tl_outside_processing_out,
2692: tl_outside_processing_temp,
2731: NVL(wppb2.tl_overhead_out,0) *
2732: p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
2733: 0,1,wppb2.relieved_assembly_units),
2734: 0)
2735: FROM wip_pac_period_balances wppb2
2736: WHERE wppb2.pac_period_id = p_prior_period_id
2737: AND wppb2.cost_group_id = p_cost_group_id
2738: AND wppb2.wip_entity_id = p_entity_id
2739: AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
2761: -- If this period is not the first period
2762: IF p_prior_period_id <> -1 then
2763:
2764: l_stmt_num := 32;
2765: UPDATE wip_pac_period_balances wppb
2766: SET (tl_resource_out,
2767: tl_resource_temp,
2768: tl_outside_processing_out,
2769: tl_outside_processing_temp,
2808: NVL(wppb2.tl_overhead_out,0) *
2809: l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
2810: 0,1,wppb2.relieved_assembly_units),
2811: 0)
2812: FROM wip_pac_period_balances wppb2
2813: WHERE wppb2.pac_period_id = p_prior_period_id
2814: AND wppb2.cost_group_id = p_cost_group_id
2815: AND wppb2.wip_entity_id = p_entity_id
2816: AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
2825: decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
2826:
2827: END IF; /* p_final_completion_flag = 1 */
2828: l_stmt_num := 34;
2829: UPDATE wip_pac_period_balances wppb
2830: SET (tl_resource_out,
2831: tl_resource_temp,
2832: tl_outside_processing_out,
2833: tl_outside_processing_temp,
2883: (NVL(wppb2.tl_overhead_in,0)-NVL(wppb2.tl_overhead_out,0)
2884: - NVL(wppb2.tl_overhead_var,0)),
2885: 0
2886: )
2887: FROM wip_pac_period_balances wppb2
2888: WHERE wppb2.pac_period_id = p_pac_period_id
2889: AND wppb2.cost_group_id = p_cost_group_id
2890: AND wppb2.wip_entity_id = p_entity_id
2891: AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
2900: decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
2901:
2902: ELSE /* No Final Completion */
2903: l_stmt_num := 36;
2904: UPDATE wip_pac_period_balances wppb
2905: SET (
2906: tl_resource_temp,
2907: tl_outside_processing_temp,
2908: tl_overhead_temp
2928: NVL(tl_overhead_out,0) /
2929: DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
2930: 0
2931: )
2932: FROM wip_pac_period_balances
2933: WHERE pac_period_id = p_prior_period_id
2934: AND cost_group_id = p_cost_group_id
2935: AND wip_entity_id = p_entity_id
2936: AND NVL(line_id,-99) = NVL(p_line_id,-99)
2948: IF (p_net_qty > 0) THEN -- completion are more than returns
2949:
2950: IF p_final_completion_flag = 1 THEN /* Final completion exists */
2951: l_stmt_num := 40;
2952: UPDATE wip_pac_period_balances wppb
2953: SET (tl_resource_out,
2954: tl_resource_temp,
2955: tl_outside_processing_out,
2956: tl_outside_processing_temp,
3099: (NVL(wppb.pl_overhead_in,0)-NVL(wppb.pl_overhead_out,0)-nvl(wppb.pl_overhead_in_apull,0)
3100: - NVL(wppb.pl_overhead_var,0)) + nvl(wppb.pl_overhead_in_apull,0),
3101: 0
3102: )
3103: FROM wip_pac_period_balances wppb2
3104: WHERE wppb2.pac_period_id = p_pac_period_id
3105: AND wppb2.cost_group_id = p_cost_group_id
3106: AND wppb2.wip_entity_id = p_entity_id
3107: AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
3113: AND NVL(wppb.line_id,-99) = NVL(p_line_id,-99)
3114: AND wppb.operation_seq_num <= wppb.operation_seq_num;
3115: ELSE /* No Final completion exists */
3116: l_stmt_num := 42;
3117: UPDATE wip_pac_period_balances wppb
3118: SET (tl_resource_out,
3119: tl_resource_temp,
3120: tl_outside_processing_out,
3121: tl_outside_processing_temp,
3414: ),
3415: NVL(wppb.scrap_pull_overhead,0) +
3416: decode(p_scrap,1, p_net_qty * nvl(wppb2.pl_overhead_in_apull,0)/(wppb2.relieved_scrap_qty + nvl(l_net_completion,0)),0)
3417:
3418: FROM wip_pac_period_balances wppb2
3419: WHERE wppb2.pac_period_id = p_pac_period_id
3420: AND wppb2.cost_group_id = p_cost_group_id
3421: AND wppb2.wip_entity_id = p_entity_id
3422: AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
3430: decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
3431: END IF;
3432: ELSIF p_net_qty < 0 THEN -- This is a net return
3433: l_stmt_num := 44;
3434: UPDATE wip_pac_period_balances wppb
3435: SET (tl_resource_out,
3436: tl_resource_temp,
3437: tl_outside_processing_out,
3438: tl_outside_processing_temp,
3553: NVL(wppb2.pl_overhead_out,0) *
3554: p_net_qty/decode(NVL(wppb2.relieved_assembly_units,0),
3555: 0,1,wppb2.relieved_assembly_units),
3556: 0)
3557: FROM wip_pac_period_balances wppb2
3558: WHERE wppb2.pac_period_id = p_prior_period_id
3559: AND wppb2.cost_group_id = p_cost_group_id
3560: AND wppb2.wip_entity_id = p_entity_id
3561: AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
3584:
3585: IF (p_prior_period_id <> -1) then
3586:
3587: l_stmt_num := 50;
3588: UPDATE wip_pac_period_balances wppb
3589: SET ( tl_resource_out,
3590: tl_outside_processing_out,
3591: tl_overhead_out,
3592: pl_material_out,
3651: NVL(wppb2.pl_overhead_out,0) *
3652: l_assembly_return_cnt/decode(NVL(wppb2.relieved_assembly_units,0),
3653: 0,1,wppb2.relieved_assembly_units),
3654: 0)
3655: FROM wip_pac_period_balances wppb2
3656: WHERE wppb2.pac_period_id = p_prior_period_id
3657: AND wppb2.cost_group_id = p_cost_group_id
3658: AND wppb2.wip_entity_id = p_entity_id
3659: AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
3667: decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
3668:
3669: END IF; /* end of period <> -1 */
3670: l_stmt_num := 52;
3671: UPDATE wip_pac_period_balances wppb
3672: SET ( tl_resource_out,
3673: tl_resource_temp,
3674: tl_outside_processing_out,
3675: tl_outside_processing_temp,
3826: - NVL(wppb.pl_overhead_var,0))
3827: + nvl(wppb.pl_overhead_in_apull,0),
3828: 0
3829: )
3830: FROM wip_pac_period_balances wppb2
3831: WHERE wppb2.pac_period_id = p_pac_period_id
3832: AND wppb2.cost_group_id = p_cost_group_id
3833: AND wppb2.wip_entity_id = p_entity_id
3834: AND NVL(wppb2.line_id,-99) = NVL(p_line_id,-99)
3842: AND wppb.operation_seq_num <=
3843: decode(p_scrap,1,p_op_seq,wppb.operation_seq_num);
3844: ELSE
3845: l_stmt_num := 54;
3846: UPDATE wip_pac_period_balances wppb
3847: SET (
3848: tl_resource_temp,
3849: tl_outside_processing_temp,
3850: tl_overhead_temp,
3910: NVL(pl_overhead_out,0) /
3911: DECODE(NVL(relieved_assembly_units,0),0,1,relieved_assembly_units),
3912: 0
3913: )
3914: FROM wip_pac_period_balances
3915: WHERE pac_period_id = p_prior_period_id
3916: AND cost_group_id = p_cost_group_id
3917: AND wip_entity_id = p_entity_id
3918: AND NVL(line_id,-99) = NVL(p_line_id,-99)
3930: ------------------------------------------------------------------
3931: -- Update wppb.RELIEVED_ASSEMBLY_UNITS
3932: ------------------------------------------------------------------
3933: l_stmt_num := 56;
3934: UPDATE wip_pac_period_balances wppb
3935: SET wppb.relieved_assembly_units =
3936: NVL(wppb.relieved_assembly_units,0) + p_net_qty
3937: WHERE wppb.pac_period_id = p_pac_period_id
3938: AND wppb.cost_group_id = p_cost_group_id
4540:
4541: IF (c_scrap_rec.from_op_step <> 5 and c_scrap_rec.to_op_step = 5) then
4542:
4543: l_stmt_num := 20;
4544: UPDATE WIP_PAC_PERIOD_BALANCES
4545: SET unrelieved_scrap_quantity = nvl(unrelieved_scrap_quantity,0) + c_scrap_rec.pri_qty
4546: WHERE wip_entity_id = c_scrap_rec.wip_entity
4547: AND nvl(line_id,-99) = decode(wip_entity_type, 4, -99, nvl(c_scrap_rec.line,-99))
4548: AND operation_seq_num <= c_scrap_rec.to_op_seq
4555: IF (c_scrap_rec.from_op_step = 5 and c_scrap_rec.to_op_step <> 5) then
4556:
4557: l_stmt_num := 25;
4558:
4559: UPDATE WIP_PAC_PERIOD_BALANCES
4560: SET unrelieved_scrap_quantity = nvl(unrelieved_scrap_quantity,0) - c_scrap_rec.pri_qty
4561: WHERE wip_entity_id = c_scrap_rec.wip_entity
4562: AND nvl(line_id,-99) = decode(wip_entity_type, 4, -99, nvl(c_scrap_rec.line,-99))
4563: AND operation_seq_num <= c_scrap_rec.from_op_seq
4573:
4574: IF (c_scrap_rec.from_op_seq > c_scrap_rec.to_op_seq) THEN
4575:
4576: l_stmt_num := 35;
4577: UPDATE WIP_PAC_PERIOD_BALANCES
4578: SET unrelieved_scrap_quantity = nvl(unrelieved_scrap_quantity,0) - c_scrap_rec.pri_qty
4579: WHERE wip_entity_id = c_scrap_rec.wip_entity
4580: AND nvl(line_id,-99) = decode(wip_entity_type, 4, -99, nvl(c_scrap_rec.line,-99))
4581: AND operation_seq_num > c_scrap_rec.to_op_seq
4587: ELSE
4588:
4589: l_stmt_num := 40;
4590:
4591: UPDATE WIP_PAC_PERIOD_BALANCES
4592: SET unrelieved_scrap_quantity = nvl(unrelieved_scrap_quantity,0) + c_scrap_rec.pri_qty
4593: WHERE wip_entity_id = c_scrap_rec.wip_entity
4594: AND nvl(line_id,-99) = decode(wip_entity_type, 4, -99, nvl(c_scrap_rec.line,-99))
4595: AND operation_seq_num > c_scrap_rec.from_op_seq
4607:
4608: fnd_file.put_line(fnd_file.log,' Scrap_Qty Update << ');
4609: l_stmt_num := 45;
4610:
4611: UPDATE WIP_PAC_PERIOD_BALANCES
4612: SET relieved_scrap_qty = nvl(relieved_scrap_qty,0) + c_ent_rec.net_scrap
4613: WHERE wip_entity_id = c_ent_rec.entity_id
4614: AND nvl(line_id,-99) = decode(wip_entity_type, 4, -99, nvl(c_ent_rec.line_id,-99))
4615: AND operation_seq_num <= c_ent_rec.op_seq