Requistion Creation
CREATE TABLE XX.XX_PO_REQUISITIONS_INT_STG
(
OU_NAME VARCHAR2(240 BYTE),
ITEM_CODE VARCHAR2(40 BYTE),
QTY NUMBER,
UOM VARCHAR2(40 BYTE),
SOURCE_INV VARCHAR2(240 BYTE),
DESTINATION_INV VARCHAR2(240 BYTE),
FROM_SUB_INV VARCHAR2(240 BYTE),
TO_SUB_INV VARCHAR2(240 BYTE),
NEED_BY_DATE DATE,
GRADE VARCHAR2(100 BYTE),
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER(15),
LAST_UPDATE_LOGIN NUMBER(15),
CREATION_DATE DATE,
CREATED_BY NUMBER(15),
REQUEST_ID NUMBER,
ATTRIBUTE1 VARCHAR2(150 BYTE),
ATTRIBUTE2 VARCHAR2(150 BYTE),
ATTRIBUTE3 VARCHAR2(150 BYTE),
ATTRIBUTE4 VARCHAR2(150 BYTE),
ATTRIBUTE5 VARCHAR2(150 BYTE),
ATTRIBUTE6 VARCHAR2(150 BYTE),
ATTRIBUTE7 VARCHAR2(150 BYTE),
ATTRIBUTE8 VARCHAR2(150 BYTE),
ATTRIBUTE9 VARCHAR2(150 BYTE),
ATTRIBUTE10 VARCHAR2(150 BYTE),
STATUS VARCHAR2(20 BYTE),
ERROR_MESSAGE VARCHAR2(4000 BYTE),
IMPORT_SOURCE VARCHAR2(30 BYTE),
UPLOAD_ID NUMBER,
TRANSFER_TYPE VARCHAR2(240 BYTE),
HDR_PREFERRED_TRANSPORTER VARCHAR2(240 BYTE),
LIN_SHIPPING_INSTRUCTIONS VARCHAR2(2000 BYTE),
LIN_PACKING_INSTRUCTIONS VARCHAR2(2000 BYTE),
HDR_INSPECTION VARCHAR2(240 BYTE),
HDR_TRIAL_ORDER VARCHAR2(240 BYTE),
HDR_INSURANCE VARCHAR2(240 BYTE),
HDR_INSURANCE_POLICY_NUMBER VARCHAR2(240 BYTE),
HDR_BRANDING_REQUIRED VARCHAR2(240 BYTE),
HDR_SPECIAL_INSTRUCTIONS VARCHAR2(240 BYTE),
HDR_TC_STANDARD VARCHAR2(240 BYTE),
HDR_SMALL_QUANTITY_SURCHARGE VARCHAR2(240 BYTE),
HDR_ANTICIPATORY VARCHAR2(240 BYTE),
LIN_END_APPLICATION VARCHAR2(1000 BYTE),
LIN_F_TRUCK_LOAD_PONABLE_FLAG VARCHAR2(1000 BYTE),
LIN_PLANNING_REQUIRED VARCHAR2(1000 BYTE),
LIN_CSR_NUMBER VARCHAR2(1000 BYTE),
LIN_DIVERSION_ORDER_NUMBER VARCHAR2(1000 BYTE),
EXT_LIN_LENGTH_TOLERANCE_TAR VARCHAR2(1000 BYTE),
EXT_LIN_LENGTH_TOLERANCE_MAX VARCHAR2(1000 BYTE),
EXT_LIN_LENGTH_TOLERANCE_MIN VARCHAR2(1000 BYTE),
EXT_LIN_CONDUCTIVITY_TARGET VARCHAR2(1000 BYTE),
EXT_LIN_CONDUCTIVITY_MAX VARCHAR2(1000 BYTE),
EXT_LIN_CONDUCTIVITY_MIN VARCHAR2(1000 BYTE),
EXT_LIN_END_SEGMENT VARCHAR2(1000 BYTE),
EXT_LIN_UST_ON_BILLET VARCHAR2(1000 BYTE),
EXT_LIN_UST_ON_SECTION VARCHAR2(1000 BYTE),
EXT_LIN_REELING VARCHAR2(1000 BYTE),
EXT_LIN_COIL_WEIGHT_TARGET VARCHAR2(1000 BYTE),
EXT_LIN_COIL_WEIGHT_MAX VARCHAR2(1000 BYTE),
EXT_LIN_COIL_WEIGHT_MIN VARCHAR2(1000 BYTE),
EXT_LIN_PACKAGE_WEIGHT_TA VARCHAR2(1000 BYTE),
EXT_LIN_PACKAGE_WEIGHT_MA VARCHAR2(1000 BYTE),
EXT_LIN_PACKAGE_WEIGHT_MI VARCHAR2(1000 BYTE),
EXT_LIN_WIDTH_TOLERANCE_TARGET VARCHAR2(1000 BYTE),
EXT_LIN_WIDTH_TOLERANCE_MAX VARCHAR2(1000 BYTE),
EXT_LIN_WIDTH_TOLERANCE_MIN VARCHAR2(1000 BYTE),
EXT_LIN_THICKNESS_TOLERANCE_TA VARCHAR2(1000 BYTE),
EXT_LIN_THICKNESS_TOLERANCE_MA VARCHAR2(1000 BYTE),
EXT_LIN_THICKNESS_TOLERANCE_MI VARCHAR2(1000 BYTE),
FRP_LIN_ULTIMATE_TEN_STREN_MIN VARCHAR2(1000 BYTE),
FRP_LIN_COIL_DENSITY_RANGE_MIN VARCHAR2(1000 BYTE),
FRP_LIN_COIL_DENSITY_RANGE_MAX VARCHAR2(1000 BYTE),
FRP_LIN_COIL_DENSITY_RANGE_TAR VARCHAR2(1000 BYTE),
FRP_LIN_COIL_OD_MIN VARCHAR2(1000 BYTE),
FRP_LIN_COIL_OD_MAX VARCHAR2(1000 BYTE),
FRP_LIN_COIL_OD_TARGET VARCHAR2(1000 BYTE),
FRP_LIN_COIL_ID_MIN VARCHAR2(1000 BYTE),
FRP_LIN_COIL_ID_MAX VARCHAR2(1000 BYTE),
FRP_LIN_COIL_ID_TARGET VARCHAR2(1000 BYTE),
FRP_LIN_ELONGATION_MIN VARCHAR2(1000 BYTE),
FRP_LIN_ELONGATION_MAX VARCHAR2(1000 BYTE),
FRP_LIN_ELONGATION_TARGET VARCHAR2(1000 BYTE),
FRP_LIN_ULTIMATE_TEN_STREN_MAX VARCHAR2(1000 BYTE),
FRP_LIN_ULTIMATE_TEN_STREN_TAR VARCHAR2(1000 BYTE),
FRP_LIN_YIELD_STRENGTH_MIN VARCHAR2(1000 BYTE),
FRP_LIN_YIELD_STRENGTH_MAX VARCHAR2(1000 BYTE),
FRP_LIN_YIELD_STRENGTH_TARGET VARCHAR2(1000 BYTE),
FRP_LIN_CONDUCTIVITY_MIN VARCHAR2(1000 BYTE),
FRP_LIN_CONDUCTIVITY_MAX VARCHAR2(1000 BYTE),
FRP_LIN_CONDUCTIVITY_TARGET VARCHAR2(1000 BYTE),
FRP_LIN_LENGTH_TOLERANCE_MIN VARCHAR2(1000 BYTE),
FRP_LIN_LENGTH_TOLERANCE_MAX VARCHAR2(1000 BYTE),
FRP_LIN_LENGTH_TOLERANCE_TARGE VARCHAR2(1000 BYTE),
FRP_LIN_THICKNESS_TOLERANCE_MI VARCHAR2(1000 BYTE),
FRP_LIN_THICKNESS_TOLERANCE_MA VARCHAR2(1000 BYTE),
FRP_LIN_THICKNESS_TOLERANCE_TA VARCHAR2(1000 BYTE),
FRP_LIN_WIDTH_TOLERANCE_MIN VARCHAR2(1000 BYTE),
FRP_LIN_WIDTH_TOLERANCE_MAX VARCHAR2(1000 BYTE),
FRP_LIN_WIDTH_TOLERANCE_TARGET VARCHAR2(1000 BYTE),
FRP_LIN_PACKAGE_WEIGHT_MI VARCHAR2(1000 BYTE),
FRP_LIN_PACKAGE_WEIGHT_MA VARCHAR2(1000 BYTE),
FRP_LIN_PACKAGE_WEIGHT_TA VARCHAR2(1000 BYTE),
FRP_LIN_COIL_WEIGHT_MIN VARCHAR2(1000 BYTE),
FRP_LIN_COIL_WEIGHT_MAX VARCHAR2(1000 BYTE),
FRP_LIN_COIL_WEIGHT_TARGET VARCHAR2(1000 BYTE),
FRP_LIN_END_SEGMENT VARCHAR2(1000 BYTE),
FRP_LIN_COIL_ORIENTATION VARCHAR2(1000 BYTE),
FRP_LIN_CORE_TYPE VARCHAR2(1000 BYTE),
FRP_LIN_RAL_CODE VARCHAR2(1000 BYTE),
FRP_LIN_SPOOL_TYPE VARCHAR2(1000 BYTE),
PRM_LIN_CONDUCTIVITY_TARGET VARCHAR2(1000 BYTE),
PRM_LIN_CONDUCTIVITY_MAX VARCHAR2(1000 BYTE),
PRM_LIN_CONDUCTIVITY_MIN VARCHAR2(1000 BYTE),
PRM_LIN_UST_ON_BILLET VARCHAR2(1000 BYTE),
PRM_LIN_END_SEGMENT VARCHAR2(1000 BYTE),
REQUISITION_NUMBER VARCHAR2(50 BYTE),
REQ_LINE_NUM VARCHAR2(50 BYTE),
ORG_ID NUMBER,
REQ_HDR_ID NUMBER,
REQ_LINE_ID NUMBER,
SO_HDR_CONTEXT VARCHAR2(240 BYTE),
SO_LINE_CONTEXT VARCHAR2(240 BYTE),
SO_HDR_ID NUMBER,
SO_LINE_ID NUMBER,
ISO_HEADER_ID NUMBER,
ISO_LINE_ID NUMBER,
DELIVER_AT_LOCATION VARCHAR2(45 BYTE),
CUST_PO VARCHAR2(50 BYTE),
REQ_HDR_ATTRIBUTE_CATERGORY VARCHAR2(50 BYTE),
SHIPMENT_ABOVE_TOLERANCE VARCHAR2(10 BYTE),
SHIPMENT_BELOW_TOLERANCE VARCHAR2(10 BYTE)
);
CREATE SEQUENCE XX.XX_PO_REQ_WEBADI_LINES_S START WITH 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE XX.XX_PO_REQ_WEBADI_BATCH_S START WITH 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE OR REPLACE VIEW apps.XX_PO_REQ_ITEM_V
(SEGMENT1, DESCRIPTION, INVENTORY_ITEM_ID)
AS
SELECT msib.segment1, msib.description, msib.inventory_item_id
FROM mtl_system_items_b msib, mtl_parameters mp
WHERE msib.organization_id = mp.organization_id
AND mp.organization_code = 'XX'; CREATE OR REPLACE PACKAGE BODY APPS.XX_po_req_import_int_pkg
AS
--------------------------------------------------------------------------------------
--File Name: XX_PO_REQ_IMPORT_INT_PKG.pkb
--Object Name: XX_PO_REQ_IMPORT_INT_PKG
--Old Object Name: New Object
--RICEW Object id: --Description: Custom package body
--Maintenance History
-- Date Author Name Version Description
-- ------- ----------- -------- -----------
---------------------------Variable Declaration-------------------------------
g_user_id fnd_user.user_id%TYPE
:= fnd_profile.VALUE ('USER_ID');
g_resp_appl_id NUMBER := fnd_profile.VALUE ('RESP_APPL_ID');
g_resp_id NUMBER := fnd_profile.VALUE ('RESP_ID');
g_org_id NUMBER := fnd_profile.VALUE ('ORG_ID');
g_iriso_lookup_type VARCHAR2 (50) := 'XX_IR_ISO_TAX_VALIDATION';
g_regime_code_lookup_vl VARCHAR2 (30) := 'REGIME_CODE';
PROCEDURE load_data_webadi_stg_prc (
p_item_code IN VARCHAR2,
p_qty IN VARCHAR2,
p_uom IN VARCHAR2,
p_source_inv IN VARCHAR2,
p_destination_inv IN VARCHAR2,
p_from_sub_inv IN VARCHAR2,
p_to_sub_inv IN VARCHAR2,
p_need_by_date IN DATE,
-- p_packing_code IN VARCHAR2,
p_grade IN VARCHAR2,
-- p_upload_id IN VARCHAR2,
p_transfer_type IN VARCHAR2,
p_hdr_preferred_transporter IN VARCHAR2,
p_lin_shipping_instructions IN VARCHAR2,
p_lin_packing_instructions IN VARCHAR2,
p_hdr_inspection IN VARCHAR2,
p_hdr_trial_order IN VARCHAR2,
p_hdr_insurance IN VARCHAR2,
p_hdr_insurance_policy_number IN VARCHAR2,
p_hdr_branding_required IN VARCHAR2,
p_hdr_special_instructions IN VARCHAR2,
p_hdr_tc_standard IN VARCHAR2,
p_hdr_small_quantity_surcharge IN VARCHAR2,
p_hdr_anticipatory IN VARCHAR2,
p_lin_end_application IN VARCHAR2,
p_lin_f_truck_load_ponable_fg IN VARCHAR2,
p_lin_planning_required IN VARCHAR2,
p_lin_csr_number IN VARCHAR2,
p_lin_diversion_order_number IN VARCHAR2,
p_ext_lin_length_tolerance_tar IN VARCHAR2,
p_ext_lin_length_tolerance_max IN VARCHAR2,
p_ext_lin_length_tolerance_min IN VARCHAR2,
p_ext_lin_conductivity_target IN VARCHAR2,
p_ext_lin_conductivity_max IN VARCHAR2,
p_ext_lin_conductivity_min IN VARCHAR2,
p_ext_lin_end_segment IN VARCHAR2,
p_ext_lin_ust_on_billet IN VARCHAR2,
p_ext_lin_ust_on_section IN VARCHAR2,
p_ext_lin_reeling IN VARCHAR2,
p_ext_lin_coil_weight_target IN VARCHAR2,
p_ext_lin_coil_weight_max IN VARCHAR2,
p_ext_lin_coil_weight_min IN VARCHAR2,
p_ext_lin_package_weight_ta IN VARCHAR2,
p_ext_lin_package_weight_ma IN VARCHAR2,
p_ext_lin_package_weight_mi IN VARCHAR2,
p_ext_lin_width_tolerance_tar IN VARCHAR2,
p_ext_lin_width_tolerance_max IN VARCHAR2,
p_ext_lin_width_tolerance_min IN VARCHAR2,
p_ext_lin_thick_tolerance_ta IN VARCHAR2,
p_ext_lin_thick_tolerance_ma IN VARCHAR2,
p_ext_lin_thick_tolerance_mi IN VARCHAR2,
p_frp_lin_ulti_ten_stren_min IN VARCHAR2,
p_frp_lin_coil_dens_range_min IN VARCHAR2,
p_frp_lin_coil_dens_range_max IN VARCHAR2,
p_frp_lin_coil_dens_range_tar IN VARCHAR2,
p_frp_lin_coil_od_min IN VARCHAR2,
p_frp_lin_coil_od_max IN VARCHAR2,
p_frp_lin_coil_od_target IN VARCHAR2,
p_frp_lin_coil_id_min IN VARCHAR2,
p_frp_lin_coil_id_max IN VARCHAR2,
p_frp_lin_coil_id_target IN VARCHAR2,
p_frp_lin_elongation_min IN VARCHAR2,
p_frp_lin_elongation_max IN VARCHAR2,
p_frp_lin_elongation_target IN VARCHAR2,
p_frp_lin_ulti_ten_stren_max IN VARCHAR2,
p_frp_lin_ulti_ten_stren_tar IN VARCHAR2,
p_frp_lin_yield_strength_min IN VARCHAR2,
p_frp_lin_yield_strength_max IN VARCHAR2,
p_frp_lin_yield_strength_tar IN VARCHAR2,
p_frp_lin_conductivity_min IN VARCHAR2,
p_frp_lin_conductivity_max IN VARCHAR2,
p_frp_lin_conductivity_target IN VARCHAR2,
p_frp_lin_length_tolerance_min IN VARCHAR2,
p_frp_lin_length_tolerance_max IN VARCHAR2,
p_frp_lin_length_tolerance_tar IN VARCHAR2,
p_frp_lin_thick_tolerance_mi IN VARCHAR2,
p_frp_lin_thick_tolerance_ma IN VARCHAR2,
p_frp_lin_thick_tolerance_ta IN VARCHAR2,
p_frp_lin_width_tolerance_min IN VARCHAR2,
p_frp_lin_width_tolerance_max IN VARCHAR2,
p_frp_lin_width_tolerance_tar IN VARCHAR2,
p_frp_lin_package_weight_mi IN VARCHAR2,
p_frp_lin_package_weight_ma IN VARCHAR2,
p_frp_lin_package_weight_ta IN VARCHAR2,
p_frp_lin_coil_weight_min IN VARCHAR2,
p_frp_lin_coil_weight_max IN VARCHAR2,
p_frp_lin_coil_weight_target IN VARCHAR2,
p_frp_lin_end_segment IN VARCHAR2,
p_frp_lin_coil_orientation IN VARCHAR2,
p_frp_lin_core_type IN VARCHAR2,
p_frp_lin_ral_code IN VARCHAR2,
p_frp_lin_spool_type IN VARCHAR2,
p_prm_lin_conductivity_target IN VARCHAR2,
p_prm_lin_conductivity_max IN VARCHAR2,
p_prm_lin_conductivity_min IN VARCHAR2,
p_prm_lin_ust_on_billet IN VARCHAR2,
p_prm_lin_end_segment IN VARCHAR2
-- ,p_deliver_at_location IN VARCHAR2,
,P_CUST_PO IN VARCHAR2,
p_shipment_above_toler IN VARCHAR2,
p_shipment_below_toler IN VARCHAR2
-- ,P_CUSTOMER_PO IN VARCHAR2
)
IS
-- l_grade_exp EXCEPTION;
l_itemcode_exp EXCEPTION;
l_qty_exp EXCEPTION;
l_source_inv_exp EXCEPTION;
l_destination_inv_exp EXCEPTION;
l_meaning fnd_lookup_values.meaning%type;
BEGIN
IF p_item_code IS NULL
THEN
RAISE l_itemcode_exp;
ELSIF p_qty IS NULL
THEN
RAISE l_qty_exp;
ELSIF p_source_inv IS NULL
THEN
RAISE l_source_inv_exp;
ELSIF p_destination_inv IS NULL
THEN
RAISE l_destination_inv_exp;
END IF;
BEGIN -- Added on 05-jan-2020 INT-O2C-6055
SELECT meaning
INTO l_meaning
FROM fnd_lookup_values
WHERE lookup_type = 'XX_IR_ISO_TAX_VALIDATION'
AND tag = 'IR'
AND lookup_code = 'HEADER_CONTEXT_IR'
AND TO_DATE (NVL (end_date_active, SYSDATE), 'DD/MM/RRRR') >=
TO_DATE (SYSDATE, 'DD/MM/RRRR')
AND enabled_flag = 'Y';
EXCEPTION
WHEN OTHERS
THEN
l_meaning := '';
END;
INSERT INTO XX_po_requisitions_int_stg
(item_code, qty, uom, source_inv,
destination_inv, from_sub_inv, to_sub_inv,
need_by_date, grade, creation_date, last_update_date,
last_updated_by, created_by, import_source,
upload_id,
attribute8,
transfer_type, hdr_preferred_transporter,
lin_shipping_instructions, lin_packing_instructions,
hdr_inspection, hdr_trial_order, hdr_insurance,
hdr_insurance_policy_number, hdr_branding_required,
hdr_special_instructions, hdr_tc_standard,
hdr_small_quantity_surcharge, hdr_anticipatory,
lin_end_application, lin_f_truck_load_ponable_flag,
lin_planning_required, lin_csr_number,
lin_diversion_order_number,
ext_lin_length_tolerance_tar,
ext_lin_length_tolerance_max,
ext_lin_length_tolerance_min,
ext_lin_conductivity_target, ext_lin_conductivity_max,
ext_lin_conductivity_min, ext_lin_end_segment,
ext_lin_ust_on_billet, ext_lin_ust_on_section,
ext_lin_reeling, ext_lin_coil_weight_target,
ext_lin_coil_weight_max, ext_lin_coil_weight_min,
ext_lin_package_weight_ta, ext_lin_package_weight_ma,
ext_lin_package_weight_mi,
ext_lin_width_tolerance_target,
ext_lin_width_tolerance_max,
ext_lin_width_tolerance_min,
ext_lin_thickness_tolerance_ta,
ext_lin_thickness_tolerance_ma,
ext_lin_thickness_tolerance_mi,
frp_lin_ultimate_ten_stren_min,
frp_lin_coil_density_range_min,
frp_lin_coil_density_range_max,
frp_lin_coil_density_range_tar, frp_lin_coil_od_min,
frp_lin_coil_od_max, frp_lin_coil_od_target,
frp_lin_coil_id_min, frp_lin_coil_id_max,
frp_lin_coil_id_target, frp_lin_elongation_min,
frp_lin_elongation_max, frp_lin_elongation_target,
frp_lin_ultimate_ten_stren_max,
frp_lin_ultimate_ten_stren_tar,
frp_lin_yield_strength_min,
frp_lin_yield_strength_max,
frp_lin_yield_strength_target, frp_lin_conductivity_min,
frp_lin_conductivity_max, frp_lin_conductivity_target,
frp_lin_length_tolerance_min,
frp_lin_length_tolerance_max,
frp_lin_length_tolerance_targe,
frp_lin_thickness_tolerance_mi,
frp_lin_thickness_tolerance_ma,
frp_lin_thickness_tolerance_ta,
frp_lin_width_tolerance_min,
frp_lin_width_tolerance_max,
frp_lin_width_tolerance_target,
frp_lin_package_weight_mi, frp_lin_package_weight_ma,
frp_lin_package_weight_ta, frp_lin_coil_weight_min,
frp_lin_coil_weight_max, frp_lin_coil_weight_target,
frp_lin_end_segment, frp_lin_coil_orientation,
frp_lin_core_type, frp_lin_ral_code,
frp_lin_spool_type, prm_lin_conductivity_target,
prm_lin_conductivity_max, prm_lin_conductivity_min,
prm_lin_ust_on_billet, prm_lin_end_segment
--,deliver_at_location
,cust_po
-- ,customer_po -- ,
--REQUISITION_NUMBER ,
-- REQ_LINE_NUM ,
-- ORG_ID
,req_hdr_attribute_catergory
,shipment_above_tolerance,
shipment_below_tolerance
)
VALUES (p_item_code, p_qty, p_uom, p_source_inv,
p_destination_inv, p_from_sub_inv, p_to_sub_inv,
p_need_by_date, p_grade, SYSDATE, SYSDATE,
g_user_id, g_user_id, 'IMPORT_REQ_WEBADI',
TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS'),
xxhil.XX_po_req_webadi_lines_s.NEXTVAL,
p_transfer_type, p_hdr_preferred_transporter,
p_lin_shipping_instructions, p_lin_packing_instructions,
p_hdr_inspection, p_hdr_trial_order, p_hdr_insurance,
p_hdr_insurance_policy_number, p_hdr_branding_required,
p_hdr_special_instructions, p_hdr_tc_standard,
p_hdr_small_quantity_surcharge, p_hdr_anticipatory,
p_lin_end_application, p_lin_f_truck_load_ponable_fg,
p_lin_planning_required, p_lin_csr_number,
p_lin_diversion_order_number,
p_ext_lin_length_tolerance_tar,
p_ext_lin_length_tolerance_max,
p_ext_lin_length_tolerance_min,
p_ext_lin_conductivity_target, p_ext_lin_conductivity_max,
p_ext_lin_conductivity_min, p_ext_lin_end_segment,
p_ext_lin_ust_on_billet, p_ext_lin_ust_on_section,
p_ext_lin_reeling, p_ext_lin_coil_weight_target,
p_ext_lin_coil_weight_max, p_ext_lin_coil_weight_min,
p_ext_lin_package_weight_ta, p_ext_lin_package_weight_ma,
p_ext_lin_package_weight_mi,
p_ext_lin_width_tolerance_tar,
p_ext_lin_width_tolerance_max,
p_ext_lin_width_tolerance_min,
p_ext_lin_thick_tolerance_ta,
p_ext_lin_thick_tolerance_ma,
p_ext_lin_thick_tolerance_mi,
p_frp_lin_ulti_ten_stren_min,
p_frp_lin_coil_dens_range_min,
p_frp_lin_coil_dens_range_max,
p_frp_lin_coil_dens_range_tar, p_frp_lin_coil_od_min,
p_frp_lin_coil_od_max, p_frp_lin_coil_od_target,
p_frp_lin_coil_id_min, p_frp_lin_coil_id_max,
p_frp_lin_coil_id_target, p_frp_lin_elongation_min,
p_frp_lin_elongation_max, p_frp_lin_elongation_target,
p_frp_lin_ulti_ten_stren_max,
p_frp_lin_ulti_ten_stren_tar,
p_frp_lin_yield_strength_min,
p_frp_lin_yield_strength_max,
p_frp_lin_yield_strength_tar, p_frp_lin_conductivity_min,
p_frp_lin_conductivity_max, p_frp_lin_conductivity_target,
p_frp_lin_length_tolerance_min,
p_frp_lin_length_tolerance_max,
p_frp_lin_length_tolerance_tar,
p_frp_lin_thick_tolerance_mi,
p_frp_lin_thick_tolerance_ma,
p_frp_lin_thick_tolerance_ta,
p_frp_lin_width_tolerance_min,
p_frp_lin_width_tolerance_max,
p_frp_lin_width_tolerance_tar,
p_frp_lin_package_weight_mi, p_frp_lin_package_weight_ma,
p_frp_lin_package_weight_ta, p_frp_lin_coil_weight_min,
p_frp_lin_coil_weight_max, p_frp_lin_coil_weight_target,
p_frp_lin_end_segment, p_frp_lin_coil_orientation,
p_frp_lin_core_type, p_frp_lin_ral_code,
p_frp_lin_spool_type, p_prm_lin_conductivity_target,
p_prm_lin_conductivity_max, p_prm_lin_conductivity_min,
p_prm_lin_ust_on_billet, p_prm_lin_end_segment
-- ,p_deliver_at_location
,p_cust_po
,l_meaning--'Internal Requisition'
,p_shipment_above_toler
,p_shipment_below_toler
);
NULL;
EXCEPTION
WHEN l_itemcode_exp
THEN
raise_application_error (-20116,
'Error - Item Code Field is mandatory'
);
WHEN l_qty_exp
THEN
raise_application_error (-20116,
'Error - Quantity Field is mandatory'
);
WHEN l_source_inv_exp
THEN
raise_application_error
(-20116,
'Error - Source Inventory Field is mandatory'
);
WHEN l_destination_inv_exp
THEN
raise_application_error (-20116,
'Error - Destination Field is mandatory'
);
WHEN OTHERS
THEN
raise_application_error
(-20117,
'Error Inserting Data into Staging Table. '
|| SQLERRM
);
END load_data_webadi_stg_prc;
PROCEDURE int_req_prc (p_errbuf OUT VARCHAR2, p_retcode OUT NUMBER)
IS
l_verify_flag CHAR (1) := 'Y';
l_error_message VARCHAR2 (3000);
l_org_id hr_operating_units.organization_id%TYPE;
l_org_id_loop hr_operating_units.organization_id%TYPE;
l_item_code mtl_system_items_b.inventory_item_id%TYPE;
l_source_inv org_organization_definitions.organization_id%TYPE;
l_dest_inv org_organization_definitions.organization_id%TYPE;
l_from_sub mtl_secondary_inventories.secondary_inventory_name%TYPE;
l_to_sub mtl_secondary_inventories.secondary_inventory_name%TYPE;
l_charge_acc mtl_parameters.material_account%TYPE;
l_per_id per_people_f.person_id%TYPE;
l_req_id per_people_f.person_id%TYPE;
l_loc_id hr_locations.location_id%TYPE;
l_record_found BOOLEAN := FALSE;
--g_user_id NUMBER := fnd_profile.VALUE ('USER_ID');
l_source_subinv VARCHAR2 (30);
l_dest_subinv VARCHAR2 (30);
l_request VARCHAR2 (100);
l_interface_header_id NUMBER :=0;
l_grade VARCHAR2 (30);
l_grade_code VARCHAR2 (30);
l_item_id_dest mtl_system_items_b.inventory_item_id%TYPE;
l_uom_code VARCHAR2 (30);
l_item_uom VARCHAR2 (30);
lv_conv NUMBER;
l_phase VARCHAR2 (200);
l_status VARCHAR2 (200);
l_to_uom_code VARCHAR2 (10);
l_batch_id NUMBER := 0;
l_line_cnt NUMBER := 0;
l_first_party_reporting NUMBER := 0;
l_third_party_reporting NUMBER := 0;
l_item_reporting_code NUMBER := 0;
l_request_id NUMBER := 0;
l_req_success_cnt NUMBER := 0;
l_source_inv_code mtl_parameters.organization_code%TYPE := NULL;
l_dest_inv_code mtl_parameters.organization_code%TYPE := NULL;
l_temp VARCHAR2 (500) := NULL;
l_mand_count NUMBER;
l_meaning VARCHAR2 (10);
l_tag VARCHAR2 (10);
l_tt_qty_sum NUMBER;
l_tt VARCHAR2 (10);
l_qty_allowed NUMBER;
l_month_date DATE;
l_end_date DATE;
l_quan_rec NUMBER;
l_quan_pend NUMBER;
l_flag1 NUMBER:=1;
l_qty_rail_allow NUMBER;
l_qty_road_allow NUMBER;
l_flag_road NUMBER:=1;
l_flag_rail NUMBER:=1;
l_quan_rec_rl NUMBER;
l_quan_rec_rd NUMBER;
l_quan_pend_rl NUMBER;
l_quan_pend_rd NUMBER;
l_month VARCHAR2(100);
l_tot1_rl NUMBER;
l_tot1_rd NUMBER;
l_tot1_tot NUMBER;
l_tt_qty_sum_d NUMBER;
l_tot_raised_rl NUMBER;
l_tot_raised_rd NUMBER;
l_source_ou varchar2(240):= null;
--- l_record_found Boolean := FALSE;
CURSOR cur_ou_name
IS
SELECT DISTINCT ou_name, destination_inv,
(SELECT organization_code
FROM mtl_parameters
WHERE organization_id =
destination_inv)
dest_org_code,
upload_id
FROM XX_po_requisitions_int_stg
WHERE NVL (status, 'N') = 'N'
GROUP BY ou_name, destination_inv, upload_id;
CURSOR cur_req_hdr (p_ou_name VARCHAR2)
IS
SELECT DISTINCT ou_name, org_id, source_inv, destination_inv,
transfer_type
FROM XX_po_requisitions_int_stg
WHERE NVL (status, 'N') = 'N' AND ou_name = p_ou_name;
CURSOR cur_req_line (
v_ou_name VARCHAR2,
v_src_inv VARCHAR2,
v_dest_inv VARCHAR2
)
IS
SELECT ROWID row_id, ou_name, item_code, source_inv,
destination_inv, from_sub_inv, to_sub_inv, qty, uom,
status, NVL (need_by_date, TRUNC (SYSDATE)) need_by_date,
grade, transfer_type, import_source,Cust_po,req_hdr_attribute_catergory
FROM XX_po_requisitions_int_stg
WHERE NVL (status, 'N') = 'N'
AND ou_name = v_ou_name
AND source_inv = v_src_inv
AND destination_inv = v_dest_inv
ORDER BY upload_id, attribute8;
CURSOR cur_org_id
IS
SELECT DISTINCT org_id, import_source
FROM XX_po_req_org_details
;
BEGIN
--dbms_output.put_line();
fnd_file.put_line (fnd_file.LOG, '***********Start IR***********');
BEGIN
------update OU Name based on Destination Ing Org. It will be Org ID-------
UPDATE XX_po_requisitions_int_stg
SET ou_name =
(SELECT NAME
FROM hr_operating_units
WHERE organization_id =
(SELECT operating_unit
FROM org_organization_definitions
WHERE organization_id = destination_inv)),
org_id =
(SELECT organization_id
FROM hr_operating_units
WHERE organization_id =
(SELECT operating_unit
FROM org_organization_definitions
WHERE organization_id = destination_inv))
WHERE NVL (status, 'N') = 'N';
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error updating OU Name. ' || SQLERRM
);
END;
-----------Delete a month old data from staging-----
DELETE XX_po_requisitions_int_stg
WHERE TRUNC (creation_date) < TRUNC (SYSDATE) - 30;
fnd_file.put_line (fnd_file.LOG,
'Months old '
|| SQL%ROWCOUNT
|| ' Records Deleted from Req Staging Table.'
|| CHR (10)
);
------------Get Batch Id ------------------------
l_batch_id := XX_PO_REQ_WEBADI_BATCH_S.nextval;--- TO_CHAR (SYSDATE, 'YYMMDDHHSS');
BEGIN
FOR ou_name IN cur_ou_name
LOOP
l_line_cnt := 1;
l_source_ou := null;
fnd_file.put_line
(fnd_file.LOG,
CHR (10)
|| 'Details Information For The Destination Inventory : '
|| ou_name.dest_org_code
|| ' Given Below. BatchID:'
|| l_batch_id
);
DELETE FROM XX_po_req_org_details;
COMMIT;
FOR h IN cur_req_hdr (ou_name.ou_name)
LOOP
l_record_found := TRUE;
l_interface_header_id := po_requisitions_interface_s.NEXTVAL;
l_source_inv_code := NULL;
l_dest_inv_code := NULL;
FOR i IN cur_req_line (h.ou_name,
h.source_inv,
h.destination_inv
)
LOOP
l_record_found := TRUE;
l_error_message := NULL;
---------------------------Validation Start----------------------
BEGIN
--------------------Validating Operating Unit--------------------
BEGIN
SELECT organization_id
INTO l_org_id
FROM hr_operating_units
WHERE NAME = TRIM (i.ou_name)
and nvl( date_to,trunc(sysdate)) >= trunc(sysdate);
--AND business_group_id <> 0;
EXCEPTION
WHEN OTHERS
THEN
l_error_message := NULL;
l_org_id := NULL;
l_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' Operating Unit For Destination Organization : '
|| i.destination_inv
|| ' is Invalid. '
|| ','
|| SQLERRM;
fnd_file.put_line (fnd_file.LOG, l_error_message);
END;
INSERT INTO XX_po_req_org_details
VALUES (l_org_id, l_interface_header_id,
i.import_source);
COMMIT;
----------------Validating Source Inventory-------------
BEGIN
SELECT organization_id, organization_code, operating_unit
INTO l_source_inv, l_source_inv_code,l_source_ou
FROM org_organization_definitions
WHERE organization_id = i.source_inv;
EXCEPTION
WHEN OTHERS
THEN
l_error_message := NULL;
l_source_inv := NULL;
l_verify_flag := 'N';
l_error_message :=
'Error: '
|| l_error_message
|| ' Source Inventory :'
|| i.source_inv
||', Source OU:'
||l_source_ou
|| '.'
|| SUBSTR (SQLERRM, 1, 200);
fnd_file.put_line (fnd_file.LOG, l_error_message);
END;
-------------------------Validating Destination Inventory-------------
BEGIN
SELECT organization_id, organization_code
INTO l_dest_inv, l_dest_inv_code
FROM org_organization_definitions
WHERE organization_id = i.destination_inv;
EXCEPTION
WHEN OTHERS
THEN
l_error_message := NULL;
l_dest_inv := NULL;
l_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' Destination Inventory :'
|| i.destination_inv
|| ' not found For Operating Unit -> '
|| h.ou_name
;
fnd_file.put_line (fnd_file.LOG, l_error_message);
END;
------------check Sour Inv org Reporting Code ----------------
BEGIN
SELECT COUNT (jra.reporting_code)
INTO l_first_party_reporting
FROM jai_party_regs_v jprv,
jai_reporting_associations_v jra
WHERE jprv.party_reg_id = jra.entity_id
AND jprv.party_type_code = 'IO'
AND jprv.reg_class_code = 'FIRST_PARTY'
AND jprv.party_id = h.source_inv
AND jra.reporting_type_code in ( SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = g_iriso_lookup_type
AND NVL (end_date_active, TRUNC (SYSDATE)) >= TRUNC (SYSDATE)
AND tag = 'FIRST_PARTY'
)
---XX_po_req_import_int_pkg.get_lookup_meaning('FIRST_PARTY')
AND jra.entity_code = 'FIRST_PARTY'
AND NVL (jra.effective_to, TRUNC (SYSDATE)) >=
TRUNC (SYSDATE)
AND jra.entity_source_table = 'JAI_PARTY_REGS'
AND jra.regime_code in ( SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = g_iriso_lookup_type
AND NVL (end_date_active, TRUNC (SYSDATE)) >= TRUNC (SYSDATE)
AND upper( tag) = upper( g_regime_code_lookup_vl)
);
-- XX_po_req_import_int_pkg.get_lookup_meaning (g_regime_code_lookup_vl);
END;
----if reporting code not defined then error out-----------
IF l_first_party_reporting = 0
THEN
l_first_party_reporting := NULL;
l_verify_flag := 'N';
l_error_message :=
l_error_message
|| './ First Party Report Code not defined for Source Inventory Org : '
|| h.source_inv ||','||l_source_inv_code ||--,Reporting Type Code:'||XX_po_req_import_int_pkg.get_lookup_meaning ('FIRST_PARTY')
'. JAI_STATE_FIRST_PARTY_HIL, Regime Code:';---|| XX_po_req_import_int_pkg.get_lookup_meaning (g_regime_code_lookup_vl);
fnd_file.put_line (fnd_file.LOG, l_error_message);
END IF;
------------Check Customer Site Reporting Defined based on Destination Inventory Attached in Customer Site business Purpose--------
BEGIN
SELECT COUNT (jra.reporting_code)
INTO l_third_party_reporting
FROM jai_party_regs_v jprv,
jai_reporting_associations_v jra
WHERE 1 = 1
AND jprv.party_reg_id = jra.entity_id
AND jprv.party_type_code LIKE 'THIRD_PARTY%'
AND jprv.party_class_code = 'CUSTOMER'
AND jprv.customer_flag = 'Y'
AND jra.entity_source_table = 'JAI_PARTY_REGS'
AND jra.entity_code LIKE 'THIRD_PARTY%'
AND jra.reporting_type_code in ( SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = g_iriso_lookup_type
AND NVL (end_date_active, TRUNC (SYSDATE)) >= TRUNC (SYSDATE)
AND tag = 'THIRD_PARTY'
)
--XX_po_req_import_int_pkg.get_lookup_meaning ('THIRD_PARTY')
AND jra.regime_code in ( SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = g_iriso_lookup_type
AND NVL (end_date_active, TRUNC (SYSDATE)) >= TRUNC (SYSDATE)
AND tag = g_regime_code_lookup_vl
)
---XX_po_req_import_int_pkg.get_lookup_meaning (g_regime_code_lookup_vl)
AND NVL (jra.effective_to, TRUNC (SYSDATE)) >=
TRUNC (SYSDATE)
AND (jprv.party_id,
jprv.party_site_id,
jprv.org_id
) IN (
SELECT hcsa.cust_account_id,
hcsa.cust_acct_site_id, hcsa.org_id
FROM hz_cust_acct_sites_all hcsa,
hz_party_sites hps,
hz_cust_site_uses_all hcsua,
po_location_associations_all pla
WHERE 1 = 1
AND hcsa.party_site_id =
hps.party_site_id
AND hcsa.cust_acct_site_id =
hcsua.cust_acct_site_id
AND hcsua.status = 'A'
AND hcsua.site_use_id = pla.site_use_id
AND hcsua.org_id = pla.org_id
AND pla.organization_id =
h.destination_inv
AND pla.org_id IN (
SELECT operating_unit
FROM org_organization_definitions
WHERE organization_id =
h.source_inv));
EXCEPTION
WHEN OTHERS
THEN
l_verify_flag := 'N';
l_error_message :=
l_error_message
|| '/. Error Fetching Third Party Reporting Code. Destination Inv: '
|| h.destination_inv
|| ','
|| l_dest_inv_code
|| ',Destination OU Name:'
|| h.ou_name
||', Source OU Name:'--commented on 23062021
|| l_source_ou
/* || ',Reorting Type Code:'
|| XX_po_req_import_int_pkg.get_lookup_meaning ('THIRD_PARTY')
|| ',Regime Code:'
|| XX_po_req_import_int_pkg.get_lookup_meaning (g_regime_code_lookup_vl)*/
|| SUBSTR (SQLERRM, 1, 200);
fnd_file.put_line (fnd_file.LOG, l_error_message);
END;
----if reporting code not defined then error out-----------
IF l_third_party_reporting = 0
THEN
l_third_party_reporting := NULL;
l_verify_flag := 'N';
l_error_message :=
l_error_message
|| './ Third Party Report Code not defined for Customer. Destination Inv: '
|| h.destination_inv
|| ','
|| l_dest_inv_code
|| ',Destination OU Name:'
|| h.ou_name
||', Source OU Name:'--commented on 23062021
|| l_source_ou
|| '. Reporting Type Code: XX_TWO_DIGIT_STATE_CODE, HIL_GST_REGIME';
fnd_file.put_line (fnd_file.LOG, l_error_message);
END IF;
--------------------------Validating Item Number--------------------
BEGIN
SELECT DISTINCT inventory_item_id
INTO l_item_code
FROM mtl_system_items
WHERE segment1 = i.item_code
AND organization_id = l_source_inv;
EXCEPTION
WHEN OTHERS
THEN
l_error_message := NULL;
l_item_code := NULL;
l_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' Item Code :'
|| i.item_code
|| ' Not Valid For Inventory Organization -> '
|| i.source_inv;
fnd_file.put_line (fnd_file.LOG, l_error_message);
END;
BEGIN
SELECT DISTINCT inventory_item_id
INTO l_item_id_dest
FROM mtl_system_items
WHERE segment1 = i.item_code
AND organization_id = l_dest_inv;
EXCEPTION
WHEN OTHERS
THEN
l_error_message := NULL;
l_item_id_dest := NULL;
l_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' Item Code :'
|| i.item_code
|| ' Not Valid For Inventory Organization -> '
|| i.destination_inv;
fnd_file.put_line (fnd_file.LOG, l_error_message);
END;
----------------------------Transfer Type Mandatory ------------------------------------------------
BEGIN
SELECT COUNT(MEANING) INTO l_mand_count
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'XX_IR_SBU_VALIDATION'
AND ENABLED_FLAG = 'Y'
AND end_date_active IS NULL
AND MEANING IN (SELECT SEGMENT1
FROM MTL_ITEM_CATEGORIES_V
WHERE INVENTORY_ITEM_ID =l_item_id_dest --197916
AND ORGANIZATION_ID = l_dest_inv --218
AND UPPER(CATEGORY_SET_NAME) = UPPER('SALES'));
EXCEPTION
WHEN OTHERS
THEN
l_verify_flag := 'N';
l_error_message :=
l_error_message
|| '/. Error Fetching SBU Segment1 of this IR. INVENTORY_ITEM_ID: '
|| l_item_id_dest
|| ','
|| ',ORGANIZATION_ID:'
|| l_dest_inv
/* || ',Reorting Type Code:'
|| XX_po_req_import_int_pkg.get_lookup_meaning ('THIRD_PARTY')
|| ',Regime Code:'
|| XX_po_req_import_int_pkg.get_lookup_meaning (g_regime_code_lookup_vl)*/
|| SUBSTR (SQLERRM, 1, 200);
fnd_file.put_line (fnd_file.LOG, l_error_message);
END;
IF l_mand_count > 0 AND i.transfer_type IS NULL
THEN
l_mand_count := NULL;
l_verify_flag := 'N';
l_error_message :=
-- l_error_message ||
'./ IR Header Transfer Type is mandatory for the IR with Item: '||i.item_code
;
fnd_file.put_line (fnd_file.LOG, l_error_message);
END IF;
----------------------------------Validation for RAIL/ROAD-----------------------------------------------------------
BEGIN
SELECT MEANING--,TAG
INTO l_meaning --, l_tag
FROM FND_LOOKUP_VALUES_VL A2
WHERE a2.lookup_type= 'XX_DAP_IR_VALIDATION'
AND a2.enabled_flag = 'Y'
AND a2.end_date_active is NULL
AND a2.meaning = i.item_code;
--AND tag = i.destination_inv;
EXCEPTION
WHEN OTHERS THEN
l_meaning := NULL;
-- l_tag := NULL;
END;
fnd_file.put_line (fnd_file.LOG,'Dest Inv Id :'
||i.destination_inv
|| ' item_code : '
||i.item_code
);
BEGIN
SELECT sum(QTY) --,transfer_type
INTO l_tt_qty_sum --, l_tt
FROM XX_po_requisitions_int_stg
WHERE item_code in l_meaning
AND destination_inv in i.destination_inv
AND source_inv in i.source_inv
AND ou_name in i.ou_name
AND NVL (status, 'N') = 'N'
AND NVL(transfer_type,'X') = NVL(transfer_type,'X')
AND APPS.XX_IR_ORG_CHECK_P(i.source_inv, i.destination_inv) = 0;
--group by transfer_type;
EXCEPTION
WHEN OTHERS THEN
l_tt_qty_sum := 0;
-- l_tag := NULL;
END;
-----------------------------------------DOF----------------------------------------------------------------------------------
/* BEGIN
SELECT sum(QTY)
INTO l_tt_qty_sum_d
FROM XX_po_requisitions_int_stg
WHERE item_code in l_meaning
AND destination_inv in i.destination_inv --l_tag
AND APPS.XX_IR_ORG_CHECK_P(i.source_inv, i.destination_inv) = 0
group by transfer_type;
EXCEPTION
WHEN OTHERS THEN
l_tt_qty_sum_d := 0;
END;
*/
BEGIN
select qty_allowed, month_date, end_Date
INTO l_qty_allowed, l_month_date,l_end_date
from XXHIL.XX_OM_DISPATCH_PLAN
where ou_id = l_org_id
AND UPPER(prod_segment1) = UPPER(l_meaning)
AND TRUNC(SYSDATE) BETWEEN TRUNC(MONTH_DATE) AND TRUNC(END_DATE);
EXCEPTION
WHEN OTHERS THEN
l_flag1:= 0;
l_month_date:= TO_DATE(SYSDATE,'DD-MON-YY');
l_end_date:= TO_DATE(SYSDATE,'DD-MON-YY');
END;
BEGIN
select sum(sum_qty) INTO l_quan_rec
FROM
(SELECT (sum(rsl.quantity_received) * inv_convert.inv_um_convert(rsl.item_id,((select distinct UOM_CODE from mtl_uom_conversions where unit_of_measure = RSL.unit_of_measure)), 'MT')) sum_qty
from apps.rcv_shipment_lines rsl,apps.po_requisition_lines_all RL ,apps.rcv_shipment_headers rsh
where rsl.item_id IN ( SELECT inventory_item_id
FROM mtl_system_items_b
WHERE UPPER(segment1) IN UPPER(l_meaning)
AND organization_id IN i.destination_inv --l_tag
AND enabled_flag = 'Y'
AND end_date_active is NULL)
AND rsl.requisition_line_id = rl.requisition_line_id
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.source_document_code = 'REQ'
AND rsh.RECEIPT_SOURCE_CODE = 'INTERNAL ORDER'
AND rl.org_id = l_org_id--161 --REQ Destination ORG_ID
AND TRUNC(rsl.creation_date) BETWEEN TRUNC(l_month_date) AND TRUNC(l_end_date) --DATE RANGE OF MONTH DATE AND END DATE
AND RL.org_id NOT IN (SELECT LISTAGG(operating_unit, ', ') WITHIN GROUP (ORDER BY operating_unit DESC) operating_unit FROM apps.ORG_ORGANIZATION_DEFINITIONS WHERE organization_id =i.source_inv)
GROUP BY RSL.unit_of_measure,rsl.item_id
);
EXCEPTION
WHEN OTHERS THEN
l_quan_rec:= 0;
END;
BEGIN
SELECT SUM(sum_qty1) INTO l_quan_pend
FROM
(SELECT (sum(a2.quantity - a2.quantity_delivered) * inv_convert.inv_um_convert(A2.item_id,((select distinct UOM_CODE from mtl_uom_conversions where unit_of_measure = A2.unit_meas_lookup_code)), 'MT')) sum_qty1
FROM apps.po_requisition_headers_all A1, apps.po_requisition_lines_all A2
where a1.requisition_header_id =A2.requisition_header_id
AND authorization_status = 'APPROVED'
AND a1.type_lookup_code = 'INTERNAL'
AND a2.cancel_Date IS NULL
AND TRUNC(A1.CREATION_DATE) <=SYSDATE
AND a2.ITEM_ID IN ( SELECT inventory_item_id
FROM mtl_system_items_b
WHERE UPPER(segment1) IN UPPER(l_meaning)
AND organization_id IN i.destination_inv --l_tag
AND enabled_flag = 'Y'
AND end_date_active is NULL)
AND a1.org_id = l_org_id--212
AND a1.org_id NOT IN (SELECT LISTAGG(operating_unit, ', ') WITHIN GROUP (ORDER BY operating_unit DESC) operating_unit FROM apps.ORG_ORGANIZATION_DEFINITIONS WHERE organization_id =i.source_inv)
GROUP BY A2.item_id,A2.unit_meas_lookup_code
);
EXCEPTION
WHEN OTHERS THEN
l_quan_pend:= 0;
END;
------------------------------------------RAIL----------------------------------------------------------------------
BEGIN
select SUM(PLAN_RAIL) INTO l_qty_rail_allow
from XX_OM_TRAN_PLAN
where ou_id = l_org_id --212
and Plan_prod_cd IN ( SELECT inventory_item_id
FROM mtl_system_items_b
WHERE UPPER(segment1) IN UPPER(l_meaning)
AND organization_id IN i.destination_inv --l_tag
AND enabled_flag = 'Y'
AND end_date_active is NULL)
AND trunc(plan_date)BETWEEN (SELECT trunc(sysdate) - (to_number(to_char(sysdate,'DD')) - 1) FROM dual) AND SYSDATE;
EXCEPTION
WHEN OTHERS THEN
l_flag_rail:= 0;
END;
BEGIN
select (sum(rsl.quantity_received) * inv_convert.inv_um_convert(rsl.item_id,((select distinct UOM_CODE from mtl_uom_conversions where unit_of_measure = RSL.unit_of_measure)), 'MT'))
INTO l_quan_rec_rl
from apps.rcv_shipment_lines rsl,apps.po_requisition_lines_all RL ,apps.rcv_shipment_headers rsh, apps.po_requisition_headers_all prh
where rsl.item_id IN ( SELECT inventory_item_id
FROM mtl_system_items_b
WHERE UPPER(segment1) IN UPPER(l_meaning)
AND organization_id IN i.destination_inv --l_tag
AND enabled_flag = 'Y'
AND end_date_active is NULL)
AND rsl.requisition_line_id = rl.requisition_line_id
AND prh.requisition_header_id = rl.requisition_header_id
AND UPPER(prh.attribute10) = UPPER('RAIL')
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.source_document_code = 'REQ'
AND rsh.RECEIPT_SOURCE_CODE = 'INTERNAL ORDER'
AND rl.org_id = l_org_id--161 --REQ Destination ORG_ID
AND TRUNC(rsl.creation_date) BETWEEN (SELECT trunc(sysdate) - (to_number(to_char(sysdate,'DD')) - 1) FROM dual) AND TRUNC(SYSDATE)
AND RL.org_id NOT IN (SELECT LISTAGG(operating_unit, ', ') WITHIN GROUP (ORDER BY operating_unit DESC) operating_unit FROM apps.ORG_ORGANIZATION_DEFINITIONS WHERE organization_id =i.source_inv)
group by unit_of_measure,RSL.unit_of_measure ,rsl.item_id;
EXCEPTION
WHEN OTHERS THEN
l_quan_rec_rl:= 0;
END;
BEGIN
SELECT (sum(a2.quantity - a2.quantity_delivered) * inv_convert.inv_um_convert(A2.item_id,((select distinct UOM_CODE from mtl_uom_conversions where unit_of_measure = A2.unit_meas_lookup_code)), 'MT')) INTO l_quan_pend_rl
FROM apps.po_requisition_headers_all A1, apps.po_requisition_lines_all A2
where a1.requisition_header_id =A2.requisition_header_id
AND authorization_status = 'APPROVED'
AND a1.type_lookup_code = 'INTERNAL'
AND UPPER(A1.attribute10) = UPPER('RAIL')
AND a2.cancel_Date IS NULL
AND TRUNC(A1.CREATION_DATE) <=SYSDATE
AND a2.ITEM_ID IN ( SELECT inventory_item_id
FROM mtl_system_items_b
WHERE UPPER(segment1) IN UPPER(l_meaning)
AND organization_id IN i.destination_inv --l_tag
AND enabled_flag = 'Y'
AND end_date_active is NULL)
AND a1.org_id = l_org_id--212
AND a1.org_id NOT IN (SELECT LISTAGG(operating_unit, ', ') WITHIN GROUP (ORDER BY operating_unit DESC) operating_unit FROM apps.ORG_ORGANIZATION_DEFINITIONS WHERE organization_id =i.source_inv)
group by A2.unit_meas_lookup_code,a2.item_id;
EXCEPTION
WHEN OTHERS THEN
l_quan_pend_rl:=0;
END;
---------------------------------ROAD----------------------------------------------------------------------------------------
BEGIN
select SUM(PLAN_ROAD) INTO l_qty_road_allow
from XX_OM_TRAN_PLAN
where ou_id = l_org_id --212
and Plan_prod_cd IN ( SELECT inventory_item_id
FROM mtl_system_items_b
WHERE UPPER(segment1) IN UPPER(l_meaning)
AND organization_id IN i.destination_inv --l_tag
AND enabled_flag = 'Y'
AND end_date_active is NULL)
AND trunc(plan_date)BETWEEN (SELECT trunc(sysdate) - (to_number(to_char(sysdate,'DD')) - 1) FROM dual) AND SYSDATE;
EXCEPTION
WHEN OTHERS THEN
l_flag_road:= 0;
END;
BEGIN
select (sum(rsl.quantity_received) * inv_convert.inv_um_convert(rsl.item_id,((select distinct UOM_CODE from mtl_uom_conversions where unit_of_measure = RSL.unit_of_measure)), 'MT'))
INTO l_quan_rec_rd
from apps.rcv_shipment_lines rsl,apps.po_requisition_lines_all RL ,apps.rcv_shipment_headers rsh, apps.po_requisition_headers_all prh
where rsl.item_id IN ( SELECT inventory_item_id
FROM mtl_system_items_b
WHERE UPPER(segment1) IN UPPER(l_meaning)
AND organization_id IN i.destination_inv --l_tag
AND enabled_flag = 'Y'
AND end_date_active is NULL)
AND rsl.requisition_line_id = rl.requisition_line_id
AND prh.requisition_header_id = rl.requisition_header_id
AND UPPER(prh.attribute10) = UPPER('ROAD')
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.source_document_code = 'REQ'
AND rsh.RECEIPT_SOURCE_CODE = 'INTERNAL ORDER'
AND rl.org_id = l_org_id--161 --REQ Destination ORG_ID
AND TRUNC(rsl.creation_date) BETWEEN (SELECT trunc(sysdate) - (to_number(to_char(sysdate,'DD')) - 1) FROM dual) AND TRUNC(SYSDATE)
AND RL.org_id NOT IN (SELECT LISTAGG(operating_unit, ', ') WITHIN GROUP (ORDER BY operating_unit DESC) operating_unit FROM apps.ORG_ORGANIZATION_DEFINITIONS WHERE organization_id =i.source_inv)
group by unit_of_measure,RSL.unit_of_measure ,rsl.item_id;
EXCEPTION
WHEN OTHERS THEN
l_quan_rec_rd:= 0;
END;
BEGIN
SELECT (sum(a2.quantity - a2.quantity_delivered) * inv_convert.inv_um_convert(A2.item_id,((select distinct UOM_CODE from mtl_uom_conversions where unit_of_measure = A2.unit_meas_lookup_code)), 'MT')) INTO l_quan_pend_rd
FROM apps.po_requisition_headers_all A1, apps.po_requisition_lines_all A2
where a1.requisition_header_id =A2.requisition_header_id
AND authorization_status = 'APPROVED'
AND a1.type_lookup_code = 'INTERNAL'
AND UPPER(A1.attribute10) = UPPER('ROAD')
AND a2.cancel_Date IS NULL
AND TRUNC(A1.CREATION_DATE) <=SYSDATE
AND a2.ITEM_ID IN ( SELECT inventory_item_id
FROM mtl_system_items_b
WHERE UPPER(segment1) IN UPPER(l_meaning)
AND organization_id IN i.destination_inv --l_tag
AND enabled_flag = 'Y'
AND end_date_active is NULL)
AND a1.org_id = l_org_id--212
AND a1.org_id NOT IN (SELECT LISTAGG(operating_unit, ', ') WITHIN GROUP (ORDER BY operating_unit DESC) operating_unit FROM apps.ORG_ORGANIZATION_DEFINITIONS WHERE organization_id =i.source_inv)
group by A2.unit_meas_lookup_code,a2.item_id;
EXCEPTION
WHEN OTHERS THEN
l_quan_pend_rd:=0;
END;
BEGIN
select TO_CHAR(SYSDATE,'MON-RRRR') INTO l_month FROM DUAL;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
l_tot1_rl := NVL(l_quan_pend_rl,0) + NVL(l_quan_rec_rl,0) + NVL(l_tt_qty_sum,0);
l_tot1_rd := NVL(l_quan_pend_rd,0) + NVL(l_quan_rec_rd,0) + NVL(l_tt_qty_sum,0);
l_tot1_tot:= NVL(l_quan_pend,0) + NVL(l_quan_rec,0) + NVL(l_tt_qty_sum,0);
l_tot_raised_rl:= NVL(l_quan_pend_rl,0) + NVL(l_quan_rec_rl,0);
l_tot_raised_rd:= NVL(l_quan_pend_rd,0) + NVL(l_quan_rec_rd,0);
/* fnd_file.put_line (fnd_file.LOG,'Rail l_org_id'
|| l_org_id
|| ' Meaning :'
||l_meaning
||' l_qty_rail_allow: '
||l_qty_rail_allow);
fnd_file.put_line (fnd_file.LOG, 'IR Qty raised against RAIL: '
|| l_tot1_rl
|| 'IR Qty raised against ROAD: '
||l_tot1_rd
-- ||' Overall Amount Allowed: '
-- ||l_tot1_tot
||' IR Qty raised Overall (DOP Supply Form) : '
||l_qty_allowed);
*/
fnd_file.put_line (fnd_file.LOG, 'Overall Qty raised considering this IR : '
|| l_tot1_tot
|| ' Overall qty allowed for the current month (DOF Supply plan): '
||l_qty_allowed
);
-------------------------------------------------Validation for RAIL-----------------------------------------------------------------------------------------------------
IF UPPER(i.transfer_type) = UPPER('RAIL') AND l_meaning IS NOT NULL
THEN
fnd_file.put_line (fnd_file.LOG, 'Quantity already raised for RAIL: '
|| l_tot_raised_rl
|| ' Quantity raised for RAIL considering this IR: '
||l_tot1_rl
||' RAIL Qty allowed as of current Month-Date (Dispatch Plan) : '
|| l_qty_rail_allow
);
IF l_qty_rail_allow IS NULL
THEN
l_verify_flag := 'N';
l_error_message :=
-- l_error_message ||
'./ No data found for RAIL in DISPATCH supply plan form for the used item :'
||i.item_code
||' and dest_org_id: '
||i.destination_inv
||' for Month: '
||l_month;
fnd_file.put_line (fnd_file.LOG, l_error_message);
ELSIF l_tot1_rl > l_qty_rail_allow
THEN
l_verify_flag := 'N';
l_error_message :=
-- l_error_message ||
'./ Requestion Quantity(RAIL) Exceeds the planned Qty for this item in the Destination OU as per Dispatch supply plan form: Planned Qty: '
||l_qty_rail_allow
||' for Month: '
||l_month;
fnd_file.put_line (fnd_file.LOG, l_error_message);
END IF;
END IF;
-------------------------------------------------Validation for ROAD-----------------------------------------------------------------------------------------------------
IF UPPER(i.transfer_type) = UPPER('ROAD') AND l_meaning IS NOT NULL
THEN
fnd_file.put_line (fnd_file.LOG, 'Quantity already raised for ROAD: '
|| l_tot_raised_rd
|| ' Quantity raised for ROAD considering this IR: '
||l_tot1_rd
|| ' ROAD Qty allowed as of current Month-Date (Dispatch Plan): '
||l_qty_road_allow
);
IF l_qty_road_allow IS NULL
THEN
l_verify_flag := 'N';
l_error_message :=
-- l_error_message ||
'./ No data found for ROAD in DISPATCH supply plan form for the used item :'
||i.item_code
||' and dest_org_id: '
||i.destination_inv
||' for Month: '
||l_month;
fnd_file.put_line (fnd_file.LOG, l_error_message);
ELSIF l_tot1_rd > l_qty_road_allow
THEN
l_verify_flag := 'N';
l_error_message :=
-- l_error_message ||
'./ Requestion Quantity(ROAD) Exceeds the planned Qty for this item in the Destination OU as per Dispatch supply plan form: Planned Qty: '
||l_qty_road_allow
||' for Month: '
||l_month;
fnd_file.put_line (fnd_file.LOG, l_error_message);
END IF;
END IF;
-------------------------------------Validation for DOF Supply Form ------------------------------------------------------------------
/* IF l_meaning IS NOT NULL
THEN
IF l_qty_allowed IS NULL
THEN
l_verify_flag := 'N';
l_error_message :=
-- l_error_message ||
'./ No data found in DOF supply plan form for the used item :'
||i.item_code
||' and dest_org_id: '
||i.destination_inv
||' for Month: '
||l_month;
fnd_file.put_line (fnd_file.LOG, l_error_message);
ELSIF l_tot1_tot > l_qty_allowed
THEN
l_verify_flag := 'N';
l_error_message :=
-- l_error_message ||
'./ Requestion Quantity Exceeds the planned Qty for this item in the Destination OU as per DOF supply plan form: Planned Qty: '
||l_qty_allowed
||' for Month: '
||l_month;
fnd_file.put_line (fnd_file.LOG, l_error_message);
END IF;
END IF;
*/
IF l_meaning IS NOT NULL AND APPS.XX_IR_ORG_CHECK_P(i.source_inv, i.destination_inv) = 0
THEN
IF l_qty_allowed IS NULL
THEN
l_verify_flag := 'N';
l_error_message :=
-- l_error_message ||
'./ No data found in DOF supply plan form for the used item :'
||i.item_code
||' and dest_org_id: '
||i.destination_inv
||' for Month: '
||l_month;
fnd_file.put_line (fnd_file.LOG, l_error_message);
ELSIF l_tot1_tot > l_qty_allowed
THEN
l_verify_flag := 'N';
l_error_message :=
-- l_error_message ||
'./ Requestion Quantity Exceeds the planned Qty for this item in the Destination OU as per DOF supply plan form: Planned Qty: '
||l_qty_allowed
||' for Month: '
||l_month;
fnd_file.put_line (fnd_file.LOG, l_error_message);
END IF;
END IF;
---------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
SELECT COUNT (jra.reporting_code)
INTO l_item_reporting_code
FROM jai_item_templ_hdr_v jith,
jai_reporting_associations_v jra
WHERE 1 = 1
AND jith.template_hdr_id = jra.entity_id
AND jith.entity_type_code = 'ITEM'
AND jith.inventory_item_id = l_item_code
AND jith.organization_id = l_source_inv
AND jra.reporting_type_code in ( SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = g_iriso_lookup_type
AND NVL (end_date_active, TRUNC (SYSDATE)) >= TRUNC (SYSDATE)
AND tag = 'ITEM_CLASSIFICATION'
)
--- XX_po_req_import_int_pkg.get_lookup_meaning('ITEM_CLASSIFICATION')
AND jra.reporting_usage = 'TD'
--- AND jra.regime_code = XX_po_req_import_int_pkg.get_lookup_meaning (g_regime_code_lookup_vl)
AND NVL (jra.effective_to, TRUNC (SYSDATE)) >=
TRUNC (SYSDATE)
AND jra.entity_source_table = 'JAI_ITEM_TEMPL_HDR'
AND jra.entity_code = 'ITEM';
EXCEPTION
WHEN OTHERS
THEN
l_item_reporting_code := NULL;
l_error_message := NULL;
l_verify_flag := 'N';
l_error_message :=
' Error Fetching Item Reporting Code :'
|| i.item_code
|| ', Source InvOrg:'
|| l_source_inv
|| ','
|| l_source_inv_code
|| '.'
|| SUBSTR (SQLERRM, 1, 200);
fnd_file.put_line (fnd_file.LOG, l_error_message);
END;
----if reporting code not defined then error out-----------
IF l_item_reporting_code = 0
THEN
l_item_reporting_code := NULL;
l_verify_flag := 'N';
l_error_message :=
'./ Item Reporting Code not defined.Source Inv: '
|| h.source_inv||','||l_source_inv_code
|| ', ItemCode:'
|| i.item_code;
-- || '. Reporting Type Code: '||XX_po_req_import_int_pkg.get_lookup_meaning ('ITEM_CLASSIFICATION');
fnd_file.put_line (fnd_file.LOG, l_error_message);
END IF;
--------------------------Validating Item Grade-----------------------
IF l_grade_code IS NOT NULL
THEN
BEGIN
SELECT grade_code
INTO l_grade_code
FROM mtl_grades
WHERE grade_code = i.grade;
EXCEPTION
WHEN OTHERS
THEN
l_error_message := NULL;
l_verify_flag := 'N';
l_grade_code := NULL;
l_error_message :=
l_error_message
|| ' Grade Code :'
|| i.grade
|| ' Not Found In Grade Master -> ';
fnd_file.put_line (fnd_file.LOG,
l_error_message);
END;
END IF;
IF l_grade_code IS NOT NULL
THEN
BEGIN
SELECT NVL (grade_control_flag, 'N')
INTO l_grade
FROM mtl_system_items
WHERE inventory_item_id = l_item_code
AND organization_id = l_dest_inv;
EXCEPTION
WHEN OTHERS
THEN
l_grade := NULL;
END;
IF l_grade = 'N'
THEN
l_error_message := NULL;
l_verify_flag := 'N';
l_error_message :=
l_error_message
|| 'The Item '
|| i.item_code
|| ' Is Not Grade Control For Destination Organization -> '
|| i.destination_inv;
fnd_file.put_line (fnd_file.LOG, l_error_message);
END IF;
END IF;
-------------------------Validating From Sub Inventory----------------
BEGIN
SELECT organization_id, secondary_inventory_name
INTO l_from_sub, l_source_subinv
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = i.from_sub_inv
AND organization_id = l_source_inv;
EXCEPTION
WHEN OTHERS
THEN
l_from_sub := NULL;
l_source_subinv := NULL;
END;
------------------------Validating To Sub Inventory--------------------
BEGIN
SELECT organization_id, secondary_inventory_name
INTO l_to_sub, l_dest_subinv
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = i.to_sub_inv
AND organization_id = l_dest_inv;
EXCEPTION
WHEN OTHERS
THEN
l_to_sub := NULL;
l_dest_subinv := NULL;
END;
--------------------Validating Charge Account---------------------
BEGIN
SELECT material_account
INTO l_charge_acc
FROM mtl_parameters
WHERE organization_id = l_dest_inv;
EXCEPTION
WHEN OTHERS
THEN
l_error_message := NULL;
l_to_sub := NULL;
l_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' Charge Acc'
|| ' not found For Operating Unit -> '
|| h.ou_name;
fnd_file.put_line (fnd_file.LOG, l_error_message);
END;
----------------------Validating Location Id---------------------
BEGIN
SELECT location_id
INTO l_loc_id
FROM hr_locations
WHERE inventory_organization_id = l_dest_inv;
EXCEPTION
WHEN OTHERS
THEN
l_error_message := NULL;
l_to_sub := NULL;
l_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' Location Id of Destination Organization'
|| ' not found For Operating Unit -> '
|| h.ou_name;
fnd_file.put_line (fnd_file.LOG, l_error_message);
END;
--------------------Validating Preparer Id---------------------
BEGIN
SELECT employee_id
INTO l_per_id
FROM fnd_user
WHERE user_id = g_user_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_message := NULL;
l_to_sub := NULL;
l_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' Preparer Id'
|| ' not found For Operating Unit -> '
|| h.ou_name;
fnd_file.put_line (fnd_file.LOG, l_error_message);
END;
----------------------Validating Unit Of Measure----------------
BEGIN
SELECT uom_code
INTO l_uom_code
FROM mtl_units_of_measure_vl
WHERE ---unit_of_measure = i.uom
(unit_of_measure = i.uom OR uom_code = i.uom
);
EXCEPTION
WHEN OTHERS
THEN
l_error_message := NULL;
l_verify_flag := 'N';
l_uom_code := NULL;
l_error_message :=
l_error_message
|| ' UOM '
|| i.uom
|| ' Not Found In System -> ';
fnd_file.put_line (fnd_file.LOG, l_error_message);
END;
BEGIN
SELECT mtl.primary_uom_code
INTO l_to_uom_code
FROM mtl_system_items mtl
WHERE inventory_item_id = l_item_code
AND organization_id = l_dest_inv;
EXCEPTION
WHEN OTHERS
THEN
l_to_uom_code := NULL;
END;
BEGIN
lv_conv :=
inv_convert.inv_um_convert
(item_id => l_item_code,
organization_id => l_dest_inv,
PRECISION => 5,
from_quantity => i.qty,
from_unit => l_uom_code,
to_unit => l_to_uom_code,
from_name => NULL,
to_name => NULL
);
IF lv_conv = -99999
THEN
l_verify_flag := 'N';
fnd_file.put_line
(fnd_file.LOG,
'UOM Setup Is Missing For The UOM --> '
|| i.uom
|| ' And The Item Is '
|| i.item_code
);
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error!! conversion error'
);
END;
END;
IF l_verify_flag <> 'N'
THEN
BEGIN
INSERT INTO po_requisitions_interface_all
(interface_source_code, org_id,
requisition_type, item_id, quantity,
uom_code, authorization_status,
preparer_id,
destination_organization_id,
deliver_to_location_id,
charge_account_id, need_by_date,
source_type_code,
source_organization_id,
destination_type_code,
requisition_header_id,
deliver_to_requestor_id,
source_subinventory,
destination_subinventory,
preferred_grade, batch_id,
header_attribute10, creation_date,
line_num,header_attribute11,
header_attribute_category
)
VALUES (i.import_source, l_org_id,
'INTERNAL', l_item_code, i.qty,
l_uom_code, 'APPROVED',
l_per_id,
l_dest_inv,
l_loc_id,
l_charge_acc, i.need_by_date,
'INVENTORY',
l_source_inv,
'INVENTORY',
l_interface_header_id,
l_per_id,
l_source_subinv,
l_dest_subinv,
i.grade, l_batch_id,
i.transfer_type, SYSDATE,
l_line_cnt,
i.cust_po,
i.req_hdr_attribute_catergory
);
l_line_cnt := l_line_cnt + 1; --- SQL%ROWCOUNT;
EXCEPTION
WHEN OTHERS
THEN
l_error_message := SQLERRM;
l_verify_flag := 'N';
UPDATE XX_po_requisitions_int_stg
SET status = 'FAILED',
error_message =
'Error Inserting Line Interface '
|| l_error_message,
last_update_date = SYSDATE
WHERE ou_name = i.ou_name
AND item_code = i.item_code
AND source_inv = i.source_inv
AND destination_inv = i.destination_inv
AND NVL (from_sub_inv, 'NULL') =
NVL (i.from_sub_inv, 'NULL')
AND NVL (to_sub_inv, 'NULL') =
NVL (i.to_sub_inv, 'NULL')
AND qty = i.qty
AND uom = i.uom
AND need_by_date = i.need_by_date
AND status IS NULL
AND upload_id = ou_name.upload_id;
fnd_file.put_line (fnd_file.LOG, l_error_message);
END;
UPDATE XX_po_requisitions_int_stg
SET status = 'S',
attribute10 = l_batch_id,
org_id = l_org_id,
last_update_date = SYSDATE,
attribute9 = (l_line_cnt) - 1,
error_message = 'S'
WHERE ROWID = i.row_id;
/* ou_name = i.ou_name
AND item_code = i.item_code
AND source_inv = i.source_inv
AND destination_inv = i.destination_inv
AND NVL (from_sub_inv, 'NULL') =
NVL (i.from_sub_inv, 'NULL')
AND NVL (to_sub_inv, 'NULL') =
NVL (i.to_sub_inv, 'NULL')
AND qty = i.qty
AND uom = i.uom
AND need_by_date = i.need_by_date
AND status IS NULL
and upload_id =ou_name.upload_id
and nvl( status,'N') != 'S';
*/
ELSE
--------if error update the staging table---
UPDATE XX_po_requisitions_int_stg
SET status = 'FAILED',
attribute10 = l_batch_id,
last_update_date = SYSDATE,
error_message = 'Not Inserted.' || l_error_message,
request_id = fnd_global.conc_request_id
WHERE ROWID = i.row_id;
END IF;
END LOOP; ---------End Loop Line Cursor-------
IF l_record_found = TRUE
THEN
fnd_file.put_line (fnd_file.LOG,
(l_line_cnt)
- 1
|| ' Line Record Found and inserted'
);
ELSE
fnd_file.put_line (fnd_file.LOG, 'Line Record not Found');
p_retcode := 1;
END IF;
l_interface_header_id := null;
END LOOP; ---------End Loop Header-------
IF l_record_found = TRUE
THEN
fnd_file.put_line (fnd_file.LOG,
'Header and Line Record Found'
);
ELSE
fnd_file.put_line (fnd_file.LOG,
'No Header and Line Record Found'
);
END IF;
COMMIT;
IF l_verify_flag = 'N'
THEN
UPDATE XX_po_requisitions_int_stg
SET status = 'FAILED',
last_update_date = SYSDATE,
error_message = 'XX1.' || l_error_message,
request_id = fnd_global.conc_request_id
WHERE (status = 'S' OR status IS NULL)
AND ou_name = ou_name.ou_name
AND attribute10 = l_batch_id
AND upload_id = ou_name.upload_id
AND NVL (status, 'N') != 'FAILED';
DELETE FROM po_requisitions_interface_all
WHERE requisition_header_id IN (
SELECT requisition_header_id
FROM XX_po_req_org_details);
p_retcode := 1;
p_errbuf := 'Some Error occur';
END IF;
COMMIT;
IF l_verify_flag <> 'N'
THEN
FOR cur_org IN cur_org_id
LOOP
--++--------------------------------------------------------
--Call below api to create Internal Requisition
--++--------------------------------------------------------
apps.XX_po_req_import_int_pkg.run_req_api
(l_batch_id,
cur_org.import_source, -- 'IMPORT_REQ_WEBADI',
cur_org.org_id,
l_request
);
IF l_request <> 'Error'
THEN
--++--------------------------------------------------------
--Call SO Line Reservation API if REquisition Created Successfully
-- for Source IRISO which is not part of WEBADI
--++--------------------------------------------------------
BEGIN
IF cur_org.import_source = 'IMPORT_REQ_IRISO'
THEN
apps.XX_om_order_iriso_pkg.create_item_reserve
(p_errbuff => p_errbuf,
p_retcode => p_retcode,
p_requisition_no => NULL,
p_batch_id => l_batch_id
);
END IF;
END;
ELSE
fnd_file.put_line
(fnd_file.LOG,
'Error Calling apps.XX_po_req_import_int_pkg.run_req_api. '|| substr(SQLERRM,1,200)
);
END IF;
END LOOP; -----End Loop cur_org_id----
END IF; -----Verify Flag---
END LOOP; ---End Loop OU Cursor----
IF l_record_found = TRUE
THEN
fnd_file.put_line (fnd_file.LOG,
CHR (10) || 'OU Name Record Found'
);
ELSE
fnd_file.put_line (fnd_file.LOG,
'*****No Eligible Record Found********'
);
p_retcode := 1;
END IF;
END;
---+--------------------------------------------------------------------------
--Submit IRISO Program which will update IFACE DFF and Create UDA through Interface
---+--------------------------------------------------------------------------
BEGIN
---------Check Number Of Record get success----
SELECT COUNT (requisition_number)
INTO l_req_success_cnt
FROM XX_po_requisitions_int_stg
WHERE attribute10 = l_batch_id AND status = 'SUCCESS';
fnd_file.put_line (fnd_file.LOG,
CHR (10)
|| 'Number of Success Requisition: '
|| l_req_success_cnt
|| ' for BatchID:'
|| l_batch_id
);
----Check Success Record-----
IF l_req_success_cnt >= 1
THEN
fnd_global.apps_initialize (g_user_id, g_resp_id, g_resp_appl_id);
BEGIN
fnd_file.put_line (fnd_file.LOG,
chr(10)|| 'Call IRISO Process Program.'
);
l_request_id :=
fnd_request.submit_request
(application => 'XXHIL',
program => 'XX_OM_SO_ACTIVITY_CP',
description => NULL,
start_time => SYSDATE,
sub_request => FALSE,
argument1 => 'IRISO',
argument2 => NULL,
argument3 => l_batch_id
);
COMMIT;
END;
ELSE
fnd_file.put_line
(fnd_file.LOG,
CHR (10)
|| 'No Record Imported for BatchID:'
|| l_batch_id
|| '. Not Able to call Program to Update Order DFF/UDA'
);
p_errbuf := 'No Record Imported for BatchID:' || l_batch_id;
p_retcode := 1;
END IF;
END;
----Code to update Attribute18 of Sales Order-------
/*
UPDATE oe_order_headers_all
SET attribute18 = p_deliver_at_location
where source_document_id = i.req_hdr_id;
commit;
*/
EXCEPTION
WHEN OTHERS
THEN
p_errbuf := ' Exception int_req_prc ' || SQLERRM;
raise_application_error (-20117, 'Error int_req_prc. ' || SQLERRM);
END int_req_prc;
PROCEDURE run_req_api (
p_batch_id IN NUMBER,
p_source_code IN VARCHAR2,
p_org_id IN NUMBER,
p_request OUT VARCHAR2
)
AS
l_request_id NUMBER;
--g_user_id fnd_user.user_id%TYPE := fnd_profile.VALUE ('user_id');
--g_resp_appl_id NUMBER := fnd_profile.VALUE ('resp_appl_id');
--g_resp_id NUMBER := fnd_profile.VALUE ('resp_id');
wait_status BOOLEAN;
l_phase VARCHAR2 (200);
l_status VARCHAR2 (200);
l_dev_phase VARCHAR2 (200);
l_dev_status VARCHAR2 (200);
l_message VARCHAR2 (500);
l_req_number VARCHAR2 (2000) := NULL;
l_req_hdr_id NUMBER := NULL;
l_operating_unit VARCHAR2 (100) := NULL;
l_error_table VARCHAR2 (32000);
sl_no NUMBER;
l_int_use VARCHAR2(10);
CURSOR req_number (p_request_id NUMBER)
IS
SELECT segment1
FROM po_requisition_headers_all
WHERE request_id = p_request_id;
CURSOR error_table (p_request_id_err NUMBER)
IS
SELECT error_message
FROM po_interface_errors
WHERE request_id = p_request_id_err;
-------------------Get Requisition Details against Request ID-----------------
CURSOR cur_get_req_dtl (v_request_id NUMBER)
IS
SELECT poh.requisition_header_id, poh.segment1 req_num,
requisition_line_id, pol.line_num, msib.segment1,
pol.quantity, pol.source_organization_id,
pol.destination_organization_id, pol.need_by_date,
pol.source_subinventory, pol.destination_subinventory,
pol.org_id
FROM po_requisition_headers_all poh,
po_requisition_lines_all pol,
mtl_system_items_b msib
WHERE poh.requisition_header_id = pol.requisition_header_id
AND poh.org_id = pol.org_id
AND pol.item_id = msib.inventory_item_id
AND pol.source_organization_id = msib.organization_id
AND pol.request_id = v_request_id;
BEGIN
mo_global.init ('PO');
mo_global.set_policy_context ('S', p_org_id);
fnd_global.apps_initialize (g_user_id, g_resp_id, g_resp_appl_id);
fnd_request.set_org_id (p_org_id);
BEGIN
SELECT meaning INTO l_int_use
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'XX_IR_ISO_TAX_VALIDATION'
AND lookup_code LIKE '%INTENDED%USE%'
AND enabled_flag = 'Y'
AND end_date_active IS NULL;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
BEGIN
fnd_file.put_line (fnd_file.LOG,
'Submit Requisition Import For OU ID:' || p_org_id
);
l_request_id :=
fnd_request.submit_request (application => 'PO',
program => 'REQIMPORT',
description => NULL,
start_time => SYSDATE,
sub_request => FALSE,
argument1 => p_source_code,
argument2 => p_batch_id,
argument3 => 'ALL',
argument4 => NULL,
argument5 => 'N',
argument6 => 'N'
);
COMMIT;
IF l_request_id > 0
THEN
LOOP
wait_status :=
fnd_concurrent.wait_for_request (l_request_id,
10,
0,
l_phase,
l_status,
l_dev_phase,
l_dev_status,
l_message
);
EXIT WHEN UPPER (l_phase) IS NOT NULL;
END LOOP;
IF UPPER (l_phase) = 'COMPLETED' AND UPPER (l_status) = 'ERROR'
THEN
fnd_file.put_line (fnd_file.LOG,
'Oracle request id: '
|| l_request_id
|| ' '
|| SQLERRM
);
UPDATE XX_po_requisitions_int_stg
SET status = 'FAILED',
request_id = l_request_id,
last_update_date = SYSDATE,
error_message =
'XX2.'
|| error_message
|| '. Request Completed with Error.'
WHERE status = 'S'
AND ou_name = l_operating_unit
AND attribute10 = p_batch_id;
ELSIF UPPER (l_phase) = 'COMPLETED' AND UPPER (l_status) =
'NORMAL'
THEN
l_error_table := NULL;
l_req_number := NULL;
FOR i IN req_number (l_request_id)
LOOP
l_req_number := l_req_number || ',' || i.segment1;
END LOOP;
SELECT NAME
INTO l_operating_unit
FROM hr_operating_units
WHERE organization_id = p_org_id;
IF l_req_number IS NOT NULL
THEN
SELECT SUBSTR (l_req_number, 2)
INTO l_req_number
FROM DUAL;
END IF;
IF l_req_number IS NULL
THEN
sl_no := 0;
fnd_file.put_line
(fnd_file.LOG,
CHR (10)
|| 'Requisitions Number Generation Failed For '
|| l_operating_unit
|| ' Reasons Below :'
);
FOR j IN error_table (l_request_id)
LOOP
sl_no := sl_no + 1;
fnd_file.put_line (fnd_file.LOG,
sl_no || '---> ' || j.error_message
);
END LOOP;
UPDATE XX_po_requisitions_int_stg
SET status = 'FAILED',
request_id = l_request_id,
last_update_date = SYSDATE
WHERE status = 'S'
AND ou_name = l_operating_unit
AND attribute10 = p_batch_id;
ELSE
fnd_file.put_line
(fnd_file.output,
'Successful Requisitions Number for Operating Unit '
|| l_operating_unit
|| ' is ---> '
|| l_req_number
);
UPDATE XX_po_requisitions_int_stg
SET status = 'SUCCESS',
request_id = l_request_id,
last_update_date = SYSDATE
WHERE status = 'S'
AND ou_name = l_operating_unit
AND attribute10 = p_batch_id;
/*fnd_file.put_line
(fnd_file.output,
'xxx1Successful Requisitions Number for Operating Unit '
|| l_operating_unit
|| ' is ---> '
|| l_req_number
);*/
/* UPDATE XX_po_requisitions_int_stg
SET requisition_number = l_req_number,
request_id = l_request_id
WHERE status = 'SUCCESS' AND attribute10 = p_batch_id;*/
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--Update Req Details to Staging Table-
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
BEGIN
FOR i IN cur_get_req_dtl (l_request_id)
LOOP
fnd_file.put_line
(fnd_file.LOG,
'Update Requisition Details in Staging Table. '
|| i.source_organization_id
|| ','
|| i.destination_organization_id
|| ','
|| i.source_subinventory
|| ','
|| i.destination_organization_id
|| ','
|| l_request_id
|| ','
|| p_batch_id
|| ','
|| i.org_id
|| ','
|| i.line_num
);
UPDATE XX_po_requisitions_int_stg xxstg
SET xxstg.req_hdr_id = i.requisition_header_id,
xxstg.req_line_id = i.requisition_line_id,
xxstg.req_line_num = i.line_num,
xxstg.requisition_number = i.req_num,
last_update_date = SYSDATE
WHERE xxstg.item_code = i.segment1
AND xxstg.qty = i.quantity
AND xxstg.source_inv = i.source_organization_id
AND xxstg.destination_inv =
i.destination_organization_id
AND TRUNC (xxstg.need_by_date) =
TRUNC (i.need_by_date)
AND NVL (xxstg.from_sub_inv, '@') =
NVL (i.source_subinventory, '@')
AND NVL (xxstg.to_sub_inv, '@') =
NVL (i.destination_subinventory, '@')
AND request_id = l_request_id
AND attribute10 = p_batch_id
AND org_id = i.org_id
AND attribute9 = i.line_num;
UPDATE JA.JAI_TAX_DET_FACTORS
SET intended_use = l_int_use
WHERE ENTITY_CODE = 'REQUISITION'
AND trx_type = 'INTERNAL'
AND trx_id = i.requisition_header_id;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error updating Requisiton Details to Staging Table'
|| l_request_id
|| ' '
|| SQLERRM
);
END;
END IF;
END IF;
ELSE
fnd_file.put_line (fnd_file.LOG,
'the request failed. oracle request id: '
|| l_request_id
|| ' '
|| SQLERRM
);
UPDATE XX_po_requisitions_int_stg
SET status = 'FAILED',
request_id = l_request_id,
last_update_date = SYSDATE
WHERE status = 'S'
AND ou_name = l_operating_unit
AND attribute10 = p_batch_id;
END IF;
COMMIT;
p_request := l_request_id;
EXCEPTION
WHEN OTHERS
THEN
p_request := 'Error';
END;
END;
PROCEDURE import_prog_webadi_prc
IS
l_request_id NUMBER := 0;
l_status BOOLEAN := FALSE;
l_rtn_status VARCHAR2 (1000) := NULL;
l_rtn_msg VARCHAR2 (2000) := NULL;
l_batch_id VARCHAR2 (50) := NULL;
BEGIN
mo_global.set_policy_context ('S', g_org_id);
fnd_global.apps_initialize (g_user_id, g_resp_id, g_resp_appl_id);
fnd_request.set_org_id (g_org_id);
BEGIN
l_request_id :=
fnd_request.submit_request (application => 'XXHIL',
program => 'XX_PO_IR_IMPORT_CP',
description => NULL,
start_time => SYSDATE,
sub_request => FALSE
);
COMMIT;
---- update XXHIL.XX_PO_REQUISITIONS_INT_STG set attribute6=l_batch_id;
IF l_request_id > 0
THEN
DBMS_OUTPUT.put_line ( 'Request Id:'
|| l_request_id
|| ' submitted successfully'
);
----------------checking Rquest Status---------------------------
get_concurrent_req_status (p_status => l_status,
p_req_id => l_request_id,
p_rtn_msg => l_rtn_msg
);
---If Request completed successfully----------------------------
IF l_status = TRUE
THEN
--if import Request run successfully--
DBMS_OUTPUT.put_line
( ' Request Id :'
|| l_request_id
|| ' Request Run Successully..Status : True'
);
ELSE
fnd_file.put_line (fnd_file.LOG,
' Request Id :'
|| l_request_id
|| ' completed with error..Status : True'
);
END IF;
ELSE
DBMS_OUTPUT.put_line ( 'Request Id:'
|| l_request_id
|| ' not submitted successfully'
);
END IF;
END;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20117,
'Error import_prog_webadi_prc. ' || SQLERRM
);
END;
/*************************************************************************
-- Program name : get_concurrent_req_status
--
-- Description : This Program used to check request status continuously and it will be completed once request get completed.
-- Parameters :
-- IN ----
p_req_id :- Request Id
OUT----
p_status :- Return True of if REquest Completed normal Successfully else FALSE.
p_rtn_msg :- Rrturn Log Message.
*************************************************************************/
PROCEDURE get_concurrent_req_status (
p_status OUT BOOLEAN,
p_req_id IN NUMBER,
p_rtn_msg OUT VARCHAR2
)
IS
lv_req_return_status BOOLEAN;
lc_phase VARCHAR2 (100);
lc_status VARCHAR2 (100);
lc_dev_phase VARCHAR2 (100);
lc_dev_status VARCHAR2 (100);
lc_message VARCHAR2 (100);
BEGIN
p_rtn_msg :=
'Concurrent Request Submitted for Request Id :-> ' || p_req_id;
IF p_req_id > 0
THEN
LOOP
lv_req_return_status :=
fnd_concurrent.wait_for_request (request_id => p_req_id,
INTERVAL => 15,
max_wait => 360
-- out arguments
,
phase => lc_phase,
status => lc_status,
dev_phase => lc_dev_phase,
dev_status => lc_dev_status,
MESSAGE => lc_message
);
EXIT WHEN UPPER (lc_phase) = 'COMPLETED'
OR UPPER (lc_status) IN
('CANCELLED', 'ERROR', 'TERMINATED');
END LOOP;
IF UPPER (lc_phase) = 'COMPLETED' AND UPPER (lc_status) = 'ERROR'
THEN
p_status := FALSE;
ELSIF UPPER (lc_phase) = 'COMPLETED' AND UPPER (lc_status) = 'NORMAL'
THEN
p_status := TRUE;
END IF;
ELSE
fnd_message.set_name ('FND', 'FND_GENERIC_MESSAGE');
fnd_message.set_token ('MESSAGE',
'Error in Concurrent Request' || p_req_id
);
fnd_message.raise_error;
END IF;
END;
FUNCTION get_lookup_meaning (p_tag IN VARCHAR2)
RETURN VARCHAR2
IS
l_meaning fnd_lookup_values.meaning%TYPE := NULL;
BEGIN
SELECT meaning
INTO l_meaning
FROM fnd_lookup_values
WHERE lookup_type = g_iriso_lookup_type
AND NVL (end_date_active, TRUNC (SYSDATE)) >= TRUNC (SYSDATE)
AND tag = p_tag;
RETURN l_meaning;
EXCEPTION
WHEN OTHERS
THEN
RETURN l_meaning;
END get_lookup_meaning;
END XX_po_req_import_int_pkg;
/
CREATE OR REPLACE PACKAGE APPS.XX_po_req_import_int_pkg
AS
--------------------------------------------------------------------------------------
--File Name: XX_PO_REQ_IMPORT_INT_PKG.pkb
--Object Name: XX_PO_REQ_IMPORT_INT_PKG
--Old Object Name: New Object
--RICEW Object id:
--Description: Custom package body
--Maintenance History
-- Date Author Name Version Description
-- ------- ----------- -------- -----------
-- 11-09-2019 1.0 Created
-- 11-12-2019 1.0 Added ISO Related Columns
-------------------------------------------------------------------------------------
PROCEDURE load_data_webadi_stg_prc (
--- p_ou_name IN VARCHAR2,
p_item_code IN VARCHAR2,
p_qty IN VARCHAR2,
p_uom IN VARCHAR2,
p_source_inv IN VARCHAR2,
p_destination_inv IN VARCHAR2,
p_from_sub_inv IN VARCHAR2,
p_to_sub_inv IN VARCHAR2,
p_need_by_date IN DATE,
--- p_packing_code IN VARCHAR2,
p_grade IN VARCHAR2,
-- p_upload_id IN VARCHAR2,
P_TRANSFER_TYPE IN VARCHAR2,
P_HDR_PREFERRED_TRANSPORTER IN VARCHAR2,
P_LIN_SHIPPING_INSTRUCTIONS IN VARCHAR2,
P_LIN_PACKING_INSTRUCTIONS IN VARCHAR2,
P_HDR_INSPECTION IN VARCHAR2,
P_HDR_TRIAL_ORDER IN VARCHAR2,
P_HDR_INSURANCE IN VARCHAR2,
P_HDR_INSURANCE_POLICY_NUMBER IN VARCHAR2,
P_HDR_BRANDING_REQUIRED IN VARCHAR2,
P_HDR_SPECIAL_INSTRUCTIONS IN VARCHAR2,
P_HDR_TC_STANDARD IN VARCHAR2,
P_HDR_SMALL_QUANTITY_SURCHARGE IN VARCHAR2,
P_HDR_ANTICIPATORY IN VARCHAR2,
P_LIN_END_APPLICATION IN VARCHAR2,
P_LIN_F_TRUCK_LOAD_PONABLE_FG IN VARCHAR2,
P_LIN_PLANNING_REQUIRED IN VARCHAR2,
P_LIN_CSR_NUMBER IN VARCHAR2,
P_LIN_DIVERSION_ORDER_NUMBER IN VARCHAR2,
P_EXT_LIN_LENGTH_TOLERANCE_TAR IN VARCHAR2,
P_EXT_LIN_LENGTH_TOLERANCE_MAX IN VARCHAR2,
P_EXT_LIN_LENGTH_TOLERANCE_MIN IN VARCHAR2,
P_EXT_LIN_CONDUCTIVITY_TARGET IN VARCHAR2,
P_EXT_LIN_CONDUCTIVITY_MAX IN VARCHAR2,
P_EXT_LIN_CONDUCTIVITY_MIN IN VARCHAR2,
P_EXT_LIN_END_SEGMENT IN VARCHAR2,
P_EXT_LIN_UST_ON_BILLET IN VARCHAR2,
P_EXT_LIN_UST_ON_SECTION IN VARCHAR2,
P_EXT_LIN_REELING IN VARCHAR2,
P_EXT_LIN_COIL_WEIGHT_TARGET IN VARCHAR2,
P_EXT_LIN_COIL_WEIGHT_MAX IN VARCHAR2,
P_EXT_LIN_COIL_WEIGHT_MIN IN VARCHAR2,
P_EXT_LIN_PACKAGE_WEIGHT_TA IN VARCHAR2,
P_EXT_LIN_PACKAGE_WEIGHT_MA IN VARCHAR2,
P_EXT_LIN_PACKAGE_WEIGHT_MI IN VARCHAR2,
P_EXT_LIN_WIDTH_TOLERANCE_TAR IN VARCHAR2,
P_EXT_LIN_WIDTH_TOLERANCE_MAX IN VARCHAR2,
P_EXT_LIN_WIDTH_TOLERANCE_MIN IN VARCHAR2,
P_EXT_LIN_THICK_TOLERANCE_TA IN VARCHAR2,
P_EXT_LIN_THICK_TOLERANCE_MA IN VARCHAR2,
P_EXT_LIN_THICK_TOLERANCE_MI IN VARCHAR2,
P_FRP_LIN_ULTI_TEN_STREN_MIN IN VARCHAR2,
P_FRP_LIN_COIL_DENS_RANGE_MIN IN VARCHAR2,
P_FRP_LIN_COIL_DENS_RANGE_MAX IN VARCHAR2,
P_FRP_LIN_COIL_DENS_RANGE_TAR IN VARCHAR2,
P_FRP_LIN_COIL_OD_MIN IN VARCHAR2,
P_FRP_LIN_COIL_OD_MAX IN VARCHAR2,
P_FRP_LIN_COIL_OD_TARGET IN VARCHAR2,
P_FRP_LIN_COIL_ID_MIN IN VARCHAR2,
P_FRP_LIN_COIL_ID_MAX IN VARCHAR2,
P_FRP_LIN_COIL_ID_TARGET IN VARCHAR2,
P_FRP_LIN_ELONGATION_MIN IN VARCHAR2,
P_FRP_LIN_ELONGATION_MAX IN VARCHAR2,
P_FRP_LIN_ELONGATION_TARGET IN VARCHAR2,
P_FRP_LIN_ULTI_TEN_STREN_MAX IN VARCHAR2,
P_FRP_LIN_ULTI_TEN_STREN_TAR IN VARCHAR2,
P_FRP_LIN_YIELD_STRENGTH_MIN IN VARCHAR2,
P_FRP_LIN_YIELD_STRENGTH_MAX IN VARCHAR2,
P_FRP_LIN_YIELD_STRENGTH_TAR IN VARCHAR2,
P_FRP_LIN_CONDUCTIVITY_MIN IN VARCHAR2,
P_FRP_LIN_CONDUCTIVITY_MAX IN VARCHAR2,
P_FRP_LIN_CONDUCTIVITY_TARGET IN VARCHAR2,
P_FRP_LIN_LENGTH_TOLERANCE_MIN IN VARCHAR2,
P_FRP_LIN_LENGTH_TOLERANCE_MAX IN VARCHAR2,
P_FRP_LIN_LENGTH_TOLERANCE_TAR IN VARCHAR2,
P_FRP_LIN_THICK_TOLERANCE_MI IN VARCHAR2,
P_FRP_LIN_THICK_TOLERANCE_MA IN VARCHAR2,
P_FRP_LIN_THICK_TOLERANCE_TA IN VARCHAR2,
P_FRP_LIN_WIDTH_TOLERANCE_MIN IN VARCHAR2,
P_FRP_LIN_WIDTH_TOLERANCE_MAX IN VARCHAR2,
P_FRP_LIN_WIDTH_TOLERANCE_TAR IN VARCHAR2,
P_FRP_LIN_PACKAGE_WEIGHT_MI IN VARCHAR2,
P_FRP_LIN_PACKAGE_WEIGHT_MA IN VARCHAR2,
P_FRP_LIN_PACKAGE_WEIGHT_TA IN VARCHAR2,
P_FRP_LIN_COIL_WEIGHT_MIN IN VARCHAR2,
P_FRP_LIN_COIL_WEIGHT_MAX IN VARCHAR2,
P_FRP_LIN_COIL_WEIGHT_TARGET IN VARCHAR2,
P_FRP_LIN_END_SEGMENT IN VARCHAR2,
P_FRP_LIN_COIL_ORIENTATION IN VARCHAR2,
P_FRP_LIN_CORE_TYPE IN VARCHAR2,
P_FRP_LIN_RAL_CODE IN VARCHAR2,
P_FRP_LIN_SPOOL_TYPE IN VARCHAR2,
P_PRM_LIN_CONDUCTIVITY_TARGET IN VARCHAR2,
P_PRM_LIN_CONDUCTIVITY_MAX IN VARCHAR2,
P_PRM_LIN_CONDUCTIVITY_MIN IN VARCHAR2,
P_PRM_LIN_UST_ON_BILLET IN VARCHAR2,
P_PRM_LIN_END_SEGMENT IN VARCHAR2,
-- P_DELIVER_AT_LOCATION IN VARCHAR2,
P_CUST_PO IN VARCHAR2 ,
p_shipment_above_toler IN VARCHAR2,
p_shipment_below_toler IN VARCHAR2
);
PROCEDURE int_req_prc (p_errbuf OUT VARCHAR2, p_retcode OUT NUMBER);
PROCEDURE run_req_api (p_batch_id IN NUMBER,
p_source_code IN VARCHAR2,
p_org_id IN NUMBER,
p_request OUT VARCHAR2);
PROCEDURE import_prog_webadi_prc;
PROCEDURE get_concurrent_req_status (
p_status OUT BOOLEAN,
p_req_id IN NUMBER,
p_rtn_msg OUT VARCHAR2
);
FUNCTION get_lookup_meaning (p_tag IN VARCHAR2)
RETURN VARCHAR2;
END XX_po_req_import_int_pkg;
/
EXIT
