IS
l_source_code VARCHAR2 (30);
l_process_flag NUMBER := 1;
l_lock_flag NUMBER := 2;
l_transaction_mode NUMBER := 3;
l_user_id NUMBER := apps.fnd_global.user_id;
--fnd_global.user_id;
l_from_organization_id NUMBER := NULL;
l_to_organization_id NUMBER := NULL;
l_transaction_type_id NUMBER := NULL; --intransit shipment
l_vendor_site_name VARCHAR2 (50) := NULL;
-- l_transaction_action_id NUMBER := 21;
--l_transaction_source_type_id NUMBER := 13;
l_organization_id NUMBER := NULL;
l_inventory_item_id NUMBER := NULL;
l_vendor_name VARCHAR2 (50) := NULL;
l_status NUMBER := 0;
l_bill_to_location_id NUMBER := NULL;
l_ship_to_location_id NUMBER := NULL;
l_buyer_id NUMBER := NULL;
l_ship_to_org_code VARCHAR2 (50) := NULL;
CURSOR c1
IS
SELECT *
from xxcs.xx_iit_po_headers
WHERE status_flag <> 'P' AND SOURCE_MODULE = 'XXMOD';
CURSOR c2 (po_num IN NUMBER)
IS
SELECT *
FROM xxcs.xx_iit_po_lines
WHERE legacy_po_no = po_num;
PROCEDURE mark_error (p_transaction_id NUMBER, p_error_description VARCHAR2)
IS
BEGIN
UPDATE xxcs.xx_iit_po_headers
SET error_message = p_error_description,
status_flag = 'E',
last_updated_by = l_user_id,
last_update_date = SYSDATE
WHERE transaction_id = p_transaction_id;
END;
BEGIN
--
--GETIING DEFAULT BUYER FROM FND LOOKUPS
--
SELECT meaning
INTO l_buyer_id
FROM fnd_lookup_values
WHERE lookup_type = 'XX_GLOBAL_DEFAULTS' AND lookup_code = 'BUYER';
--
--GETIING SHIP TO ORGANIZATION FOR PROCESSING
--
SELECT organization_code
INTO l_ship_to_org_code
from org_organization_definitions
WHERE organization_name = 'ORG-C3';
FOR r1 IN c1
LOOP
l_status := 0;
BEGIN
--
--GETIING ORGANIZATION ID of OPERATING UNIT
--
IF r1.legacy_ou_code = '' then
SELECT organization_id
INTO l_organization_id
from hr_operating_units
WHERE NAME = 'Vision Company';
ELSIF r1.legacy_ou_code = 'LEG_OU' then
SELECT organization_id
INTO l_organization_id
from hr_operating_units
WHERE NAME = 'OU_NAME';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
mark_error (r1.transaction_id, 'Organization ID Not Found');
l_status := -1;
WHEN TOO_MANY_ROWS
THEN
mark_error (r1.transaction_id, 'Too Many Items Found');
l_status := -1;
WHEN OTHERS
THEN
mark_error (r1.transaction_id, SQLCODE || '-' || SQLERRM);
l_status := -1;
END;
BEGIN
--
--GETIING VENDOR NAME & VENDOR SITE NAME FROM GIVEN VENDOR SITE
--
SELECT ap.vendor_name,aps.vendor_site_code,
aps.bill_to_location_id,aps.ship_to_location_id
INTO l_vendor_name,l_vendor_site_name,
l_bill_to_location_id,l_ship_to_location_id
FROM ap_supplier_sites_all aps, ap_suppliers ap
WHERE ap.vendor_id = aps.vendor_id
AND aps.attribute11 = r1.legacy_supplier_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
mark_error (r1.transaction_id, 'Vendor Not Found');
l_status := -1;
WHEN TOO_MANY_ROWS
THEN
mark_error (r1.transaction_id, 'Too Many Vendors Found');
l_status := -1;
WHEN OTHERS
THEN
mark_error (r1.transaction_id, SQLCODE || '-' || SQLERRM);
l_status := -1;
END;
continue when l_status = -1;
INSERT INTO po.po_headers_interface
(interface_header_id, batch_id, process_code,
action, org_id, document_type_code,
currency_code, agent_id,
vendor_name, vendor_site_code,
ship_to_location_id, bill_to_location_id,
revision_num
)
VALUES (apps.po_headers_interface_s.NEXTVAL, 9, 'PENDING',
'ORIGINAL', l_organization_id, -- Operating unit id
r1.legacy_po_type,
r1.legacy_currency_code, -- Your currency code
l_buyer_id,
-- Your buyer id
l_vendor_name, --vendor_name
l_vendor_site_name, --Vendor Site
l_ship_to_location_id, -- Your ship to
l_bill_to_location_id,
-- Your bill to
r1.legacy_revison_no
);
FOR r2 IN c2 (r1.legacy_po_no)
LOOP
BEGIN
--
--RETRIVING ITEM FROM LEGACY COLOR AND QUALITY CODE
--
SELECT mev.inventory_item_id
INTO l_inventory_item_id
FROM apps.mtl_descr_element_values_v mev
GROUP BY inventory_item_id
HAVING r2.legacy_quality_code =
MAX (DECODE (mev.element_name, 'Quality', mev.element_value))
AND r2.legacy_color_code =
MAX (DECODE (mev.element_name, 'Color', mev.element_value));
EXCEPTION
WHEN NO_DATA_FOUND
THEN
mark_error (r1.transaction_id, 'Item Not Found');
l_status := -1;
WHEN TOO_MANY_ROWS
THEN
mark_error (r1.transaction_id, 'Too Many Items Found');
l_status := -1;
WHEN OTHERS
THEN
mark_error (r1.transaction_id, SQLCODE || '-' || SQLERRM);
l_status := -1;
END;
INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id, line_num, shipment_num,
line_type, item_id, uom_code, quantity,
unit_price, promised_date,ship_to_organization_code
-- SECONDARY_UNIT_OF_MEASURE,
--SECONDARY_QUANTITY
)
VALUES (po_lines_interface_s.NEXTVAL,
po_headers_interface_s.CURRVAL, r2.line_number, 1,
r2.po_line_type, l_inventory_item_id, r2.uom, r2.quantity,
r2.unit_price, r2.promised_date,l_ship_to_org_code
-- 'Bales',
--193.54839
);
INSERT INTO po.po_distributions_interface
(interface_header_id,
interface_line_id,
interface_distribution_id,
distribution_num,
quantity_ordered
--charge_account_id
)
VALUES (po_headers_interface_s.CURRVAL,
po.po_lines_interface_s.CURRVAL,
po.po_distributions_interface_s.NEXTVAL,
r2.line_number,--1,
r2.quantity
--1069
) ;
UPDATE xxcs.xx_iit_po_headers
SET error_message = NULL,
status_flag = 'P',
last_updated_by = 'EBS',
last_update_date = SYSDATE
WHERE transaction_id = r1.transaction_id;
COMMIT;
END LOOP;
END LOOP;
END;
/
No comments:
Post a Comment