ORACLE MASTERMINDS: Interface for Receiving of Internal PO Requisition and Purchase order

PROCEDURE IRISO_PO_PROCESS_PRC (p_errbuf OUT VARCHAR2,
p_retcode OUT VARCHAR2,
p_record_id IN NUMBER)
IS
ln_rcv_request_id NUMBER;
lv_error_flag VARCHAR2(10);
lv_error_message VARCHAR2(2000);
ln_error_id NUMBER;
l_in_record NUMBER;
l_err_record XXIT_INTEGRATION_ERROR_REC;
ln_serial_trx_id NUMBER;
lv_update_msg VARCHAR2(10);
ln_group_id NUMBER;
ln_error_count NUMBER := 0;
ex_validation_error EXCEPTION;
ex_process_error exception;
lv_trx_status_code RCV_TRANSACTIONS_INTERFACE.TRANSACTION_STATUS_CODE%TYPE;
lv_iface_status_code RCV_TRANSACTIONS_INTERFACE.PROCESSING_STATUS_CODE%TYPE;
lv_iface_error_message PO_INTERFACE_ERRORS.ERROR_MESSAGE%TYPE;
lv_phase VARCHAR2(50);
lv_status VARCHAR2(50);
lv_dev_phase VARCHAR2(50);
lv_dev_status VARCHAR2(50);
lv_message VARCHAR2(50);
lb_req_return_status BOOLEAN;
lv_is_valid_record VARCHAR2(1);
v_pgm_id NUMBER;
ln_shipment_header_id NUMBER;
ln_shipment_line_id NUMBER;
ln_transaction_id NUMBER;
ln_inventory_location_id NUMBER;
ln_locator_control NUMBER;
lv_subinventory_code VARCHAR2(10);
ln_exists NUMBER;
vl_org_code MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
vl_shipment_num RCV_SHIPMENT_HEADERS.SHIPMENT_NUM%TYPE;
--
CURSOR po_rcv(p_po_dist_id NUMBER,p_trans_type_code VARCHAR2) IS
SELECT 'PO' Doc_type,
pha.segment1,
pha.po_header_id,
NULL requisition_header_id,
pha.vendor_id,
pla.item_id,
pla.po_line_id,
NULL requisition_line_id,
pla.line_num,
plla.quantity,
to_char(plla.shipment_num) shipment_num,
pra.release_num,
pla.unit_meas_lookup_code,
mp.organization_code,
mp.organization_id,
plla.line_location_id,
plla.closed_code,
plla.quantity_received,
plla.cancel_flag,
msib.serial_number_control_code,
msib.lot_control_code,
msib.location_control_code,
msib.segment1 Item_Number,
NULL req_distribution_id
FROM po_headers_all pha,
po_lines_all pla,
po_releases_all pra,
po_line_locations_all plla,
po_distributions_all pda,
mtl_parameters mp,
mtl_system_items_b msib
WHERE pha.po_header_id = pla.po_header_id
AND pha.po_header_id = plla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND plla.line_location_id = pda.line_location_id
AND pra.po_release_id(+) = plla.po_release_id
AND plla.ship_to_organization_id = mp.organization_id
AND msib.inventory_item_id = pla.item_id
AND msib.organization_id = mp.organization_id
AND pda.po_distribution_id = p_po_dist_id
AND p_trans_type_code = 'STANDARD'
union
SELECT 'IR' doc_type,
prha.segment1,
NULL po_header_id,
prha.requisition_header_id,
ass.vendor_id,
prla.item_id,
NULL po_line_id,
prla.requisition_line_id,
prla.LINE_NUM,
prla.quantity,
(select rsh.shipment_num
from rcv_shipment_headers rsh, rcv_shipment_lines rsl
where 1 = 1
and rsh.shipment_header_id = rsl.shipment_header_id
and rsl.REQUISITION_LINE_ID = prla.REQUISITION_LINE_ID
and rownum < 2
) as SHIPMENT_NUM,
NULL release_num,
prla.unit_meas_lookup_code,
mp.organization_code,
mp.organization_id,
prla.line_location_id,
prla.closed_code,
prla.quantity_received,
prla.CANCEL_FLAG,
msib.serial_number_control_code,
msib.lot_control_code,
msib.location_control_code,
msib.segment1 Item_Number,
prda.distribution_id req_distribution_id
from oe_order_headers_all ooha,
oe_order_lines_all oola,
po_requisition_headers_all prha,
po_requisition_lines_all prla,
po_req_distributions_all prda,
mtl_parameters mp,
mtl_transaction_flow_headers_v mtfh,
MTL_INTERCOMPANY_PARAMETERS mip,
mtl_system_items_b msib,
ap_suppliers ass,
ap_supplier_sites_all assa
where ooha.header_id = oola.header_id
and prha.segment1 = oola.orig_sys_document_ref
and oola.source_document_id = prha.requisition_header_id
and oola.source_document_line_id = prla.requisition_line_id
and mp.organization_id = prla.destination_organization_id
and prla.requisition_line_id = prda.requisition_line_id
and prla.source_organization_id = mtfh.organization_id
and mip.ship_organization_id = mtfh.start_org_id
and mip.sell_organization_id = mtfh.end_org_id
and msib.inventory_item_id = prla.item_id
and msib.organization_id = mp.organization_id
and mtfh.end_org_id = prha.org_id
and nvl(mtfh.END_DATE, sysdate + 1) > sysdate
and ass.vendor_ID = mip.vendor_id
and assa.vendor_site_id = mip.vendor_site_id
and prha.authorization_status = 'APPROVED'
and prha.type_lookup_code = p_trans_type_code
and p_trans_type_code = 'INTERNAL'
and prda.distribution_id = p_po_dist_id;
--
BEGIN
IF p_record_id IS NOT NULL THEN
-- lt_record_ids.extend(1);
-- lt_record_ids(1) := p_record_id;

