DECLARE
l_msg VARCHAR2(2000) := NULL;
x_message_list Error_Handler.Error_Tbl_Type;
x_return_status VARCHAR2(100);
x_msg_count NUMBER := 0;
l_rowcnt NUMBER := 0;
x_session_key NUMBER;
x_return_msg VARCHAR2 (2000);
x_errors po_asl_api_error_rec;
l_n_purchasing_org_id NUMBER := NULL;
l_n_bec_org_Id NUMBER := NULL;
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(2) := FND_API.G_TRUE;
l_commit VARCHAR2(2) := FND_API.G_TRUE;
--Table type to update the must_use_approved_vendor_flag
l_item_tbl_typ ego_item_pub.item_tbl_type;
x_item_table ego_item_pub.item_tbl_type;
--Table type to do the substitute item assignment
l_item_org_assignment_tbl EGO_ITEM_PUB.ITEM_ORG_ASSIGNMENT_TBL_TYPE;
--Cursor to fetch substitute item details
Cursor Cur_item_dtls
IS
select *
from mtl_system_items_b
WHERE <your condition to filter the records for which ASL needs to be copied>.
--Declare the record of the cursor type
Cur_item_dtls_rec Cur_item_dtls%ROWTYPE;
--Declare the table of record type
TYPE Cur_item_dtls_table is TABLE OF Cur_item_dtls_rec%TYPE;
--Declare a table
item_dtls_tab Cur_item_dtls_table;
--Time for API parameter type records etc to be declared
l_rel_item_rec_type mtl_related_items_pub.rel_item_rec_type;
l_pln_info_tbl_type mtl_related_items_pub.pln_info_tbl_type;
l_cust_ref_tbl_type mtl_related_items_pub.cust_ref_tbl_type;
p_transaction_type VARCHAR2 (20) := 'CREATE';
l_return_status VARCHAR2(1000) := NULL;
l_msg_list Error_Handler.Error_Tbl_Type;
l_msg_count NUMBER := 0;
l_v_error_message VARCHAR2(4000) := NULL;
l_v_final_error_msg VARCHAR2(4000) := NULL;
l_n_org_id NUMBER := NULL;
l_n_flag_set_counter NUMBER := 0;
--Cursor to fetch the Approved Supplier List Data
CURSOR cur_Get_asl_data ( p_in_n_inventory_item_id IN NUMBER,
p_in_n_org_id IN NUMBER
)
IS
SELECT asl.asl_id,
asl.owning_organization_id,
asl.vendor_id,
asl.item_id,
asl.vendor_site_id,
asl.vendor_business_type,
asl.asl_status_id,
asl.manufacturer_id,
asl.category_id,
asl.primary_vendor_item,
asl.manufacturer_asl_id,
asl.review_by_date,
asl.comments,
asl.DISABLE_FLAG,
asl.attribute_category ,
asl.attribute1,
asl.attribute2,
asl.attribute3,
asl.attribute4,
asl.attribute5,
asl.attribute6,
asl.attribute7,
asl.attribute8,
asl.attribute9,
asl.attribute10,
asl.attribute11,
asl.attribute12,
asl.attribute13,
asl.attribute14,
asl.attribute15,
paa.min_order_qty min_ord_qty ,
paa.enable_authorizations_flag,
paa.enable_plan_schedule_flag,
paa.enable_ship_schedule_flag,
paa.plan_schedule_type,
paa.ship_schedule_type,
paa.enable_autoschedule_flag,
paa.enable_vmi_flag ,
paa.vmi_min_qty,
paa.vmi_max_qty,
paa.vmi_min_days,
paa.vmi_max_days,
paa.enable_vmi_auto_replenish_flag,
paa.consigned_from_supplier_flag,
paa.consume_on_aging_flag,
paa.FIXED_ORDER_QUANTITY,
paa.FORECAST_HORIZON,
paa.fixed_lot_multiple,
paa.attribute_category paa_attribute_category ,
paa.attribute1 paa_attribute1 ,
paa.attribute2 paa_attribute2 ,
paa.attribute3 paa_attribute3 ,
paa.attribute4 paa_attribute4 ,
paa.attribute5 paa_attribute5 ,
paa.attribute6 paa_attribute6 ,
paa.attribute7 paa_attribute7 ,
paa.attribute8 paa_attribute8 ,
paa.attribute9 paa_attribute9 ,
paa.attribute10 paa_attribute10 ,
paa.attribute11 paa_attribute11 ,
paa.attribute12 paa_attribute12 ,
paa.attribute13 paa_attribute13 ,
paa.attribute14 paa_attribute14 ,
paa.attribute15 paa_attribute15 ,
paa.release_generation_method,
paa.purchasing_unit_of_measure,
paa.plan_bucket_pattern_id,
paa.ship_bucket_pattern_id,
paa.scheduler_id,
paa.category_id paa_category_id,
paa.price_update_tolerance,
paa.processing_lead_time,
paa.delivery_calendar,
paa.country_of_origin_code,
paa.vmi_replenishment_approval,
paa.last_billing_date,
paa.consigned_billing_cycle,
paa.aging_period,
paa.replenishment_method
FROM po_approved_supplier_list asl,
po_asl_attributes paa
WHERE 1=1
AND asl.asl_id = paa.asl_id
AND asl.item_id = p_in_n_inventory_item_id
AND asl.using_organization_id = paa.using_organization_id
AND asl.using_organization_id = p_in_n_org_id ;
--Declare the record of the cursor type
cur_Get_asl_data_rec cur_Get_asl_data%ROWTYPE;
--Declare the table of record type
TYPE cur_Get_asl_data_table is TABLE OF cur_Get_asl_data_rec%TYPE;
--Declare a table
cur_Get_asl_data_tab cur_Get_asl_data_table;
--Cursor to get ASL Doc Records
CURSOR CUR_GET_ASL_DOCS (P_IN_N_ASL_ID IN NUMBER)
IS
SELECT *
FROM po_asl_documents
WHERE asl_id = P_IN_N_ASL_ID;
--Declare the record of the cursor type
cur_Get_asl_doc_rec CUR_GET_ASL_DOCS%ROWTYPE;
--Declare the table of record type
TYPE cur_Get_asl_doc_table is TABLE OF cur_Get_asl_doc_rec%TYPE;
--Declare a table
cur_Get_asl_doc_tab cur_Get_asl_doc_table;
--Cursor to get Supplier's Item capacity Records
CURSOR CUR_GET_ITEM_CAPACITY (P_IN_N_ASL_ID IN NUMBER)
IS
SELECT *
FROM po_supplier_item_capacity
WHERE asl_id = P_IN_N_ASL_ID;
--Declare the record of the cursor type
cur_get_item_capacity_rec cur_get_item_capacity%ROWTYPE;
--Declare the table of record type
TYPE cur_get_item_capacity_table is TABLE OF cur_get_item_capacity_rec%TYPE;
--Declare a table
cur_get_item_capacity_tab cur_get_item_capacity_table;
--Cursor to get Supplier's Item tolerance Records
CURSOR CUR_GET_ITEM_TLRNC (P_IN_N_ASL_ID IN NUMBER)
IS
SELECT *
FROM po_supplier_item_tolerance
WHERE asl_id = P_IN_N_ASL_ID;
--Declare the record of the cursor type
cur_get_item_tlrnc_rec cur_get_item_tlrnc%ROWTYPE;
--Declare the table of record type
TYPE cur_get_item_tlrnc_table is TABLE OF cur_get_item_tlrnc_rec%TYPE;
--Declare a table
cur_get_item_tlrnc_tab cur_get_item_tlrnc_table;
Appr_Supp_Rec po_approved_supplier_list_rec ;
Appr_Supp_attr_Rec po_asl_attributes_rec ;
asl_doc_Rec po_asl_documents_rec ;
chv_auth_rec chv_authorizations_rec ;
item_capacity_rec po_supplier_item_capacity_rec ;
item_tolerance_rec po_supplier_item_tolerance_rec;
/* Changes ends for ORDSC-10966 */
BEGIN
OPEN Cur_item_dtls( p_in_v_batch_id ,p_in_v_sub_batch_id );
--Now get all the records from the cursor
FETCH Cur_item_dtls BULK COLLECT INTO item_dtls_tab;
--TIme to close the cursor
CLOSE Cur_item_dtls;
--NOw call the API once for each record..
/* ############# code starts for Approved Supplier List creation ##########*/
Appr_Supp_Rec := NEW po_approved_supplier_list_rec ();
Appr_Supp_attr_Rec := NEW po_asl_attributes_rec ();
IF item_dtls_tab.count>0
THEN
FOR j in item_dtls_tab.first..item_dtls_tab.last
LOOP
OPEN cur_Get_asl_data( item_dtls_tab(j).inventory_item_id , p_in_v_src_org_id);
--Now get all the records from the cursor
FETCH cur_Get_asl_data BULK COLLECT INTO cur_Get_asl_data_tab;
--TIme to close the cursor
CLOSE cur_Get_asl_data;
--Time to populate the records
IF cur_Get_asl_data_tab.count>0
THEN
FOR i in cur_Get_asl_data_tab.FIRST..cur_Get_asl_data_tab.LAST
LOOP
Appr_Supp_Rec.item_id := po_tbl_number (cur_Get_asl_data_tab(i).item_id);
Appr_Supp_Rec.vendor_id := po_tbl_number (cur_Get_asl_data_tab(i).vendor_id);
Appr_Supp_Rec.vendor_site_id := po_tbl_number (cur_Get_asl_data_tab(i).vendor_site_id);
Appr_Supp_Rec.owning_organization_id := po_tbl_number (item_dtls_tab(j).dest_org_id);
Appr_Supp_Rec.vendor_business_type := po_tbl_varchar25 (cur_Get_asl_data_tab(i).vendor_business_type);
Appr_Supp_Rec.user_key := po_tbl_number (1);
Appr_Supp_Rec.process_action := po_tbl_varchar30 ('CREATE');
Appr_Supp_Rec.global_flag := po_tbl_varchar1 ('N');
Appr_Supp_Rec.asl_status_id := po_tbl_number (2);
Appr_Supp_Rec.asl_status_dsp := po_tbl_varchar25 (NULL);
Appr_Supp_Rec.manufacturer_id := po_tbl_number (cur_Get_asl_data_tab(i).MANUFACTURER_ID);
Appr_Supp_Rec.manufacturer_dsp := po_tbl_varchar100 (NULL);
Appr_Supp_Rec.owning_organization_dsp := po_tbl_varchar240 (NULL);
Appr_Supp_Rec.vendor_dsp := po_tbl_varchar240 (NULL);
Appr_Supp_Rec.item_dsp := po_tbl_varchar50 (NULL);
Appr_Supp_Rec.category_id := po_tbl_number (cur_Get_asl_data_tab(i).CATEGORY_ID);
Appr_Supp_Rec.category_dsp := po_tbl_varchar240 (NULL);
Appr_Supp_Rec.vendor_site_dsp := po_tbl_varchar50 (NULL);
Appr_Supp_Rec.primary_vendor_item := po_tbl_varchar25 (cur_Get_asl_data_tab(i).PRIMARY_VENDOR_ITEM);
Appr_Supp_Rec.manufacturer_asl_id := po_tbl_number (cur_Get_asl_data_tab(i).MANUFACTURER_ASL_ID);
Appr_Supp_Rec.manufacturer_asl_dsp := po_tbl_varchar50 (NULL);
Appr_Supp_Rec.review_by_date := po_tbl_date (cur_Get_asl_data_tab(i).review_by_date);
Appr_Supp_Rec.comments := po_tbl_varchar240 (cur_Get_asl_data_tab(i).comments);
Appr_Supp_Rec.attribute_category := po_tbl_varchar30 (cur_Get_asl_data_tab(i).attribute_category );
Appr_Supp_Rec.attribute1 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).attribute1);
Appr_Supp_Rec.attribute2 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).attribute2);
Appr_Supp_Rec.attribute3 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).attribute3);
Appr_Supp_Rec.attribute4 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).attribute4);
Appr_Supp_Rec.attribute5 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).attribute5);
Appr_Supp_Rec.attribute6 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).attribute6);
Appr_Supp_Rec.attribute7 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).attribute7);
Appr_Supp_Rec.attribute8 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).attribute8);
Appr_Supp_Rec.attribute9 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).attribute9);
Appr_Supp_Rec.attribute10 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).attribute10);
Appr_Supp_Rec.attribute11 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).attribute11);
Appr_Supp_Rec.attribute12 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).attribute12);
Appr_Supp_Rec.attribute13 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).attribute13);
Appr_Supp_Rec.attribute14 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).attribute14);
Appr_Supp_Rec.attribute15 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).attribute15);
Appr_Supp_Rec.request_id := po_tbl_number (NULL);
Appr_Supp_Rec.program_application_id := po_tbl_number (NULL);
Appr_Supp_Rec.program_id := po_tbl_number (NULL);
Appr_Supp_Rec.program_update_date := po_tbl_date (NULL);
Appr_Supp_Rec.disable_flag := po_tbl_varchar1 (cur_Get_asl_data_tab(i).DISABLE_FLAG);
/* Time to handle the approved supplier attributes */
Appr_Supp_attr_Rec.aging_period_dsp := po_tbl_number (cur_get_asl_data_tab(i).aging_period);
Appr_Supp_attr_Rec.attribute1 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).paa_attribute1);
Appr_Supp_attr_Rec.attribute2 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).paa_attribute2);
Appr_Supp_attr_Rec.attribute3 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).paa_attribute3);
Appr_Supp_attr_Rec.attribute4 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).paa_attribute4);
Appr_Supp_attr_Rec.attribute5 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).paa_attribute5);
Appr_Supp_attr_Rec.attribute6 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).paa_attribute6);
Appr_Supp_attr_Rec.attribute7 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).paa_attribute7);
Appr_Supp_attr_Rec.attribute8 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).paa_attribute8);
Appr_Supp_attr_Rec.attribute9 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).paa_attribute9);
Appr_Supp_attr_Rec.attribute10 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).paa_attribute10);
Appr_Supp_attr_Rec.attribute11 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).paa_attribute11);
Appr_Supp_attr_Rec.attribute12 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).paa_attribute12);
Appr_Supp_attr_Rec.attribute13 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).paa_attribute13);
Appr_Supp_attr_Rec.attribute14 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).paa_attribute14);
Appr_Supp_attr_Rec.attribute15 := po_tbl_varchar240 (cur_Get_asl_data_tab(i).paa_attribute15);
Appr_Supp_attr_Rec.attribute_category := po_tbl_varchar30 (cur_Get_asl_data_tab(i).paa_attribute_category );
Appr_Supp_attr_Rec.category_id := po_tbl_number (cur_Get_asl_data_tab(i).paa_category_id);
Appr_Supp_attr_Rec.category_dsp := po_tbl_varchar50 (NULL);
Appr_Supp_attr_Rec.consigned_from_supp_flag_dsp := po_tbl_varchar1 (cur_Get_asl_data_tab(i).consigned_from_supplier_flag);
Appr_Supp_attr_Rec.consigned_billing_cycle_dsp := po_tbl_number (cur_get_asl_data_tab(i).consigned_billing_cycle);
Appr_Supp_attr_Rec.consume_on_aging_flag_dsp := po_tbl_varchar1 (cur_Get_asl_data_tab(i).consume_on_aging_flag);
Appr_Supp_attr_Rec.country_of_origin_code_dsp := po_tbl_varchar25 (cur_get_asl_data_tab(i).country_of_origin_code);
Appr_Supp_attr_Rec.delivery_calendar_dsp := po_tbl_varchar25 (cur_get_asl_data_tab(i).delivery_calendar);
Appr_Supp_attr_Rec.enable_autoschedule_flag_dsp := po_tbl_varchar1 (cur_Get_asl_data_tab(i).enable_autoschedule_flag);
Appr_Supp_attr_Rec.enable_authorizations_flag_dsp := po_tbl_varchar1 (cur_Get_asl_data_tab(i).enable_authorizations_flag);
Appr_Supp_attr_Rec.enable_plan_schedule_flag_dsp := po_tbl_varchar1 (cur_Get_asl_data_tab(i).enable_plan_schedule_flag);
Appr_Supp_attr_Rec.enable_vmi_auto_replenish_flag := po_tbl_varchar1 (cur_Get_asl_data_tab(i).enable_vmi_flag);
Appr_Supp_attr_Rec.enable_vmi_flag_dsp := po_tbl_varchar1 (cur_get_asl_data_tab(i).enable_vmi_flag);
Appr_Supp_attr_Rec.enable_ship_schedule_flag_dsp := po_tbl_varchar1 (cur_Get_asl_data_tab(i).enable_ship_schedule_flag);
Appr_Supp_attr_Rec.fixed_lot_multiple_dsp := po_tbl_number (cur_Get_asl_data_tab(i).fixed_lot_multiple);
Appr_Supp_attr_Rec.fixed_order_quantity_dsp := po_tbl_number (cur_Get_asl_data_tab(i).fixed_order_quantity);
Appr_Supp_attr_Rec.forecast_horizon_dsp := po_tbl_number (cur_Get_asl_data_tab(i).forecast_horizon);
Appr_Supp_attr_Rec.item_id := po_tbl_number (cur_Get_asl_data_tab(i).item_id);
Appr_Supp_attr_Rec.item_dsp := po_tbl_varchar50 (NULL);
Appr_Supp_attr_Rec.last_billing_date := po_tbl_date (cur_get_asl_data_tab(i).last_billing_date);
Appr_Supp_attr_Rec.min_order_qty_dsp := po_tbl_number (cur_Get_asl_data_tab(i).min_ord_qty);
Appr_Supp_attr_Rec.plan_bucket_pattern_dsp := po_tbl_varchar50 (NULL);
Appr_Supp_attr_Rec.plan_schedule_type := po_tbl_varchar25 (cur_Get_asl_data_tab(i).plan_schedule_type);
Appr_Supp_attr_Rec.plan_schedule_type_dsp := po_tbl_varchar50 (NULL);
Appr_Supp_attr_Rec.program_update_date := po_tbl_date (NULL);
Appr_Supp_attr_Rec.price_update_tolerance_dsp := po_tbl_number (cur_get_asl_data_tab(i).price_update_tolerance);
Appr_Supp_attr_Rec.processing_lead_time_dsp := po_tbl_number (cur_get_asl_data_tab(i).processing_lead_time);
Appr_Supp_attr_Rec.process_action := po_tbl_varchar30 ('ADD');
Appr_Supp_attr_Rec.program_application_id := po_tbl_number (NULL);
Appr_Supp_attr_Rec.program_id := po_tbl_number (NULL);
Appr_Supp_attr_Rec.purchasing_unit_of_measure_dsp := po_tbl_varchar25 (cur_Get_asl_data_tab(i).purchasing_unit_of_measure);
Appr_Supp_attr_Rec.release_generation_method := po_tbl_varchar25 (cur_Get_asl_data_tab(i).release_generation_method);
Appr_Supp_attr_Rec.release_generation_method_dsp := po_tbl_varchar50 (NULL);
Appr_Supp_attr_Rec.replenishment_method := po_tbl_number (cur_get_asl_data_tab(i).replenishment_method);
Appr_Supp_attr_Rec.replenishment_method_dsp := po_tbl_varchar50 (NULL);
Appr_Supp_attr_Rec.request_id := po_tbl_number (NULL);
Appr_Supp_attr_Rec.user_key := po_tbl_number (1);
--Appr_Supp_attr_Rec.using_organization_id := po_tbl_number (p_in_v_dest_org_id);
Appr_Supp_attr_Rec.using_organization_id := po_tbl_number (item_dtls_tab(j).dest_org_id);
Appr_Supp_attr_Rec.using_organization_dsp := po_tbl_varchar240 (NULL);
Appr_Supp_attr_Rec.ship_schedule_type := po_tbl_varchar25 (cur_Get_asl_data_tab(i).ship_schedule_type);
Appr_Supp_attr_Rec.ship_schedule_type_dsp := po_tbl_varchar50 (NULL);
Appr_Supp_attr_Rec.plan_bucket_pattern_id := po_tbl_number (cur_Get_asl_data_tab(i).plan_bucket_pattern_id);
Appr_Supp_attr_Rec.ship_bucket_pattern_id := po_tbl_number (cur_Get_asl_data_tab(i).ship_bucket_pattern_id);
Appr_Supp_attr_Rec.ship_bucket_pattern_dsp := po_tbl_varchar50 (NULL);
Appr_Supp_attr_Rec.scheduler_id := po_tbl_number (cur_Get_asl_data_tab(i).scheduler_id);
Appr_Supp_attr_Rec.scheduler_dsp := po_tbl_varchar50 (NULL);
Appr_Supp_attr_Rec.vendor_id := po_tbl_number (cur_Get_asl_data_tab(i).vendor_id);
Appr_Supp_attr_Rec.vendor_dsp := po_tbl_varchar240 (NULL);
Appr_Supp_attr_Rec.vendor_site_id := po_tbl_number (cur_Get_asl_data_tab(i).vendor_site_id);
Appr_Supp_attr_Rec.vendor_site_dsp := po_tbl_varchar50 (NULL);
Appr_Supp_attr_Rec.vmi_min_qty_dsp := po_tbl_number (cur_Get_asl_data_tab(i).vmi_min_qty);
Appr_Supp_attr_Rec.vmi_max_qty_dsp := po_tbl_number (cur_Get_asl_data_tab(i).vmi_max_qty);
Appr_Supp_attr_Rec.vmi_min_days_dsp := po_tbl_number (cur_Get_asl_data_tab(i).vmi_min_days);
Appr_Supp_attr_Rec.vmi_max_days_dsp := po_tbl_number (cur_Get_asl_data_tab(i).vmi_max_days);
Appr_Supp_attr_Rec.vmi_replenishment_approval := po_tbl_varchar30 (cur_get_asl_data_tab(i).vmi_replenishment_approval);
Appr_Supp_attr_Rec.vmi_replenishment_approval_dsp := po_tbl_varchar30 (NULL);
/* Lets check if we have any ASL Docs available for this ASL */
--Cursor to get ASL Doc Records
OPEN CUR_GET_ASL_DOCS (P_IN_N_ASL_ID => cur_Get_asl_data_tab(i).asl_id);
FETCH CUR_GET_ASL_DOCS BULK COLLECT INTO cur_Get_asl_doc_tab;
CLOSE CUR_GET_ASL_DOCS;
IF cur_Get_asl_doc_tab.count>0
THEN
FOR i in cur_Get_asl_doc_tab.FIRST..cur_Get_asl_doc_tab.LAST
LOOP
log_msg ('Data feeding for asl_doc_Rec');
asl_doc_Rec := NEW po_asl_documents_rec ();
--Hoping that there will be only 1 record for each ASL. If no, will tackle it later when we get such scenario
asl_doc_Rec.user_key := po_tbl_number (1);
asl_doc_Rec.process_action := po_tbl_varchar30 ('ADD');
asl_doc_Rec.using_organization_id := po_tbl_number (item_dtls_tab(j).dest_org_id);
asl_doc_Rec.using_organization_dsp := po_tbl_varchar240 (NULL);
asl_doc_Rec.sequence_num := po_tbl_number (1);
asl_doc_Rec.document_type_code := po_tbl_varchar25(cur_Get_asl_doc_tab(1).document_type_code);
asl_doc_Rec.document_type_dsp := po_tbl_varchar50 (NULL);
asl_doc_Rec.document_header_id := po_tbl_number (cur_Get_asl_doc_tab(1).DOCUMENT_HEADER_ID);
asl_doc_Rec.document_header_dsp := po_tbl_varchar50 (NULL);
asl_doc_Rec.document_line_id := po_tbl_number (cur_Get_asl_doc_tab(1).DOCUMENT_LINE_ID);
asl_doc_Rec.document_line_num_dsp := po_tbl_number (2);
asl_doc_Rec.attribute_category := po_tbl_varchar30 (cur_Get_asl_doc_tab(1).attribute_category);
asl_doc_Rec.attribute1 := po_tbl_varchar240 (cur_Get_asl_doc_tab(1).attribute1);
asl_doc_Rec.attribute2 := po_tbl_varchar240 (cur_Get_asl_doc_tab(1).attribute2);
asl_doc_Rec.attribute3 := po_tbl_varchar240 (cur_Get_asl_doc_tab(1).attribute3);
asl_doc_Rec.attribute4 := po_tbl_varchar240 (cur_Get_asl_doc_tab(1).attribute4);
asl_doc_Rec.attribute5 := po_tbl_varchar240 (cur_Get_asl_doc_tab(1).attribute5);
asl_doc_Rec.attribute6 := po_tbl_varchar240 (cur_Get_asl_doc_tab(1).attribute6);
asl_doc_Rec.attribute7 := po_tbl_varchar240 (cur_Get_asl_doc_tab(1).attribute7);
asl_doc_Rec.attribute8 := po_tbl_varchar240 (cur_Get_asl_doc_tab(1).attribute8);
asl_doc_Rec.attribute9 := po_tbl_varchar240 (cur_Get_asl_doc_tab(1).attribute9);
asl_doc_Rec.attribute10 := po_tbl_varchar240 (cur_Get_asl_doc_tab(1).attribute10);
asl_doc_Rec.attribute11 := po_tbl_varchar240 (cur_Get_asl_doc_tab(1).attribute11);
asl_doc_Rec.attribute12 := po_tbl_varchar240 (cur_Get_asl_doc_tab(1).attribute12);
asl_doc_Rec.attribute13 := po_tbl_varchar240 (cur_Get_asl_doc_tab(1).attribute13);
asl_doc_Rec.attribute14 := po_tbl_varchar240 (cur_Get_asl_doc_tab(1).attribute14);
asl_doc_Rec.attribute15 := po_tbl_varchar240 (cur_Get_asl_doc_tab(1).attribute15);
asl_doc_Rec.request_id := po_tbl_number (NULL);
asl_doc_Rec.program_application_id := po_tbl_number (NULL);
asl_doc_Rec.program_id := po_tbl_number (NULL);
asl_doc_Rec.program_update_date := po_tbl_date (NULL);
asl_doc_Rec.org_id := po_tbl_number (NULL);
EXIT; --Just coming out from the loop.. we will handle multiple records as and when we encounter them in the future
END LOOP ;
END IF ;
/* Lets check if any Item Capacity record is present or not..If yes, handle it */
OPEN CUR_GET_ITEM_CAPACITY (P_IN_N_ASL_ID => cur_Get_asl_data_tab(i).asl_id);
FETCH CUR_GET_ITEM_CAPACITY BULK COLLECT INTO cur_get_item_capacity_tab;
CLOSE CUR_GET_ITEM_CAPACITY;
IF cur_get_item_capacity_tab.count>0
THEN
FOR i in cur_get_item_capacity_tab.FIRST..cur_get_item_capacity_tab.LAST
LOOP
log_msg ('Data feeding for item_capacity_rec');
item_capacity_rec := NEW po_supplier_item_capacity_rec ();
item_capacity_rec.user_key := po_tbl_number (1);
item_capacity_rec.process_action := po_tbl_varchar30 ('ADD');
item_capacity_rec.using_organization_id := po_tbl_number (item_dtls_tab(j).dest_org_id);
item_capacity_rec.using_organization_dsp := po_tbl_varchar240 (NULL);
item_capacity_rec.from_date_dsp := po_tbl_date (cur_get_item_capacity_tab(1).from_date);
item_capacity_rec.to_date_dsp := po_tbl_date (cur_get_item_capacity_tab(1).to_Date);
item_capacity_rec.capacity_per_day_dsp := po_tbl_number (cur_get_item_capacity_tab(1).CAPACITY_PER_DAY);
item_capacity_rec.attribute_category := po_tbl_varchar30 (cur_get_item_capacity_tab(1).attribute_category);
item_capacity_rec.attribute1 := po_tbl_varchar240 (cur_get_item_capacity_tab(1).attribute1);
item_capacity_rec.attribute2 := po_tbl_varchar240 (cur_get_item_capacity_tab(1).attribute2);
item_capacity_rec.attribute3 := po_tbl_varchar240 (cur_get_item_capacity_tab(1).attribute3);
item_capacity_rec.attribute4 := po_tbl_varchar240 (cur_get_item_capacity_tab(1).attribute4);
item_capacity_rec.attribute5 := po_tbl_varchar240 (cur_get_item_capacity_tab(1).attribute5);
item_capacity_rec.attribute6 := po_tbl_varchar240 (cur_get_item_capacity_tab(1).attribute6);
item_capacity_rec.attribute7 := po_tbl_varchar240 (cur_get_item_capacity_tab(1).attribute7);
item_capacity_rec.attribute8 := po_tbl_varchar240 (cur_get_item_capacity_tab(1).attribute8);
item_capacity_rec.attribute9 := po_tbl_varchar240 (cur_get_item_capacity_tab(1).attribute9);
item_capacity_rec.attribute10 := po_tbl_varchar240 (cur_get_item_capacity_tab(1).attribute10);
item_capacity_rec.attribute11 := po_tbl_varchar240 (cur_get_item_capacity_tab(1).attribute11);
item_capacity_rec.attribute12 := po_tbl_varchar240 (cur_get_item_capacity_tab(1).attribute12);
item_capacity_rec.attribute13 := po_tbl_varchar240 (cur_get_item_capacity_tab(1).attribute13);
item_capacity_rec.attribute14 := po_tbl_varchar240 (cur_get_item_capacity_tab(1).attribute14);
item_capacity_rec.attribute15 := po_tbl_varchar240 (cur_get_item_capacity_tab(1).attribute15);
item_capacity_rec.request_id := po_tbl_number (NULL);
item_capacity_rec.program_application_id := po_tbl_number (NULL);
item_capacity_rec.program_id := po_tbl_number (NULL);
item_capacity_rec.program_update_date := po_tbl_date (NULL);
EXIT; --Just coming out from the loop.. we will handle multiple records as and when we encounter them in the future
END LOOP ;
END IF ;
/* Time to check item tolerance */
--Cursor to get Supplier's Item tolerance Records
OPEN CUR_GET_ITEM_TLRNC (P_IN_N_ASL_ID => cur_Get_asl_data_tab(i).asl_id);
FETCH CUR_GET_ITEM_TLRNC BULK COLLECT INTO cur_get_item_tlrnc_tab;
CLOSE CUR_GET_ITEM_TLRNC;
IF cur_get_item_tlrnc_tab.count>0
THEN
FOR i in cur_get_item_tlrnc_tab.first..cur_get_item_tlrnc_tab.last
LOOP
log_msg ('Data feeding for item_tolerance_rec');
item_tolerance_rec := NEW po_supplier_item_tolerance_rec ();
item_tolerance_rec.user_key := po_tbl_number (1);
item_tolerance_rec.process_action := po_tbl_varchar30 ('ADD');
item_tolerance_rec.using_organization_id := po_tbl_number (item_dtls_tab(j).dest_org_id);
item_tolerance_rec.using_organization_dsp := po_tbl_varchar240 (NULL);
item_tolerance_rec.number_of_days_dsp := po_tbl_number (cur_get_item_tlrnc_tab(1).NUMBER_OF_DAYS);
item_tolerance_rec.tolerance_dsp := po_tbl_number (cur_get_item_tlrnc_tab(1).TOLERANCE);
item_tolerance_rec.attribute_category := po_tbl_varchar30 (cur_get_item_tlrnc_tab(1).attribute_category);
item_tolerance_rec.attribute1 := po_tbl_varchar240 (cur_get_item_tlrnc_tab(1).attribute1);
item_tolerance_rec.attribute2 := po_tbl_varchar240 (cur_get_item_tlrnc_tab(1).attribute2);
item_tolerance_rec.attribute3 := po_tbl_varchar240 (cur_get_item_tlrnc_tab(1).attribute3);
item_tolerance_rec.attribute4 := po_tbl_varchar240 (cur_get_item_tlrnc_tab(1).attribute4);
item_tolerance_rec.attribute5 := po_tbl_varchar240 (cur_get_item_tlrnc_tab(1).attribute5);
item_tolerance_rec.attribute6 := po_tbl_varchar240 (cur_get_item_tlrnc_tab(1).attribute6);
item_tolerance_rec.attribute7 := po_tbl_varchar240 (cur_get_item_tlrnc_tab(1).attribute7);
item_tolerance_rec.attribute8 := po_tbl_varchar240 (cur_get_item_tlrnc_tab(1).attribute8);
item_tolerance_rec.attribute9 := po_tbl_varchar240 (cur_get_item_tlrnc_tab(1).attribute9);
item_tolerance_rec.attribute10 := po_tbl_varchar240 (cur_get_item_tlrnc_tab(1).attribute10);
item_tolerance_rec.attribute11 := po_tbl_varchar240 (cur_get_item_tlrnc_tab(1).attribute11);
item_tolerance_rec.attribute12 := po_tbl_varchar240 (cur_get_item_tlrnc_tab(1).attribute12);
item_tolerance_rec.attribute13 := po_tbl_varchar240 (cur_get_item_tlrnc_tab(1).attribute13);
item_tolerance_rec.attribute14 := po_tbl_varchar240 (cur_get_item_tlrnc_tab(1).attribute14);
item_tolerance_rec.attribute15 := po_tbl_varchar240 (cur_get_item_tlrnc_tab(1).attribute15);
item_tolerance_rec.request_id := po_tbl_number (NULL);
item_tolerance_rec.program_application_id := po_tbl_number (NULL);
item_tolerance_rec.program_id := po_tbl_number (NULL);
item_tolerance_rec.program_update_date := po_tbl_date (NULL);
EXIT; --Just coming out from the loop.. we will handle multiple records as and when we encounter them in the future
END LOOP ;
END IF ;
--Lets call the API to create the records
log_msg ('Calling the API for inventory item id: '||cur_Get_asl_data_tab(i).item_id);
po_asl_api_pub.create_update_asl (p_asl_rec => Appr_Supp_Rec,
p_asl_attr_rec => Appr_Supp_attr_Rec,
p_asl_doc_rec => asl_doc_Rec,
p_chv_auth_rec => chv_auth_rec,
p_capacity_rec => item_capacity_rec,
p_tolerance_rec => item_tolerance_rec,
p_commit => 'Y',
x_session_key => x_session_key,
x_return_status => x_return_status,
x_return_msg => x_return_msg,
x_errors => x_errors
);
log_msg ('Return Status>>>>' || x_return_status);
IF (x_return_status<> 'S')
THEN
log_msg ('Session Key>>>> ' || x_session_key);
log_msg ('Return Msg>>>> ' || x_return_msg);
l_v_final_error_msg := l_v_final_error_msg|| ' Few records failed while creating the ASL';
p_out_v_retcode := 2;
l_v_error_message := 'Session Key: '
|| x_errors.session_key (i)
|| ' user key:'
|| x_errors.user_key (i)
|| ' entity:'
|| x_errors.entity_name (i)
|| ' reason:';
FOR i IN 1 .. x_errors.user_key.COUNT
LOOP
l_v_error_message := SUBSTR ( l_v_error_message || CHR (10) || x_errors.rejection_reason(i),1,2000);
log_msg ( l_v_error_message );
END LOOP;
END IF ;
END LOOP;
log_msg ( 'All records processed.. time for ASL status update' );
END IF ;
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_msg := 'Unexpected error in procedure ASSIGN_ITEMS: ' || sqlerrm;
log_msg (l_msg);
p_out_v_retcode := 2;
p_out_v_errbuf := 'Unexpected Error in procedure ASSIGN_ITEMS: '||SQLERRM;
END ;Check out the Amazon Deals of the day based on your interests..
1 thought on “ASL Creation – Copy from One Inv Org to Another”
Comments are closed.