[Home] [Help]
1142: begin
1143: l_stmt_num := 50;
1144:
1145: --fix for bug5990761 allow update of job name for unreleased jobs
1146: select decode(wdj.status_type, WIP_CONSTANTS.UNRELEASED,NVL(p_job_name,we.wip_entity_name),we.wip_entity_name),we.wip_entity_name
1147: into p_job_name,l_old_job_name
1148: from wip_discrete_jobs wdj,
1149: wip_entities we
1150: where wdj.wip_entity_id = p_wip_entity_id
1148: from wip_discrete_jobs wdj,
1149: wip_entities we
1150: where wdj.wip_entity_id = p_wip_entity_id
1151: and wdj.status_type in (
1152: WIP_CONSTANTS.UNRELEASED,
1153: WIP_CONSTANTS.RELEASED,
1154: WIP_CONSTANTS.HOLD,
1155: WIP_CONSTANTS.CANCELLED)
1156: and wdj.wip_entity_id = we.wip_entity_id
1149: wip_entities we
1150: where wdj.wip_entity_id = p_wip_entity_id
1151: and wdj.status_type in (
1152: WIP_CONSTANTS.UNRELEASED,
1153: WIP_CONSTANTS.RELEASED,
1154: WIP_CONSTANTS.HOLD,
1155: WIP_CONSTANTS.CANCELLED)
1156: and wdj.wip_entity_id = we.wip_entity_id
1157: and we.organization_id = p_org_id;
1150: where wdj.wip_entity_id = p_wip_entity_id
1151: and wdj.status_type in (
1152: WIP_CONSTANTS.UNRELEASED,
1153: WIP_CONSTANTS.RELEASED,
1154: WIP_CONSTANTS.HOLD,
1155: WIP_CONSTANTS.CANCELLED)
1156: and wdj.wip_entity_id = we.wip_entity_id
1157: and we.organization_id = p_org_id;
1158: exception
1151: and wdj.status_type in (
1152: WIP_CONSTANTS.UNRELEASED,
1153: WIP_CONSTANTS.RELEASED,
1154: WIP_CONSTANTS.HOLD,
1155: WIP_CONSTANTS.CANCELLED)
1156: and wdj.wip_entity_id = we.wip_entity_id
1157: and we.organization_id = p_org_id;
1158: exception
1159: when others then
1299: cumulative_yield,/*Added for bugfix:7248992 */
1300: reverse_cumulative_yield/*Added for bugfix:7248992 */
1301: from wsm_lot_job_dtl_interface
1302: where parent_header_id = p_parent_header_id
1303: and process_status = WIP_CONSTANTS.RUNNING;
1304:
1305: cursor c_wljdi_indp is
1306: select record_id,
1307: -- ST Fix for bug 4285032 :
1373: from wsm_lot_job_dtl_interface
1374: where parent_header_id IS NULL
1375: and wip_entity_id = p_wip_entity_id
1376: and organization_id = p_org_id
1377: and process_status = WIP_CONSTANTS.RUNNING
1378: and NVL(group_id, -1) = NVL(p_group_id, -1);
1379:
1380: l_stmt_num number;
1381: l_d number;
2140:
2141: if v_wljdi_src_phantom_item_id(l_d) is not null then
2142: if ( (v_wljdi_src_phantom_item_id(l_d)= v_wljdi_inventory_item_id_old(l_d) and
2143: l_sub_type = WSM_SUB_DEL) OR
2144: (v_wljdi_wip_supply_type(l_d)= WIP_CONSTANTS.PHANTOM and
2145: l_sub_type = WSM_SUB_ADD and
2146: v_wljdi_src_phantom_item_id(l_d)= v_wljdi_inventory_item_id_new(l_d)) ) then
2147: v_wljdi_src_phantom_item_id(l_d):=0;
2148: end if;
2146: v_wljdi_src_phantom_item_id(l_d)= v_wljdi_inventory_item_id_new(l_d)) ) then
2147: v_wljdi_src_phantom_item_id(l_d):=0;
2148: end if;
2149: else
2150: if (v_wljdi_wip_supply_type(l_d)= WIP_CONSTANTS.PHANTOM and
2151: l_sub_type = WSM_SUB_ADD) then
2152: v_wljdi_src_phantom_item_id(l_d):= 0;
2153: else
2154: v_wljdi_src_phantom_item_id(l_d):= -1;
2279: end if;
2280:
2281: -- Validate Count Point
2282: if (v_wljdi_count_point_type(l_d) is not null and
2283: v_wljdi_count_point_type(l_d) not in (WIP_CONSTANTS.YES_AUTO,
2284: WIP_CONSTANTS.NO_AUTO,
2285: WIP_CONSTANTS.NO_DIRECT)) then
2286: raise e_invalid_count_point;
2287: end if;
2280:
2281: -- Validate Count Point
2282: if (v_wljdi_count_point_type(l_d) is not null and
2283: v_wljdi_count_point_type(l_d) not in (WIP_CONSTANTS.YES_AUTO,
2284: WIP_CONSTANTS.NO_AUTO,
2285: WIP_CONSTANTS.NO_DIRECT)) then
2286: raise e_invalid_count_point;
2287: end if;
2288: if (v_wljdi_count_point_type(l_d) is null) then
2281: -- Validate Count Point
2282: if (v_wljdi_count_point_type(l_d) is not null and
2283: v_wljdi_count_point_type(l_d) not in (WIP_CONSTANTS.YES_AUTO,
2284: WIP_CONSTANTS.NO_AUTO,
2285: WIP_CONSTANTS.NO_DIRECT)) then
2286: raise e_invalid_count_point;
2287: end if;
2288: if (v_wljdi_count_point_type(l_d) is null) then
2289: v_wljdi_count_point_type(l_d) :=0;
2290: end if;
2291:
2292: -- Validate Backflush flag.
2293: if (v_wljdi_backflush_flag(l_d) is not null and
2294: v_wljdi_backflush_flag(l_d) not in (WIP_CONSTANTS.WBF_BF_NOPAGE ,
2295: WIP_CONSTANTS.WBF_BF_PAGE)) then
2296: raise e_invalid_backflush_flag;
2297: end if;
2298: if (v_wljdi_backflush_flag(l_d) is null) then
2291:
2292: -- Validate Backflush flag.
2293: if (v_wljdi_backflush_flag(l_d) is not null and
2294: v_wljdi_backflush_flag(l_d) not in (WIP_CONSTANTS.WBF_BF_NOPAGE ,
2295: WIP_CONSTANTS.WBF_BF_PAGE)) then
2296: raise e_invalid_backflush_flag;
2297: end if;
2298: if (v_wljdi_backflush_flag(l_d) is null) then
2299: v_wljdi_backflush_flag(l_d) :=0;
3867: end if;
3868:
3869: if l_basis_type is null then
3870: l_basis_type := l_res_default_basis_type;
3871: elsif l_basis_type not in (WIP_CONSTANTS.PER_ITEM,
3872: WIP_CONSTANTS.PER_LOT) then
3873: raise e_inv_res_basis_type;
3874: end if;
3875:
3868:
3869: if l_basis_type is null then
3870: l_basis_type := l_res_default_basis_type;
3871: elsif l_basis_type not in (WIP_CONSTANTS.PER_ITEM,
3872: WIP_CONSTANTS.PER_LOT) then
3873: raise e_inv_res_basis_type;
3874: end if;
3875:
3876: if l_res_autocharge_type = WIP_CONSTANTS.PO_MOVE then
3872: WIP_CONSTANTS.PER_LOT) then
3873: raise e_inv_res_basis_type;
3874: end if;
3875:
3876: if l_res_autocharge_type = WIP_CONSTANTS.PO_MOVE then
3877: raise e_inv_res_charge_type;
3878: end if;
3879:
3880: if l_res_autocharge_type is null then
3881: raise e_inv_resource_charge_type;
3882: end if;
3883:
3884: if l_sch_flag is null then
3885: l_sch_flag := WIP_CONSTANTS.SCHED_NO;
3886: elsif l_sch_flag not in (WIP_CONSTANTS.SCHED_YES,
3887: WIP_CONSTANTS.SCHED_NO,
3888: WIP_CONSTANTS.SCHED_PRIOR,
3889: WIP_CONSTANTS.SCHED_NEXT) then
3882: end if;
3883:
3884: if l_sch_flag is null then
3885: l_sch_flag := WIP_CONSTANTS.SCHED_NO;
3886: elsif l_sch_flag not in (WIP_CONSTANTS.SCHED_YES,
3887: WIP_CONSTANTS.SCHED_NO,
3888: WIP_CONSTANTS.SCHED_PRIOR,
3889: WIP_CONSTANTS.SCHED_NEXT) then
3890: raise e_inv_res_sch_flag;
3883:
3884: if l_sch_flag is null then
3885: l_sch_flag := WIP_CONSTANTS.SCHED_NO;
3886: elsif l_sch_flag not in (WIP_CONSTANTS.SCHED_YES,
3887: WIP_CONSTANTS.SCHED_NO,
3888: WIP_CONSTANTS.SCHED_PRIOR,
3889: WIP_CONSTANTS.SCHED_NEXT) then
3890: raise e_inv_res_sch_flag;
3891: end if;
3884: if l_sch_flag is null then
3885: l_sch_flag := WIP_CONSTANTS.SCHED_NO;
3886: elsif l_sch_flag not in (WIP_CONSTANTS.SCHED_YES,
3887: WIP_CONSTANTS.SCHED_NO,
3888: WIP_CONSTANTS.SCHED_PRIOR,
3889: WIP_CONSTANTS.SCHED_NEXT) then
3890: raise e_inv_res_sch_flag;
3891: end if;
3892:
3885: l_sch_flag := WIP_CONSTANTS.SCHED_NO;
3886: elsif l_sch_flag not in (WIP_CONSTANTS.SCHED_YES,
3887: WIP_CONSTANTS.SCHED_NO,
3888: WIP_CONSTANTS.SCHED_PRIOR,
3889: WIP_CONSTANTS.SCHED_NEXT) then
3890: raise e_inv_res_sch_flag;
3891: end if;
3892:
3893: if l_assg_units <=0 or l_assg_units is null then
5805: where exists ( select 1
5806: from wip_discrete_jobs
5807: where wip_entity_id = p_wip_entity_id
5808: and organization_id = p_org_id
5809: and job_type = WIP_CONSTANTS.STANDARD
5810: and primary_item_id = l_item_id );
5811:
5812: raise e_no_assy_as_comp;
5813:
5831: end;*/
5832:
5833: ---Validate wip supply type
5834:
5835: if l_wip_supply_type= WIP_CONSTANTS.PHANTOM then
5836: raise e_phantom_comp;
5837: end if;
5838:
5839: if l_wip_supply_type not in (WIP_CONSTANTS.PUSH,
5835: if l_wip_supply_type= WIP_CONSTANTS.PHANTOM then
5836: raise e_phantom_comp;
5837: end if;
5838:
5839: if l_wip_supply_type not in (WIP_CONSTANTS.PUSH,
5840: WIP_CONSTANTS.ASSY_PULL,
5841: WIP_CONSTANTS.OP_PULL,
5842: WIP_CONSTANTS.BULK,
5843: WIP_CONSTANTS.VENDOR) or l_wip_supply_type is null then
5836: raise e_phantom_comp;
5837: end if;
5838:
5839: if l_wip_supply_type not in (WIP_CONSTANTS.PUSH,
5840: WIP_CONSTANTS.ASSY_PULL,
5841: WIP_CONSTANTS.OP_PULL,
5842: WIP_CONSTANTS.BULK,
5843: WIP_CONSTANTS.VENDOR) or l_wip_supply_type is null then
5844: raise e_invalid_wip_supply_type;
5837: end if;
5838:
5839: if l_wip_supply_type not in (WIP_CONSTANTS.PUSH,
5840: WIP_CONSTANTS.ASSY_PULL,
5841: WIP_CONSTANTS.OP_PULL,
5842: WIP_CONSTANTS.BULK,
5843: WIP_CONSTANTS.VENDOR) or l_wip_supply_type is null then
5844: raise e_invalid_wip_supply_type;
5845: end if;
5838:
5839: if l_wip_supply_type not in (WIP_CONSTANTS.PUSH,
5840: WIP_CONSTANTS.ASSY_PULL,
5841: WIP_CONSTANTS.OP_PULL,
5842: WIP_CONSTANTS.BULK,
5843: WIP_CONSTANTS.VENDOR) or l_wip_supply_type is null then
5844: raise e_invalid_wip_supply_type;
5845: end if;
5846:
5839: if l_wip_supply_type not in (WIP_CONSTANTS.PUSH,
5840: WIP_CONSTANTS.ASSY_PULL,
5841: WIP_CONSTANTS.OP_PULL,
5842: WIP_CONSTANTS.BULK,
5843: WIP_CONSTANTS.VENDOR) or l_wip_supply_type is null then
5844: raise e_invalid_wip_supply_type;
5845: end if;
5846:
5847: --check if component already defined for given job and operation.
5896: if l_quantity_per_assembly is null or l_quantity_per_assembly=0 then
5897: raise e_invalid_qpa;
5898: end if;
5899:
5900: if l_mrp_net_flag is null or l_mrp_net_flag not in (WIP_CONSTANTS.YES,WIP_CONSTANTS.NO)then
5901: raise e_invalid_mrp_net_flag;
5902: end if;
5903:
5904: -- For Adhoc components Bill QPA is considered as 1.
5903:
5904: -- For Adhoc components Bill QPA is considered as 1.
5905: p_v_comp(l_i).component_yield := round(1/l_quantity_per_assembly,6);
5906:
5907: if(l_wip_supply_type in (WIP_CONSTANTS.ASSY_PULL,WIP_CONSTANTS.OP_PULL) and
5908: l_supply_subinventory IS NULL) then
5909:
5910: select wip_supply_subinventory, wip_supply_locator_id
5911: into l_supply_subinventory, l_supply_locator_id
5942: when others then
5943: raise e_invalid_item;
5944: end;*/
5945:
5946: if l_wip_supply_type not in (WIP_CONSTANTS.PUSH,
5947: WIP_CONSTANTS.ASSY_PULL,
5948: WIP_CONSTANTS.OP_PULL,
5949: WIP_CONSTANTS.BULK,
5950: WIP_CONSTANTS.VENDOR,
5943: raise e_invalid_item;
5944: end;*/
5945:
5946: if l_wip_supply_type not in (WIP_CONSTANTS.PUSH,
5947: WIP_CONSTANTS.ASSY_PULL,
5948: WIP_CONSTANTS.OP_PULL,
5949: WIP_CONSTANTS.BULK,
5950: WIP_CONSTANTS.VENDOR,
5951: WIP_CONSTANTS.PHANTOM) or l_wip_supply_type is null then
5944: end;*/
5945:
5946: if l_wip_supply_type not in (WIP_CONSTANTS.PUSH,
5947: WIP_CONSTANTS.ASSY_PULL,
5948: WIP_CONSTANTS.OP_PULL,
5949: WIP_CONSTANTS.BULK,
5950: WIP_CONSTANTS.VENDOR,
5951: WIP_CONSTANTS.PHANTOM) or l_wip_supply_type is null then
5952: raise e_invalid_wip_supply_type;
5945:
5946: if l_wip_supply_type not in (WIP_CONSTANTS.PUSH,
5947: WIP_CONSTANTS.ASSY_PULL,
5948: WIP_CONSTANTS.OP_PULL,
5949: WIP_CONSTANTS.BULK,
5950: WIP_CONSTANTS.VENDOR,
5951: WIP_CONSTANTS.PHANTOM) or l_wip_supply_type is null then
5952: raise e_invalid_wip_supply_type;
5953: end if;
5946: if l_wip_supply_type not in (WIP_CONSTANTS.PUSH,
5947: WIP_CONSTANTS.ASSY_PULL,
5948: WIP_CONSTANTS.OP_PULL,
5949: WIP_CONSTANTS.BULK,
5950: WIP_CONSTANTS.VENDOR,
5951: WIP_CONSTANTS.PHANTOM) or l_wip_supply_type is null then
5952: raise e_invalid_wip_supply_type;
5953: end if;
5954:
5947: WIP_CONSTANTS.ASSY_PULL,
5948: WIP_CONSTANTS.OP_PULL,
5949: WIP_CONSTANTS.BULK,
5950: WIP_CONSTANTS.VENDOR,
5951: WIP_CONSTANTS.PHANTOM) or l_wip_supply_type is null then
5952: raise e_invalid_wip_supply_type;
5953: end if;
5954:
5955: --check if component already defined for given job and operation.
6022: if l_quantity_per_assembly is null or l_quantity_per_assembly=0 then
6023: raise e_invalid_qpa;
6024: end if;
6025:
6026: if l_mrp_net_flag is null or l_mrp_net_flag not in (WIP_CONSTANTS.YES,WIP_CONSTANTS.NO)then
6027: raise e_invalid_mrp_net_flag;
6028: end if;
6029:
6030: -- For Adhoc components Bill QPA is considered as 1.
6029:
6030: -- For Adhoc components Bill QPA is considered as 1.
6031: p_v_comp(l_i).component_yield := round(1/l_quantity_per_assembly,6);
6032:
6033: if(l_wip_supply_type in (WIP_CONSTANTS.ASSY_PULL,WIP_CONSTANTS.OP_PULL) and
6034: l_supply_subinventory IS NULL) then
6035:
6036: select wip_supply_subinventory, wip_supply_locator_id
6037: into l_supply_subinventory, l_supply_locator_id
6277: error_code,
6278: error_msg,
6279: num_of_children
6280: from wsm_lot_job_interface
6281: where process_status = WIP_CONSTANTS.PENDING
6282: and NVL(transaction_date, creation_date) <= sysdate+1
6283: and NVL(group_id, -99) = NVL(p_group_id, NVL(group_id, -99))
6284: and load_type in (5,6)
6285: order by load_type;
6294: process_status,
6295: error_code,
6296: error_msg
6297: from wsm_lot_job_dtl_interface
6298: where process_status = WIP_CONSTANTS.PENDING
6299: and parent_header_id IS NULL
6300: and NVL(group_id, -99) = NVL(p_group_id, NVL(group_id, -99))
6301: and transaction_date <= sysdate+1;
6302:
6384: WHILE l_h <= v_wlji_header_id.last
6385: LOOP
6386: v_wlji_err_code(l_h) := 0;
6387: v_wlji_err_msg(l_h) := '';
6388: v_wlji_process_status(l_h) := WIP_CONSTANTS.RUNNING;
6389:
6390: Declare
6391: e_org_weid_error exception;
6392: Begin -- {{ check org/job_name for this header_id
6424: Exception
6425:
6426: when e_org_weid_error then
6427: l_stmt_num := 70;
6428: v_wlji_process_status(l_h) := WIP_CONSTANTS.ERROR;
6429: v_wlji_err_code(l_h) := -1;
6430: v_wlji_err_msg(l_h) := l_err_msg;
6431: fnd_file.put_line(fnd_file.log,
6432: 'Error (WLJI.Header_Id = ' || v_wlji_header_id(l_h) || ') : '
6477: WHILE l_d <= v_wljdi_record_id.last
6478: LOOP
6479: v_wljdi_err_code(l_d) := 0;
6480: v_wljdi_err_msg(l_d) := NULL;
6481: v_wljdi_process_status(l_d) := WIP_CONSTANTS.RUNNING;
6482:
6483: Declare
6484: e_org_weid_error exception;
6485: Begin -- {{ check org/job_name for this header_id
6516:
6517: Exception
6518: when e_org_weid_error then
6519: l_stmt_num := 130;
6520: v_wljdi_process_status(l_d) := WIP_CONSTANTS.ERROR;
6521: v_wljdi_err_code(l_d) := -1;
6522: v_wljdi_err_msg(l_d) := l_err_msg;
6523: fnd_file.put_line(fnd_file.log,
6524: 'Error: WLJDI(Record_Id = ' || v_wljdi_record_id(l_d) ||') '
6648: if(l_h_rows > 0) then
6649: l_h := v_wlji_header_id.first;
6650: WHILE l_h <= v_wlji_header_id.last
6651: LOOP
6652: if v_wlji_process_status(l_h) <> WIP_CONSTANTS.ERROR then
6653: l_job_name_hash := dbms_utility.get_hash_value(
6654: v_wlji_job_name(l_h) || v_wlji_org_id(l_h),
6655: 37, 1073741824);
6656: if not l_v_job_gid.exists(l_job_name_hash) then -- one new job name
6677: if(l_d_rows > 0) then
6678: l_d := v_wljdi_record_id.first;
6679: WHILE l_d <= v_wljdi_record_id.last
6680: LOOP
6681: if v_wljdi_process_status(l_d) <> WIP_CONSTANTS.ERROR then
6682: l_job_name_hash := dbms_utility.get_hash_value(
6683: v_wljdi_job_name(l_d) || v_wljdi_org_id(l_d),
6684: 37, 1073741824);
6685: if not l_v_job_gid.exists(l_job_name_hash) then -- a new job
6945: num_of_children
6946: --, NVL(infinite_schedule_flag, 'N')
6947: from wsm_lot_job_interface
6948: where group_id = p_group_id
6949: and process_status = 2 -- WIP_CONSTANTS.RUNNING;
6950: order by organization_id,
6951: load_type,
6952: NVL(transaction_date, creation_date),
6953: priority,
6957: select unique
6958: wip_entity_id,
6959: organization_id
6960: from wsm_lot_job_dtl_interface
6961: where process_status = WIP_CONSTANTS.RUNNING
6962: and parent_header_id IS NULL
6963: and group_id = p_group_id
6964: and transaction_date <= sysdate+1
6965: order by organization_id;
7500:
7501: l_stmt_num := 115;
7502: -- Validate: STATUS TYPE
7503: if v_wlji_status_type(v_idx) not in (
7504: WIP_CONSTANTS.UNRELEASED,
7505: WIP_CONSTANTS.RELEASED,
7506: WIP_CONSTANTS.HOLD,
7507: WIP_CONSTANTS.CANCELLED
7508: ) then
7501: l_stmt_num := 115;
7502: -- Validate: STATUS TYPE
7503: if v_wlji_status_type(v_idx) not in (
7504: WIP_CONSTANTS.UNRELEASED,
7505: WIP_CONSTANTS.RELEASED,
7506: WIP_CONSTANTS.HOLD,
7507: WIP_CONSTANTS.CANCELLED
7508: ) then
7509: l_aux_mesg := '';
7502: -- Validate: STATUS TYPE
7503: if v_wlji_status_type(v_idx) not in (
7504: WIP_CONSTANTS.UNRELEASED,
7505: WIP_CONSTANTS.RELEASED,
7506: WIP_CONSTANTS.HOLD,
7507: WIP_CONSTANTS.CANCELLED
7508: ) then
7509: l_aux_mesg := '';
7510: handle_invalid_field('STATUS TYPE', l_aux_mesg, l_stmt_num);
7503: if v_wlji_status_type(v_idx) not in (
7504: WIP_CONSTANTS.UNRELEASED,
7505: WIP_CONSTANTS.RELEASED,
7506: WIP_CONSTANTS.HOLD,
7507: WIP_CONSTANTS.CANCELLED
7508: ) then
7509: l_aux_mesg := '';
7510: handle_invalid_field('STATUS TYPE', l_aux_mesg, l_stmt_num);
7511: l_error_code := -1;
7587: -- Now wip_entity_id is available, check job status
7588: begin
7589: l_stmt_num := 140.2;
7590: -- For unreleased jobs allow job name update if wip_entity_id is not null - fix for bug5990761
7591: select decode(wdj.status_type, WIP_CONSTANTS.UNRELEASED, NVL(v_wlji_job_name(v_idx),we.wip_entity_name), we.wip_entity_name)
7592: into v_wlji_job_name(v_idx)
7593: from wip_discrete_jobs wdj,
7594: wip_entities we
7595: where wdj.wip_entity_id = v_wlji_wip_entity_id(v_idx)
7593: from wip_discrete_jobs wdj,
7594: wip_entities we
7595: where wdj.wip_entity_id = v_wlji_wip_entity_id(v_idx)
7596: and wdj.status_type in (
7597: WIP_CONSTANTS.UNRELEASED,
7598: WIP_CONSTANTS.RELEASED,
7599: WIP_CONSTANTS.HOLD,
7600: WIP_CONSTANTS.CANCELLED)
7601: and wdj.wip_entity_id = we.wip_entity_id
7594: wip_entities we
7595: where wdj.wip_entity_id = v_wlji_wip_entity_id(v_idx)
7596: and wdj.status_type in (
7597: WIP_CONSTANTS.UNRELEASED,
7598: WIP_CONSTANTS.RELEASED,
7599: WIP_CONSTANTS.HOLD,
7600: WIP_CONSTANTS.CANCELLED)
7601: and wdj.wip_entity_id = we.wip_entity_id
7602: and we.organization_id = v_wlji_org_id(v_idx);
7595: where wdj.wip_entity_id = v_wlji_wip_entity_id(v_idx)
7596: and wdj.status_type in (
7597: WIP_CONSTANTS.UNRELEASED,
7598: WIP_CONSTANTS.RELEASED,
7599: WIP_CONSTANTS.HOLD,
7600: WIP_CONSTANTS.CANCELLED)
7601: and wdj.wip_entity_id = we.wip_entity_id
7602: and we.organization_id = v_wlji_org_id(v_idx);
7603: exception
7596: and wdj.status_type in (
7597: WIP_CONSTANTS.UNRELEASED,
7598: WIP_CONSTANTS.RELEASED,
7599: WIP_CONSTANTS.HOLD,
7600: WIP_CONSTANTS.CANCELLED)
7601: and wdj.wip_entity_id = we.wip_entity_id
7602: and we.organization_id = v_wlji_org_id(v_idx);
7603: exception
7604: when others then
8813: -----------------------------------------------------
8814:
8815: l_stmt_num := 380;
8816: -- {{ Validate Date_Released, job_type 1 and 3
8817: if (v_wlji_status_type(v_idx) = WIP_CONSTANTS.UNRELEASED) and
8818: (v_wlji_date_released(v_idx) is not null ) then
8819:
8820: v_wlji_date_released(v_idx) := null;
8821: if lbji_debug = 'Y' then
8821: if lbji_debug = 'Y' then
8822: fnd_file.put_line(fnd_file.log,
8823: 'Date_Released is ignored for unreleased job');
8824: end if;
8825: elsif (v_wlji_status_type(v_idx) = WIP_CONSTANTS.RELEASED ) then
8826: if (v_wlji_date_released(v_idx) > sysdate ) then
8827: l_error_code := -1;
8828: handle_errmsg_name('WIP', 'WIP_INVALID_RELEASE_DATE', l_stmt_num);
8829: l_error_count := l_error_count + 1;
9982: -- 2. user updates only quantity (increases) for a released job, status is unchanged
9983: -- 3. user updates staus to released, and increases quantity.
9984:
9985: if WSMPLCVA.v_org(v_wlji_org_id(v_idx)).PO_CREATION_TIME
9986: <> WIP_CONSTANTS.MANUAL_CREATION
9987: then
9988: if (v_wlji_status_type(v_idx) = 3 and l_cur_status_type = 1) then
9989: if wsmputil.check_osp_operation(
9990: v_wlji_wip_entity_id(v_idx),
9997: p_organization_id => v_wlji_org_id(v_idx),
9998: p_repetitive_schedule_id => null,
9999: p_operation_seq_num => l_osp_op_seq_num,
10000: p_resource_seq_num => null,
10001: p_run_reqimport => wip_constants.no);
10002: end if; -- check_osp_operation
10003: end if;
10004: l_stmt_num := 740.1;
10005: if (v_wlji_start_quantity(v_idx) > l_cur_start_qty) AND
10011: p_repetitive_schedule_id => null,
10012: p_added_quantity => (v_wlji_start_quantity(v_idx) - l_cur_start_qty),
10013: p_op_seq => null);
10014: end if;
10015: end if; -- WIP_CONSTANTS.MANUAL_CREATION
10016: -- }} osp end
10017:
10018: l_stmt_num := 750;
10019: -- {{ Validate net-quantity
10177:
10178: -- BA: BUG 3479666
10179: ELSIF v_wlji_scheduling_method(v_idx) = 1 THEN
10180: if(v_wlji_fusd(v_idx) is not null) then
10181: l_scheduling_mode := WIP_CONSTANTS.FORWARDS;
10182: else
10183: l_scheduling_mode := WIP_CONSTANTS.BACKWARDS;
10184: end if;
10185: -- EA: BUG 3479666
10179: ELSIF v_wlji_scheduling_method(v_idx) = 1 THEN
10180: if(v_wlji_fusd(v_idx) is not null) then
10181: l_scheduling_mode := WIP_CONSTANTS.FORWARDS;
10182: else
10183: l_scheduling_mode := WIP_CONSTANTS.BACKWARDS;
10184: end if;
10185: -- EA: BUG 3479666
10186:
10187: ELSIF v_wlji_scheduling_method(v_idx) = 2 THEN
10955: -- last_unit_completion_date),
10956: -- ED: BUG 3479666
10957: scheduled_quantity
10958: = round(v_wlji_start_quantity(v_idx),
10959: wip_constants.max_displayed_precision),
10960: quantity_in_queue
10961: = decode(v_wlji_status_type(v_idx),
10962: 1, 0,
10963: 3, decode(l_cur_status_type,
10963: 3, decode(l_cur_status_type,
10964: 1, decode(operation_seq_num,
10965: g_op_seq_incr,
10966: round(v_wlji_start_quantity(v_idx),
10967: wip_constants.max_displayed_precision),
10968: quantity_in_queue),
10969: decode(l_qntydiff,
10970: 1, decode(operation_seq_num,
10971: g_op_seq_incr,
10969: decode(l_qntydiff,
10970: 1, decode(operation_seq_num,
10971: g_op_seq_incr,
10972: round(v_wlji_start_quantity(v_idx),
10973: wip_constants.max_displayed_precision),
10974: quantity_in_queue),
10975: quantity_in_queue)),
10976: quantity_in_queue),
10977: last_updated_by = v_wlji_last_updt_by(v_idx),
11160: l_stmt_num := 950.3;
11161: wsm_reservations_pvt.modify_reservations_jobupdate (
11162:
11163: p_wip_entity_id => v_wlji_wip_entity_id(v_idx),
11164: p_old_net_qty => round(v_wlji_net_quantity(v_idx),wip_constants.max_displayed_precision),
11165: p_new_net_qty => round(v_wlji_net_quantity(v_idx),wip_constants.max_displayed_precision),
11166: p_inventory_item_id => v_wlji_item_id(v_idx),
11167: p_org_id => v_wlji_org_id(v_idx),
11168: p_status_type => v_wlji_status_type(v_idx),
11161: wsm_reservations_pvt.modify_reservations_jobupdate (
11162:
11163: p_wip_entity_id => v_wlji_wip_entity_id(v_idx),
11164: p_old_net_qty => round(v_wlji_net_quantity(v_idx),wip_constants.max_displayed_precision),
11165: p_new_net_qty => round(v_wlji_net_quantity(v_idx),wip_constants.max_displayed_precision),
11166: p_inventory_item_id => v_wlji_item_id(v_idx),
11167: p_org_id => v_wlji_org_id(v_idx),
11168: p_status_type => v_wlji_status_type(v_idx),
11169: x_return_status => l_return_status,
11262:
11263: wsm_reservations_pvt.modify_reservations_jobupdate (
11264: p_wip_entity_id => v_wlji_wip_entity_id(v_idx),
11265: p_old_net_qty => l_net_qty_old ,
11266: p_new_net_qty => round(v_wlji_net_quantity(v_idx),wip_constants.max_displayed_precision),
11267: p_inventory_item_id => v_wlji_item_id(v_idx),
11268: p_org_id => v_wlji_org_id(v_idx),
11269: p_status_type => v_wlji_status_type(v_idx),
11270: x_return_status => l_return_status,
11300: alternate_bom_designator = v_wlji_alt_bom(v_idx),
11301: alternate_routing_designator= v_wlji_alt_rtg(v_idx),
11302: firm_planned_flag = v_wlji_firm_planned_flag(v_idx),
11303: start_quantity = nvl(round(v_wlji_start_quantity(v_idx),
11304: wip_constants.max_displayed_precision),
11305: wdj.start_quantity),
11306: net_quantity = nvl(round(v_wlji_net_quantity(v_idx),
11307: wip_constants.max_displayed_precision),
11308: wdj.net_quantity),
11303: start_quantity = nvl(round(v_wlji_start_quantity(v_idx),
11304: wip_constants.max_displayed_precision),
11305: wdj.start_quantity),
11306: net_quantity = nvl(round(v_wlji_net_quantity(v_idx),
11307: wip_constants.max_displayed_precision),
11308: wdj.net_quantity),
11309: lot_number = decode(wdj.status_type, WIP_CONSTANTS.UNRELEASED,
11310: v_wlji_job_name(v_idx),wdj.lot_number), -- fix for bug5990761
11311: status_type = nvl(v_wlji_status_type(v_idx),wdj.status_type),
11305: wdj.start_quantity),
11306: net_quantity = nvl(round(v_wlji_net_quantity(v_idx),
11307: wip_constants.max_displayed_precision),
11308: wdj.net_quantity),
11309: lot_number = decode(wdj.status_type, WIP_CONSTANTS.UNRELEASED,
11310: v_wlji_job_name(v_idx),wdj.lot_number), -- fix for bug5990761
11311: status_type = nvl(v_wlji_status_type(v_idx),wdj.status_type),
11312: date_released = v_wlji_date_released(v_idx), -- bug 2697295
11313: scheduled_start_date = decode(l_txnexist, 0,
11434: from wsm_lot_based_jobs
11435: where wip_entity_id = v_wlji_wip_entity_id(v_idx);
11436: l_stmt_num := 985.1;
11437: if(l_on_reco_path = 'Y' ) then
11438: if(l_scheduling_mode = WIP_CONSTANTS.FORWARDS) then
11439: l_start_date := v_wlji_fusd(v_idx);
11440: l_end_date := null;
11441: else
11442: l_start_date := null;
11473:
11474: l_stmt_num := 990;
11475: -- {{ Releasing job, insert period balance
11476: if (v_wlji_load_type(v_idx) = 6 AND
11477: v_wlji_status_type(v_idx) = WIP_CONSTANTS.RELEASED and
11478: l_cur_status_type = WIP_CONSTANTS.UNRELEASED)
11479: then
11480:
11481: --
11474: l_stmt_num := 990;
11475: -- {{ Releasing job, insert period balance
11476: if (v_wlji_load_type(v_idx) = 6 AND
11477: v_wlji_status_type(v_idx) = WIP_CONSTANTS.RELEASED and
11478: l_cur_status_type = WIP_CONSTANTS.UNRELEASED)
11479: then
11480:
11481: --
11482: -- Begin R12Dev Update to set the current_job/rtg_op_seq_num on WLBJ
12263: request_id = fnd_global.conc_request_id,
12264: program_id = fnd_global.conc_program_id,
12265: program_application_id = fnd_global.prog_appl_id
12266: where parent_header_id IS NULL
12267: and process_status = WIP_CONSTANTS.RUNNING
12268: and wip_entity_id = l_v_d_we_id_err(i)
12269: and organization_id = l_v_d_org_id_err(i);
12270:
12271: errbuf := 'The interface process marked at least one row as errored';
12277: if(l_d_idx_ok > 1) then
12278: forall i in l_v_d_we_id_ok.first..l_v_d_we_id_ok.last
12279: delete from wsm_lot_job_dtl_interface
12280: where parent_header_id IS NULL
12281: and process_status = WIP_CONSTANTS.RUNNING
12282: and wip_entity_id = l_v_d_we_id_ok(i)
12283: and organization_id = l_v_d_org_id_ok(i);
12284: end if;
12285: end if; -- l_d_jobs > 0
12618: l_scheduled_compl_date := p_job_scheduled_compl_date;
12619:
12620: if(p_infinite_schedule = 'Y') then
12621: if(p_job_scheduled_start_date IS NULL) then
12622: l_sch_mode := WIP_CONSTANTS.BACKWARDS;
12623: l_sch_date := p_job_scheduled_compl_date;
12624: else
12625: l_sch_mode := WIP_CONSTANTS.FORWARDS;
12626: l_sch_date := p_job_scheduled_start_date;
12621: if(p_job_scheduled_start_date IS NULL) then
12622: l_sch_mode := WIP_CONSTANTS.BACKWARDS;
12623: l_sch_date := p_job_scheduled_compl_date;
12624: else
12625: l_sch_mode := WIP_CONSTANTS.FORWARDS;
12626: l_sch_date := p_job_scheduled_start_date;
12627: end if;
12628: -- force import_lot_job_details to get scheduled_start_date
12629: -- and scheduled_compl_date from WDJ in such case
12897: p_rtg_op_seq_num => p_rtg_op_seq_num,
12898: p_error_code => p_error_code,
12899: p_error_msg => p_error_msg,
12900: p_src_client_server => 0,
12901: p_po_creation_time => wip_constants.manual_creation); -- default is not to create PO
12902:
12903: END build_job_detail_info;
12904:
12905:
14377: p_organization_id => p_org_id,
14378: p_repetitive_schedule_id => null,
14379: p_operation_seq_num => p_job_op_seq_num,
14380: p_resource_seq_num => v_wljdi_resource_seq_num(l_index),
14381: p_run_reqimport => wip_constants.no
14382: );
14383:
14384: g_num_of_osp_exists := nvl(g_num_of_osp_exists,0) + 1;
14385:
14976: cumulative_yield, /*Added for bugfix:7248992 */
14977: reverse_cumulative_yield/*Added for bugfix:7248992 */
14978: from wsm_lot_job_dtl_interface
14979: where parent_header_id = p_parent_header_id
14980: and process_status = WIP_CONSTANTS.RUNNING
14981: order by load_type,
14982: NVL(parent_resource_seq_num,0), -- Detailed Scheduling Code review remark...
14983: -- (Moved parent resource_seq_num before substitution_type so that setup resources are processed after runtime resource
14984: -- ST : Detailed Scheduling Added to grpup the new setup resources for a runtime resource together
15103: from wsm_lot_job_dtl_interface
15104: where parent_header_id IS NULL
15105: and wip_entity_id = p_wip_entity_id
15106: and organization_id = p_org_id
15107: and process_status = WIP_CONSTANTS.RUNNING
15108: and NVL(group_id, -1) = NVL(p_group_id, -1)
15109: order by load_type,
15110: NVL(parent_resource_seq_num,0),
15111: -- Detailed Scheduling Code review remark...
15254: l_job_qty_scrapped
15255: from wip_discrete_jobs
15256: where wip_entity_id = p_wip_entity_id;
15257:
15258: if(l_job_status = WIP_CONSTANTS.UNRELEASED) then
15259: l_cur_job_op_seq_num := g_op_seq_incr;
15260: l_cur_rtg_op_seq_num := l_start_op_seq_num;
15261: l_cur_op_seq_id := l_start_op_seq_id;
15262: l_cur_job_qty := l_job_start_qty;
15288: end;
15289: end if;
15290: exception
15291: when no_data_found then
15292: if (l_job_status IN ( WIP_CONSTANTS.COMP_CHRG,
15293: WIP_CONSTANTS.COMP_NOCHRG,
15294: WIP_CONSTANTS.CLOSED) )
15295: then
15296: l_cur_job_op_seq_num := null;
15289: end if;
15290: exception
15291: when no_data_found then
15292: if (l_job_status IN ( WIP_CONSTANTS.COMP_CHRG,
15293: WIP_CONSTANTS.COMP_NOCHRG,
15294: WIP_CONSTANTS.CLOSED) )
15295: then
15296: l_cur_job_op_seq_num := null;
15297: l_cur_rtg_op_seq_num := l_end_op_seq_num;
15290: exception
15291: when no_data_found then
15292: if (l_job_status IN ( WIP_CONSTANTS.COMP_CHRG,
15293: WIP_CONSTANTS.COMP_NOCHRG,
15294: WIP_CONSTANTS.CLOSED) )
15295: then
15296: l_cur_job_op_seq_num := null;
15297: l_cur_rtg_op_seq_num := l_end_op_seq_num;
15298: l_cur_op_seq_id := l_end_op_seq_id;
15915: from dual
15916: where exists ( SELECT 1
15917: FROM bom_std_op_resources bsor
15918: WHERE bsor.standard_operation_id = v_wljdi_std_op_id(l_d)
15919: AND bsor.autocharge_type = WIP_CONSTANTS.PO_MOVE)
15920: or exists ( SELECT 1
15921: FROM bom_std_sub_op_resources bsor
15922: WHERE bsor.standard_operation_id = v_wljdi_std_op_id(l_d)
15923: AND bsor.autocharge_type = WIP_CONSTANTS.PO_MOVE);
15919: AND bsor.autocharge_type = WIP_CONSTANTS.PO_MOVE)
15920: or exists ( SELECT 1
15921: FROM bom_std_sub_op_resources bsor
15922: WHERE bsor.standard_operation_id = v_wljdi_std_op_id(l_d)
15923: AND bsor.autocharge_type = WIP_CONSTANTS.PO_MOVE);
15924: if l_temp=1 then
15925: fnd_message.set_name('WSM','WSM_OP_PO_MOVE');
15926: p_error_msg := fnd_message.get;
15927: p_error_code := - 1;
16251: where wcor.organization_id = p_org_id
16252: and wcor.wip_entity_id = p_wip_entity_id
16253: and wcor.operation_seq_num = v_wljdi_routing_op_seq_num(l_d)
16254: and wcor.recommended = 'Y'
16255: and wcor.schedule_flag <> WIP_CONSTANTS.SCHED_NO;
16256:
16257: end if;
16258: end if;
16259: l_d := v_wljdi_record_id.next(l_d);
17454: p_program_id,
17455: p_prog_updt_date);
17456:
17457: -- If resource is PO_RECIPT, we should create requisition.
17458: if l_v_res(l_resource_id).autocharge_type = WIP_CONSTANTS.PO_RECEIPT then
17459:
17460: wip_osp.create_requisition(
17461: p_wip_entity_id => p_wip_entity_id,
17462: p_organization_id => p_org_id,
17462: p_organization_id => p_org_id,
17463: p_repetitive_schedule_id => null,
17464: p_operation_seq_num => l_job_op_seq_num,
17465: p_resource_seq_num => l_v_res(l_resource_id).res_seq_num,
17466: p_run_reqimport => wip_constants.no);
17467:
17468: g_num_of_osp_exists := nvl(g_num_of_osp_exists,0) + 1;
17469:
17470: end if;
17569: p_program_id,
17570: p_prog_updt_date);
17571: END IF;
17572:
17573: if l_v_res(l_resource_id).scheduled_flag <> WIP_CONSTANTS.SCHED_NO and
17574: l_v_res(l_resource_id).reco_flag = 'Y' then
17575:
17576: insert into wip_operation_resource_usage
17577: (wip_entity_id,
17778: v_wljdi_attribute15(l_d),
17779: null);
17780:
17781: if l_v_res(l_resource_id).reco_flag='Y' and
17782: l_v_res(l_resource_id).scheduled_flag <> WIP_CONSTANTS.SCHED_NO then
17783:
17784: insert into wsm_copy_op_resource_usage
17785: (wip_entity_id,
17786: operation_seq_num,
19377: wip_picking_pvt.cancel_comp_allocations(
19378: p_wip_entity_id => p_wip_entity_id,
19379: p_operation_seq_num => l_job_op_seq_num,
19380: p_inventory_item_id => l_item_old_id,
19381: p_wip_entity_type => WIP_CONSTANTS.LOTBASED,
19382: x_return_status => x_return_status,
19383: x_msg_data => p_error_msg);
19384:
19385: if (x_return_status <> fnd_api.g_ret_sts_success) then
19686: attribute14,
19687: attribute15)
19688: select
19689: p_wip_entity_id,
19690: decode(l_v_comp(l_in).wip_supply_type, WIP_CONSTANTS.PHANTOM,(-1*l_rtg_op_seq_num),l_rtg_op_seq_num),
19691: l_v_comp(l_in).item_id,
19692: decode(nvl(v_wljdi_src_phantom_item_id(l_d),-1),-1,l_v_comp(l_in).item_id,
19693: 0,l_v_comp(l_in).item_id, v_wljdi_src_phantom_item_id(l_d)),
19694: -1, -- Since not in bill
20110: wsm_lot_job_interface wlji,
20111: mtl_system_items msi
20112: where wsli.header_id = wlji.source_line_id
20113: and wlji.group_id = p_group_id
20114: and wlji.process_status = 2 -- WIP_CONSTANTS.running
20115: and wlji.mode_flag = 2
20116: and msi.inventory_item_id = wsli.inventory_item_id
20117: and msi.organization_id = wsli.organization_id;
20118:
20745: decode(operation_seq_num,
20746: l_max_op_seq_num,
20747: (decode(p_status_type,
20748: 3, round(p_start_quantity,
20749: wip_constants.max_displayed_precision),
20750: 0)),
20751: 0)
20752: where wo.wip_entity_id = p_wip_entity_id;
20753: if lbji_debug = 'Y' then
20824: --
20825:
20826: -- bug 3311985: osp code moved from build_job_header_info
20827: -- osp begin -- this has to be put after insertion into wip_entities
20828: if p_status_type = 3 and p_po_creation_time <> wip_constants.manual_creation then
20829: if p_src_client_server = 1 then
20830: l_stmt_num := 130;
20831: if wsmputil.check_osp_operation(p_wip_entity_id, l_job_op_seq_num, p_org_id) then
20832: g_num_of_osp_exists := g_num_of_osp_exists + 1;
20835: p_organization_id => p_org_id,
20836: p_repetitive_schedule_id => null,
20837: p_operation_seq_num => l_job_op_seq_num,
20838: p_resource_seq_num => null,
20839: p_run_reqimport => wip_constants.yes);
20840: end if; -- check_osp_operation
20841: else
20842: l_stmt_num := 140;
20843: -- if build_job_info is called from form, only then P_Run_ReqImport should be YES, i.e.
20849: p_organization_id => p_org_id,
20850: p_repetitive_schedule_id => null,
20851: p_operation_seq_num => l_job_op_seq_num,
20852: p_resource_seq_num => null,
20853: p_run_reqimport => wip_constants.no);
20854: end if; -- check_osp_operation
20855: end if; -- p_src_client_server
20856: end if; -- p_status_type = 3
20857: -- osp end
21139: p_source_code,
21140: p_description,
21141: p_status_type,
21142: -- BC: bug 3382472
21143: --NVL(p_date_released, decode(p_status_type, wip_constants.unreleased, null, sysdate)),
21144: NVL(p_date_released, decode(p_status_type,
21145: wip_constants.released, sysdate,
21146: null)),
21147: -- EC: bug 3382472
21141: p_status_type,
21142: -- BC: bug 3382472
21143: --NVL(p_date_released, decode(p_status_type, wip_constants.unreleased, null, sysdate)),
21144: NVL(p_date_released, decode(p_status_type,
21145: wip_constants.released, sysdate,
21146: null)),
21147: -- EC: bug 3382472
21148: p_item,
21149: decode(p_job_type, 3, p_bom_reference_id, null),
21148: p_item,
21149: decode(p_job_type, 3, p_bom_reference_id, null),
21150: decode(p_job_type, 3, p_routing_reference_id, null),
21151: p_firm_planned_flag,
21152: decode(p_job_type, 3, wip_constants.nonstandard, wip_constants.standard),
21153: p_wip_supply_type,
21154: p_class_code,
21155: l_material_account,
21156: l_material_overhead_account,
21163: l_overhead_account,
21164: l_overhead_variance_account,
21165: trunc(p_job_scheduled_start_date,'mi'), --round(p_job_scheduled_start_date,'mi'),
21166: trunc(p_job_scheduled_compl_date,'mi'), --round(p_job_scheduled_compl_date,'mi'),
21167: round(p_start_quantity, wip_constants.max_displayed_precision),
21168: 0, -- quantity_completed
21169: 0, -- quantity_scrapped
21170: round(p_net_quantity, wip_constants.max_displayed_precision),
21171: p_common_bill_sequence_id,
21166: trunc(p_job_scheduled_compl_date,'mi'), --round(p_job_scheduled_compl_date,'mi'),
21167: round(p_start_quantity, wip_constants.max_displayed_precision),
21168: 0, -- quantity_completed
21169: 0, -- quantity_scrapped
21170: round(p_net_quantity, wip_constants.max_displayed_precision),
21171: p_common_bill_sequence_id,
21172: p_common_routing_sequence_id,
21173: p_bom_revision,
21174: p_routing_revision,
21334: end if;
21335: end if;
21336:
21337: l_stmt_num := 160;
21338: if p_status_type = WIP_CONSTANTS.RELEASED then
21339: insert_into_period_balances (
21340: p_wip_entity_id => p_wip_entity_id,
21341: p_organization_id => p_org_id,
21342: p_class_code => p_class_code,