This integration will integrate IR/ISO based on upload file from APEX application that has been created as custom.
Once file been uploaded lot based data based on FIFO basis will be happening and finally integrated IR/ISO into EBS.
Scripts to migrate IRISO
——————————
Package Specification
***********************************
CREATE OR REPLACE PACKAGE “XX_IRISO_IMP_PKG”
IS
/* Global variable declaration*/
g_module_prefix VARCHAR2 (250) := ‘plsql.xx_iriso_imp_pkg.’;
gc_responsiblity_name VARCHAR2 (250) := ‘India Local Order Management’;
/* Validation Process*/
PROCEDURE pr_validation (
p_batch_run_id IN VARCHAR2,
— p_source_org IN VARCHAR2,
— p_dest_org IN VARCHAR2,
x_status OUT VARCHAR2,
x_error_message OUT VARCHAR2
);
PROCEDURE GROUPING_BATCH (P_BATCH_RUN_ID IN NUMBER);
/* Get Onhand Quantity*/
PROCEDURE get_onhand_quantities (
p_item_name IN VARCHAR2,
p_organziation_id IN NUMBER,
p_subinv IN VARCHAR2 DEFAULT NULL,
x_qty_oh OUT NOCOPY NUMBER,
x_qty_res_oh OUT NOCOPY NUMBER,
x_qty_res OUT NOCOPY NUMBER,
x_qty_sug OUT NOCOPY NUMBER,
x_qty_att OUT NOCOPY NUMBER,
x_qty_atr OUT NOCOPY NUMBER
);
— PROCEDURE xx_impo_pr_pro (
— errbuf OUT VARCHAR2,
— retcode OUT VARCHAR2,
— p_batch_run_id IN VARCHAR2,
— p_source_org IN VARCHAR2,
— p_dest_org IN VARCHAR2
— );
PROCEDURE wait_request (
p_request_id IN NUMBER,
p_program_name IN VARCHAR2,
x_status OUT VARCHAR2,
x_error_message OUT VARCHAR2
);
/*Tax*/
FUNCTION add_gst_tax_req (p_req_id NUMBER)
RETURN VARCHAR2;
/* Insert Lot Staging*/
–PROCEDURE import_lot_bkup (p_batch_run_id IN NUMBER);
/*Reservation*/
PROCEDURE iriso_lot_reservation (p_batch_run_id IN NUMBER);
/* Import IRISO*/
PROCEDURE impo_pr_pro (
p_batch_run_id IN VARCHAR2);
PROCEDURE import_lot (p_batch_run_id IN NUMBER ,p_error_code out number);
PROCEDURE pickrelease (p_batch_run_id NUMBER,p_header_id NUMBER);
PROCEDURE xx_iriso_book_pro (
p_batch_run_id NUMBER,
p_header_id NUMBER
);
PROCEDURE xx_iriso_ship_confirm (
p_batch_run_id NUMBER,
p_header_id NUMBER
);
function get_conversion_factor (p_item_code varchar2,p_UOM varchar2) return number;
END APPS.xx_iriso_imp_pkg;
Package Body
************************************
/* Formatted on 2020/09/10 19:22 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PACKAGE BODY apps.xx_iriso_imp_pkg
IS
PROCEDURE submit_pr_request (p_batch_run_id IN NUMBER);
PROCEDURE so_submit_request (p_batch_run_id IN VARCHAR2);
PROCEDURE print_log (p_message IN VARCHAR2)
AS
BEGIN
fnd_file.put_line (fnd_file.LOG, p_message);
DBMS_OUTPUT.put_line (p_message);
END;
PROCEDURE print_output (p_message IN VARCHAR2)
AS
BEGIN
fnd_file.put_line (fnd_file.output, p_message);
DBMS_OUTPUT.put_line (p_message);
END;
PROCEDURE grouping_batch (p_batch_run_id IN NUMBER)
AS
CURSOR cur_loc
IS
SELECT a.source_org_code, destination_org_code, productgroup
FROM xx_iriso_stg_tab a
WHERE batch_run_id = p_batch_run_id
AND req_header_id IS NULL
AND NVL (status, ‘E’) <> ‘E’
GROUP BY a.source_org_code, destination_org_code, productgroup;
i NUMBER := 0;
l_api_name VARCHAR2 (500) := g_module_prefix || ‘grouping_batch’;
l_progress VARCHAR2 (240) := ‘001’;
lc_error_message LONG := NULL;
BEGIN
print_log (l_api_name || ‘ Started : ‘);
FOR loc_rec IN cur_loc
LOOP
i := i + 1;
UPDATE xx_iriso_stg_tab a
SET batch_id = p_batch_run_id || ‘-‘ || i
WHERE a.source_org_code = loc_rec.source_org_code
AND destination_org_code = loc_rec.destination_org_code
AND productgroup = loc_rec.productgroup
AND batch_run_id = p_batch_run_id
AND NVL (status, ‘E’) <> ‘E’;
print_log (‘ p_batch_run_id : ‘ || p_batch_run_id || ‘-‘ || i);
END LOOP;
COMMIT;
print_log (l_api_name || ‘ Ended : ‘);
EXCEPTION
WHEN OTHERS
THEN
lc_error_message :=
‘EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line;
print_log ( ‘**** EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
END;
PROCEDURE wait_request (
p_request_id IN NUMBER,
p_program_name IN VARCHAR2,
x_status OUT VARCHAR2,
x_error_message OUT VARCHAR2
)
AS
l_req_return_status BOOLEAN;
lc_phase VARCHAR2 (50) := NULL;
lc_status VARCHAR2 (50) := NULL;
lc_dev_phase VARCHAR2 (50) := NULL;
lc_dev_status VARCHAR2 (50) := NULL;
lc_message VARCHAR2 (50) := NULL;
l_status VARCHAR2 (50) := NULL;
lc_error_msg LONG := NULL;
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_org_id NUMBER := :org_id;
BEGIN
print_output (p_message => ‘wait request : start ‘);
BEGIN
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = ‘ARUN’;
EXCEPTION
WHEN OTHERS
THEN
l_user_id := NULL;
END;
BEGIN
SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = ‘India Local Purchasing’
AND NVL (TRUNC (end_date), TRUNC (SYSDATE)) >= TRUNC (SYSDATE);
EXCEPTION
WHEN OTHERS
THEN
l_resp_id := NULL;
l_appl_id := NULL;
END;
fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
mo_global.set_policy_context (‘S’, l_org_id);
IF p_request_id > 0
THEN
LOOP
l_req_return_status :=
fnd_concurrent.wait_for_request (request_id => p_request_id,
INTERVAL => 5
–interval Number of seconds to wait between checks
,
max_wait => 60
–Maximum number of seconds to wait for the request completion
— 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
print_output
(p_message => ‘The ‘
|| p_program_name
|| ‘completed in error. Oracle request id: ‘
|| p_request_id
|| ‘ ‘
|| SQLERRM
);
l_status := ‘E’;
lc_error_msg :=
‘The ‘
|| p_program_name
|| ‘completed in error. Oracle request id: ‘
|| p_request_id
|| ‘ ‘
|| SQLERRM;
ELSIF UPPER (lc_phase) = ‘COMPLETED’ AND UPPER (lc_status) = ‘NORMAL’
THEN
l_status := ‘S’;
lc_error_msg :=
‘The ‘
|| p_program_name
|| ‘request successful for request id: ‘
|| p_request_id;
print_output
(p_message => ‘The ‘
|| p_program_name
|| ‘request successful for request id: ‘
|| p_request_id
);
ELSE
l_status := ‘E’;
lc_error_msg :=
‘The ‘
|| p_program_name
|| ‘request failed. Oracle request id: ‘
|| p_request_id
|| ‘ ‘
|| SQLERRM;
print_output
(p_message => ‘The ‘
|| p_program_name
|| ‘request successful for request id: ‘
|| p_request_id
);
END IF;
ELSE
l_status := ‘E’;
lc_error_msg := ‘Unable to find the request Id : ‘ || p_request_id;
END IF;
print_output (p_message => ‘wait request : End ‘);
x_status := l_status;
x_error_message := lc_error_msg;
EXCEPTION
WHEN OTHERS
THEN
lc_error_msg :=
‘EXCPETION : Wait Request ‘
|| p_program_name
|| ‘ ‘
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line;
x_status := ‘E’;
x_error_message := lc_error_msg;
END;
PROCEDURE pr_validation (
p_batch_run_id IN VARCHAR2,
x_status OUT VARCHAR2,
x_error_message OUT VARCHAR2
)
AS
CURSOR cur
IS
SELECT st.*
FROM xx_iriso_stg_tab st
WHERE 1 = 1
AND NVL (status, ‘E’) = ‘O’ — IN (‘E’, ‘O’)
AND batch_run_id = p_batch_run_id
AND req_header_id IS NULL;
l_err_msg LONG := NULL;
l_ou_id NUMBER;
l_preparer_id NUMBER;
l_source_organization_id NUMBER;
l_dest_organization_id NUMBER;
l_delv_location_id NUMBER;
l_item_id NUMBER;
l_cat_id NUMBER;
l_uom_code VARCHAR2 (100);
l_source_item_id NUMBER;
l_dest_item_id NUMBER;
l_batch_id NUMBER;
l_item_cost NUMBER;
l_err_flag CHAR (2) := NULL;
lb_valid_flg BOOLEAN := TRUE;
l_qty_oh NUMBER;
l_qty_res_oh NUMBER;
l_qty_res NUMBER;
l_qty_sug NUMBER;
l_qty_att NUMBER;
l_qty_atr NUMBER;
l_qty_s NUMBER;
BEGIN
l_err_msg := NULL;
l_ou_id := NULL;
l_preparer_id := NULL;
l_source_organization_id := NULL;
l_dest_organization_id := NULL;
l_delv_location_id := NULL;
l_source_item_id := NULL;
l_dest_item_id := NULL;
l_cat_id := NULL;
l_uom_code := NULL;
l_batch_id := NULL;
l_item_cost := NULL;
l_err_flag := ‘N’;
l_qty_oh := 0;
l_qty_res_oh := 0;
l_qty_res := 0;
l_qty_sug := 0;
l_qty_att := 0;
l_qty_atr := 0;
l_qty_s := 0;
FOR rec IN cur
LOOP
l_err_msg := NULL;
l_ou_id := NULL;
l_preparer_id := NULL;
l_source_organization_id := NULL;
l_dest_organization_id := NULL;
l_delv_location_id := NULL;
l_source_item_id := NULL;
l_dest_item_id := NULL;
l_cat_id := NULL;
l_uom_code := NULL;
l_batch_id := NULL;
l_item_cost := NULL;
l_err_flag := ‘N’;
l_qty_oh := 0;
l_qty_res_oh := 0;
l_qty_res := 0;
l_qty_sug := 0;
l_qty_att := 0;
l_qty_atr := 0;
l_qty_s := 0;
— OU Validation
BEGIN
SELECT organization_id
INTO l_ou_id
FROM hr_operating_units
WHERE NAME = rec.ou_name;
EXCEPTION
WHEN OTHERS
THEN
l_err_flag := ‘E’;
l_err_msg := ‘Invalid OU’;
lb_valid_flg := FALSE;
print_log (‘Invalid OU.’);
END;
— Preparer Validation
BEGIN
SELECT agent_id
INTO l_preparer_id
FROM per_all_people_f ppf, po_agents pa
WHERE full_name = rec.preparer
AND SYSDATE BETWEEN effective_start_date AND effective_end_date
AND ppf.person_id = pa.agent_id;
—
EXCEPTION
WHEN OTHERS
THEN
l_err_flag := ‘E’;
l_err_msg := l_err_msg || ‘, ‘ || ‘Preparer does not Exist’;
lb_valid_flg := FALSE;
print_log (‘Preparer does not Exist’);
—
END;
— Source Org Validation
BEGIN
SELECT hou.organization_id
INTO l_source_organization_id
FROM hr_all_organization_units hou,
org_organization_definitions ood
WHERE 1 = 1
AND ood.organization_id = hou.organization_id
AND UPPER (organization_code) = TRIM (rec.source_org_code);
—
EXCEPTION
WHEN OTHERS
THEN
l_err_flag := ‘E’;
l_err_msg := l_err_msg || ‘, ‘ || ‘Invalid Source Org.’;
lb_valid_flg := FALSE;
print_log (‘Invalid Source Org.’);
—
END;
— Destination Org Validation
BEGIN
SELECT hou.organization_id
INTO l_dest_organization_id
FROM hr_all_organization_units hou,
org_organization_definitions ood
WHERE 1 = 1
AND ood.organization_id = hou.organization_id
AND UPPER (organization_code) = TRIM (rec.destination_org_code);
—
EXCEPTION
WHEN OTHERS
THEN
l_err_flag := ‘E’;
l_err_msg := l_err_msg || ‘, ‘ || ‘Invalid Destination Org.’;
lb_valid_flg := FALSE;
—
print_log (‘Invalid Destination Org.’);
END;
— Delivery Location Validation
BEGIN
SELECT hou.location_id
INTO l_delv_location_id
FROM hr_all_organization_units hou,
org_organization_definitions ood
WHERE 1 = 1
AND ood.organization_id = hou.organization_id
AND hou.organization_id = l_dest_organization_id
AND UPPER (organization_code) = TRIM (rec.destination_org_code);
—
EXCEPTION
WHEN OTHERS
THEN
l_err_flag := ‘E’;
l_err_msg := l_err_msg || ‘, ‘ || ‘Invalid Location.’;
lb_valid_flg := FALSE;
print_log (‘Invalid Location.’);
END;
— Item Validation
BEGIN
SELECT inventory_item_id
INTO l_source_item_id
FROM mtl_system_items_b
WHERE UPPER (segment1) = UPPER (rec.item_code)
AND organization_id = l_source_organization_id
AND internal_order_flag = ‘Y’
AND inventory_item_status_code = ‘Active’;
EXCEPTION
WHEN OTHERS
THEN
l_err_flag := ‘E’;
l_err_msg :=
l_err_msg || ‘, ‘ || ‘Item is not Assigned to Source Org’;
lb_valid_flg := FALSE;
print_log (‘Item is not Assigned to Source Org’);
END;
BEGIN
—
SELECT inventory_item_id
INTO l_dest_item_id
FROM mtl_system_items_b
WHERE UPPER (segment1) = UPPER (rec.item_code)
AND organization_id = l_dest_organization_id
AND internal_order_flag = ‘Y’
AND inventory_item_status_code = ‘Active’;
—
EXCEPTION
WHEN OTHERS
THEN
l_err_flag := ‘E’;
l_err_msg :=
l_err_msg
|| ‘, ‘
|| ‘Item is not Assigned to Destination Org’;
lb_valid_flg := FALSE;
print_log (‘Item is not Assigned to Destination Org’);
END;
— UOM Validation
BEGIN
—
SELECT unit_of_measure
INTO l_uom_code
FROM mtl_units_of_measure_tl
— WHERE unit_of_measure = rec.uom_code; changed by rajan on 17072020
WHERE uom_code = rec.uom_code;
—
EXCEPTION
WHEN OTHERS
THEN
l_err_flag := ‘E’;
l_err_msg := l_err_msg || ‘, ‘ || ‘Invalid UOM.’;
lb_valid_flg := FALSE;
print_log (‘Invalid UOM.’);
END;
— Unit PRice Validation
BEGIN
SELECT item_cost
INTO l_item_cost
FROM cst_item_costs
WHERE inventory_item_id = l_source_item_id
AND organization_id = l_source_organization_id
AND cost_type_id = 2;
EXCEPTION
WHEN OTHERS
THEN
l_err_flag := ‘E’;
l_err_msg := l_err_msg || ‘, ‘ || ‘Item Cost does not Exist’;
lb_valid_flg := FALSE;
print_log (‘Item Cost does not Exist’);
END;
— Item category validation
BEGIN
SELECT category_id
INTO l_cat_id
FROM mtl_item_categories
WHERE inventory_item_id = l_source_item_id
AND organization_id = l_source_organization_id
AND category_set_id = 1100000002;
EXCEPTION
WHEN OTHERS
THEN
l_err_flag := ‘E’;
l_err_msg :=
l_err_msg || ‘, ‘ || ‘Item Category does not Exist’;
lb_valid_flg := FALSE;
print_log (‘Item Category does not Exist’);
END;
BEGIN
get_onhand_quantities (UPPER (rec.item_code),
l_source_organization_id,
NULL,
l_qty_oh,
l_qty_res_oh,
l_qty_res,
l_qty_sug,
l_qty_att,
l_qty_atr
);
print_log (‘l_qty_oh : ‘ || l_qty_oh);
print_log (‘rec.item_code : ‘ || rec.item_code);
print_log (‘l_qty_res_oh : ‘ || l_qty_res_oh);
print_log (‘l_qty_resv : ‘ || l_qty_res);
— IF NVL (l_qty_oh, 0) = 0
IF NVL (l_qty_atr, 0) = 0
THEN
l_err_flag := ‘E’;
l_err_msg := l_err_msg || ‘, ‘ || ‘No Available Quantity’;
lb_valid_flg := FALSE;
print_log (‘No Available Quantity’);
END IF;
EXCEPTION —
WHEN OTHERS
THEN
l_err_flag := ‘E’;
l_err_msg := l_err_msg || ‘, ‘ || ‘Quantity does not Exist’;
lb_valid_flg := FALSE;
print_log (‘Quantity does not Exist’);
END;
print_log (‘l_err_flag’ || ‘->’ || l_err_flag);
IF l_err_flag = ‘N’
THEN
UPDATE xx_iriso_stg_tab
SET status = ‘V’,
inventory_item_id = l_dest_item_id,
category_id = l_cat_id,
source_org_id = l_source_organization_id,
destination_org_id = l_dest_organization_id,
delievery_location_id = l_delv_location_id,
preparer_id = l_preparer_id,
org_id = l_ou_id,
unit_price = l_item_cost,
err_msg = NULL
WHERE batch_run_id = rec.batch_run_id AND rec_id = rec.rec_id;
–ROWID = rec.ROWID;
ELSE
lb_valid_flg := FALSE;
UPDATE xx_iriso_stg_tab
SET status = ‘E’,
err_msg = l_err_msg
WHERE batch_run_id = rec.batch_run_id AND rec_id = rec.rec_id;
— ROWID = rec.ROWID;
END IF;
END LOOP;
x_status := ‘S’;
x_error_message := NULL;
EXCEPTION
WHEN OTHERS
THEN
l_err_msg :=
‘EXCPETION : ‘
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line;
x_status := ‘E’;
x_error_message := l_err_msg;
END pr_validation;
PROCEDURE get_onhand_quantities (
p_item_name IN VARCHAR2,
p_organziation_id IN NUMBER,
p_subinv IN VARCHAR2 DEFAULT NULL,
x_qty_oh OUT NOCOPY NUMBER,
x_qty_res_oh OUT NOCOPY NUMBER,
x_qty_res OUT NOCOPY NUMBER,
x_qty_sug OUT NOCOPY NUMBER,
x_qty_att OUT NOCOPY NUMBER,
x_qty_atr OUT NOCOPY NUMBER
)
IS
CURSOR c_get_item_id (c_item_name VARCHAR2, c_organization_id NUMBER)
IS
SELECT inventory_item_id, organization_id
FROM mtl_system_items_b
WHERE segment1 = c_item_name
AND organization_id = c_organization_id;
l_api_return_status VARCHAR2 (1);
l_qty_oh NUMBER;
l_qty_res_oh NUMBER;
l_qty_res NUMBER;
l_qty_sug NUMBER;
l_qty_att NUMBER;
l_qty_atr NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2 (250);
x_return_msg VARCHAR2 (4000);
l_msg_index_out NUMBER;
l_organziation_id NUMBER;
l_item_id NUMBER;
BEGIN
OPEN c_get_item_id (p_item_name, p_organziation_id);
LOOP
FETCH c_get_item_id
INTO l_item_id, l_organziation_id;
EXIT WHEN c_get_item_id%NOTFOUND;
apps.inv_quantity_tree_grp.clear_quantity_cache;
apps.inv_quantity_tree_pub.query_quantities
(p_api_version_number => 1.0,
p_init_msg_lst => apps.fnd_api.g_false,
x_return_status => l_api_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => l_organziation_id,
p_inventory_item_id => l_item_id,
p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,
p_onhand_source => 3,
p_is_revision_control => FALSE,
p_is_lot_control => FALSE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => NULL,
p_subinventory_code => p_subinv,
p_locator_id => NULL,
x_qoh => l_qty_oh,
x_rqoh => l_qty_res_oh,
x_qr => l_qty_res,
x_qs => l_qty_sug,
x_att => l_qty_att,
x_atr => l_qty_atr
);
IF l_api_return_status = fnd_api.g_ret_sts_success
THEN
NULL;
ELSE
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => i,
p_encoded => fnd_api.g_false,
p_data => l_msg_data,
p_msg_index_out => l_msg_index_out
);
IF x_return_msg IS NULL
THEN
x_return_msg := SUBSTR (l_msg_data, 1, 250);
ELSE
x_return_msg :=
x_return_msg || ‘,’ || SUBSTR (l_msg_data, 1, 250);
END IF;
END LOOP;
print_log (‘ QUERY_QUANTITIES API Failure : ‘ || x_return_msg);
END IF;
END LOOP;
CLOSE c_get_item_id;
x_qty_oh := l_qty_oh;
x_qty_res_oh := l_qty_res_oh;
x_qty_res := l_qty_res;
x_qty_sug := l_qty_sug;
x_qty_att := l_qty_att;
x_qty_atr := l_qty_atr;
EXCEPTION
WHEN OTHERS
THEN
print_log (‘UNEXP_ERROR : ‘ || SUBSTR (SQLERRM, 1, 250));
END;
/*Tax*/
FUNCTION add_gst_tax_req (p_req_id NUMBER)
RETURN VARCHAR2
AS
CURSOR c
IS
————Code Changes by Tax category based on source code on 21-Jul-2020——
SELECT prla.requisition_header_id, prla.requisition_line_id,
prla.source_organization_id,
prla.destination_organization_id, prla.item_id,
— DECODE(SUBSTR (organization_code, -1, 1), code changes done to include 05A and 08A
DECODE (SUBSTR (DECODE (organization_code,
’05A’, ‘555’,
’08A’, ‘555’,
organization_code
),
-1,
1
),
‘1’, ‘IRISO Inter GST’,
‘2’, ‘IRISO Inter GST-EPD’,
‘3’, ‘IRISO Inter GST-HVD’,
‘5’, ‘IRISO Inter GST-FDS’,
‘9’, ‘IRISO Inter GST-ORT’
) tax_cat_list
FROM po_requisition_lines_all prla,
jai_tax_det_factors jtd,
org_organization_definitions ood
WHERE 1 = 1
AND prla.requisition_header_id = jtd.trx_id
AND prla.requisition_line_id = jtd.trx_line_id
AND jtd.override_tax_category_id IS NULL
AND prla.source_organization_id = ood.organization_id
AND requisition_header_id = p_req_id;
v_source_gst VARCHAR2 (250) := NULL;
v_dest_gst VARCHAR2 (250) := NULL;
v_tax_category_id NUMBER;
vz_tax_category_id NUMBER;
v_item_type VARCHAR2 (250) := NULL;
v_item_no VARCHAR2 (250) := NULL;
v_template_id NUMBER;
v_return_msg VARCHAR2 (1000) := NULL;
v_regime_id NUMBER;
v_regime_code VARCHAR2 (250) := NULL;
v_regime_type VARCHAR2 (250) := NULL;
v_appl_id NUMBER;
v_rec_val VARCHAR2 (10) := NULL;
l_api_name VARCHAR2 (500)
:= g_module_prefix || ‘add_gst_tax_req’;
l_progress VARCHAR2 (240) := ‘001’;
lc_error_message LONG := NULL;
lx_status VARCHAR2 (240) := NULL;
lx_error_message LONG := NULL;
BEGIN
DBMS_OUTPUT.put_line (l_api_name || ‘Started : ‘ || l_progress);
DBMS_OUTPUT.put_line (‘p_req_no : ‘ || p_req_id || CHR (13) || ‘001’);
BEGIN
SELECT regime_id, regime_code, regime_type
INTO v_regime_id, v_regime_code, v_regime_type
FROM jai_regimes
WHERE regime_code = ‘GST’;
SELECT application_id
INTO v_appl_id
FROM fnd_application
WHERE application_short_name = ‘PO’;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( ‘****regime EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
END;
DBMS_OUTPUT.put_line ( ‘v_regime_id : ‘
|| v_regime_id
|| ‘ v_regime_code : ‘
|| v_regime_code
|| ‘ v_regime_type : ‘
|| v_regime_type
|| CHR (13)
|| ‘002’
);
DBMS_OUTPUT.put_line (‘v_appl_id : ‘ || v_appl_id);
FOR i IN c
LOOP
DBMS_OUTPUT.put_line ( ‘ requisition_line_id : ‘
|| i.requisition_line_id
|| CHR (13)
|| ‘ i.source_organization_id : ‘
|| i.source_organization_id
|| CHR (13)
|| ‘003’
);
BEGIN
SELECT registration_number
INTO v_source_gst
FROM jai_party_reg_lines
WHERE regime_id = v_regime_id
AND party_reg_id IN (
SELECT party_reg_id
FROM jai_party_regs
WHERE party_id = i.source_organization_id
AND party_type_code = ‘IO’);
EXCEPTION
WHEN OTHERS
THEN
v_return_msg :=
v_return_msg || ‘ Party Source GST Reg# Not found.’;
END;
DBMS_OUTPUT.put_line ( ‘ v_source_gst : ‘
|| v_source_gst
|| CHR (13)
|| ‘004’
);
BEGIN
SELECT registration_number
INTO v_dest_gst
FROM jai_party_reg_lines
WHERE regime_id = v_regime_id
AND party_reg_id IN (
SELECT party_reg_id
FROM jai_party_regs
WHERE party_id = i.destination_organization_id
AND party_type_code = ‘IO’);
EXCEPTION
WHEN OTHERS
THEN
v_return_msg :=
v_return_msg || ‘ Party Dest GST Reg# Not found.’;
END;
DBMS_OUTPUT.put_line ( ‘destination_organization_id : ‘
|| i.destination_organization_id
|| CHR (13)
|| ‘ v_dest_gst : ‘
|| v_dest_gst
|| CHR (13)
|| ‘005’
);
IF v_source_gst <> v_dest_gst
THEN
DBMS_OUTPUT.put_line (‘ if condition : true ‘ || CHR (13)
|| ‘006’
);
BEGIN
SELECT tax_category_id
INTO v_tax_category_id
FROM jai_inv_itm_taxctg_dtls jiitd,
jai_inv_itm_taxctg_hdrs jiith
WHERE 1 = 1
AND jiitd.inventory_item_id = i.item_id
AND jiith.tax_category_list = i.tax_cat_list
— AND jiith.organization_id = i.source_organization_id
AND jiitd.tax_category_list = jiith.tax_category_list;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line
(‘Error : GST Tax Category is not attached to the Item’);
DBMS_OUTPUT.put_line (‘Error : Item ID :’ || i.item_id);
v_return_msg :=
v_return_msg
|| ‘ GST Tax Category’
|| ‘IRISO Inter GST’
|| ‘not attached to the Item.’
|| i.item_id;
UPDATE xx_iriso_stg_tab rec
SET err_msg =
err_msg
|| ‘GST Tax Category is not attached to the Item’
WHERE req_header_id = i.requisition_header_id
AND inventory_item_id = i.item_id
— AND batch_run_id = p_batch_run_id
AND status = ‘PR’;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( ‘**** Category EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
UPDATE xx_iriso_stg_tab rec
SET err_msg =
err_msg
|| ‘Error retrieving Tax Category for this item.’
WHERE req_header_id = i.requisition_header_id
AND inventory_item_id = i.item_id
— AND batch_run_id = p_batch_run_id
AND status = ‘PR’;
v_tax_category_id := NULL;
END;
DBMS_OUTPUT.put_line ( ‘ i.item_id : ‘
|| i.item_id
|| CHR (13)
|| ‘i.source_organization_id : ‘
|| i.source_organization_id
|| CHR (13)
|| ‘Item No : ‘
|| v_item_no
|| CHR (13)
|| ‘ v_tax_category_id : ‘
|| v_tax_category_id
|| CHR (13)
|| ‘007’
);
IF v_tax_category_id IS NOT NULL
THEN
BEGIN
SELECT item_type, segment1
INTO v_item_type, v_item_no
FROM mtl_system_items_b
WHERE inventory_item_id = i.item_id
AND organization_id = i.destination_organization_id;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( ‘**** item_type EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
END;
DBMS_OUTPUT.put_line ( ‘ i.item_id : ‘
|| i.item_id
|| CHR (13)
|| ‘ i.destination_organization_id : ‘
|| i.destination_organization_id
|| ‘ Item type : ‘
|| v_item_type
|| CHR (13)
|| ‘007’
);
BEGIN
SELECT attribute_value
INTO v_rec_val
FROM jai_item_templ_hdr_v jih, jai_item_templ_dtls_v jid
WHERE 1 = 1
AND jih.template_hdr_id = jid.template_hdr_id
AND jid.attribute_code = ‘RECOVERABLE’
AND jih.inventory_item_id = i.item_id
AND jih.organization_id = i.destination_organization_id;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( ‘**** attribute_value EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
END;
DBMS_OUTPUT.put_line ( ‘ i.item_id : ‘
|| i.item_id
|| CHR (13)
|| ‘ i.destination_organization_id : ‘
|| i.destination_organization_id
|| CHR (13)
|| ‘v_rec_val : ‘
|| v_rec_val
|| CHR (13)
|| ‘008’
);
IF v_item_type IS NOT NULL
THEN
BEGIN
UPDATE jai_tax_det_factors
SET override_tax_category_id = v_tax_category_id,
user_modified_flag = ‘Y’,
intended_use =
DECODE (v_rec_val,
‘Y’, ‘RECOVERABLE’,
‘NON RECOVERABLE’
)
–sac_code_id = v_template_id
WHERE trx_id = i.requisition_header_id
AND trx_line_id = i.requisition_line_id;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( ‘**** UPDATE jai_tax_det_factors EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
END;
END IF;
END IF;
BEGIN
INSERT INTO jai_tax_lines
(tax_line_id, det_factor_id, org_id,
organization_id, location_id, application_id,
entity_code, event_class_code, event_type_code,
tax_event_class_code, tax_event_type_code,
ledger_id, trx_id, trx_line_id, trx_level_type,
trx_number, trx_line_number, trx_type, trx_date,
legal_entity_id,
–frogen_flag,
party_type, party_id,
party_site_id, trx_uom, item_id, unit_price,
line_amt, trx_line_quantity, trx_currency_code,
tax_currency_code, functional_currency_code,
tax_regime_id, tax_regime_code, tax_regime_type,
first_party_reg_id, first_party_primary_reg_name,
first_party_primary_reg_num, reporting_only_flag,
tax_authority_id, tax_authority_site_id,
tax_type_id, abatement_flag,
update_vendor_on_transaction, offset_flag,
recoverable_flag, self_assessed_flag,
tax_point_basis, tax_rate_id, tax_rate_code,
tax_rate_type, recovery_percentage,
inclusive_flag, tax_rate_percentage,
actual_tax_rate, tax_rounded_to,
tax_rounding_factor, taxable_rounding_factor,
unround_taxable_amt_trx_curr,
unround_taxable_amt_tax_curr,
unround_taxable_amt_fun_curr,
unround_tax_amt_trx_curr,
unround_tax_amt_fun_curr,
rounded_tax_amt_trx_curr,
rounded_tax_amt_fun_curr,
rounded_taxable_amt_trx_curr,
rounded_taxable_amt_tax_curr,
rounded_taxable_amt_fun_curr,
rec_tax_amt_trx_curr, rec_tax_amt_funcl_curr,
nrec_tax_amt_trx_curr, nrec_tax_amt_tax_curr,
nrec_tax_amt_funcl_curr, manual_overridden_flag,
manual_tax_line_flag, tax_line_num, precedence_1,
record_type_code, creation_date, created_by,
last_update_date, last_updated_by, taxable_basis,
rounded_tax_amt_tax_curr, rec_tax_amt_tax_curr)
SELECT jai_tax_lines_s.NEXTVAL tax_line_id, det_factor_id,
org_id, organization_id, location_id,
v_appl_id application_id, entity_code,
event_class_code, event_type_code,
tax_event_class_code, tax_event_type_code,
1 ledger_id, trx_id, trx_line_id, trx_level_type,
trx_number, trx_line_number, trx_type, trx_date,
legal_entity_id,
–‘n’ frogen_flag,
party_type, party_id, party_site_id,
trx_uom_code, item_id, unit_price, line_amt,
trx_line_quantity, trx_currency_code,
trx_currency_code tax_currency_code,
trx_currency_code functional_currency_code,
v_regime_id tax_regime_id,
v_regime_code tax_regime_code,
v_regime_type tax_regime_type,
(SELECT party_reg_id
FROM jai_party_reg_lines
WHERE regime_id = v_regime_id
AND party_reg_id IN (
SELECT party_reg_id
FROM jai_party_regs
WHERE party_id = organization_id
AND party_type_code = ‘IO’))
first_party_reg_id,
v_regime_code first_party_primary_reg_name,
(SELECT registration_number
FROM jai_party_reg_lines
WHERE regime_id = v_regime_id
AND party_reg_id IN (
SELECT party_reg_id
FROM jai_party_regs
WHERE party_id = organization_id
AND party_type_code = ‘IO’))
first_party_primary_reg_num,
‘N’ reporting_only_flag,
(SELECT tax_authority_id
FROM jai_party_reg_lines
WHERE regime_id = v_regime_id
AND party_reg_id IN (
SELECT party_reg_id
FROM jai_party_regs
WHERE party_id = organization_id
AND party_type_code = ‘IO’))
tax_authority_id,
(SELECT tax_authority_site_id
FROM jai_party_reg_lines
WHERE regime_id =
v_regime_id
AND party_reg_id IN (
SELECT party_reg_id
FROM jai_party_regs
WHERE party_id = organization_id
AND party_type_code = ‘IO’))
tax_authority_site_id,
jtc.tax_type_id tax_type_id, ‘N’ abatement_flag,
‘N’ update_vendor_on_transaction, ‘Y’ offset_flag,
— ‘N’ recoverable_flag,
(SELECT attribute_value
FROM jai_item_templ_hdr_v jitmh,
jai_item_templ_dtls_v jitml
WHERE 1 = 1
AND jitmh.template_hdr_id = jitml.template_hdr_id
AND jitmh.inventory_item_id = jts.item_id
AND jitmh.organization_id = jts.organization_id
AND attribute_code = ‘RECOVERABLE’
AND trx_id = i.requisition_header_id
AND trx_line_id = i.requisition_line_id)
recoverable_flag,
— changed by vijay to include recoverable flag in tax
‘N’ self_assessed_flag, ‘DELIVERY’ tax_point_basis,
jtc.tax_rate_id tax_rate_id,
(SELECT tax_rate_code
FROM jai_tax_rates
WHERE tax_rate_id = jtc.tax_rate_id)
tax_rate_code,
(SELECT tax_rate_type
FROM jai_tax_rates
WHERE tax_rate_id = jtc.tax_rate_id)
tax_rate_type,
(SELECT recovery_percentage
FROM jai_tax_rates
WHERE tax_rate_id =
jtc.tax_rate_id)
recovery_percentage,
‘N’ inclusive_flag,
(SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id =
jtc.tax_rate_id)
tax_rate_percentage,
(SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id =
jtc.tax_rate_id)
actual_tax_rate,
‘ND’ tax_rounded_to, 2 tax_rounding_factor,
2 taxable_rounding_factor,
line_amt unround_taxable_amt_trx_curr,
line_amt unround_taxable_amt_tax_curr,
line_amt unround_taxable_amt_fun_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 unround_tax_amt_trx_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 unround_tax_amt_fun_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rounded_tax_amt_trx_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rounded_tax_amt_fun_curr,
line_amt rounded_taxable_amt_trx_curr,
line_amt rounded_taxable_amt_tax_curr,
line_amt rounded_taxable_amt_fun_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rec_tax_amt_trx_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rec_tax_amt_funcl_curr,
0 nrec_tax_amt_trx_curr, 0 nrec_tax_amt_tax_curr,
0 nrec_tax_amt_funcl_curr,
‘N’ manual_overridden_flag,
‘N’ manual_tax_line_flag,
jtc.line_number tax_line_num, 0 precedence_1,
(SELECT record_type_code
FROM jai_tax_rates
WHERE tax_rate_id =
jtc.tax_rate_id)
record_type_code,
SYSDATE creation_date, 0 created_by,
SYSDATE last_update_date, 0 last_updated_by,
0 taxable_basis,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rounded_tax_amt_tax_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rec_tax_amt_tax_curr
FROM jai_tax_det_factors jts, jai_tax_category_lines jtc
WHERE 1 = 1
AND jtc.tax_category_id(+) = jts.override_tax_category_id
AND trx_id = i.requisition_header_id
AND trx_line_id = i.requisition_line_id;
DBMS_OUTPUT.put_line ( ‘rowcount ‘
|| SQL%ROWCOUNT
|| CHR (13)
|| ‘009’
);
UPDATE xx_iriso_stg_tab rec
SET err_msg = err_msg || ‘ Tax Line Attached.’
WHERE req_header_id = i.requisition_header_id
AND inventory_item_id = i.item_id
— AND batch_run_id = p_batch_run_id
AND status = ‘PR’;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
UPDATE xx_iriso_stg_tab rec
SET err_msg =
err_msg
|| ‘Tax Category is not atached for this item’
WHERE req_header_id = i.requisition_header_id
AND inventory_item_id = i.item_id
— AND batch_run_id = p_batch_run_id
AND status = ‘PR’;
DBMS_OUTPUT.put_line
( ‘**** Insert jai_tax_lines EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
END;
ELSIF v_source_gst = v_dest_gst
THEN
DBMS_OUTPUT.put_line ( ‘ if GST condition : true ‘
|| CHR (13)
|| ‘0007’
);
BEGIN
SELECT tax_category_id
INTO v_tax_category_id
FROM jai_tax_categories
WHERE tax_category_name = ‘Within State GST 0%’
AND org_id = :org_id;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( ‘**** Zero Tax Category EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
v_tax_category_id := NULL;
END;
DBMS_OUTPUT.put_line ( ‘ i.item_id : ‘
|| i.item_id
|| CHR (13)
|| ‘i.source_organization_id : ‘
|| i.source_organization_id
|| CHR (13)
|| ‘Item No : ‘
|| v_item_no
|| CHR (13)
|| ‘ v_tax_category_id : ‘
|| v_tax_category_id
|| CHR (13)
|| ‘007’
);
IF v_tax_category_id IS NOT NULL
THEN
BEGIN
SELECT item_type, segment1
INTO v_item_type, v_item_no
FROM mtl_system_items_b
WHERE inventory_item_id = i.item_id
AND organization_id = i.destination_organization_id;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( ‘**** item_type EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
END;
DBMS_OUTPUT.put_line ( ‘ i.item_id : ‘
|| i.item_id
|| CHR (13)
|| ‘ i.destination_organization_id : ‘
|| i.destination_organization_id
|| ‘ Item type : ‘
|| v_item_type
|| CHR (13)
|| ‘007’
);
BEGIN
SELECT attribute_value
INTO v_rec_val
FROM jai_item_templ_hdr_v jih, jai_item_templ_dtls_v jid
WHERE 1 = 1
AND jih.template_hdr_id = jid.template_hdr_id
AND jid.attribute_code = ‘RECOVERABLE’
AND jih.inventory_item_id = i.item_id
AND jih.organization_id = i.destination_organization_id;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( ‘**** attribute_value EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
END;
DBMS_OUTPUT.put_line ( ‘ i.item_id : ‘
|| i.item_id
|| CHR (13)
|| ‘ i.destination_organization_id : ‘
|| i.destination_organization_id
|| CHR (13)
|| ‘v_rec_val : ‘
|| v_rec_val
|| CHR (13)
|| ‘008’
);
IF v_item_type IS NOT NULL
THEN
BEGIN
UPDATE jai_tax_det_factors
SET override_tax_category_id = v_tax_category_id,
user_modified_flag = ‘Y’,
intended_use =
DECODE (v_rec_val,
‘Y’, ‘RECOVERABLE’,
‘NON RECOVERABLE’
)
–sac_code_id = v_template_id
WHERE trx_id = i.requisition_header_id
AND trx_line_id = i.requisition_line_id;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( ‘**** UPDATE jai_tax_det_factors EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
END;
END IF;
END IF;
BEGIN
INSERT INTO jai_tax_lines
(tax_line_id, det_factor_id, org_id,
organization_id, location_id, application_id,
entity_code, event_class_code, event_type_code,
tax_event_class_code, tax_event_type_code,
ledger_id, trx_id, trx_line_id, trx_level_type,
trx_number, trx_line_number, trx_type, trx_date,
legal_entity_id,
–frogen_flag,
party_type, party_id,
party_site_id, trx_uom, item_id, unit_price,
line_amt, trx_line_quantity, trx_currency_code,
tax_currency_code, functional_currency_code,
tax_regime_id, tax_regime_code, tax_regime_type,
first_party_reg_id, first_party_primary_reg_name,
first_party_primary_reg_num, reporting_only_flag,
tax_authority_id, tax_authority_site_id,
tax_type_id, abatement_flag,
update_vendor_on_transaction, offset_flag,
recoverable_flag, self_assessed_flag,
tax_point_basis, tax_rate_id, tax_rate_code,
tax_rate_type, recovery_percentage,
inclusive_flag, tax_rate_percentage,
actual_tax_rate, tax_rounded_to,
tax_rounding_factor, taxable_rounding_factor,
unround_taxable_amt_trx_curr,
unround_taxable_amt_tax_curr,
unround_taxable_amt_fun_curr,
unround_tax_amt_trx_curr,
unround_tax_amt_fun_curr,
rounded_tax_amt_trx_curr,
rounded_tax_amt_fun_curr,
rounded_taxable_amt_trx_curr,
rounded_taxable_amt_tax_curr,
rounded_taxable_amt_fun_curr,
rec_tax_amt_trx_curr, rec_tax_amt_funcl_curr,
nrec_tax_amt_trx_curr, nrec_tax_amt_tax_curr,
nrec_tax_amt_funcl_curr, manual_overridden_flag,
manual_tax_line_flag, tax_line_num, precedence_1,
record_type_code, creation_date, created_by,
last_update_date, last_updated_by, taxable_basis,
rounded_tax_amt_tax_curr, rec_tax_amt_tax_curr)
SELECT jai_tax_lines_s.NEXTVAL tax_line_id, det_factor_id,
org_id, organization_id, location_id,
v_appl_id application_id, entity_code,
event_class_code, event_type_code,
tax_event_class_code, tax_event_type_code,
1 ledger_id, trx_id, trx_line_id, trx_level_type,
trx_number, trx_line_number, trx_type, trx_date,
legal_entity_id,
–‘n’ frogen_flag,
party_type, party_id, party_site_id,
trx_uom_code, item_id, unit_price, line_amt,
trx_line_quantity, trx_currency_code,
trx_currency_code tax_currency_code,
trx_currency_code functional_currency_code,
v_regime_id tax_regime_id,
v_regime_code tax_regime_code,
v_regime_type tax_regime_type,
(SELECT party_reg_id
FROM jai_party_reg_lines
WHERE regime_id = v_regime_id
AND party_reg_id IN (
SELECT party_reg_id
FROM jai_party_regs
WHERE party_id = organization_id
AND party_type_code = ‘IO’))
first_party_reg_id,
v_regime_code first_party_primary_reg_name,
(SELECT registration_number
FROM jai_party_reg_lines
WHERE regime_id = v_regime_id
AND party_reg_id IN (
SELECT party_reg_id
FROM jai_party_regs
WHERE party_id = organization_id
AND party_type_code = ‘IO’))
first_party_primary_reg_num,
‘N’ reporting_only_flag,
(SELECT tax_authority_id
FROM jai_party_reg_lines
WHERE regime_id = v_regime_id
AND party_reg_id IN (
SELECT party_reg_id
FROM jai_party_regs
WHERE party_id = organization_id
AND party_type_code = ‘IO’))
tax_authority_id,
(SELECT tax_authority_site_id
FROM jai_party_reg_lines
WHERE regime_id =
v_regime_id
AND party_reg_id IN (
SELECT party_reg_id
FROM jai_party_regs
WHERE party_id = organization_id
AND party_type_code = ‘IO’))
tax_authority_site_id,
jtc.tax_type_id tax_type_id, ‘N’ abatement_flag,
‘N’ update_vendor_on_transaction, ‘Y’ offset_flag,
— ‘N’ recoverable_flag,
(SELECT attribute_value
FROM jai_item_templ_hdr_v jitmh,
jai_item_templ_dtls_v jitml
WHERE 1 = 1
AND jitmh.template_hdr_id = jitml.template_hdr_id
AND jitmh.inventory_item_id = jts.item_id
AND jitmh.organization_id = jts.organization_id
AND attribute_code = ‘RECOVERABLE’
AND trx_id = i.requisition_header_id
AND trx_line_id = i.requisition_line_id)
recoverable_flag,
— changed by vijay to include recoverable flag in tax
‘N’ self_assessed_flag, ‘DELIVERY’ tax_point_basis,
jtc.tax_rate_id tax_rate_id,
(SELECT tax_rate_code
FROM jai_tax_rates
WHERE tax_rate_id = jtc.tax_rate_id)
tax_rate_code,
(SELECT tax_rate_type
FROM jai_tax_rates
WHERE tax_rate_id = jtc.tax_rate_id)
tax_rate_type,
(SELECT recovery_percentage
FROM jai_tax_rates
WHERE tax_rate_id =
jtc.tax_rate_id)
recovery_percentage,
‘N’ inclusive_flag,
(SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id =
jtc.tax_rate_id)
tax_rate_percentage,
(SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id =
jtc.tax_rate_id)
actual_tax_rate,
‘ND’ tax_rounded_to, 2 tax_rounding_factor,
2 taxable_rounding_factor,
line_amt unround_taxable_amt_trx_curr,
line_amt unround_taxable_amt_tax_curr,
line_amt unround_taxable_amt_fun_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 unround_tax_amt_trx_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 unround_tax_amt_fun_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rounded_tax_amt_trx_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rounded_tax_amt_fun_curr,
line_amt rounded_taxable_amt_trx_curr,
line_amt rounded_taxable_amt_tax_curr,
line_amt rounded_taxable_amt_fun_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rec_tax_amt_trx_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rec_tax_amt_funcl_curr,
0 nrec_tax_amt_trx_curr, 0 nrec_tax_amt_tax_curr,
0 nrec_tax_amt_funcl_curr,
‘N’ manual_overridden_flag,
‘N’ manual_tax_line_flag,
jtc.line_number tax_line_num, 0 precedence_1,
(SELECT record_type_code
FROM jai_tax_rates
WHERE tax_rate_id =
jtc.tax_rate_id)
record_type_code,
SYSDATE creation_date, 0 created_by,
SYSDATE last_update_date, 0 last_updated_by,
0 taxable_basis,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rounded_tax_amt_tax_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rec_tax_amt_tax_curr
FROM jai_tax_det_factors jts, jai_tax_category_lines jtc
WHERE 1 = 1
AND jtc.tax_category_id(+) = jts.override_tax_category_id
AND trx_id = i.requisition_header_id
AND trx_line_id = i.requisition_line_id;
DBMS_OUTPUT.put_line ( ‘rowcount ‘
|| SQL%ROWCOUNT
|| CHR (13)
|| ‘009’
);
UPDATE xx_iriso_stg_tab rec
SET err_msg = err_msg || ‘ Tax Line Attached.’
WHERE req_header_id = i.requisition_header_id
AND inventory_item_id = i.item_id
— AND batch_run_id = p_batch_run_id
AND status = ‘PR’;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
UPDATE xx_iriso_stg_tab rec
SET err_msg =
err_msg
|| ‘Tax Category is not atached for this item’
WHERE req_header_id = i.requisition_header_id
AND inventory_item_id = i.item_id
— AND batch_run_id = p_batch_run_id
AND status = ‘PR’;
DBMS_OUTPUT.put_line
( ‘**** Insert jai_tax_lines EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
END;
/* ELSE
v_return_msg := ‘No Record inserted.’; */
END IF;
END LOOP;
DBMS_OUTPUT.put_line (‘v_return_msg ‘ || v_return_msg);
/*
IF TRIM (v_return_msg) IS NULL
THEN
v_return_msg := ‘Tax Line Attached. ‘;
END IF;
*/
RETURN v_return_msg;
END;
PROCEDURE import_lot (p_batch_run_id IN NUMBER, p_error_code OUT NUMBER)
IS
ln_atr NUMBER := 0;
ln_onhand NUMBER := 0;
ln_batch_run_id NUMBER := NULL;
ln_sum_avr_qty NUMBER := NULL;
ln_cnt_lot NUMBER := 0;
i NUMBER := 0;
ln_run_avr_qty NUMBER := NULL;
ln_tt_avr_qty NUMBER := NULL;
ln_lot_qty NUMBER := NULL;
ln_qty NUMBER := NULL;
ln_cnt NUMBER := 0;
ln_sign NUMBER := 0;
l_api_return_status VARCHAR2 (1);
l_qty_oh NUMBER;
l_qty_res_oh NUMBER;
l_qty_res NUMBER;
l_qty_sug NUMBER;
l_qty_att NUMBER;
l_qty_atr NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2 (250);
x_return_msg VARCHAR2 (4000);
l_msg_index_out NUMBER;
ln_qty1 NUMBER := 0;
ln_qty2 NUMBER := 0;
ln_sign_qty NUMBER := 0;
lc_last_lot_number VARCHAR2 (250);
ln_last_onhand NUMBER := NULL;
ln_last_avr NUMBER := NULL;
ln_lot_cnt NUMBER := 0;
ln_lot_max_seq_id NUMBER := NULL;
ln_req_header_id NUMBER := NULL;
CURSOR cur_batch
IS
SELECT msi.segment1, msi.inventory_item_id, msi.organization_id,
xist.uom_code, NVL (xist.expiry_norm, 30) expiry_norm,
xist.rec_id, xist.quantity, xist.batch_run_id,
xist.source_org_code, xist.destination_org_code,
xist.preparer, xist.ou_name, xist.creation_date,
xist.filename, xist.status, xist.category_id,
xist.source_org_id, xist.destination_org_id,
xist.delievery_location_id, xist.preparer_id, xist.org_id,
xist.unit_price, xist.onhandqty, xist.avr,
xist.productgroup, xist.item_code
FROM xx_iriso_stg_tab xist,
mtl_system_items msi,
org_organization_definitions ood
WHERE xist.item_code = msi.segment1
AND msi.organization_id = ood.organization_id
AND xist.source_org_code = ood.organization_code
AND xist.batch_run_id = p_batch_run_id
AND NVL (xist.status, ‘O’) = ‘V’
AND NOT EXISTS (SELECT ‘x’
FROM xx_iriso_lot_tbl irlt
WHERE irlt.batch_run_id = xist.batch_run_id)
ORDER BY xist.rec_id, xist.source_org_code, xist.item_code;
— AND req_header_id IS NULL;
CURSOR cur_lot (
p_item_id IN NUMBER,
p_org_id IN NUMBER,
p_exp_norm IN NUMBER
)
IS
SELECT mln.inventory_item_id, mln.organization_id, mln.lot_number,
mln.origination_date, mln.expiration_date,
mmt.subinventory_code
FROM mtl_transaction_lot_numbers mtln,
mtl_material_transactions mmt,
mtl_lot_numbers mln
WHERE 1 = 1
AND mtln.transaction_id = mmt.transaction_id
AND mtln.lot_number = mln.lot_number
AND mtln.inventory_item_id = mln.inventory_item_id
AND mtln.organization_id = mln.organization_id
AND mtln.inventory_item_id = p_item_id
AND mmt.subinventory_code IN (‘FGI’, ‘ORT’)
— added ORT for ORTHO orgs dated 23-Jul-2020
AND mtln.organization_id = p_org_id
AND TRUNC (NVL (expiration_date, SYSDATE + 365)) >
TRUNC (SYSDATE + p_exp_norm)
AND mln.lot_number IS NOT NULL
AND mln.origination_date IS NOT NULL
AND NVL (mln.expiration_date, SYSDATE) IS NOT NULL
AND xx_get_lot_avr (mln.lot_number, mln.organization_id) > 0
GROUP BY mln.inventory_item_id,
mln.organization_id,
mln.lot_number,
mln.origination_date,
mln.expiration_date,
mmt.subinventory_code
ORDER BY mln.expiration_date ASC;
–mln.origination_date ASC; logic changed FIFO as per expiration date on 22-Jul-20
ln_consumed_qty NUMBER := 0;
ln_bal_qty NUMBER := 0;
ln_avr_prev NUMBER := 0;
ln_reqd_qty NUMBER := 0;
ln_count NUMBER := 0;
ln_rec_id NUMBER := 0;
ln_batch_rec_id NUMBER := 0;
lv_conversion_factor NUMBER := 0;
BEGIN
print_log (‘ Quantity Allocation started’);
DBMS_OUTPUT.put_line (‘ Quantity Allocation started’);
–ln_batch_run_id := p_batch_run_id;
BEGIN
— apps.inv_quantity_tree_grp.clear_quantity_cache;
FOR batch_rec IN cur_batch
LOOP
ln_bal_qty := batch_rec.quantity;
ln_consumed_qty := 0;
ln_reqd_qty := batch_rec.quantity;
i := 0;
DBMS_OUTPUT.put_line (‘ ‘);
DBMS_OUTPUT.put_line ( ‘Processing for batch_rec_id ‘
|| batch_rec.rec_id
);
DBMS_OUTPUT.put_line
(‘———————————————————————‘
);
FOR lot_rec IN cur_lot (batch_rec.inventory_item_id,
batch_rec.organization_id,
batch_rec.expiry_norm
)
LOOP
ln_atr := 0;
ln_onhand := 0;
ln_consumed_qty := 0;
ln_avr_prev := 0;
ln_count := 0;
lv_conversion_factor := 0;
BEGIN
lv_conversion_factor :=
get_conversion_factor
(p_item_code => batch_rec.item_code,
p_uom => batch_rec.uom_code
);
— Calculate already Consumed AVR for the same Lot in the Same Batch
— SELECT SUM (t.lot_transfer_qty), COUNT (1)
SELECT SUM
( t.lot_transfer_qty
* get_conversion_factor
(p_item_code => batch_rec.item_code,
p_uom => t.uom
)
) tot_pri_qty,
COUNT (1)
INTO ln_avr_prev,
ln_count
FROM xx_iriso_lot_tbl t
WHERE batch_run_id = batch_rec.batch_run_id
AND rec_id != batch_rec.rec_id
AND lot_number = lot_rec.lot_number
AND item_code = batch_rec.segment1;
DBMS_OUTPUT.put_line (‘ln_avr_prev := ‘ || ln_avr_prev);
DBMS_OUTPUT.put_line ( ‘LV_CONVERSION_FACTOR:= ‘
|| lv_conversion_factor
);
ln_avr_prev := FLOOR (ln_avr_prev / lv_conversion_factor);
DBMS_OUTPUT.put_line ( ‘after convertion ln_avr_prev:= ‘
|| ln_avr_prev
);
DBMS_OUTPUT.put_line ( ‘lot_rec.Lot_number = ‘
|| lot_rec.lot_number
|| ‘ batch_rec.batch_run_id =’
|| batch_rec.batch_run_id
|| ‘ batch_rec.rec_id = ‘
|| batch_rec.rec_id
|| ‘ batch_rec.segment1 =’
|| batch_rec.segment1
|| ‘ ln_avr_prev = ‘
|| ln_avr_prev
|| ‘ ln count = ‘
|| ln_count
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
ln_avr_prev := 0;
END;
— ln_avr_prev := 0;
— ln_onhand := xx_get_lot_onhand(lot_rec.Lot_number,lot_rec.organization_id);
ln_atr :=
xx_get_lot_avr (lot_rec.lot_number, lot_rec.organization_id);
DBMS_OUTPUT.put_line (‘before convertion ln_atr:= ‘ || ln_atr);
IF lv_conversion_factor > 0
THEN
ln_atr := FLOOR (ln_atr / lv_conversion_factor);
END IF;
DBMS_OUTPUT.put_line (‘after convertion ln_atr:= ‘ || ln_atr);
DBMS_OUTPUT.put_line ( ‘lot_rec.Lot_number’
|| ‘ ‘
|| lot_rec.lot_number
|| ‘ ln_atr-> ‘
|| ln_atr
|| ‘ ln_avr_prev1–> ‘
|| ln_avr_prev
|| ‘ ln_reqd_qty–> ‘
|| ln_reqd_qty
|| ‘ ln_consumed_qty–> ‘
|| ln_consumed_qty
);
IF ln_avr_prev > 0
THEN
— Reduce the AVR to the extent of already consumed Qty.
ln_atr := ln_atr – ln_avr_prev;
END IF;
—-dbms_output.put_line(‘ln_reqd_qty–> ‘||ln_reqd_qty);
IF ln_atr > 0
THEN
IF (ln_reqd_qty <= ln_atr)
THEN
ln_bal_qty := 0;
ln_consumed_qty := ln_reqd_qty;
i := i + 1;
ELSE
ln_consumed_qty := ln_atr;
ln_bal_qty := ln_reqd_qty – ln_atr;
i := i + 1;
END IF;
DBMS_OUTPUT.put_line (‘ln_bal_qty:= ‘ || ln_bal_qty);
DBMS_OUTPUT.put_line ( ‘ln_reqd_qty1–> ‘
|| ln_reqd_qty
|| ‘ ‘
|| ‘ ln_atr–> ‘
|| ln_atr
|| ‘ ln_consumed_qty1–> ‘
|| ln_consumed_qty
);
— dbms_output.put_line();
DBMS_OUTPUT.put_line ( ‘Before Inserting into lot:’
|| lot_rec.lot_number
);
/*—————————ADDED BY RAJAN ON 16-JUL-2020 FOR LOT SPLIT UPDATE STARTS———————-*/
ln_batch_rec_id := batch_rec.rec_id;
ln_onhand :=
xx_get_lot_onhand (lot_rec.lot_number,
lot_rec.organization_id
);
ln_onhand := FLOOR (ln_onhand / lv_conversion_factor);
IF i > 1
THEN
BEGIN
SELECT MAX (rec_id)
INTO ln_rec_id
FROM xx_iriso_stg_tab
WHERE batch_run_id = p_batch_run_id;
EXCEPTION
WHEN OTHERS
THEN
ln_rec_id := NULL;
END;
ln_batch_rec_id := ln_rec_id + 1;
IF NVL (ln_rec_id, 0) > 0
THEN
INSERT INTO xx_iriso_stg_tab
(item_code,
source_org_code,
destination_org_code, quantity,
uom_code, preparer,
ou_name,
expiry_norm, rec_id,
batch_run_id,
creation_date,
filename, status,
inventory_item_id,
category_id,
source_org_id,
destination_org_id,
delievery_location_id,
preparer_id,
org_id, unit_price,
onhandqty, avr, productgroup
)
VALUES (batch_rec.segment1,
batch_rec.source_org_code,
batch_rec.destination_org_code, NULL,
batch_rec.uom_code, batch_rec.preparer,
batch_rec.ou_name,
batch_rec.expiry_norm, ln_batch_rec_id,
batch_rec.batch_run_id,
batch_rec.creation_date,
batch_rec.filename, batch_rec.status,
batch_rec.inventory_item_id,
batch_rec.category_id,
batch_rec.source_org_id,
batch_rec.destination_org_id,
batch_rec.delievery_location_id,
batch_rec.preparer_id,
batch_rec.org_id, batch_rec.unit_price,
NULL, NULL, batch_rec.productgroup
);
END IF;
END IF;
/*—————————ADDED BY RAJAN ON 16-JUL-2020 FOR LOT SPLIT UPDATE ENDS———————-*/
INSERT INTO xx_iriso_lot_tbl
(batch_run_id, lot_seq_id, rec_id,
item_code,
item_id,
organization_id, lot_number,
onhand, avr, lot_transfer_qty, status,
err_msg, uom
)
VALUES (batch_rec.batch_run_id, i, ln_batch_rec_id,
batch_rec.segment1,
batch_rec.inventory_item_id,
batch_rec.organization_id, lot_rec.lot_number,
ln_onhand, ln_atr, ln_consumed_qty, ‘I’,
NULL, batch_rec.uom_code
);
— ELSE
— NO QTY exists for this lot
— ln_bal_qty := ln_reqd_qty;
— i := 0;
— EXIT;
END IF;
IF ln_bal_qty = 0
THEN
EXIT;
ELSE
ln_reqd_qty := ln_bal_qty;
END IF;
END LOOP; — Lot
——————————-
— Insert any Shortage Data —
——————————-
DBMS_OUTPUT.put_line (‘ln_bal_qty := ‘ || ln_bal_qty || ‘ i :=’
|| i
);
IF ln_bal_qty > 0 AND i > 0
THEN
DBMS_OUTPUT.put_line ( ‘batch_rec.batch_run_id := ‘
|| batch_rec.batch_run_id
|| ‘batch_rec.rec_id := ‘
|| batch_rec.rec_id
|| ‘batch_rec.segment1 := ‘
|| batch_rec.segment1
|| ‘lot_seq_id := ‘
|| i
);
UPDATE xx_iriso_lot_tbl
SET status = ‘I’,
err_msg = ‘Insufficient Qty =’ || ln_bal_qty
WHERE batch_run_id = batch_rec.batch_run_id
AND rec_id = ln_batch_rec_id –batch_rec.rec_id
AND item_code = batch_rec.segment1
AND lot_seq_id = i;
ELSIF ln_bal_qty > 0 AND i = 0
THEN
INSERT INTO xx_iriso_lot_tbl
(batch_run_id, lot_seq_id, rec_id,
item_code, item_id,
organization_id, lot_number, onhand, avr,
lot_transfer_qty, status,
err_msg,
uom
)
VALUES (batch_rec.batch_run_id, i, ln_batch_rec_id,
–batch_rec.rec_id,
batch_rec.segment1, batch_rec.inventory_item_id,
batch_rec.organization_id, NULL, 0, 0,
0, ‘E’,
‘ NO Lots available. ln_bal_qty = ‘ || ln_bal_qty,
batch_rec.uom_code
);
/* added by rajan for stoping process if No lot available */
UPDATE xx_iriso_stg_tab
SET status = ‘E’,
err_msg = ‘No Lots available for the given Expiry Norm.’
WHERE batch_run_id = batch_rec.batch_run_id
AND rec_id = batch_rec.rec_id;
DELETE FROM xx_iriso_lot_tbl
WHERE batch_run_id = batch_rec.batch_run_id;
DELETE FROM xx_iriso_stg_tab
WHERE batch_run_id = batch_rec.batch_run_id
AND quantity IS NULL;
p_error_code := 1;
END IF;
END LOOP; — Batch
END;
UPDATE xx_iriso_stg_tab a
SET (quantity, avr, onhandqty) =
(SELECT lot_transfer_qty, avr, onhand
FROM xx_iriso_lot_tbl b
WHERE b.batch_run_id = a.batch_run_id
AND b.rec_id = a.rec_id)
WHERE batch_run_id = p_batch_run_id
AND EXISTS (
SELECT ‘x’
FROM xx_iriso_lot_tbl c
WHERE c.batch_run_id = a.batch_run_id
AND c.rec_id = a.rec_id);
COMMIT;
print_log (‘ Quantity Allocation Ended’);
END;
PROCEDURE iriso_lot_reservation (p_batch_run_id IN NUMBER)
IS
l_orgn_id NUMBER := :org_id;
ld_sysdate DATE := SYSDATE;
lv_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
ln_msg_count NUMBER;
lv_msg_data VARCHAR2 (3000);
lr_orig_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
lr_orig_serial_number inv_reservation_global.serial_number_tbl_type;
x_serial_number inv_reservation_global.serial_number_tbl_type;
ln_msg_index NUMBER;
l_init_msg_list VARCHAR2 (2) := fnd_api.g_true;
x_quantity_reserved NUMBER := 0;
x_sec_qty_reserved NUMBER := 0;
x_reservation_id NUMBER := 0;
x_return_status VARCHAR2 (2);
l_error_message VARCHAR2 (4000);
x_msg_count NUMBER := 0;
x_msg_data VARCHAR2 (250);
l_msg_index_out NUMBER;
l_user_id NUMBER;
l_resp_id NUMBER;
l_resp_appl_id NUMBER;
l_line_id NUMBER;
l_header_id NUMBER;
l_org_id NUMBER;
l_item_id NUMBER;
ln_line_id NUMBER;
l_resv_qty NUMBER;
l_uom oe_order_lines_all.order_quantity_uom%TYPE;
l_req_date DATE;
l_order_number oe_order_headers_all.order_number%TYPE;
l_demand_source_header_id NUMBER;
l_subinv_code mtl_onhand_quantities.subinventory_code%TYPE;
l_trans_qty NUMBER;
l_reserved_qty NUMBER;
l_avail_qty NUMBER;
l_lot_number VARCHAR2 (50);
l_serial_number inv_reservation_global.serial_number_tbl_type;
l_exception VARCHAR2 (4000);
l_reservation_id NUMBER;
l_rowid VARCHAR2 (100);
l_ouom VARCHAR2 (50);
l_puom VARCHAR2 (50);
l_sub_code VARCHAR2 (100);
l_org_code VARCHAR2 (100);
l_loc_id NUMBER;
l_link NUMBER;
l_sec_qty NUMBER;
l_pqty NUMBER;
l_err_flg VARCHAR2 (1) := ‘N’;
CURSOR c1
IS
SELECT xilt.batch_run_id, xilt.rec_id, xilt.lot_seq_id,
xistb.order_number, xistb.so_header_id, xilt.item_id,
xilt.organization_id, xilt.lot_number, xistb.uom_code uom,
xilt.lot_transfer_qty, xistb.so_line_id
FROM xx_iriso_lot_tbl xilt, xx_iriso_stg_tab xistb
WHERE xilt.item_code = xistb.item_code
AND xilt.batch_run_id = xistb.batch_run_id
AND xistb.rec_id = xilt.rec_id
AND xistb.status = ‘SO’
AND xilt.status = ‘I’
AND xistb.order_number IS NOT NULL
AND xilt.batch_run_id = p_batch_run_id
AND iriso_status IN (‘ISO’, ‘CONFIRM’)
ORDER BY xilt.batch_run_id,
xilt.item_code,
xilt.rec_id,
xilt.lot_seq_id; –1029
BEGIN
DBMS_OUTPUT.put_line (‘In’);
FOR c1_rec IN c1
LOOP
DBMS_OUTPUT.put_line (‘Started’);
l_lot_number := c1_rec.lot_number;
l_ouom := c1_rec.uom;
l_resv_qty := c1_rec.lot_transfer_qty;
l_err_flg := ‘N’;
BEGIN
SELECT ool.line_id, ool.header_id, ool.ship_from_org_id,
ool.inventory_item_id, ool.order_quantity_uom,
ool.promise_date, ooh.order_number
INTO l_line_id, l_header_id, l_org_id,
l_item_id, l_uom,
l_req_date, l_order_number
FROM oe_order_headers_all ooh, oe_order_lines_all ool
WHERE ooh.header_id = ool.header_id
AND ooh.org_id = ool.org_id
AND ooh.order_number = c1_rec.order_number
AND ool.ship_from_org_id = c1_rec.organization_id
AND ooh.header_id = c1_rec.so_header_id
AND ool.inventory_item_id = c1_rec.item_id
AND ool.line_id = c1_rec.so_line_id;
EXCEPTION
WHEN OTHERS
THEN
l_exception := SQLERRM;
l_err_flg := ‘Y’;
UPDATE xx_iriso_lot_tbl
SET status = ‘E’,
err_msg =
SUBSTR ( err_msg
|| SUBSTR ( ‘ Error Retreiving SO Line – ‘
|| l_exception,
1,
2000
),
1,
4000
)
WHERE 1 = 1
AND batch_run_id = c1_rec.batch_run_id
AND rec_id = c1_rec.rec_id
AND lot_number = c1_rec.lot_number
AND lot_seq_id = c1_rec.lot_seq_id;
— AND status = ‘I’;
END;
BEGIN
SELECT sales_order_id
INTO l_demand_source_header_id
FROM apps.mtl_sales_orders
WHERE segment1 = l_order_number;
DBMS_OUTPUT.put_line (‘SO ID’);
EXCEPTION
WHEN OTHERS
THEN
l_exception := SQLERRM;
l_err_flg := ‘Y’;
UPDATE xx_iriso_lot_tbl
SET status = ‘E’,
err_msg =
err_msg
|| SUBSTR ( ‘ Error retriving SO Demand Line : ‘
|| l_exception,
1,
2000
)
WHERE batch_run_id = c1_rec.batch_run_id
AND rec_id = c1_rec.rec_id
AND lot_number = c1_rec.lot_number
AND lot_seq_id = c1_rec.lot_seq_id;
— AND status = ‘I’;
DBMS_OUTPUT.put_line (‘SO ID Err ‘ || l_exception);
END;
BEGIN
SELECT SUBSTR (organization_code, -1, 1)
INTO l_org_code
FROM org_organization_definitions
WHERE organization_id = l_org_id;
DBMS_OUTPUT.put_line (‘l_org_code ‘ || l_org_code);
END;
BEGIN
IF l_org_code = ‘9’
THEN
l_sub_code := ‘ORT’;
ELSE
l_sub_code := ‘FGI’;
END IF;
DBMS_OUTPUT.put_line (‘l_sub_code ‘ || l_sub_code);
END;
BEGIN
SELECT subinventory_code
INTO l_subinv_code
FROM mtl_onhand_quantities
WHERE inventory_item_id = l_item_id
AND organization_id = l_org_id
AND lot_number = l_lot_number
AND subinventory_code = l_sub_code — ‘FGI’
AND ROWNUM < 2;
— added since same lot received will have more than one records in onhand table
— AND ROWNUM < 2;
DBMS_OUTPUT.put_line (‘Sub.Inv’);
EXCEPTION
WHEN OTHERS
THEN
l_exception := SQLERRM;
l_err_flg := ‘Y’;
UPDATE xx_iriso_lot_tbl
SET status = ‘E’,
err_msg =
SUBSTR
( err_msg
|| SUBSTR
( ‘ Error Retreiving Sub Inventory code. ‘
|| l_exception,
1,
2000
),
1,
4000
)
WHERE batch_run_id = c1_rec.batch_run_id
AND rec_id = c1_rec.rec_id
AND lot_number = c1_rec.lot_number
AND lot_seq_id = c1_rec.lot_seq_id;
— AND status = ‘I’;
DBMS_OUTPUT.put_line (‘Sub.Inv Err ‘ || l_exception);
END;
BEGIN
SELECT fnd.user_id, fresp.responsibility_id, fresp.application_id
INTO l_user_id, l_resp_id, l_resp_appl_id
FROM fnd_user fnd,
fnd_responsibility_tl fresp,
fnd_application_vl fvl
WHERE 1 = 1
–AND fnd.user_id = fnd_global.user_id
AND fnd.user_name = ‘HARI’
AND fvl.application_id = fresp.application_id
AND fvl.application_short_name = ‘JA’
AND fresp.responsibility_name = ‘India Local Order Management’;
EXCEPTION
WHEN OTHERS
THEN
l_user_id := NULL;
l_resp_id := NULL;
l_resp_appl_id := NULL;
l_err_flg := ‘Y’;
END;
IF l_err_flg = ‘N’
THEN
fnd_global.apps_initialize (user_id => l_user_id,
resp_id => l_resp_id,
resp_appl_id => l_resp_appl_id
);
— fnd_global.apps_initialize (1318, 21623, 660);
mo_global.init (‘ONT’);
mo_global.set_policy_context (‘S’, l_orgn_id);
l_reservation_id := NULL;
l_rsv_rec.organization_id := l_org_id;
l_rsv_rec.inventory_item_id := l_item_id;
DBMS_OUTPUT.put_line (‘2–>Item_id-‘ || l_item_id);
DBMS_OUTPUT.put_line (‘1–>Organization_id-‘ || l_org_id);
DBMS_OUTPUT.put_line (‘3–>SUBINV-‘ || l_subinv_code);
l_rsv_rec.requirement_date := l_req_date;
l_rsv_rec.demand_source_type_id :=
inv_reservation_global.g_source_type_internal_ord;
l_rsv_rec.supply_source_type_id :=
inv_reservation_global.g_source_type_inv;
l_rsv_rec.primary_reservation_quantity := NULL; –l_resv_qty;
l_rsv_rec.primary_uom_code := NULL;
l_rsv_rec.subinventory_code := l_subinv_code;
l_rsv_rec.demand_source_header_id := l_demand_source_header_id;
–mtl_sales_order.sales_order_id
l_rsv_rec.demand_source_line_id := l_line_id;
–oe_order_lines_all.line_id
DBMS_OUTPUT.put_line (‘4–>line_id-‘ || l_line_id);
l_rsv_rec.demand_source_name := NULL;
l_rsv_rec.ship_ready_flag := NULL;
l_rsv_rec.reservation_uom_code := SUBSTR (l_uom, 1, 3);
DBMS_OUTPUT.put_line (‘5–>UOM-‘ || l_uom);
l_rsv_rec.reservation_quantity := l_resv_qty;
—-l_reservation_qty;
DBMS_OUTPUT.put_line (‘6–>Reservation_qty-‘ || l_resv_qty);
l_rsv_rec.lot_number := l_lot_number;
DBMS_OUTPUT.put_line (‘7–>Lot-‘ || l_lot_number);
l_rsv_rec.serial_number := NULL;
l_rsv_rec.crossdock_flag := ‘N’;
–l_rsv_rec.secondary_uom_code := ‘Box’;
–l_rsv_rec.secondary_reservation_quantity := l_sec_qty;
DBMS_OUTPUT.put_line (‘END’);
l_rsv_rec.supply_source_header_id := NULL;
l_rsv_rec.supply_source_line_id := NULL;
l_rsv_rec.supply_source_name := NULL;
l_rsv_rec.supply_source_line_detail := NULL;
l_rsv_rec.attribute15 := NULL;
l_rsv_rec.attribute14 := NULL;
l_rsv_rec.attribute13 := NULL;
l_rsv_rec.attribute12 := NULL;
l_rsv_rec.attribute11 := NULL;
l_rsv_rec.attribute10 := NULL;
l_rsv_rec.attribute9 := NULL;
l_rsv_rec.attribute8 := NULL;
l_rsv_rec.attribute7 := NULL;
l_rsv_rec.attribute6 := NULL;
l_rsv_rec.attribute5 := NULL;
l_rsv_rec.attribute4 := NULL;
l_rsv_rec.attribute3 := NULL;
l_rsv_rec.attribute2 := NULL;
l_rsv_rec.attribute1 := NULL;
l_rsv_rec.attribute_category := NULL;
l_rsv_rec.lpn_id := NULL;
l_rsv_rec.pick_slip_number := NULL;
l_rsv_rec.lot_number_id := NULL;
l_rsv_rec.locator_id := NULL;
l_rsv_rec.subinventory_id := NULL;
l_rsv_rec.revision := NULL;
l_rsv_rec.external_source_line_id := NULL;
l_rsv_rec.external_source_code := NULL;
l_rsv_rec.autodetail_group_id := NULL;
l_rsv_rec.reservation_uom_id := NULL;
l_rsv_rec.primary_uom_id := NULL;
l_rsv_rec.demand_source_delivery := NULL;
ln_msg_count := NULL;
lv_msg_data := NULL;
lv_return_status := NULL;
DBMS_OUTPUT.put_line ( ‘l_reservation_id:–>’
|| ‘ ‘
|| l_reservation_id
);
inv_reservation_pub.create_reservation
(p_api_version_number => 1.0,
p_init_msg_lst => l_init_msg_list,
p_rsv_rec => l_rsv_rec,
p_serial_number => l_serial_number,
p_partial_reservation_flag => fnd_api.g_false,
p_force_reservation_flag => fnd_api.g_false,
p_partial_rsv_exists => FALSE,
p_validation_flag => fnd_api.g_true,
x_serial_number => x_serial_number,
x_return_status => lv_return_status,
x_msg_count => ln_msg_count,
x_msg_data => lv_msg_data,
x_quantity_reserved => x_quantity_reserved,
x_reservation_id => x_reservation_id
);
IF lv_return_status != fnd_api.g_ret_sts_success
THEN
FOR i IN 1 .. ln_msg_count
LOOP
apps.fnd_msg_pub.get (p_msg_index => i,
p_encoded => fnd_api.g_false,
p_data => lv_msg_data,
p_msg_index_out => l_msg_index_out
);
IF l_error_message IS NULL
THEN
l_error_message := SUBSTR (lv_msg_data, 1, 250);
ELSE
l_error_message :=
l_error_message || ‘ /’
|| SUBSTR (lv_msg_data, 1, 250);
END IF;
END LOOP;
UPDATE xx_iriso_lot_tbl
SET status = ‘E’,
err_msg =
SUBSTR ( err_msg
|| ‘ Lot Reservation Error – ‘
|| l_error_message,
1,
4000
)
WHERE batch_run_id = c1_rec.batch_run_id
AND rec_id = c1_rec.rec_id
AND lot_number = c1_rec.lot_number
AND lot_seq_id = c1_rec.lot_seq_id
AND status = ‘I’;
l_error_message := NULL;
ELSE
UPDATE xx_iriso_lot_tbl
SET lot_resv_id = x_reservation_id,
status = ‘C’
— err_msg = NULL
WHERE batch_run_id = c1_rec.batch_run_id
AND rec_id = c1_rec.rec_id
AND lot_number = c1_rec.lot_number
AND lot_seq_id = c1_rec.lot_seq_id
AND status = ‘I’;
DBMS_OUTPUT.put_line (‘API Finished’);
DBMS_OUTPUT.put_line (‘reservation_id—-> ‘
|| x_reservation_id
);
END IF; — lv_return_status
END IF; — l_err_flg
l_error_message := NULL;
l_exception := NULL;
l_reservation_id := NULL;
l_subinv_code := NULL;
l_demand_source_header_id := NULL;
l_order_number := NULL;
l_line_id := NULL;
l_header_id := NULL;
l_org_id := NULL;
l_item_id := NULL;
l_resv_qty := NULL;
l_uom := NULL;
l_req_date := NULL;
END LOOP;
COMMIT;
— Update records which got missed out
UPDATE xx_iriso_lot_tbl
SET status = ‘E’,
err_msg =
SUBSTR ( err_msg
|| SUBSTR (‘ LOT Records Missed – ‘ || l_exception,
1,
2000
),
1,
4000
)
WHERE 1 = 1 AND batch_run_id = p_batch_run_id AND status = ‘I’;
EXCEPTION
WHEN OTHERS
THEN
l_error_message := SQLERRM;
UPDATE xx_iriso_lot_tbl
SET status = ‘E’,
err_msg =
SUBSTR ( err_msg
|| ‘ LOT RESERVATION EXCEPTION MAIN – ‘
|| SUBSTR (l_error_message, 1, 2000),
1,
4000
)
WHERE batch_run_id = p_batch_run_id AND status = ‘I’;
DBMS_OUTPUT.put_line (‘API Finished ‘ || l_error_message);
END iriso_lot_reservation;
PROCEDURE impo_pr_pro (p_batch_run_id IN VARCHAR2)
AS
l_api_name VARCHAR2 (500)
:= g_module_prefix || ‘xx_impo_pr_pro’;
l_progress VARCHAR2 (240) := ‘001’;
lc_error_message LONG := NULL;
lx_status VARCHAR2 (240) := NULL;
lx_error_message LONG := NULL;
/**/
l_requisition_header_id NUMBER := NULL;
ln_row_count NUMBER := NULL;
ln_batch_id NUMBER := NULL;
l_pr_request_id NUMBER := NULL;
l_request_id NUMBER := NULL;
l_ir_request_id NUMBER := NULL;
l_sub_request_id NUMBER := NULL;
l_so_request_id NUMBER := NULL;
l_user_id NUMBER := NULL;
l_resp_id NUMBER := NULL;
l_resp_appl_id NUMBER := NULL;
l_d_ou_id NUMBER := NULL;
ln_user_id NUMBER;
ln_resp_id NUMBER;
ln_appl_id NUMBER;
ln_org_id NUMBER := :org_id;
ls_user_id NUMBER;
ls_resp_id NUMBER;
ls_appl_id NUMBER;
ls_org_id NUMBER := :org_id;
lc_tax_status VARCHAR2 (4000) := NULL;
ln_req_hdr_ct NUMBER;
ln_pr_iface_cnt NUMBER := 0;
lc_error_msg LONG := NULL;
l_count NUMBER := 0;
ln_sts_cnt NUMBER := 0;
BEGIN
UPDATE apps.xx_processingstatus_tbl
SET status = ‘PR Request – Process Started’
WHERE id1 = p_batch_run_id AND process_name = ‘IRISO’;
print_log (l_api_name || ‘Started : ‘ || l_progress);
grouping_batch (p_batch_run_id);
l_count := 0;
BEGIN
SELECT COUNT (1)
INTO l_count
FROM xx_iriso_stg_tab st
WHERE 1 = 1
–AND NVL (status, ‘E’) = ‘I’
AND NVL (status, ‘E’) = ‘V’
AND batch_run_id = p_batch_run_id
AND req_header_id IS NULL
AND EXISTS (
SELECT ‘x’
FROM xx_iriso_lot_tbl ilt
WHERE ilt.batch_run_id = st.batch_run_id
AND ilt.rec_id = st.rec_id);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_count := 0;
END;
DBMS_OUTPUT.put_line (‘l_count—>’ || ‘ ‘ || l_count);
IF l_count > 0
THEN
SELECT po_requisition_headers_s.NEXTVAL
INTO l_requisition_header_id
FROM DUAL;
print_log ( ‘ l_requisition_header_id ‘
|| ‘->’
|| l_requisition_header_id
);
SELECT xx_iriso_s.NEXTVAL
INTO ln_batch_id
FROM DUAL;
print_log (‘lc_batch_id’ || ‘->’ || ln_batch_id);
BEGIN
SELECT user_id
INTO ln_user_id
FROM fnd_user
WHERE user_name = ‘ARUN’;
EXCEPTION
WHEN OTHERS
THEN
ln_user_id := NULL;
END;
BEGIN
SELECT responsibility_id, application_id
INTO ln_resp_id, ln_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = ‘India Local Purchasing’
AND NVL (TRUNC (end_date), TRUNC (SYSDATE)) >= TRUNC (SYSDATE);
EXCEPTION
WHEN OTHERS
THEN
ln_resp_id := NULL;
ln_appl_id := NULL;
END;
fnd_global.apps_initialize (ln_user_id, ln_resp_id, ln_appl_id);
mo_global.set_policy_context (‘S’, ln_org_id);
ln_req_hdr_ct := l_count;
DBMS_OUTPUT.put_line (‘ln_req_hdr_ct—>’ || ‘ ‘ || ln_req_hdr_ct);
IF ln_req_hdr_ct > 0
THEN
DELETE FROM po_requisitions_interface_all iface
WHERE EXISTS (
SELECT 1
FROM xx_iriso_stg_tab rec
WHERE rec.batch_run_id = p_batch_run_id
AND iface.header_attribute1 = rec.batch_id);
INSERT INTO po_requisitions_interface_all
(requisition_header_id, interface_source_code, org_id,
destination_type_code, requisition_type,
authorization_status, preparer_id, source_type_code,
source_organization_id, uom_code,
–unit_of_measure,
line_type_id,
quantity, unit_price, destination_organization_id,
deliver_to_location_id, deliver_to_requestor_id,
item_id, category_id, creation_date, created_by,
last_update_date, last_updated_by, batch_id,
need_by_date, header_attribute1, charge_account_id)
SELECT l_requisition_header_id AS requisition_header_id,
—this insert has to ne looped based on batch_id
‘IMPORT_INT’ AS interface_source_code,
rec.org_id AS org_id,
‘INVENTORY’ AS destination_type_code,
‘INTERNAL’ AS requisition_type,
–‘APPROVED’ AS authorization_status,
CASE
WHEN iriso_status = ‘IR’
THEN ‘INCOMPLETE’
ELSE ‘APPROVED’
END AS authorization_status,
rec.preparer_id AS preparer_id,
‘INVENTORY’ AS source_type_code,
rec.source_org_id AS source_organization_id,
rec.uom_code AS unit_of_measure, 1 AS line_type_id,
/*rec.quantity quantity,*/
lot_rec.lot_transfer_qty quantity,
rec.unit_price unit_price,
rec.destination_org_id AS destination_organization_id,
rec.delievery_location_id AS deliver_to_location_id,
rec.preparer_id AS deliver_to_requestor_id,
rec.inventory_item_id AS item_id, rec.category_id,
SYSDATE creation_date, fnd_global.user_id created_by,
SYSDATE last_update_date,
fnd_global.user_id last_updated_by,
–ln_batch_id batch_id,
REGEXP_REPLACE (rec.batch_id,
‘[^0-9A-Za-z]’,
”
) batch_id, — Batch Run Id
SYSDATE + 2 need_by_date, rec.batch_id,
mp.material_account
FROM xx_iriso_stg_tab rec,
(SELECT rec_id,
SUM (lot_transfer_qty) lot_transfer_qty,
batch_run_id
FROM xx_iriso_lot_tbl
GROUP BY rec_id, batch_run_id) lot_rec,
mtl_parameters mp
WHERE 1 = 1
–AND NVL (status, ‘E’) = ‘I’
AND NVL (rec.status, ‘E’) = ‘V’
AND rec.batch_run_id = p_batch_run_id
AND rec.source_org_id = mp.organization_id(+)
AND rec.batch_run_id = lot_rec.batch_run_id
AND rec.rec_id = lot_rec.rec_id
AND rec.req_header_id IS NULL
AND rec.batch_id IS NOT NULL;
ln_row_count := ln_row_count + SQL%ROWCOUNT;
print_log
(p_message => ‘No Of rows ‘
|| ln_row_count
|| ‘ Inserted into po_requisitions_interface_all’
);
COMMIT;
–END IF;
— fnd submit
print_output (p_message => ‘Calling REQIMPORT Import Program’);
submit_pr_request (p_batch_run_id => impo_pr_pro.p_batch_run_id);
— Added by Vijay
BEGIN
print_output
(p_message => ‘Calling PR- Tax Import Program started’);
lc_tax_status := NULL;
lc_error_msg := NULL;
FOR tax_rec IN
(SELECT DISTINCT req_header_id requisition_header_id
FROM xx_iriso_stg_tab rec
WHERE batch_run_id = p_batch_run_id
AND status = ‘PR’
AND req_header_id > 0)
LOOP
lc_tax_status := NULL;
lc_tax_status :=
add_gst_tax_req (tax_rec.requisition_header_id);
print_output (p_message => ‘lc_tax_status => ‘
|| lc_tax_status
);
END LOOP;
print_output
(p_message => ‘Calling PR- Tax Import Program ended’);
END;
END IF;
print_output (p_message => ‘Calling Create Internal Orders Program’);
l_request_id := 0;
SELECT COUNT (*)
INTO ln_sts_cnt
FROM xx_iriso_stg_tab rec
WHERE iriso_status IN (‘ISO’, ‘CONFIRM’)
AND batch_run_id = p_batch_run_id;
print_output (p_message => ‘ln_sts_cnt : ‘ || ln_sts_cnt);
IF ln_sts_cnt > 0
THEN
BEGIN
SELECT MAX (pr_req_id)
INTO l_request_id
FROM xx_iriso_stg_tab rec
WHERE status = ‘PR’
AND batch_run_id = p_batch_run_id
AND pr_req_id > 0;
EXCEPTION
WHEN OTHERS
THEN
l_request_id := 0;
END;
IF l_request_id > 0
THEN
l_ir_request_id :=
apps.fnd_request.submit_request
(application => ‘PO’,
program => ‘POCISO’,
description => ‘Create Internal Orders’,
start_time => SYSDATE
);
COMMIT;
print_output (p_message => ‘Calling POCISO Request Id : ‘
|| l_ir_request_id
);
l_request_id := l_ir_request_id;
IF l_ir_request_id > 0
THEN
wait_request (p_request_id => l_ir_request_id,
p_program_name => ‘POCISO’,
x_status => lx_status,
x_error_message => lx_error_message
);
END IF;
print_output (p_message => ‘Wait POCISO Request Status : ‘
|| lx_status
|| ‘ Error Message : ‘
|| lx_error_message
);
END IF;
print_output (p_message => ‘Calling Import Sales Order Program’);
— l_request_id
so_submit_request (p_batch_run_id => impo_pr_pro.p_batch_run_id);
BEGIN
iriso_lot_reservation (p_batch_run_id);
END;
END IF;
COMMIT;
END IF; — l_count
print_log (l_api_name || ‘Ended : ‘ || l_progress);
EXCEPTION
WHEN OTHERS
THEN
lc_error_message :=
‘EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line;
print_log ( ‘**** EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
END;
PROCEDURE submit_pr_request (p_batch_run_id IN NUMBER)
AS
CURSOR cur_priface
IS
SELECT rec.batch_run_id, rec.batch_id, iriso_status,
DECODE (iriso_status, ‘IR’, ‘N’, ‘Y’) approval_flag,
prh.batch_id pr_batch_id
FROM xx_iriso_stg_tab rec, po_requisitions_interface_all prh
WHERE batch_run_id = p_batch_run_id
AND prh.header_attribute1 = rec.batch_id
AND status = ‘V’
GROUP BY rec.batch_run_id, rec.batch_id, prh.batch_id, iriso_status;
CURSOR cur_prsuc (p_batch_id IN VARCHAR2)
IS
SELECT prh.requisition_header_id, prh.segment1 pr_number, rec.rec_id,
rec.batch_run_id
FROM xx_iriso_stg_tab rec, po_requisition_headers_all prh
WHERE batch_run_id = p_batch_run_id
AND prh.attribute1 = rec.batch_id
AND rec.batch_id = p_batch_id
AND status = ‘V’;
CURSOR priface_cur (p_batch_id IN VARCHAR2)
IS
SELECT rec.rec_id, rec.batch_run_id
FROM xx_iriso_stg_tab rec, po_requisitions_interface_all prh
WHERE batch_run_id = p_batch_run_id
AND prh.header_attribute1 = rec.batch_id
AND rec.batch_id = p_batch_id
AND status = ‘V’;
l_request_id NUMBER := NULL;
lx_status VARCHAR2 (240) := NULL;
lx_error_message LONG := NULL;
BEGIN
print_output (p_message => ‘submit_pr_request Started : ‘
|| CHR (13)
|| ‘Btahc run Id : ‘
|| p_batch_run_id
);
FOR priface_rec IN cur_priface
LOOP
print_output (p_message => ‘Batch Run Id : ‘
|| priface_rec.batch_run_id
|| CHR (13)
|| ‘Batch Id : ‘
|| priface_rec.batch_id
|| CHR (13)
|| ‘PR Batch Id : ‘
|| priface_rec.pr_batch_id
|| CHR (13)
|| ‘PR IRISO Status : ‘
|| priface_rec.iriso_status
);
l_request_id := NULL;
lx_status := NULL;
lx_error_message := NULL;
l_request_id :=
apps.fnd_request.submit_request
(application => ‘PO’,
program => ‘REQIMPORT’,
description => ”,
start_time => TO_CHAR
(SYSDATE,
‘DD-MON-YY HH24:MI:SS’
),
sub_request => FALSE,
argument1 => ‘IMPORT_INT’,
–Interface Source code,
argument2 => priface_rec.pr_batch_id,
argument3 => ‘LOCATION’, –Group By,
argument4 => ”, –Last Req Number,
argument5 => ‘N’,
–Multi Distributions,
argument6 => priface_rec.approval_flag
);
print_output (p_message => ‘Calling REQIMPORT Request Id : ‘
|| l_request_id
);
COMMIT;
IF l_request_id > 0
THEN
wait_request (p_request_id => l_request_id,
p_program_name => ‘REQIMPORT’,
x_status => lx_status,
x_error_message => lx_error_message
);
END IF;
print_output (p_message => ‘Wait REQIMPORT Request Status : ‘
|| lx_status
|| ‘ Error Message : ‘
|| lx_error_message
);
IF NVL (lx_status, ‘N’) = ‘S’
THEN
/*Update the PR status as Success */
FOR pr_rec IN cur_prsuc (priface_rec.batch_id)
LOOP
print_output (p_message => ‘Batch Run Id : ‘
|| pr_rec.batch_run_id
|| CHR (13)
|| ‘PR Number : ‘
|| pr_rec.pr_number
|| CHR (13)
|| ‘PR Header Id : ‘
|| pr_rec.requisition_header_id
|| CHR (13)
|| ‘Rec Id : ‘
|| pr_rec.rec_id
);
UPDATE xx_iriso_stg_tab rec
SET req_header_id = pr_rec.requisition_header_id,
req_number = pr_rec.pr_number,
status = ‘PR’,
pr_req_id = l_request_id
WHERE status = ‘V’
AND rec.rec_id = pr_rec.rec_id
AND rec.batch_run_id = pr_rec.batch_run_id
AND batch_id = priface_rec.batch_id;
END LOOP;
/*Update the PR status as Error */
FOR iface_rec IN priface_cur (priface_rec.batch_id)
LOOP
print_output (p_message => ‘Batch Run Id : ‘
|| iface_rec.batch_run_id
|| CHR (13)
|| ‘Rec Id : ‘
|| iface_rec.rec_id
);
UPDATE xx_iriso_stg_tab rec
SET status = ‘E’,
err_msg =
‘REQUISITION IMPORT FAILED : request_id : ‘
|| l_request_id,
pr_req_id = l_request_id
WHERE status = ‘V’
AND rec.rec_id = iface_rec.rec_id
AND rec.batch_run_id = iface_rec.batch_run_id
AND batch_id = priface_rec.batch_id;
END LOOP;
ELSE
— PR Import concurrent program other than Sucsess
UPDATE xx_iriso_stg_tab rec
SET status = ‘E’,
err_msg =
err_msg
|| ‘. REQUISITION IMPORT COMPLETED WITH ERROR..request_id : ‘
|| l_request_id,
pr_req_id = l_request_id
WHERE batch_run_id = p_batch_run_id
— AND STATUS = ‘I’
AND status = ‘V’
AND batch_id = priface_rec.batch_id;
END IF;
END LOOP;
BEGIN
— FOR records that are Not in Interface table and also not Found in po_requisition_headers_all table
UPDATE xx_iriso_stg_tab rec
SET status = ‘E’,
err_msg =
err_msg || ‘ . UNKNOWN ERROR. REQUISITION NOT PROCESSED..’,
pr_req_id = l_request_id
WHERE batch_run_id = p_batch_run_id AND status = ‘V’;
–AND Status = ‘I’
END;
COMMIT;
print_output (p_message => ‘submit_pr_request Ended : ‘);
END submit_pr_request;
PROCEDURE so_submit_request (p_batch_run_id IN VARCHAR2)
AS
CURSOR cur_ohiface
IS
SELECT prh.requisition_header_id, prh.segment1, rec.batch_id,
iriso_status, prh.org_id
FROM xx_iriso_stg_tab rec,
oe_headers_iface_all ohi,
po_requisition_headers_all prh
WHERE 1 = 1
AND ohi.orig_sys_document_ref = rec.req_header_id
AND rec.status = ‘PR’
AND rec.req_header_id = prh.requisition_header_id
AND prh.authorization_status = ‘APPROVED’
AND iriso_status IN (‘ISO’, ‘CONFIRM’)
AND rec.batch_run_id = p_batch_run_id
GROUP BY prh.requisition_header_id,
prh.segment1,
rec.batch_id,
prh.org_id,
iriso_status;
CURSOR cur_ohsuc (
p_batch_id IN VARCHAR2,
p_requisition_header_id IN NUMBER,
p_pr_number IN VARCHAR2
)
IS
SELECT oha.header_id, oha.order_number, rec.rec_id, rec.batch_run_id
FROM xx_iriso_stg_tab rec, oe_order_headers_all oha
WHERE 1 = 1
AND oha.source_document_id = rec.req_header_id
AND orig_sys_document_ref = p_pr_number
AND oha.source_document_id = p_requisition_header_id
AND rec.batch_id = p_batch_id
AND rec.batch_run_id = p_batch_run_id
AND rec.status = ‘PR’
AND iriso_status IN (‘ISO’, ‘CONFIRM’);
CURSOR ohiface_cur (
p_batch_id IN VARCHAR2,
p_requisition_header_id IN NUMBER
)
IS
SELECT rec.rec_id, rec.batch_run_id
FROM xx_iriso_stg_tab rec, oe_headers_iface_all ohi
WHERE 1 = 1
AND ohi.orig_sys_document_ref = rec.req_header_id
AND rec.status = ‘PR’
AND rec.batch_run_id = p_batch_run_id
AND ohi.orig_sys_document_ref = p_requisition_header_id
AND rec.batch_id = p_batch_id
AND iriso_status IN (‘ISO’, ‘CONFIRM’);
ln_line_id NUMBER := NULL;
ln_count NUMBER := 0;
CURSOR c_solines
IS
SELECT *
FROM xx_iriso_stg_tab
WHERE batch_run_id = p_batch_run_id
ORDER BY item_code, rec_id;
l_user_id NUMBER;
l_resp_id NUMBER;
l_resp_appl_id NUMBER;
l_request_id NUMBER := NULL;
lx_status VARCHAR2 (240) := NULL;
lx_error_message LONG := NULL;
l_d_ou_id NUMBER := NULL;
BEGIN
UPDATE apps.xx_processingstatus_tbl
SET status = ‘SO Creation – Process Started’
WHERE id1 = p_batch_run_id AND process_name = ‘IRISO’;
print_output (p_message => ‘ Order Submit Request Started : ‘
|| CHR (13)
|| ‘Batch Run Id : ‘
|| p_batch_run_id
);
SELECT fnd.user_id, fresp.responsibility_id, fresp.application_id
INTO l_user_id, l_resp_id, l_resp_appl_id
FROM fnd_user fnd, fnd_responsibility_tl fresp,
fnd_application_vl fvl
WHERE 1 = 1
–AND fnd.user_id = fnd_global.user_id
AND fnd.user_name = ‘HARI’
AND fvl.application_id = fresp.application_id
AND fvl.application_short_name = ‘JA’
AND fresp.responsibility_name = ‘India Local Order Management’;
print_output (p_message => ‘User Id : ‘
|| l_user_id
|| CHR (13)
|| ‘Resp Id : ‘
|| l_resp_id
|| CHR (13)
|| ‘Resp Appln Id : ‘
|| l_resp_appl_id
);
FOR ohiface_rec IN cur_ohiface
LOOP
l_request_id := NULL;
lx_status := NULL;
lx_error_message := NULL;
fnd_global.apps_initialize (user_id => l_user_id,
resp_id => l_resp_id,
resp_appl_id => l_resp_appl_id
);
mo_global.init (‘ONT’);
mo_global.set_policy_context (‘S’, ohiface_rec.org_id);
SELECT mo_utils.get_default_org_id
INTO l_d_ou_id
FROM DUAL;
print_output
(p_message => ‘Calling So Import Program Paramters : ‘
|| CHR (13)
|| ‘Org Id : ‘
|| ohiface_rec.org_id
|| CHR (13)
|| ‘Default Org ID : ‘
|| l_d_ou_id
|| CHR (13)
|| ‘Requisition_Header_Id : ‘
|| ohiface_rec.requisition_header_id
|| CHR (13)
|| ‘SO IRISO STATUS: ‘
|| ohiface_rec.iriso_status
);
IF ohiface_rec.iriso_status = ‘ISO’
THEN
UPDATE oe_headers_iface_all
SET booked_flag = ‘N’
WHERE orig_sys_document_ref = ohiface_rec.requisition_header_id;
END IF;
COMMIT;
l_request_id :=
fnd_request.submit_request
(application => ‘ONT’,
program => ‘OEOIMP’,
description => ‘Order Import’,
start_time => SYSDATE,
sub_request => NULL,
argument1 => ohiface_rec.org_id,
–P_Operating_Unit
argument2 => 10,
–P_Order_Source
argument3 => ohiface_rec.requisition_header_id,
–P_Orig_Sys_Document_Ref
argument4 => NULL,
–P_Operation_Code
argument5 => ‘N’,
–P_Validate_Only
argument6 => 1,
–P_Debug_Level
argument7 => 4,
–P_Num_Instances
argument8 => NULL,
–P_Sold_To_Org_Id
argument9 => NULL,
–P_Sold_To_Org
argument10 => NULL,
–P_Change_Sequence
argument11 => ‘Y’,
–P_Perf_Param
argument12 => ‘N’,
–P_Rtrim_Data
argument13 => ‘Y’,
–p_process_orders_with_null_org
argument14 => ohiface_rec.org_id,
–P_Default_Org_Id
argument15 => ‘N’
–P_Validate_Desc_Flex
);
COMMIT;
print_output (p_message => ‘Calling OEOIMP Request Id : ‘
|| l_request_id
);
IF l_request_id > 0
THEN
wait_request (p_request_id => l_request_id,
p_program_name => ‘OEOIMP’,
x_status => lx_status,
x_error_message => lx_error_message
);
END IF;
print_output (p_message => ‘Wait OEOIMP Request Status : ‘
|| lx_status
|| ‘ Error Message : ‘
|| lx_error_message
);
— SO Request Id
IF NVL (lx_status, ‘N’) = ‘S’
THEN
print_output (p_message => ‘Batch ID : ‘
|| ohiface_rec.batch_id
|| CHR (13)
|| ‘PR Number : ‘
|| ohiface_rec.segment1
|| CHR (13)
|| ‘PR Header Id : ‘
|| ohiface_rec.requisition_header_id
);
/*Update the SO status as Success */
FOR so_rec IN cur_ohsuc (ohiface_rec.batch_id,
ohiface_rec.requisition_header_id,
ohiface_rec.segment1
)
LOOP
print_output (p_message => ‘Batch Run Id : ‘
|| so_rec.batch_run_id
|| CHR (13)
|| ‘Rec Id : ‘
|| so_rec.rec_id
|| CHR (13)
|| ‘Order Header Id : ‘
|| so_rec.header_id
|| CHR (13)
|| ‘Order Number : ‘
|| so_rec.order_number
);
UPDATE xx_iriso_stg_tab rec
SET so_header_id = so_rec.header_id,
order_number = so_rec.order_number,
status = ‘SO’,
so_req_id = l_request_id
WHERE status = ‘PR’
AND rec.rec_id = so_rec.rec_id
AND rec.batch_run_id = so_rec.batch_run_id
AND batch_id = ohiface_rec.batch_id;
END LOOP;
/*Update the SO status as Error */
FOR oiface_rec IN ohiface_cur (ohiface_rec.batch_id,
ohiface_rec.requisition_header_id
)
LOOP
print_output (p_message => ‘Batch Run Id : ‘
|| oiface_rec.batch_run_id
|| CHR (13)
|| ‘Rec Id : ‘
|| oiface_rec.rec_id
);
UPDATE xx_iriso_stg_tab rec
SET status = ‘E’,
err_msg =
‘Order IMPORT IS FAILED : request_id : ‘
|| l_request_id,
so_req_id = l_request_id
WHERE status = ‘PR’
AND rec.rec_id = oiface_rec.rec_id
AND rec.batch_run_id = oiface_rec.batch_run_id
AND batch_id = ohiface_rec.batch_id;
END LOOP;
ELSE
— Order Import concurrent program other than Sucsess
UPDATE xx_iriso_stg_tab rec
SET status = ‘E’,
err_msg =
err_msg
|| ‘. ORDER IMPORT COMPLETED WITH ERROR..request_id : ‘
|| l_request_id,
so_req_id = l_request_id
WHERE batch_run_id = p_batch_run_id
— AND STATUS = ‘I’
AND status = ‘PR’
AND batch_id = ohiface_rec.batch_id;
END IF;
END LOOP;
BEGIN
— FOR records that are Not in Interface table and also not Found in oe_headers_iface_all table
UPDATE xx_iriso_stg_tab rec
SET status = ‘E’,
err_msg =
err_msg || ‘ . UNKNOWN ERROR. ORDER IMPORT NOT PROCESSED..’,
so_req_id = l_request_id
WHERE batch_run_id = p_batch_run_id
AND status = ‘PR’
AND iriso_status IN (‘ISO’, ‘CONFIRM’);
–AND Status = ‘I’
END;
COMMIT;
print_output (p_message => ‘ Order Submit Request Ended : ‘
|| CHR (13)
|| ‘Batch Run Id : ‘
|| p_batch_run_id
);
BEGIN
FOR i IN c_solines
LOOP
ln_line_id := NULL;
ln_count := NULL;
BEGIN
SELECT MIN (line_id), COUNT (1)
INTO ln_line_id, ln_count
FROM oe_order_lines_all ool
WHERE header_id = i.so_header_id
AND ordered_quantity = i.quantity
AND ordered_item = i.item_code
AND inventory_item_id = i.inventory_item_id
AND ool.line_id >
(SELECT NVL (MAX (so_line_id), 0)
FROM xx_iriso_stg_tab a
WHERE batch_run_id = i.batch_run_id
AND quantity = i.quantity
AND inventory_item_id = i.inventory_item_id
AND destination_org_id = i.destination_org_id
AND source_org_id = i.source_org_id
AND a.rec_id != i.rec_id);
EXCEPTION
WHEN OTHERS
THEN
ln_line_id := 0;
END;
IF ln_line_id > 0
THEN
print_output (p_message => ‘SO LINE ID : ‘ || ln_line_id);
UPDATE xx_iriso_stg_tab
SET so_line_id = ln_line_id
WHERE batch_run_id = i.batch_run_id AND rec_id = i.rec_id;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END;
PROCEDURE pickrelease (p_batch_run_id NUMBER, p_header_id NUMBER)
AS
x_return_status VARCHAR2 (2);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
p_api_version_number NUMBER := 1.0;
init_msg_list VARCHAR2 (200);
x_msg_details VARCHAR2 (3000);
x_msg_summary VARCHAR2 (3000);
p_line_rows wsh_util_core.id_tab_type;
x_del_rows wsh_util_core.id_tab_type;
l_ship_method_code VARCHAR2 (100);
i NUMBER;
l_commit VARCHAR2 (30) := fnd_api.g_false;
p_delivery_id NUMBER;
p_delivery_name VARCHAR2 (30);
x_trip_id VARCHAR2 (30);
x_trip_name VARCHAR2 (30);
exep_api EXCEPTION;
l_picked_flag VARCHAR2 (10);
l_return_status VARCHAR2 (1000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_user_id NUMBER;
l_resp_id NUMBER;
l_resp_appl_id NUMBER;
l_stn_no VARCHAR2 (200);
l_line_count NUMBER := 0;
CURSOR cur_stg
IS
SELECT DISTINCT so_header_id, oh.flow_status_code, a.delivery_id
FROM xx_iriso_stg_tab a, oe_order_headers_all oh
WHERE batch_run_id = p_batch_run_id
AND so_header_id = p_header_id
AND a.so_header_id = oh.header_id
AND iriso_status = ‘CONFIRM’
AND status = ‘SO_BOOKED’;
TYPE orddt_rec IS RECORD (
header_id NUMBER,
sales_order NUMBER,
org_id NUMBER,
line_number NUMBER,
shipment_number NUMBER,
flow_status_code oe_order_lines_all.flow_status_code%TYPE,
delivery_detail_id NUMBER,
inv_interfaced_flag wsh_delivery_details.inv_interfaced_flag%TYPE,
oe_interfaced_flag wsh_delivery_details.oe_interfaced_flag%TYPE,
released_status wsh_delivery_details.released_status%TYPE,
inventory_item_id NUMBER,
lot_number wsh_delivery_details.lot_number%TYPE
);
TYPE order_details_rec IS TABLE OF orddt_rec
INDEX BY PLS_INTEGER;
l_orderdt order_details_rec;
BEGIN
UPDATE apps.xx_processingstatus_tbl
SET status = ‘Pick release – Process Started’
WHERE id1 = p_batch_run_id
AND process_name = ‘IRISO’
AND id2 = p_header_id;
— Initializing the Applications
SELECT fnd.user_id, fresp.responsibility_id, fresp.application_id
INTO l_user_id, l_resp_id, l_resp_appl_id
FROM fnd_user fnd, fnd_responsibility_tl fresp,
fnd_application_vl fvl
WHERE 1 = 1
–AND fnd.user_id = fnd_global.user_id
AND fnd.user_name = ‘HARI’
AND fvl.application_id = fresp.application_id
AND fvl.application_short_name = ‘JA’
AND fresp.responsibility_name = ‘India Local Order Management’;
fnd_global.apps_initialize (user_id => l_user_id,
resp_id => l_resp_id,
resp_appl_id => l_resp_appl_id
);
x_return_status := wsh_util_core.g_ret_sts_success;
FOR stg_rec IN cur_stg
LOOP
BEGIN
l_orderdt.DELETE;
SELECT oha.header_id,
oha.order_number sales_order,
oha.org_id,
ola.line_number,
ola.shipment_number,
ola.flow_status_code,
wdd.delivery_detail_id,
wdd.inv_interfaced_flag,
wdd.oe_interfaced_flag,
wdd.released_status,
wdd.inventory_item_id,
wdd.lot_number
BULK COLLECT INTO l_orderdt
FROM apps.oe_order_headers_all oha,
apps.oe_order_lines_all ola,
apps.wsh_delivery_details wdd
WHERE oha.header_id = ola.header_id
AND oha.org_id = ola.org_id
AND oha.header_id = wdd.source_header_id
AND ola.line_id = wdd.source_line_id
AND oha.booked_flag = ‘Y’
AND NVL (ola.cancelled_flag, ‘N’) <> ‘Y’
AND wdd.released_status IN (‘R’, ‘B’)
AND ola.flow_status_code = ‘AWAITING_SHIPPING’
AND oha.header_id = stg_rec.so_header_id;
x_return_status := NULL;
IF stg_rec.delivery_id IS NULL
THEN
IF l_orderdt.COUNT > 0
THEN
FOR i IN l_orderdt.FIRST .. l_orderdt.LAST
LOOP
— Mandatory initialization for R12
mo_global.set_policy_context (‘S’, l_orderdt (i).org_id);
mo_global.init (‘ONT’);
p_line_rows (i) := l_orderdt (i).delivery_detail_id;
END LOOP;
END IF;
print_output
(‘Calling : wsh_delivery_details_pub.autocreate_deliveries’);
wsh_delivery_details_pub.autocreate_deliveries
(p_api_version_number => 1.0,
p_init_msg_list => apps.fnd_api.g_true,
p_commit => l_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_line_rows => p_line_rows,
x_del_rows => x_del_rows
);
print_output (‘Sttaus : ‘ || x_return_status);
print_output (‘MSg Count : ‘ || x_msg_count);
print_output (‘Msg Datat : ‘ || x_msg_data);
IF (x_return_status <> wsh_util_core.g_ret_sts_success)
THEN
ROLLBACK;
UPDATE xx_iriso_stg_tab
SET status = ‘E’,
err_msg = x_msg_data
WHERE batch_run_id = p_batch_run_id
AND so_header_id = p_header_id;
COMMIT;
DBMS_OUTPUT.put_line
(‘Failed to Auto create delivery for Sales Order’);
RAISE exep_api;
END IF;
— Pick release.
UPDATE xx_iriso_stg_tab
SET delivery_id = x_del_rows (1)
WHERE batch_run_id = p_batch_run_id
AND so_header_id = p_header_id;
p_delivery_id := x_del_rows (1);
p_delivery_name := TO_CHAR (x_del_rows (1));
ELSE
p_delivery_id := stg_rec.delivery_id;
p_delivery_name := stg_rec.delivery_id;
END IF;
print_output (‘p_delivery_id : ‘ || p_delivery_id);
print_output (‘p_delivery_name : ‘ || p_delivery_name);
print_output (‘Calling : wsh_delivery_details_pub.delivery_action’);
— API Call for Pick Release
wsh_deliveries_pub.delivery_action
(p_api_version_number => 1.0,
p_init_msg_list => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_action_code => ‘PICK-RELEASE’,
p_delivery_id => p_delivery_id,
p_delivery_name => p_delivery_name,
p_asg_trip_id => NULL,
p_asg_trip_name => NULL,
p_asg_pickup_stop_id => NULL,
p_asg_pickup_loc_id => NULL,
p_asg_pickup_stop_seq => NULL,
p_asg_pickup_loc_code => NULL,
p_asg_pickup_arr_date => NULL,
p_asg_pickup_dep_date => NULL,
p_asg_dropoff_stop_id => NULL,
p_asg_dropoff_loc_id => NULL,
p_asg_dropoff_stop_seq => NULL,
p_asg_dropoff_loc_code => NULL,
p_asg_dropoff_arr_date => NULL,
p_asg_dropoff_dep_date => NULL,
p_sc_action_flag => ‘S’,
p_sc_intransit_flag => ‘N’,
p_sc_close_trip_flag => ‘N’,
p_sc_create_bol_flag => ‘N’,
p_sc_stage_del_flag => ‘Y’,
p_sc_trip_ship_method => NULL,
p_sc_actual_dep_date => NULL,
p_sc_report_set_id => NULL,
p_sc_report_set_name => NULL,
p_sc_defer_interface_flag => ‘Y’,
p_sc_send_945_flag => NULL,
p_sc_rule_id => NULL,
p_sc_rule_name => NULL,
p_wv_override_flag => ‘N’,
x_trip_id => x_trip_id,
x_trip_name => x_trip_name
);
print_output (‘Sttaus : ‘ || x_return_status);
print_output (‘MSg Count : ‘ || x_msg_count);
print_output (‘Msg Datat : ‘ || x_msg_data);
print_output (‘ x_trip_id : ‘ || x_trip_id);
print_output (‘x_trip_name : ‘ || x_trip_name);
IF x_msg_count > 1
THEN
wsh_util_core.get_messages (‘Y’,
x_msg_summary,
x_msg_details,
x_msg_count
);
x_msg_data := x_msg_summary || x_msg_details;
DBMS_OUTPUT.put_line (x_msg_data);
ELSE
x_msg_data := x_msg_summary || x_msg_details;
DBMS_OUTPUT.put_line (x_msg_data);
END IF;
UPDATE xx_iriso_stg_tab a
SET (delivery_detail_id, released_status, released_status_name,
status, err_msg) =
(SELECT delivery_detail_id, released_status,
released_status_name,
DECODE (w.released_status,
‘Y’, ‘PICK RELEASED’,
‘ERROR-PICK RELEASE’
),
DECODE (w.released_status,
‘Y’, NULL,
x_msg_data
)
FROM wsh_deliverables_v w
WHERE w.delivery_id = a.delivery_id
AND w.inventory_item_id = a.inventory_item_id
AND w.organization_id = a.source_org_id
AND w.source_header_id = a.so_header_id
AND w.source_line_id = a.so_line_id)
WHERE a.batch_run_id = p_batch_run_id
AND a.so_header_id = p_header_id
AND a.delivery_id = p_delivery_id
AND EXISTS (
SELECT ‘x’
FROM apps.wsh_deliverables_v wv
WHERE wv.source_header_id = a.so_header_id
AND wv.inventory_item_id = a.inventory_item_id
AND wv.organization_id = a.source_org_id
AND wv.source_line_id = a.so_line_id);
COMMIT;
EXCEPTION
WHEN exep_api
THEN
DBMS_OUTPUT.put_line (‘==============’);
DBMS_OUTPUT.put_line (‘Error Details If Any’);
DBMS_OUTPUT.put_line (‘==============’);
wsh_util_core.get_messages (‘Y’,
x_msg_summary,
x_msg_details,
x_msg_count
);
IF x_msg_count > 1
THEN
x_msg_data := x_msg_summary || x_msg_details;
DBMS_OUTPUT.put_line (x_msg_data);
ELSE
x_msg_data := x_msg_summary || x_msg_details;
DBMS_OUTPUT.put_line (x_msg_data);
END IF;
UPDATE xx_iriso_stg_tab
SET status = ‘E’,
err_msg = x_msg_data
WHERE batch_run_id = p_batch_run_id
AND so_header_id = p_header_id;
END;
IF stg_rec.flow_status_code != ‘BOOKED’
THEN
UPDATE xx_iriso_stg_tab
SET status = ‘E’,
err_msg = ‘Sale Order Not Booked’
WHERE batch_run_id = p_batch_run_id
AND so_header_id = p_header_id;
END IF;
END LOOP;
COMMIT;
END;
PROCEDURE xx_iriso_book_pro (p_batch_run_id NUMBER, p_header_id NUMBER)
AS
v_api_version_number NUMBER := 1;
v_return_status VARCHAR2 (2000);
v_msg_count NUMBER;
v_msg_data VARCHAR2 (2000);
l_price_list_id NUMBER;
l_customer_id NUMBER;
l_salesrep_id NUMBER;
l_err_flag VARCHAR2 (1) := ‘N’;
l_count NUMBER := 0;
l_batch_no NUMBER;
l_user_id NUMBER;
l_resp_appl_id NUMBER;
l_resp_id NUMBER;
— IN Variables —
v_header_rec oe_order_pub.header_rec_type;
v_line_tbl oe_order_pub.line_tbl_type;
v_action_request_tbl oe_order_pub.request_tbl_type;
v_line_adj_tbl oe_order_pub.line_adj_tbl_type;
— OUT Variables —
v_header_rec_out oe_order_pub.header_rec_type;
v_header_val_rec_out oe_order_pub.header_val_rec_type;
v_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
v_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
v_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
v_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
v_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
v_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
v_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
v_line_tbl_out oe_order_pub.line_tbl_type;
v_line_val_tbl_out oe_order_pub.line_val_tbl_type;
v_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
v_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
v_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
v_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
v_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
v_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
v_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
v_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
v_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
v_action_request_tbl_out oe_order_pub.request_tbl_type;
ln_order_created NUMBER := 0;
–added by rajan on 23-May-2020 for retry booking
lv_errbuf VARCHAR2 (4000) := NULL;
ln_retcode NUMBER := 0;
CURSOR c1
IS
SELECT DISTINCT ooh.header_id, ooh.order_number, stg.org_id,
booked_flag, stg.batch_id,
NVL (salesrep_id, -3) salesrep_id,
ooh.flow_status_code so_status
FROM xx_iriso_stg_tab stg, apps.oe_order_headers_all ooh
WHERE stg.so_header_id = ooh.header_id
AND ooh.flow_status_code = ‘ENTERED’
AND ooh.booked_flag = ‘N’
AND stg.status = ‘SO’
AND stg.iriso_status = ‘CONFIRM’
AND stg.batch_run_id = p_batch_run_id
AND ooh.header_id = p_header_id;
BEGIN
DBMS_OUTPUT.put_line (‘Enter into IRISO Book Order’);
— Setting the Enviroment —
mo_global.init (‘ONT’);
SELECT fnd.user_id, fresp.responsibility_id, fresp.application_id
INTO l_user_id, l_resp_id, l_resp_appl_id
FROM fnd_user fnd, fnd_responsibility_tl fresp,
fnd_application_vl fvl
WHERE 1 = 1
AND fnd.user_name = ‘HARI’
AND fvl.application_id = fresp.application_id
AND fvl.application_short_name = ‘JA’
AND fresp.responsibility_name = ‘India Local Order Management’;
fnd_global.apps_initialize (user_id => l_user_id,
resp_id => l_resp_id,
resp_appl_id => l_resp_appl_id
);
mo_global.set_policy_context (‘S’, :org_id);
FOR so_book_rec IN c1
LOOP
— To BOOK the Sales Order
v_action_request_tbl (1) := oe_order_pub.g_miss_request_rec;
v_action_request_tbl (1).request_type := oe_globals.g_book_order;
v_action_request_tbl (1).entity_code := oe_globals.g_entity_header;
v_action_request_tbl (1).entity_id := so_book_rec.header_id;
v_header_rec.salesrep_id := so_book_rec.salesrep_id;
v_action_request_tbl (1).entity_index := 1;
DBMS_OUTPUT.put_line (‘Calling Book Order API’);
— Calling the API to create an Order —
BEGIN
oe_order_pub.process_order
(p_api_version_number => v_api_version_number,
p_header_rec => v_header_rec,
p_line_tbl => v_line_tbl,
p_action_request_tbl => v_action_request_tbl,
p_line_adj_tbl => v_line_adj_tbl,
— OUT variables
x_header_rec => v_header_rec_out,
x_header_val_rec => v_header_val_rec_out,
x_header_adj_tbl => v_header_adj_tbl_out,
x_header_adj_val_tbl => v_header_adj_val_tbl_out,
x_header_price_att_tbl => v_header_price_att_tbl_out,
x_header_adj_att_tbl => v_header_adj_att_tbl_out,
x_header_adj_assoc_tbl => v_header_adj_assoc_tbl_out,
x_header_scredit_tbl => v_header_scredit_tbl_out,
x_header_scredit_val_tbl => v_header_scredit_val_tbl_out,
x_line_tbl => v_line_tbl_out,
x_line_val_tbl => v_line_val_tbl_out,
x_line_adj_tbl => v_line_adj_tbl_out,
x_line_adj_val_tbl => v_line_adj_val_tbl_out,
x_line_price_att_tbl => v_line_price_att_tbl_out,
x_line_adj_att_tbl => v_line_adj_att_tbl_out,
x_line_adj_assoc_tbl => v_line_adj_assoc_tbl_out,
x_line_scredit_tbl => v_line_scredit_tbl_out,
x_line_scredit_val_tbl => v_line_scredit_val_tbl_out,
x_lot_serial_tbl => v_lot_serial_tbl_out,
x_lot_serial_val_tbl => v_lot_serial_val_tbl_out,
x_action_request_tbl => v_action_request_tbl_out,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘API raised error ‘ || SQLERRM);
END;
BEGIN
SELECT COUNT (1)
INTO ln_order_created
FROM apps.oe_order_headers_all a
WHERE header_id = p_header_id AND flow_status_code = ‘BOOKED’;
EXCEPTION
WHEN OTHERS
THEN
ln_order_created := 0;
END;
DBMS_OUTPUT.put_line (‘ln_order_created ‘ || ln_order_created);
IF ln_order_created = 0
THEN
DBMS_OUTPUT.put_line ( ‘Calling Retry for SO Headerid :’
|| so_book_rec.header_id
);
oe_error_wf.em_batch_retry_conc_pgm
(errbuf => lv_errbuf,
retcode => ln_retcode,
p_item_key => so_book_rec.header_id,
p_dummy1 => ‘Y’,
p_item_type => ‘OEOH’,
p_activity_name => NULL,
p_activity_error_date_from => NULL,
p_activity_error_date_to => NULL,
p_mode => ‘EXECUTE’
);
COMMIT;
ln_order_created := NULL;
BEGIN
SELECT COUNT (1)
INTO ln_order_created
FROM apps.oe_order_headers_all a
WHERE header_id = p_header_id AND flow_status_code = ‘BOOKED’;
EXCEPTION
WHEN OTHERS
THEN
ln_order_created := 0;
END;
DBMS_OUTPUT.put_line ( ‘ln_order_created after retry ‘
|| ln_order_created
);
IF ln_order_created > 0
THEN
DBMS_OUTPUT.put_line (‘SO Booked after Retry’);
UPDATE xx_iriso_stg_tab stg
SET status = ‘SO_BOOKED’
WHERE so_header_id = so_book_rec.header_id
AND status = ‘SO’
AND stg.batch_run_id = p_batch_run_id
AND stg.so_header_id = p_header_id
AND stg.batch_id = so_book_rec.batch_id;
ELSE
DBMS_OUTPUT.put_line (‘SO Booking Failed after Retry’);
UPDATE xx_iriso_stg_tab stg
SET status = ‘E’,
err_msg = ‘SO Booking Failed’ || lv_errbuf
WHERE so_header_id = so_book_rec.header_id
AND status = ‘SO’
AND stg.batch_run_id = p_batch_run_id
AND stg.so_header_id = p_header_id
AND stg.batch_id = so_book_rec.batch_id;
END IF;
END IF;
–added by rajan on 23-May-2020 for retry booking ends
DBMS_OUTPUT.put_line (‘Book Order API Completed’);
DBMS_OUTPUT.put_line ( ‘API Status: v_return_status ‘
|| v_return_status
);
DBMS_OUTPUT.put_line ( ‘fnd_api.g_ret_sts_success : ‘
|| fnd_api.g_ret_sts_success
);
DBMS_OUTPUT.put_line (‘API Count: v_msg_count ‘ || v_msg_count);
DBMS_OUTPUT.put_line (‘API Msg Date: v_msg_data ‘ || v_msg_data);
IF v_return_status = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line ( ‘Order Booking Success : ‘
|| v_action_request_tbl (1).entity_id
|| ‘ ‘
|| so_book_rec.order_number
);
DBMS_OUTPUT.put_line ( ‘Update Where data’
|| ‘p_batch_run_id : ‘
|| p_batch_run_id
|| ‘p_header_id : ‘
|| p_header_id
|| ‘so_book_rec.batch_id :’
|| so_book_rec.batch_id
|| ‘so_book_rec.header_id:’
|| so_book_rec.header_id
);
UPDATE xx_iriso_stg_tab stg
SET status = ‘SO_BOOKED’
WHERE so_header_id = so_book_rec.header_id
AND status = ‘SO’
AND stg.batch_run_id = p_batch_run_id
AND stg.so_header_id = p_header_id
AND stg.batch_id = so_book_rec.batch_id;
COMMIT;
ELSE
DBMS_OUTPUT.put_line (‘Order Booking failed:’ || v_msg_data);
ROLLBACK;
FOR i IN 1 .. v_msg_count
LOOP
v_msg_data :=
oe_msg_pub.get (p_msg_index => i,
p_encoded => ‘F’);
DBMS_OUTPUT.put_line (i || ‘) ‘ || v_msg_data);
END LOOP;
UPDATE xx_iriso_stg_tab stg
SET status = NVL (v_return_status, ‘E’),
err_msg = v_msg_data
WHERE so_header_id = so_book_rec.header_id
AND status = ‘SO’
AND stg.batch_run_id = p_batch_run_id
AND stg.so_header_id = p_header_id
AND stg.batch_id = so_book_rec.batch_id;
COMMIT;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
UPDATE xx_iriso_stg_tab stg
SET status = ‘E’,
err_msg = ‘Unknown Error in API’
WHERE 1 = 1
AND status = ‘SO’
AND stg.batch_run_id = p_batch_run_id
AND stg.so_header_id = p_header_id;
DBMS_OUTPUT.put_line (‘Unexceped Error:’ || SQLERRM);
END;
PROCEDURE xx_iriso_ship_confirm (p_batch_run_id NUMBER, p_header_id NUMBER)
AS
x_return_status VARCHAR2 (2);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
p_api_version_number NUMBER := 1.0;
init_msg_list VARCHAR2 (200);
x_msg_details VARCHAR2 (3000);
x_msg_summary VARCHAR2 (3000);
p_line_rows wsh_util_core.id_tab_type;
x_del_rows wsh_util_core.id_tab_type;
l_ship_method_code VARCHAR2 (100);
i NUMBER;
l_commit VARCHAR2 (30);
p_delivery_id NUMBER;
p_delivery_name VARCHAR2 (30);
x_trip_id VARCHAR2 (30);
x_trip_name VARCHAR2 (30);
exep_api EXCEPTION;
l_picked_flag VARCHAR2 (10);
l_return_status VARCHAR2 (1000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_user_id NUMBER;
l_resp_id NUMBER;
l_resp_appl_id NUMBER;
l_stn_no VARCHAR2 (200);
CURSOR c1
IS
SELECT DISTINCT delivery_id
FROM xx_iriso_stg_tab stg
WHERE status = ‘PICK RELEASED’
AND stg.iriso_status = ‘CONFIRM’
AND stg.batch_run_id = p_batch_run_id
AND stg.so_header_id = p_header_id
AND NVL (stg.released_status, ‘N’) = ‘Y’
AND stg.delivery_id > 0;
BEGIN
SELECT fnd.user_id, fresp.responsibility_id, fresp.application_id
INTO l_user_id, l_resp_id, l_resp_appl_id
FROM fnd_user fnd, fnd_responsibility_tl fresp,
fnd_application_vl fvl
WHERE 1 = 1
–AND fnd.user_id = fnd_global.user_id
AND fnd.user_name = ‘ARUN’
AND fvl.application_id = fresp.application_id
AND fvl.application_short_name = ‘JA’
AND fresp.responsibility_name = ‘India Local Order Management’;
fnd_global.apps_initialize (user_id => l_user_id,
resp_id => l_resp_id,
resp_appl_id => l_resp_appl_id
);
x_return_status := wsh_util_core.g_ret_sts_success;
DBMS_OUTPUT.put_line (‘x_return_status: ‘ || x_return_status);
DBMS_OUTPUT.put_line (‘Enter to Pick Confirm the sales order’);
FOR c1_rec IN c1
LOOP
wsh_deliveries_pub.delivery_action
(p_api_version_number => 1.0,
p_init_msg_list => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_action_code => ‘CONFIRM’,
p_delivery_id => c1_rec.delivery_id,
p_delivery_name => c1_rec.delivery_id,
p_asg_trip_id => NULL,
p_asg_trip_name => NULL,
p_asg_pickup_stop_id => NULL,
p_asg_pickup_loc_id => NULL,
p_asg_pickup_stop_seq => NULL,
p_asg_pickup_loc_code => NULL,
p_asg_pickup_arr_date => SYSDATE
+ 2,
p_asg_pickup_dep_date => NULL,
p_asg_dropoff_stop_id => NULL,
p_asg_dropoff_loc_id => NULL,
p_asg_dropoff_stop_seq => NULL,
p_asg_dropoff_loc_code => NULL,
p_asg_dropoff_arr_date => NULL,
p_asg_dropoff_dep_date => NULL,
p_sc_action_flag => ‘S’,
p_sc_intransit_flag => ‘N’,
p_sc_close_trip_flag => ‘Y’,
p_sc_create_bol_flag => ‘N’,
p_sc_stage_del_flag => ‘Y’,
p_sc_trip_ship_method => NULL,
p_sc_actual_dep_date => NULL,
p_sc_report_set_id => NULL,
p_sc_report_set_name => NULL,
p_sc_defer_interface_flag => ‘N’,
p_sc_send_945_flag => NULL,
p_sc_rule_id => NULL,
p_sc_rule_name => NULL,
p_wv_override_flag => ‘N’,
x_trip_id => x_trip_id,
x_trip_name => x_trip_name
);
DBMS_OUTPUT.put_line (‘x_return_status 1: ‘ || x_return_status);
DBMS_OUTPUT.put_line (‘x_msg_count: ‘ || x_msg_count);
DBMS_OUTPUT.put_line ( ‘x_msg_data: ‘
|| x_msg_data
|| ‘x_trip_id: ‘
|| x_trip_id
|| ‘x_trip_name: ‘
|| x_trip_name
);
UPDATE xx_iriso_stg_tab a
SET (released_status, released_status_name, status, err_msg) =
(SELECT released_status, released_status_name,
DECODE (w.released_status,
‘C’, ‘Interfaced’,
‘ERROR-SHIP CONFIRM’
),
DECODE (w.released_status, ‘C’, NULL, x_msg_data)
FROM wsh_deliverables_v w
WHERE w.delivery_id = a.delivery_id
AND w.inventory_item_id = a.inventory_item_id
AND w.organization_id = a.source_org_id
AND w.source_header_id = a.so_header_id
AND w.source_line_id = a.so_line_id
AND w.delivery_detail_id = a.delivery_detail_id)
WHERE a.batch_run_id = p_batch_run_id
AND a.delivery_id = c1_rec.delivery_id
AND EXISTS (
SELECT ‘x’
FROM apps.wsh_deliverables_v wv
WHERE wv.source_header_id = a.so_header_id
AND wv.source_line_id = a.so_line_id
AND wv.inventory_item_id = a.inventory_item_id
AND wv.organization_id = a.source_org_id);
COMMIT;
BEGIN
SELECT tp_attribute7
INTO l_stn_no
FROM wsh_new_deliveries
WHERE delivery_id = c1_rec.delivery_id;
EXCEPTION
WHEN OTHERS
THEN
l_stn_no := -1;
END;
UPDATE xx_iriso_stg_tab a
SET stn = l_stn_no
WHERE a.batch_run_id = p_batch_run_id
AND a.delivery_id = c1_rec.delivery_id;
COMMIT;
END LOOP;
END;
FUNCTION get_conversion_factor (p_item_code VARCHAR2, p_uom VARCHAR2)
RETURN NUMBER
AS
ln_conversion_factor NUMBER := 0;
BEGIN
BEGIN
SELECT DECODE (p_uom, prd.uomcode, 1, prd.uommain, prd.LINK, 0)
conversion_factor
INTO ln_conversion_factor
FROM prdmas prd
WHERE prd.appscode = p_item_code;
EXCEPTION
WHEN OTHERS
THEN
ln_conversion_factor := 0;
END;
RETURN ln_conversion_factor;
END get_conversion_factor;
END xx_iriso_imp_pkg;
/
———————————————————————————————————————–
Available to Reserve Function
*******************************
CREATE OR REPLACE FUNCTION APPS.xx_get_lot_avr (
p_lot_number IN VARCHAR2,
p_org_id IN NUMBER
)
RETURN NUMBER
IS
l_api_return_status VARCHAR2 (1);
l_qty_oh NUMBER;
l_qty_res_oh NUMBER;
l_qty_res NUMBER;
l_qty_sug NUMBER;
l_qty_att NUMBER;
l_qty_atr NUMBER;
l_msg_count NUMBER;
l_sub_inv_code VARCHAR2 (1000);
l_msg_data VARCHAR2 (1000);
x_return_msg VARCHAR2 (4000);
l_msg_index_out NUMBER;
CURSOR c1
IS
SELECT mln.inventory_item_id, mln.lot_number, mln.organization_id,
SUBSTR (organization_code, -1, 1) org_code
FROM mtl_lot_numbers mln, org_organization_definitions ood
WHERE 1 = 1
AND mln.organization_id = ood.organization_id
AND lot_number = p_lot_number
AND mln.organization_id = p_org_id;
BEGIN
apps.inv_quantity_tree_grp.clear_quantity_cache;
FOR c1_rec IN c1
LOOP
BEGIN
IF c1_rec.org_code = ‘9’
THEN
l_sub_inv_code := ‘ORT’;
ELSE
l_sub_inv_code := ‘FGI’;
END IF;
END;
apps.inv_quantity_tree_pub.query_quantities
(p_api_version_number => 1.0,
p_init_msg_lst => apps.fnd_api.g_false,
x_return_status => l_api_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => c1_rec.organization_id,
p_inventory_item_id => c1_rec.inventory_item_id,
p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,
p_onhand_source => 3,
p_is_revision_control => FALSE,
p_is_lot_control => TRUE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => c1_rec.lot_number,
p_subinventory_code => l_sub_inv_code,–‘FGI’, code changes made to include ORT subinventory on 24-Jul-2020
p_locator_id => NULL,
x_qoh => l_qty_oh,
x_rqoh => l_qty_res_oh,
x_qr => l_qty_res,
x_qs => l_qty_sug,
x_att => l_qty_att,
x_atr => l_qty_atr
);
IF l_api_return_status = fnd_api.g_ret_sts_success
THEN
NULL;
/*DBMS_OUTPUT.PUT_LINE ( ‘Total on hand quantity : ‘
|| l_qty_oh
|| ‘ Quantity res oh :’
|| l_qty_res_oh
|| ‘ Quantity reserved :’
|| l_qty_res
|| ‘ Quantity sug :’
|| l_qty_sug
|| ‘ Quantity Available to transat :’
|| l_qty_att
|| ‘ Quantity available to reserve :’
|| l_qty_atr
);*/
ELSE
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => i,
p_encoded => fnd_api.g_false,
p_data => l_msg_data,
p_msg_index_out => l_msg_index_out
);
IF x_return_msg IS NULL
THEN
x_return_msg := SUBSTR (l_msg_data, 1, 250);
ELSE
x_return_msg :=
x_return_msg || ‘,’ || SUBSTR (l_msg_data, 1, 250);
END IF;
END LOOP;
DBMS_OUTPUT.put_line ( ‘ QUERY_QUANTITIES API Failure : ‘
|| x_return_msg
);
END IF;
END LOOP; –C1_REC
RETURN (l_qty_atr);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_qty_atr := 0;
WHEN OTHERS
THEN
l_qty_atr := 0;
END;
/