UPDATE xxit_integration_in
SET status = 'NEW'
,process_method = 'xxit_int0200_kr_imp_exp_in.irso_po_200i_process_rec'
,process_method_id = TO_CHAR(fnd_global.conc_request_id)
,last_update_date = SYSDATE
WHERE record_id = p_record_id;

xxit_integration_common_utils.write_log('Manual Run : Number of records marked for processing = ' || SQL%ROWCOUNT);

-- If no record_id is provided then mark all unprocessed records for processing
ELSE
gv_proc_step := '1020';
UPDATE xxit_integration_in
SET status = xxit_integration_common_utils.gv_status_inprogress
,process_method = 'xxit_int0200_kr_imp_exp_in.irso_po_200i_process_rec'
,process_method_id = TO_CHAR(fnd_global.conc_request_id)
,last_update_date = SYSDATE
WHERE CONTEXT = gv_interface_name
AND status = xxit_integration_common_utils.gv_status_new;

xxit_integration_common_utils.write_log('Full Run, Number of records marked for processing = ' || SQL%ROWCOUNT);

END IF;

ln_group_id := rcv_interface_groups_s.NEXTVAL;
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, '#### IRSO_PO_REC_PROCESS-START ####');
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, 'Group ID: ' || ln_group_id);
xxit_integration_common_utils.write_log('Processing Record ID: ' || p_record_id);
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, '**** IRSO_PO_REC_PROCESS-Record ID: ' || p_record_id || ' ****');
--
lv_is_valid_record := 'N';
-- Initialise the record for updating the xxit_integration_in table
l_in_record := xxit_integration_common_utils.get_empty_integration_in_rec;
l_in_record.record_id := p_record_id;
--
BEGIN
FOR x IN (
SELECT *
FROM XXIT_INT0200I_IN_PO_RECEIPTS_V
WHERE CONTEXT = gv_interface_name
AND STATUS = xxit_integration_common_utils.gv_status_inprogress
) LOOP
--
--Call validation procedure
lv_error_flag := 'N';
validate_record(x.record_id, lv_error_flag, lv_error_message);
--
xxit_integration_common_utils.write_log('Validation Complete: x_error_flag - ' || lv_error_flag ||', x_error_message - ' || lv_error_message);
-- If there is an error then handle it
--
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, 'irso_po_rec_process-START-x.po_line_location_id=' || x.podistributionid);
--
FOR i in po_rcv(x.podistributionid,x.typecode) LOOP
IF lv_error_flag = 'Y' THEN
lv_is_valid_record := 'N';
goto END_REC;
END IF;
lv_is_valid_record := 'Y';

xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, 'irso_po_rec_process-START_01-x.transaction_type=' || x.transaction_type);
--
IF NVL(x.transaction_type,'RECEIVE') = 'RECEIVE' THEN
--
xxit_integration_common_utils.write_log('Creating entries in rcv_headers_interface, ln_group_id=' || ln_group_id);
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, 'irso_po_rec_process-BEFORE_INSERT-rcv_headers_interface');
IF X.TYPECODE = 'INTERNAL' AND i.doc_type = 'IR' THEN
begin
SELECT rsh.shipment_header_id,
rsl.shipment_line_id,
rsh.shipment_num
INTO ln_shipment_header_id,
ln_shipment_line_id,
vl_shipment_num
FROM APPS.rcv_shipment_headers rsh,
APPS.rcv_shipment_lines rsl
WHERE 1 = 1
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.REQ_DISTRIBUTION_ID = i.req_distribution_id;
EXCEPTION WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log, 'i.req_distribution_id :'||i.req_distribution_id);
END;
END IF;
--Insert records into RCV_HEADERS_INTERFACE
INSERT INTO rcv_headers_interface
(header_interface_id,
GROUP_ID,
processing_status_code,
receipt_source_code,
transaction_type,
shipment_num,
last_update_date,
last_updated_by,
vendor_id,
expected_receipt_date,
validation_flag)
VALUES (rcv_headers_interface_s.NEXTVAL,
ln_group_id,
'PENDING',
DECODE(i.doc_type,'PO','VENDOR','INTERNAL ORDER'),
'NEW',
DECODE(i.doc_type,'PO',x.asn_shipment_num,vl_shipment_num),
SYSDATE,
fnd_global.user_id,
i.vendor_id,
SYSDATE,
'Y');
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, 'IRSO_PO_REC_PROCESS-AFTER_INSERT-rcv_headers_interface');
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, 'IRSO_PO_REC_PROCESS-BEFORE_INSERT-rcv_transactions_interface');
xxit_integration_common_utils.write_log('Creating entries in rcv_transactions_interface, ln_group_id=' || ln_group_id);
--
IF x.asn_type = 'ASN' THEN
BEGIN
IF x.shipment_line_id IS NOT NULL THEN
SELECT rsh.shipment_header_id,
rsl.shipment_line_id
INTO ln_shipment_header_id,
ln_shipment_line_id
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
po_line_locations_all plla
WHERE plla.line_location_id = rsl.po_line_location_id
AND rsh.asn_type = 'ASN'
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.shipment_line_id = x.shipment_line_id;
ELSE
SELECT rsh.shipment_header_id,
rsl.shipment_line_id
INTO ln_shipment_header_id,
ln_shipment_line_id
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
po_line_locations_all plla
WHERE plla.line_location_id = rsl.po_line_location_id
AND rsh.asn_type = 'ASN'
AND rsh.shipment_header_id = rsl.shipment_header_id
AND plla.line_location_id = i.line_location_id;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE ex_process_error;
END;
END IF;
--
IF x.locator IS NOT NULL THEN
BEGIN
SELECT inventory_location_id
INTO ln_inventory_location_id
FROM mtl_item_locations_kfv
WHERE concatenated_segments LIKE x.locator || '%'
AND organization_id = i.organization_id;
EXCEPTION
WHEN OTHERS THEN
ln_inventory_location_id := NULL;
END;
IF ln_inventory_location_id IS NOT NULL THEN
BEGIN
SELECT locator_type
INTO ln_locator_control
FROM mtl_secondary_inventories
WHERE organization_id = i.organization_id
AND secondary_inventory_name = x.subinventory ;
EXCEPTION
WHEN OTHERS THEN
ln_locator_control := NULL;
lv_error_message := 'Error while retrieving locator control';
RAISE ex_validation_error ;
END;
IF ln_locator_control = 1 THEN
lv_error_message := 'Locator not allowed for non-locator controlled subinventory';
RAISE ex_validation_error;
ELSIF ln_locator_control = 2 THEN
BEGIN
SELECT 1
INTO ln_exists
FROM mtl_secondary_inventories sub, mtl_item_locations l
WHERE sub.organization_id = i.organization_id
AND NVL (sub.disable_date, SYSDATE + 1) > SYSDATE
AND sub.secondary_inventory_name = x.subinventory
AND l.subinventory_code = sub.secondary_inventory_name
AND L.SEGMENT1
|| '.'
|| l.segment2
|| '.'
|| l.segment3
|| '.'
|| l.segment4
|| '.'
|| l.segment5 like x.locator || '%'
AND NVL (l.disable_date, SYSDATE + 1) > SYSDATE ;
EXCEPTION
WHEN OTHERS THEN
ln_exists := 0 ;
lv_error_message := 'Locator is not valid for subinventory '||x.subinventory;
RAISE ex_validation_error ;
END;
END IF;
END IF;
END IF;
--
IF x.subinventory IS NULL THEN
IF i.doc_type = 'PO' THEN
SELECT DISTINCT mp.organization_code
INTO vl_org_code
FROM po_headers_all ph,
po_lines_all pl,
po_line_locations_all pll,
mtl_parameters mp
WHERE pll.po_line_id = pl.po_line_id
AND ph.po_header_id = pl.po_header_id
AND ph.po_header_id = i.po_header_id
AND mp.organization_id = pll.ship_to_organization_id;
IF vl_org_code = 'KBD' THEN
lv_subinventory_code := 'FGI';
END IF;
ELSE
BEGIN
SELECT subinventory_code
INTO lv_subinventory_code
FROM mtl_item_sub_defaults misd
WHERE misd.inventory_item_id = i.item_id
AND misd.organization_id = i.organization_id
AND misd.default_type = 2;
EXCEPTION WHEN OTHERS THEN
lv_subinventory_code:= NULL;
END;
END IF;
END IF;
--Insert records into RCV_TRANSACTIONS_INTERFACE

