ORACLE MASTERMINDS

Monday, 9 March 2020

Create REST adapter Connection in OIC

1. When integrating services with Oracle Integration Cloud service the first thing you need to do is create a connection. Connections define how to connect to a specific instance of an application.

2. In the navigation pane, click Integrations, then click Connections.

3. Click Create.

4. In the Search field enter REST and click Search.

5. The Create New Connection dialog is displayed

6. Enter Name, Identifier and optional description of the connection.

Click Create.

7. Your connection is created and you are now ready to configure connection details, such as email contact, connection properties, security policies, connection login credentials, and (for certain connections) agent group.

8. Click on Configure Connectivity, enter the required values. Also can enter optional parameter “Email Address”

9. Click Configure Security, enter below and click OK button
UserName: user name
Password: password

10. Click on Test button from the upper right corner. If everything is correct then the progress bar will reach 100% and your connection is ready to be used at integration development.

Create FTP adapter Connection in OIC

4. In the Search field enter FTP and click Search.

5. The Create New Connection dialog is displayed

6. Enter Name, Identifier and optional description of the connection.

9. Click Configure Security, enter below and click OK button
UserName: user name
Password: password

10. Click on Test button from the upper right corner. If everything is correct then the progress bar will reach 100% and your connection is ready to be used at integration development.

Create ERP adapter Connection in OIC

1. When integrating services with Oracle Integration Cloud service the first thing you need to do is create a connection. Connections define how to connect to a specific instance of an application.

2. In the navigation pane, click Integrations, then click Connections.

3. Click Create.

4. In the Search field enter Oracle ERP Cloud and click Search.

5. The Create New Connection dialog is displayed

6. Enter Name, Identifier and optional description of the connection.

Click Create.

7. Your connection is created and you are now ready to configure connection details, such as email contact, connection properties, security policies, connection login credentials, and (for certain connections) agent group.

8. Click on Configure Connectivity and enter values

9. Click Configure Security, enter below and click OK button

UserName: user name

Password: password

Confirm Password: password

10. Click on Test button from the upper right corner. If everything is correct then the progress bar will reach 100% and your connection is ready to be used at integration development.

Create DB adapter Connection in OIC

1. When integrating services with Oracle Integration Cloud service the first thing you need to do is create a connection. Connections define how to connect to a specific instance of an application.

2. In the navigation pane, click Integrations, then click Connections.

3. Click Create.

4. In the Search field enter Database and click Search.

5. The Create New Connection dialog is displayed

6. Enter Name, Identifier and optional description of the connection and Click Create.

7.Your connection is created and you are now ready to configure connection details, such as email contact, connection properties, security policies, connection login credentials, and (for certain connections) agent group.

8.Click on Configure Connectivity, enter below information and click OK button

Host: Database host or IP

Port: Database port number

SID: Database SID

9. Click Configure Agent and select the available agent (TEST_AGENT_GROUP) that is available at respective instance. Click on the Use button

10.Click Configure Security, enter below and click OK button

UserName: user name

Password: password

Confirm Password: password

11. Click on Test button from the upper right corner. If everything is correct then the progress bar will reach 100% and your connection is ready to be used at integration development.

Friday, 6 March 2020

Interface to Create delivery detail for sales order

PROCEDURE CREATE_SALES_ORD_DELIVERY_INT(errorbuff OUT VARCHAR2,
retcode OUT NUMBER,
p_record_id NUMBER DEFAULT NULL) IS
l_return_status VARCHAR2(10);
v_error_msg VARCHAR2(4000);
l_msg_summary VARCHAR2(250);
l_msg_details VARCHAR2(2000);
l_msg_count NUMBER;
l_line_rows wsh_util_core.id_tab_type;
l_del_rows wsh_util_core.id_tab_type;
l_commit VARCHAR2(10) := fnd_api.g_false;
l_cur_count NUMBER := 0;
--
CURSOR delivery_det(p_record_id NUMBER) IS
SELECT deliverydetailid delivery_detail_id
FROM XXIT_CREATE_DELIVERIES_V
WHERE record_id = nvl(p_record_id, record_id)
AND status = 'NEW';
BEGIN
--
-- Initialize return status
l_return_status := wsh_util_core.g_ret_sts_success;
-- Call this procedure to initialize applications parameters
fnd_global.apps_initialize(user_id => fnd_profile.VALUE('USER_ID'),
resp_id => fnd_profile.VALUE('RESP_ID'),
resp_appl_id => fnd_profile.VALUE('RESP_APPL_ID'));
FOR i IN delivery_det(p_record_id) LOOP
l_cur_count := l_cur_count + 1;
l_line_rows(l_cur_count) := i.delivery_detail_id;
--
UPDATE xxit_integration_in
SET status = 'NEW'
WHERE record_id = p_record_id;
--
END LOOP;

wsh_delivery_details_pub.autocreate_deliveries(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => l_commit,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => v_error_msg,
p_line_rows => l_line_rows,
x_del_rows => l_del_rows);

dbms_output.put_line('l_return_status:' || l_return_status);

IF (l_return_status <> wsh_util_core.g_ret_sts_success)
THEN
wsh_util_core.get_messages('Y', l_msg_summary, l_msg_details, l_msg_count);
dbms_output.put_line('l_msg_count:' || l_msg_count);

IF l_msg_count > 1
THEN
v_error_msg := l_msg_summary || l_msg_details;
ELSE
v_error_msg := l_msg_summary || l_msg_details;
END IF;
--
UPDATE xxit_integration_in
SET status = 'ERROR'
WHERE record_id = p_record_id;
COMMIT;
fnd_file.put_line(fnd_file.log,
'Error: Auto create delivery' || ':' || v_error_msg);
ELSE
UPDATE xxit_integration_in
SET status = 'PROCESSED'
WHERE record_id = p_record_id;
COMMIT;
fnd_file.put_line(fnd_file.log,
'Success: Auto create delivery for record_id :' || p_record_id);
END IF;

--Create Delivery
COMMIT;
EXCEPTION
WHEN OTHERS THEN
errorbuff := ('Error: ' || SQLERRM);
retcode := 1;
ROLLBACK;
fnd_file.put_line(fnd_file.log, 'Error: ' || SQLERRM);
--
END CREATE_SALES_ORD_DELIVERY_INT;

/

CREATE TABLE XXIT_CREATE_DELIVERIES_V
(
record_id NUMBER,
status VARCHAR2(150),
source_system VARCHAR2(150),
source_system_id NUMBER,
process_method VARCHAR2(150),
process_method_id NUMBER,
priority VARCHAR2(150),
soa_composite_id NUMBER,
context VARCHAR2(150),
attribute1 VARCHAR2(150),
attribute2 VARCHAR2(150),
attribute3 VARCHAR2(150),
attribute4 VARCHAR2(150),
attribute5 VARCHAR2(150),
attribute6 VARCHAR2(150),
attribute7 VARCHAR2(150),
attribute8 VARCHAR2(150),
attribute9 VARCHAR2(150),
attribute10 VARCHAR2(150),
attribute11 VARCHAR2(150),
attribute12 VARCHAR2(150),
attribute13 VARCHAR2(150),
attribute14 VARCHAR2(150),
attribute15 VARCHAR2(150),
creation_date DATE,
created_by NUMBER,
last_update_date DATE,
last_updated_by NUMBER,
SN VARCHAR2(150),
groupingId NUMBER,
CustomerName VARCHAR2(150),
DeliveryDetailId NUMBER
)

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;