21:
22:
23: -- Define Global Variable --
24: G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_WIP_JOB_PVT';
25: g_job_prefix VARCHAR2(30) := fnd_profile.value('CSD_DEFAULT_JOB_PREFIX');
26:
27: -- This procedure accepts job header, bills and routing information and inserts it into
28: -- WIP_JOB_SCHEDULE_INTERFACE table. This procedure inserts one record at a time, hence
29: -- this procedure needs to be called in a loop for multiple jobs being submitted
140: -- Fix for bug# 3109417
141: -- If the profile CSD: Default WIP MRP Net Qty to Zero is set to
142: -- null / 'N' then net_quantity = start_quantity else if the
143: -- profile is set to 'Y' then net_quantity = 0
144: IF ( nvl(fnd_profile.value('CSD_WIP_MRP_NET_QTY'),'N') = 'N' ) THEN
145: l_job_header_rec.net_quantity := p_job_header_rec.quantity;
146: ELSIF ( fnd_profile.value('CSD_WIP_MRP_NET_QTY') = 'Y' ) THEN
147: l_job_header_rec.net_quantity := 0;
148: END IF;
142: -- null / 'N' then net_quantity = start_quantity else if the
143: -- profile is set to 'Y' then net_quantity = 0
144: IF ( nvl(fnd_profile.value('CSD_WIP_MRP_NET_QTY'),'N') = 'N' ) THEN
145: l_job_header_rec.net_quantity := p_job_header_rec.quantity;
146: ELSIF ( fnd_profile.value('CSD_WIP_MRP_NET_QTY') = 'Y' ) THEN
147: l_job_header_rec.net_quantity := 0;
148: END IF;
149:
150:
621: -- local variables
622:
623: l_operation_seq_num NUMBER := 1;
624: l_estimate_uom VARCHAR2(25);
625: l_inv_org NUMBER := fnd_profile.value('CSD_DEF_REP_INV_ORG'); --cs_std.get_item_valdn_orgzn_id; bug#10144772, subhat
626: l_counter NUMBER;
627:
628: -- cursor to calculate the estimate details.
629:
664: cr.unit_of_measure,
665: cr.serial_number,
666: cr.quantity,
667: -- cr.inventory_org_id bug#10144772
668: fnd_profile.value('CSD_DEF_REP_INV_ORG')
669: FROM
670: csd_repairs_v cr
671: WHERE
672: cr.repair_line_id = p_repair_line_id;
694: -- clear the collection type.
695:
696: x_MTL_TXN_DTLS_TAB_TYPE.DELETE;
697:
698: IF nvl(fnd_profile.value('CSD_DEFAULT_RO_ITEM_AS_MATERIAL_ON_JOB'),'N') = 'Y' THEN
699: OPEN repair_item_dtls(p_repair_line_id);
700: FETCH repair_item_dtls INTO
701: x_MTL_TXN_DTLS_TAB_TYPE(l_counter).inventory_item_id,
702: x_MTL_TXN_DTLS_TAB_TYPE(l_counter).transaction_uom,
705: x_MTL_TXN_DTLS_TAB_TYPE(l_counter).organization_id ;
706: CLOSE repair_item_dtls;
707: x_MTL_TXN_DTLS_TAB_TYPE(l_counter).transaction_quantity := x_MTL_TXN_DTLS_TAB_TYPE(l_counter).required_quantity;
708: -- sub inventory is defaulted from the CSD_DEF_HV_SUBINV profile option.
709: x_MTL_TXN_DTLS_TAB_TYPE(l_counter).supply_subinventory := fnd_profile.value('CSD_DEF_HV_SUBINV');
710: --message(fnd_profile.value('CSD_DEF_HV_SUBINV'));
711: IF x_MTL_TXN_DTLS_TAB_TYPE(l_counter).supply_subinventory IS NULL THEN
712: FND_MESSAGE.SET_NAME('CSD','CSD_DEF_SUB_INV_NULL');
713: FND_MSG_PUB.ADD;
706: CLOSE repair_item_dtls;
707: x_MTL_TXN_DTLS_TAB_TYPE(l_counter).transaction_quantity := x_MTL_TXN_DTLS_TAB_TYPE(l_counter).required_quantity;
708: -- sub inventory is defaulted from the CSD_DEF_HV_SUBINV profile option.
709: x_MTL_TXN_DTLS_TAB_TYPE(l_counter).supply_subinventory := fnd_profile.value('CSD_DEF_HV_SUBINV');
710: --message(fnd_profile.value('CSD_DEF_HV_SUBINV'));
711: IF x_MTL_TXN_DTLS_TAB_TYPE(l_counter).supply_subinventory IS NULL THEN
712: FND_MESSAGE.SET_NAME('CSD','CSD_DEF_SUB_INV_NULL');
713: FND_MSG_PUB.ADD;
714: RAISE FND_API.G_EXC_ERROR;
759: x_MTL_TXN_DTLS_TAB_TYPE(l_counter).organization_id := l_inv_org;
760: x_MTL_TXN_DTLS_TAB_TYPE(l_counter).new_row := 'Y';
761:
762: -- sub inventory is defaulted from the CSD_DEF_HV_SUBINV profile option.
763: x_MTL_TXN_DTLS_TAB_TYPE(l_counter).supply_subinventory := fnd_profile.value('CSD_DEF_HV_SUBINV');
764: --message(fnd_profile.value('CSD_DEF_HV_SUBINV'));
765: IF x_MTL_TXN_DTLS_TAB_TYPE(l_counter).supply_subinventory IS NULL THEN
766: FND_MESSAGE.SET_NAME('CSD','CSD_DEF_SUB_INV_NULL');
767: FND_MSG_PUB.ADD;
760: x_MTL_TXN_DTLS_TAB_TYPE(l_counter).new_row := 'Y';
761:
762: -- sub inventory is defaulted from the CSD_DEF_HV_SUBINV profile option.
763: x_MTL_TXN_DTLS_TAB_TYPE(l_counter).supply_subinventory := fnd_profile.value('CSD_DEF_HV_SUBINV');
764: --message(fnd_profile.value('CSD_DEF_HV_SUBINV'));
765: IF x_MTL_TXN_DTLS_TAB_TYPE(l_counter).supply_subinventory IS NULL THEN
766: FND_MESSAGE.SET_NAME('CSD','CSD_DEF_SUB_INV_NULL');
767: FND_MSG_PUB.ADD;
768: RAISE FND_API.G_EXC_ERROR;
960: IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
961: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
962: END IF;
963:
964: l_default_ro_item := nvl(FND_PROFILE.VALUE('CSD_DEFAULT_RO_ITEM_AS_MATERIAL_ON_JOB'), 'N');
965:
966: -- Get the Group_id to be used for WIP Mass Load, All the records inserted into
967: -- wip_job_schedule_interface have the same group_id , so that one WIP Mass Load
968: -- request can process all the records
977: l_quantity,
978: l_serial_number;
979: -- l_inventory_org_id; -- swai: bug 9954780
980: CLOSE c_repair_line_info;
981: l_inventory_org_id := fnd_profile.value('CSD_DEF_REP_INV_ORG'); -- swai: bug 9954780
982: l_subinventory := fnd_profile.value('CSD_DEF_HV_SUBINV');
983:
984:
985: --Get serial number control code and lot control code
978: l_serial_number;
979: -- l_inventory_org_id; -- swai: bug 9954780
980: CLOSE c_repair_line_info;
981: l_inventory_org_id := fnd_profile.value('CSD_DEF_REP_INV_ORG'); -- swai: bug 9954780
982: l_subinventory := fnd_profile.value('CSD_DEF_HV_SUBINV');
983:
984:
985: --Get serial number control code and lot control code
986: OPEN cur_get_item_attribs (l_inventory_org_id,
1568: AND wdj.organization_id = msi.organization_id
1569: AND cr.inventory_item_id = msi.inventory_item_id
1570: AND cr.repair_line_id = crj.repair_line_id;
1571:
1572: l_def_ro_item VARCHAR2(1) := nvl(FND_PROFILE.VALUE('CSD_DEFAULT_RO_ITEM_AS_MATERIAL_ON_JOB'), 'N');
1573: l_mtl_details_tbl csd_hv_wip_job_pvt.mtl_txn_dtls_tbl_type;
1574: l_index NUMBER := 0;
1575: x_op_created VARCHAR2(10);
1576: -- end changes bug#13586864
1783: l_mtl_details_tbl(l_index).revision_qty_control_code := i.revision_qty_control_code;
1784: l_mtl_details_tbl(l_index).transaction_quantity := i.quantity;
1785: l_mtl_details_tbl(l_index).organization_id := i.organization_id;
1786: l_mtl_details_tbl(l_index).new_row := 'Y';
1787: l_mtl_details_tbl(l_index).supply_subinventory := fnd_profile.value('CSD_DEF_HV_SUBINV');
1788: l_mtl_details_tbl(l_index).wip_entity_id := i.wip_entity_id;
1789: l_mtl_details_tbl(l_index).operation_seq_num := l_operation_seq_num;
1790: END LOOP;
1791:
2019:
2020: -- initialize the job header rec.
2021:
2022: l_job_header_rec.organization_id :=
2023: fnd_profile.value('CSD_DEF_REP_INV_ORG');
2024:
2025: IF l_job_header_rec.organization_id is NULL THEN
2026:
2027: FND_MESSAGE.SET_NAME('CSD','CSD_DEF_REP_INV_NULL');
2029: RAISE FND_API.G_EXC_ERROR;
2030: END IF;
2031:
2032:
2033: l_job_prefix := fnd_profile.value('CSD_DEFAULT_JOB_PREFIX');
2034:
2035: -- If l_job_prefix is null, throw an error and return;
2036:
2037:
2042: RAISE FND_API.G_EXC_ERROR;
2043: END IF;
2044:
2045: l_job_header_rec.class_code :=
2046: fnd_profile.value('CSD_DEF_WIP_ACCOUNTING_CLASS');
2047:
2048: IF l_job_header_rec.class_code is NULL THEN
2049:
2050: FND_MESSAGE.SET_NAME('CSD','CSD_CLASS_CODE_NULL');
2054:
2055: -- Assign the WIP Job Status lookup codes corresponding to Released -- and Unreleased Job status,
2056: -- to be passed for WIP Interface Table
2057:
2058: IF fnd_profile.value('CSD_DEFAULT_JOB_STATUS') = 'RELEASED' THEN
2059:
2060: l_job_header_rec.status_type := lc_released_status_code ;
2061:
2062: ELSIF NVL( fnd_profile.value('CSD_DEFAULT_JOB_STATUS'), 'UNRELEASED' ) = 'UNRELEASED' THEN
2058: IF fnd_profile.value('CSD_DEFAULT_JOB_STATUS') = 'RELEASED' THEN
2059:
2060: l_job_header_rec.status_type := lc_released_status_code ;
2061:
2062: ELSIF NVL( fnd_profile.value('CSD_DEFAULT_JOB_STATUS'), 'UNRELEASED' ) = 'UNRELEASED' THEN
2063:
2064: l_job_header_rec.status_type := lc_unreleased_status_code;
2065: END IF;
2066:
2074:
2075: -- If the profile CSD: Default WIP MRP Net Qty to Zero is set to
2076: -- null / 'N' then net_quantity = start_quantity else if the
2077: -- profile is set to 'Y' then net_quantity = 0
2078: IF ( nvl(fnd_profile.value('CSD_WIP_MRP_NET_QTY'),'N') = 'N' ) THEN
2079: l_job_header_rec.net_quantity := p_ESTM_JOB_DETLS_REC_TYPE.repair_quantity;
2080: ELSIF ( fnd_profile.value('CSD_WIP_MRP_NET_QTY') = 'Y' ) THEN
2081: l_job_header_rec.net_quantity := 0;
2082: END IF;
2076: -- null / 'N' then net_quantity = start_quantity else if the
2077: -- profile is set to 'Y' then net_quantity = 0
2078: IF ( nvl(fnd_profile.value('CSD_WIP_MRP_NET_QTY'),'N') = 'N' ) THEN
2079: l_job_header_rec.net_quantity := p_ESTM_JOB_DETLS_REC_TYPE.repair_quantity;
2080: ELSIF ( fnd_profile.value('CSD_WIP_MRP_NET_QTY') = 'Y' ) THEN
2081: l_job_header_rec.net_quantity := 0;
2082: END IF;
2083:
2084:
2574: l_completion_locs JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2575: lx_job_name VARCHAR2(30);
2576: l_group_id NUMBER := 0;
2577: l_job_status NUMBER;
2578: l_mrp_net_qty_profile VARCHAR2(1) := nvl(fnd_profile.value('CSD_WIP_MRP_NET_QTY'),'N');
2579:
2580: BEGIN
2581:
2582: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2613:
2614: IF p_job_status IS NOT NULL THEN
2615: l_job_status := p_job_status;
2616: ELSE
2617: IF fnd_profile.value('CSD_DEFAULT_JOB_STATUS') = 'RELEASED'
2618: THEN
2619: l_job_status := 3;
2620:
2621: ELSIF NVL(fnd_profile.value('CSD_DEFAULT_JOB_STATUS'), 'UNRELEASED' ) = 'UNRELEASED'
2617: IF fnd_profile.value('CSD_DEFAULT_JOB_STATUS') = 'RELEASED'
2618: THEN
2619: l_job_status := 3;
2620:
2621: ELSIF NVL(fnd_profile.value('CSD_DEFAULT_JOB_STATUS'), 'UNRELEASED' ) = 'UNRELEASED'
2622: THEN
2623:
2624: l_job_status := 1;
2625: END IF;
2909: BEGIN
2910:
2911: --if profile MRP:Debug Mode set to N, then delete it.
2912:
2913: IF (nvl(fnd_profile.value('MRP_DEBUG'),'Y') = 'N') THEN
2914:
2915: for old_request in old_requests loop
2916:
2917: delete from wip_interface_errors