INSERT INTO rcv_transactions_interface
(interface_transaction_id,
GROUP_ID,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
transaction_type,
transaction_date,
processing_status_code,
processing_mode_code,
transaction_status_code,
po_header_id,
po_line_id,
item_id,
quantity,
unit_of_measure,
po_line_location_id,
auto_transact_code,
receipt_source_code,
to_organization_code,
source_document_code,
header_interface_id,
validation_flag,
subinventory,
locator_id,
shipment_header_id,
shipment_line_id,
requisition_line_id,
req_distribution_id
)
VALUES (rcv_transactions_interface_s.NEXTVAL,
ln_group_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
0,
'RECEIVE',
SYSDATE,
'PENDING',
'BATCH',
'PENDING',
i.po_header_id,
i.po_line_id,
i.item_id,
x.quantity,
i.unit_meas_lookup_code,
i.line_location_id,
'DELIVER',
DECODE(i.doc_type, 'PO', 'VENDOR', 'INTERNAL ORDER'),
i.organization_code,
DECODE(i.doc_type, 'PO', 'PO', 'REQ'),
rcv_headers_interface_s.CURRVAL,
'Y',
nvl(x.subinventory, lv_subinventory_code),
ln_inventory_location_id,
ln_shipment_header_id,
ln_shipment_line_id,
i.requisition_line_id,
i.req_distribution_id
);
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, 'IRSO_PO_REC_PROCESS-AFTER_INSERT-rcv_transactions_interface');
--
--If Serial controlled
IF i.serial_number_control_code IN (2,5) THEN
--
BEGIN
--
ln_serial_trx_id := mtl_material_transactions_s.NEXTVAL;
--
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, 'IRSO_PO_REC_PROCESS-BEFORE_INSERT-mtl_serial_numbers_interface');
xxit_integration_common_utils.write_log('Creating entries in mtl_serial_numbers_interface, transaction_interface_id=' || mtl_material_transactions_s.CURRVAL);
INSERT INTO mtl_serial_numbers_interface
(transaction_interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
fm_serial_number,
to_serial_number,
product_code,
product_transaction_id
)
VALUES (ln_serial_trx_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
NVL(x.fm_serial_number,x.serialnum),
NVL(x.to_serial_number,x.serialnum),
'RCV',
rcv_transactions_interface_s.CURRVAL
);
--
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, 'IRSO_PO_REC_PROCESS-AFTER_INSERT-mtl_serial_numbers_interface');
--
EXCEPTION
WHEN OTHERS
THEN
xxit_integration_common_utils.write_log(' Error Inserting into mtl_serial_numbers_interface. transaction_interface_id=' || ln_serial_trx_id);
xxit_integration_common_utils.write_log(' SQLERRM=' || SQLERRM);
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, 'IRSO_PO_REC_PROCESS-ERROR_INSERT-mtl_serial_numbers_interface-SQLERRM=' || SQLERRM);
END;

