CREATE OR REPLACE PROCEDURE APPS.xx_iit_mtl_trxns_prc (errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2)
IS
l_ebs_ou_code VARCHAR2 (100);
l_ebs_org_code VARCHAR2 (100);
l_ebs_sub_inv_code VARCHAR2 (100);
l_ebs_locator_seg1 VARCHAR2 (100);
l_ebs_locator_seg2 VARCHAR2 (100);
l_ebs_locator_seg3 VARCHAR2 (100);
l_ebs_locator_seg4 VARCHAR2 (100);
l_ebs_locator_seg5 VARCHAR2 (100);
l_inventory_item_id NUMBER;
l_inventory_location_id NUMBER;
l_org_id NUMBER;
l_transaction_type_id NUMBER;
l_locator_id NUMBER;
l_error_message VARCHAR2 (20000);
l_record_status CHAR (1);
l_segment1 VARCHAR2 (40);
l_segment2 VARCHAR2 (40);
l_segment3 VARCHAR2 (40);
l_segment4 VARCHAR2 (40);
CURSOR c1
IS
SELECT *
FROM xxcs.xx_iit_mtl_trxns
WHERE status_flag IN ('A', 'E')
/* AND transaction_type_code = 'Greige Receives'
and LEGACY_QUALITY_CODE = '1630HM72-T'
AND LEGACY_COLOR_CODE = 'GREIGE'*/
FOR UPDATE;
BEGIN
fnd_file.put_line (fnd_file.LOG, 'XX_IIT_MTL_TRXNS');
fnd_file.put_line (fnd_file.LOG, 'Version: 12.01.001');
fnd_file.put_line (fnd_file.LOG, 'Run Date: ' || sysdate);
FOR r1 IN c1
LOOP
UPDATE xxcs.xx_iit_mtl_trxns
SET error_message = NULL
WHERE CURRENT OF c1;
l_error_message := NULL;
l_record_status := NULL;
BEGIN
SELECT mev.inventory_item_id
INTO l_inventory_item_id
FROM apps.mtl_descr_element_values_v mev
GROUP BY inventory_item_id
HAVING r1.legacy_quality_code = MAX (decode (mev.element_name,
'Quality', mev.element_value))
AND r1.legacy_color_code = MAX (decode (mev.element_name,
'Color', mev.element_value));
EXCEPTION
WHEN OTHERS
THEN
l_error_message := l_error_message || 'invalid Item ID';
l_record_status := 'E';
fnd_file.put_line (fnd_file.LOG, l_error_message || 'invalid Item ID');
END;
BEGIN
SELECT ebs_ou_code,
ebs_org_code,
ebs_sub_inv_code,
ebs_locator_seg1,
ebs_locator_seg2,
ebs_locator_seg3,
ebs_locator_seg4,
ebs_locator_seg5
INTO l_ebs_ou_code,
l_ebs_org_code,
l_ebs_sub_inv_code,
l_ebs_locator_seg1,
l_ebs_locator_seg2,
l_ebs_locator_seg3,
l_ebs_locator_seg4,
l_ebs_locator_seg5
FROM xxcs.xx_iit_map_inv_locations xmil
WHERE nvl (trim (xmil.module_code), 'X') = NVL (trim (r1.source_module), 'X') --'GRE' --r1.module_code
AND NVL (trim (xmil.legacy_location_code), 'X') = NVL (trim (r1.legacy_location_code), 'X')
--OR xmil.legacy_location_code IS NULL)
AND NVL (trim (xmil.legacy_dept_code), 'X') = NVL (trim (r1.legacy_dept_code), 'X') --OR xmil.legacy_dept_code IS NULL)
AND NVL (trim (xmil.legacy_shed_code), 'X') = NVL (trim (r1.legacy_shed_code), 'X') --OR xmil.legacy_shed_code IS NULL)
AND NVL (trim (xmil.legacy_locator_code), 'X') = NVL (trim (r1.legacy_locator_code), 'X')
--OR xmil.legacy_locator_code IS NULL)
AND NVL (trim (xmil.legacy_store_code), 'X') = NVL (trim (r1.legacy_store_code), 'X'); --OR xmil.legacy_store_code IS NULL);
DBMS_OUTPUT.put_line ('Sub-Inventory :' || ' ' || l_ebs_sub_inv_code);
DBMS_OUTPUT.put_line ( 'Locator :'
|| ' '
|| l_ebs_locator_seg1
|| ' '
|| l_ebs_locator_seg2
|| ' '
|| l_ebs_locator_seg3
|| ' '
|| l_ebs_locator_seg4);
--AND ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_message := l_error_message || 'Mapping not found';
l_record_status := 'E';
fnd_file.put_line (fnd_file.LOG, l_error_message || 'Mapping not found');
WHEN TOO_MANY_ROWS
THEN
l_error_message := l_error_message || 'TOO MANY ROWS';
l_record_status := 'E';
DBMS_OUTPUT.put_line ('Sub-Inventory :' || ' ' || l_ebs_sub_inv_code);
DBMS_OUTPUT.put_line ( 'Locator :'
|| ' '
|| l_ebs_locator_seg1
|| ' '
|| l_ebs_locator_seg2
|| ' '
|| l_ebs_locator_seg3
|| ' '
|| l_ebs_locator_seg4);
-- DBMS_OUTPUT.put_line(R1.LEGACY_LOCATION_CODE||' '||
WHEN OTHERS
THEN
l_error_message := l_error_message || 'Invalid Maping';
l_record_status := 'E';
fnd_file.put_line (fnd_file.LOG, l_error_message || 'Invalid Maping');
END;
BEGIN
SELECT ood.organization_id
INTO l_org_id
FROM org_organization_definitions ood
WHERE ood.organization_code = l_ebs_org_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_message := l_error_message || ' ' || 'Organization Code not found ';
l_record_status := 'E';
fnd_file.put_line (fnd_file.LOG, l_error_message || 'Organization Code not found ');
WHEN OTHERS
THEN
l_error_message := l_error_message || ' ' || 'Organization Code is invalid ';
l_record_status := 'E';
fnd_file.put_line (fnd_file.LOG, l_error_message || 'Organization Code is invalid');
END;
BEGIN
SELECT mtt.transaction_type_id
INTO l_transaction_type_id
FROM mtl_transaction_types mtt
WHERE mtt.transaction_type_name =
decode (r1.transaction_action_code,
'RECEIVE', 'Miscellaneous receipt',
'ISSUE', 'Miscellaneous issue');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_message := l_error_message || 'trax type is NOT FOUND';
l_record_status := 'E';
fnd_file.put_line (fnd_file.LOG, l_error_message || 'trax type is NOT FOUND');
WHEN OTHERS
THEN
l_error_message := l_error_message || 'trxn type id is invalid';
l_record_status := 'E';
fnd_file.put_line (fnd_file.LOG, l_error_message || 'trxn type id is invalid');
END;
BEGIN
SELECT inventory_location_id
INTO l_inventory_location_id
FROM mtl_item_locations mil
WHERE mil.subinventory_code = l_ebs_sub_inv_code
AND (mil.segment1 = l_ebs_locator_seg1 OR mil.segment1 IS NULL)
AND (mil.segment2 = l_ebs_locator_seg2 OR mil.segment2 IS NULL)
AND (mil.segment3 = l_ebs_locator_seg3 OR mil.segment3 IS NULL)
AND (mil.segment4 = l_ebs_locator_seg4 OR mil.segment4 IS NULL)
AND (mil.segment5 = l_ebs_locator_seg5 OR mil.segment5 IS NULL);
EXCEPTION
WHEN OTHERS
THEN
l_error_message := l_error_message || 'location id is invalid';
l_record_status := 'E';
fnd_file.put_line (fnd_file.LOG, l_error_message || 'location id is invalid');
END;
BEGIN
--dbms_output.put_line('Thinking. . .');
--dbms_output.put_line('Status Flag '||l_record_status);
IF l_record_status IS NULL
THEN
DBMS_OUTPUT.put_line ('Inserting . . .');
INSERT INTO mtl_transactions_interface
(transaction_interface_id,
source_code,
source_line_id,
source_header_id,
process_flag,
lock_flag,
transaction_mode,
last_update_date,
last_updated_by,
creation_date,
created_by,
inventory_item_id,
locator_id,
distribution_account_id,
organization_id,
transaction_quantity,
primary_quantity,
transaction_uom,
transaction_date,
subinventory_code,
transaction_type_id,
loc_segment1,
loc_segment2,
loc_segment3,
loc_segment4
-- , LOC_SEGMENT5
)
VALUES (xx_iit_trxn_interface_seq.NEXTVAL,
'ORDER ENTRY',
xx_iit_source_line_id_seq.NEXTVAL,
xx_iit_header_id_seq.NEXTVAL,
1,
2,
3,
sysdate,
r1.last_updated_by,
sysdate,
r1.created_by,
l_inventory_item_id,
l_inventory_location_id,
1003,
l_org_id,
nvl (r1.transaction_quantity, r1.primary_quantity),
r1.primary_quantity,
r1.transaction_uom,
r1.transaction_date,
l_ebs_sub_inv_code,
l_transaction_type_id,
l_ebs_locator_seg1,
l_ebs_locator_seg2,
l_ebs_locator_seg3,
l_ebs_locator_seg4 --'B001'
);
UPDATE xxcs.xx_iit_mtl_trxns a
SET a.error_message = l_error_message,
a.status_flag = 'P' --l_record_status
WHERE CURRENT OF c1;
ELSE
--if error then
UPDATE xxcs.xx_iit_mtl_trxns a
SET a.error_message = l_error_message,
a.status_flag = l_record_status
WHERE CURRENT OF c1;
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_error_message := l_error_message; --||'NO DATA';
END;
/* UPDATE xxcs.xx_iit_mtl_trxns
SET status_flag = l_record_status--'P'
WHERE CURRENT OF c1;*/
END LOOP;
/* DECLARE
l_req_id NUMBER;
BEGIN
l_req_id := fnd_request.submit_request (application => 'INV',
program => 'INCTCM',
sub_request => TRUE);
END;*/
--commit;
END;
/
No comments:
Post a Comment