END IF;
--
--If Lot controlled
IF i.lot_control_code = 2 THEN
--
BEGIN
--
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, 'IRSO_PO_REC_PROCESS-BEFORE_INSERT-mtl_transaction_lots_interface');
xxit_integration_common_utils.write_log('Creating entries in mtl_transaction_lots_interface, transaction_interface_id=' || mtl_material_transactions_s.CURRVAL);
--
--INSERT records into MTL_TRANSACTION_LOTS_INTERFACE
INSERT INTO mtl_transaction_lots_interface
(transaction_interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
lot_number,
transaction_quantity,
primary_quantity,
serial_transaction_temp_id,
product_code,
product_transaction_id
)
VALUES (mtl_material_transactions_s.NEXTVAL,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
NVL(x.lot_number,x.serialnum),
x.quantity,
x.quantity,
ln_serial_trx_id,
'RCV',
rcv_transactions_interface_s.CURRVAL
);
--
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, 'IRSO_PO_REC_PROCESS-AFTER_INSERT-mtl_transaction_lots_interface');
--
EXCEPTION
WHEN OTHERS
THEN
xxit_integration_common_utils.write_log(' Error Inserting into mtl_transaction_lots_interface. transaction_interface_id=' || mtl_material_transactions_s.CURRVAL);
xxit_integration_common_utils.write_log(' SQLERRM=' || SQLERRM);
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, 'IRSO_PO_REC_PROCESS-ERROR_INSERT-mtl_transaction_lots_interface-SQLERRM=' || SQLERRM);
END;
--
END IF;
--
ELSIF x.transaction_type = 'RETURN TO VENDOR'
THEN

BEGIN

xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, 'IRSO_PO_REC_PROCESS-RTV-BEFORE_INSERT-rcv_transactions_interface');
xxit_integration_common_utils.write_log('Creating entries in rcv_transactions_interface, ln_group_id=' || ln_group_id);
SELECT rsh.shipment_header_id,
rsl.shipment_line_id,
rt.transaction_id
INTO ln_shipment_header_id,
ln_shipment_line_id,
ln_transaction_id
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
rcv_transactions rt
WHERE rsh.shipment_header_id = rsl.shipment_header_id
AND rt.shipment_header_id = rsh.shipment_header_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rt.transaction_type = 'DELIVER'
AND rsl.po_distribution_id = x.podistributionid
AND rt.quantity >= x.quantity
AND rownum<2;
--
--Insert records into RCV_TRANSACTIONS_INTERFACE
INSERT INTO rcv_transactions_interface
(interface_transaction_id,
GROUP_ID,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
transaction_type,
transaction_date,
processing_status_code,
processing_mode_code,
transaction_status_code,
po_header_id,
po_line_id,
item_id,
quantity,
unit_of_measure,
po_line_location_id,
receipt_source_code,
from_organization_code,
source_document_code,
shipment_header_id,
shipment_line_id,
parent_transaction_id,
validation_flag,
from_subinventory
)
VALUES (rcv_transactions_interface_s.NEXTVAL,
ln_group_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
0,
'RETURN TO VENDOR',
SYSDATE,
'PENDING',
'BATCH',
'PENDING',
i.po_header_id,
i.po_line_id,
i.item_id,
x.quantity,
i.unit_meas_lookup_code,
i.line_location_id,
'VENDOR',
i.organization_code,
decode(i.doc_type, 'PO', 'PO', 'REQ'),
ln_shipment_header_id,
ln_shipment_line_id,
ln_transaction_id,
'Y',
nvl(x.subinventory, lv_subinventory_code)
);
--
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, 'IRSO_PO_REC_PROCESS-RTV-AFTER_INSERT-rcv_transactions_interface');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
xxit_integration_common_utils.write_log(' Error while deriving Receipt Number details for RTV');
WHEN OTHERS
THEN
xxit_integration_common_utils.write_log(' Error Inserting into rcv_transactions_interface for RTV. interface_transaction_id=' || rcv_transactions_interface_s.CURRVAL);
xxit_integration_common_utils.write_log(' SQLERRM=' || SQLERRM);
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, 'IRSO_PO_REC_PROCESS-RTV-ERROR_INSERT-rcv_transactions_interface-SQLERRM=' || SQLERRM);
END;

END IF;

-- If record is not valid for receiving, then handle it
<<END_REC>>
IF lv_is_valid_record = 'N' THEN
lv_error_message := 'Record not eligible for receiving';
xxit_integration_common_utils.write_log('IR/PO Number '||x.ponumber ||' : ' ||lv_error_message);
--RAISE ex_validation_error;
END IF;
l_in_record.attribute1 := x.podistributionid;
l_in_record.attribute2 := i.segment1;
l_in_record.attribute3 := i.release_num;
l_in_record.attribute4 := i.line_num;
l_in_record.attribute5 := i.shipment_num;
l_in_record.attribute6 := i.organization_code;
l_in_record.attribute7 := rcv_transactions_interface_s.CURRVAL;
l_in_record.attribute8 := x.asn_type || ' - ' || x.asn_shipment_num;
--
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, 'PO Line Location ID: ' || x.podistributionid);
--Update xxit_integration_in status to processed
xxit_integration_common_utils.update_in_record (p_record => l_in_record, x_return_msg => lv_update_msg);
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, 'UPDATE_IN_RECORD x_return_msg: ' || lv_update_msg);
END LOOP;
--
END LOOP;
EXCEPTION
WHEN ex_validation_error
THEN
ln_error_count := ln_error_count + 1;
l_in_record.status := xxit_integration_common_utils.gv_status_error;
xxit_integration_common_utils.write_log(' Validation Error: record_id=' || p_record_id || ', lv_error_message=' || lv_error_message);

l_err_record := xxit_integration_common_utils.get_empty_integration_err_rec();
l_err_record.source_table := xxit_integration_common_utils.gv_inbound_table;
l_err_record.source_record_id := p_record_id;
l_err_record.error_message := lv_error_message;
xxit_integration_common_utils.create_error_record(p_record=> l_err_record, x_error_id => ln_error_id, x_error_message => lv_error_message);
when ex_process_error then
ln_error_count := ln_error_count + 1;
l_in_record.status := xxit_integration_common_utils.gv_status_error;
l_err_record := xxit_integration_common_utils.get_empty_integration_err_rec();
l_err_record.source_table := xxit_integration_common_utils.gv_inbound_table;
l_err_record.source_record_id := p_record_id;
l_err_record.error_message := 'Unable to find Receiving Information. Either no data found or too many rows returned.';
xxit_integration_common_utils.create_error_record(p_record=> l_err_record, x_error_id => ln_error_id, x_error_message => lv_error_message);
xxit_integration_common_utils.write_log(' Process Error: record_id=' || p_record_id || ', lv_error_message=' || lv_error_message);
WHEN OTHERS
THEN
ln_error_count := ln_error_count + 1;
l_in_record.status := xxit_integration_common_utils.gv_status_error;
xxit_integration_common_utils.write_log(' Unknown Error: record_id=' || p_record_id || ', SQLERRM=' || SQLERRM);

l_err_record := xxit_integration_common_utils.get_empty_integration_err_rec();
l_err_record.source_table := xxit_integration_common_utils.gv_inbound_table;
l_err_record.source_record_id := p_record_id;
l_err_record.error_message := SQLERRM;
xxit_integration_common_utils.create_error_record(p_record=> l_err_record, x_error_id => ln_error_id, x_error_message => lv_error_message);
xxit_integration_common_utils.write_log('gv_proc_step: ' || gv_proc_step);

apps.xxit_insert_int_err(v_pgm_id,SUBSTR(SQLERRM,1,250),gv_proc_step,
'INT0200I PO Receipt - record_id: '||p_record_id,null,null,'BOBCAT',fnd_global.conc_request_id);

END;
xxit_integration_common_utils.write_log('ln_error_count '||ln_error_count);
-- Update the record in the xxit_integration_in table
xxit_integration_common_utils.update_in_record (p_record => l_in_record,
x_return_msg => lv_update_msg);
--
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, 'UPDATE_IN_RECORD x_return_msg: ' || lv_update_msg);
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, '**** IRSO_PO_REC_PROCESS-END Record ID: ' || p_record_id || ' ****');
--
IF ln_error_count = 0 THEN
--Submit Receiving Transaction Processor
ln_rcv_request_id := SUBMIT_RCV_TRX_PROCESSOR(ln_group_id);
xxit_integration_common_utils.write_log('2. Receiving Transaction Processor submitted. Request ID=' || ln_rcv_request_id);
-- Wait for the receiving transaction processor to complete
--
if lb_req_return_status then
xxit_integration_common_utils.write_log('Waiting to be completed.');
end if;
IF ln_rcv_request_id > 0 THEN
LOOP
lb_req_return_status := fnd_concurrent.wait_for_request (
request_id => ln_rcv_request_id,
interval => 3,
max_wait => 1200, -- 20 Minutes
phase => lv_phase,
status => lv_status,
dev_phase => lv_dev_phase,
dev_status => lv_dev_status,
message => lv_message
);
EXIT WHEN UPPER (lv_phase) = 'COMPLETED' OR UPPER (lv_status) IN ('CANCELLED', 'ERROR', 'TERMINATED');
END LOOP;
END IF;

-- Check each of the records to see if they were successfully processed and update the interface table as appropriate
FOR x IN (
SELECT *
FROM XXIT_INT0200I_IN_PO_RECEIPTS_V
WHERE status = xxit_integration_common_utils.gv_status_inprogress
AND attribute7 IS NOT NULL
AND record_id = nvl(p_record_id, record_id)
) LOOP
-- Initialise the record for updating the xxit_integration_in table
l_in_record := xxit_integration_common_utils.get_empty_integration_in_rec;
l_in_record.record_id := x.record_id;
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, 'CHECK STATUS OF Record ID: ' || x.record_id);
--
BEGIN
BEGIN
SELECT processing_status_code, pie.error_message, transaction_status_code
INTO lv_iface_status_code, lv_iface_error_message, lv_trx_status_code
FROM rcv_transactions_interface rti,
po_interface_errors pie
WHERE rti.group_id = ln_group_id
AND rti.interface_transaction_id = TO_NUMBER(x.attribute7)
AND rti.group_id = pie.batch_id(+)
AND rti.interface_transaction_id = pie.interface_line_id(+);
EXCEPTION WHEN OTHERS THEN
lv_iface_status_code := 'ERROR';
lv_trx_status_code := 'ERROR';
lv_iface_error_message :='Unknwon Error: Please check the record in interface error table for the group ID :'||ln_group_id;
END;

IF lv_iface_status_code = 'ERROR' OR lv_trx_status_code = 'ERROR' THEN
l_in_record.status := xxit_integration_common_utils.gv_status_error;
l_err_record := xxit_integration_common_utils.get_empty_integration_err_rec();
l_err_record.source_table := xxit_integration_common_utils.gv_inbound_table;
l_err_record.source_record_id := x.record_id;
l_err_record.error_message := lv_iface_error_message;
xxit_integration_common_utils.create_error_record(p_record=> l_err_record, x_error_id => ln_error_id, x_error_message => lv_error_message);
xxit_integration_common_utils.write_log(' Transaction Error: record_id=' || x.record_id || ', interface_error=' || lv_iface_error_message);
ELSE
l_in_record.status := xxit_integration_common_utils.gv_status_processed;
xxit_integration_common_utils.write_log(' Transaction Complete: record_id=' || x.record_id);
END IF;

EXCEPTION
WHEN NO_DATA_FOUND
THEN
--When transaction successful records get purged from rcv_transactions_interface
l_in_record.status := xxit_integration_common_utils.gv_status_processed;
xxit_integration_common_utils.write_log(' Transaction Complete: record_id=' || x.record_id);
WHEN TOO_MANY_ROWS
THEN
--Single transaction can have multiple errors
l_in_record.status := xxit_integration_common_utils.gv_status_error;

l_err_record := xxit_integration_common_utils.get_empty_integration_err_rec();
l_err_record.source_table := xxit_integration_common_utils.gv_inbound_table;
l_err_record.source_record_id := x.record_id;
l_err_record.error_message := 'Multiple errors for Interface Transaction ID '|| x.attribute7;
xxit_integration_common_utils.create_error_record(p_record=> l_err_record, x_error_id => ln_error_id, x_error_message => lv_error_message);
xxit_integration_common_utils.write_log(' Transaction Error: record_id=' || x.record_id || ', interface_error=' || l_err_record.error_message);
WHEN OTHERS
THEN
l_in_record.status := xxit_integration_common_utils.gv_status_error;
l_err_record := xxit_integration_common_utils.get_empty_integration_err_rec();
l_err_record.source_table := xxit_integration_common_utils.gv_inbound_table;
l_err_record.source_record_id := x.record_id;
l_err_record.error_message := SQLERRM;
xxit_integration_common_utils.create_error_record(p_record=> l_err_record, x_error_id => ln_error_id, x_error_message => lv_error_message);
xxit_integration_common_utils.write_log(' Unknown Error: record_id=' || x.record_id || ', SQLERRM=' || SQLERRM);
END;
--Update xxit_integration_in status to processed
xxit_integration_common_utils.update_in_record (p_record => l_in_record, x_return_msg => lv_update_msg);
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, 'UPDATE_IN_RECORD x_return_msg: ' || lv_update_msg);
END LOOP;
END IF;
xxit_integration_common_utils.write_debug(gv_proc_step, gv_package, '#### IRSO_PO_REC_PROCESS-COMPLETE ####');
EXCEPTION
WHEN OTHERS
THEN
xxit_integration_common_utils.write_log ('INT-0200I Interface errored out with - ' || SQLERRM);
xxit_integration_common_utils.write_log ('gv_proc_step: ' || gv_proc_step);
apps.xxit_insert_int_err(v_pgm_id,substr(sqlerrm,1,250),gv_proc_step, 'INT0200I PO Receipt - request_id: '|| fnd_global.conc_request_id,null,null,'BOBCAT',fnd_global.conc_request_id);
END IRISO_PO_PROCESS_PRC;

Friday, March 6, 2020

Interface for Receiving of Internal PO Requisition and Purchase order

1 comment: