Monday 29 January 2018

Interface to Upload Approved supplier List(ASL) in Oracle Apps R12 & SQL Query



Interface to Upload Approved supplier List in Oracle Apps R12


DECLARE
x_row_id VARCHAR2(20);
x_row_id1 VARCHAR2(20);
x_asl_id NUMBER;

BEGIN

apps.po_asl_ths.insert_row
(x_row_id,
x_asl_id,
87, --using_organization_id
87, --owning_organization_id,
'DIRECT', --vendor_business_type,
2, --status_id,
SYSDATE, --last_updated_date
0, --last_updated_by,
SYSDATE, --creation_date,
0, --created_by,
NULL,
4408, --vendor_id,
484915, --inventory_item_id,
NULL,
7888, --vendor_site_id,
null, --primary_vendor_item,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL);
 
 
apps.po_asl_attributes_ths.insert_row

(x_row_id1,
x_asl_id, --asl_id
87, --using_organization_id,
SYSDATE, --last_updated_date
0, --last_updated_by,
SYSDATE, --creation_date
0, --created_by,
'ASL', --document_sourcing_method
'CREATE_AND_APPROVE', --release_generation_method
NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,
4408, --vendor_id,
7888, --vendor_site_id,
484915, --inventory_item_id,
NULL,
null, --attribute_category
null, --state(attribute1),
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,

null, --country_of_origin_code,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL);

COMMIT;

END;



SQL Query to fetch Approved Supplier List in Oracle



SELECT hou.NAME operating_unit, asl.vendor_business_type,
       pov.segment1 vendor_code, pov.vendor_name, sites.vendor_site_code,
       msi.segment1 item_code, msi.description item_desc,
       using_organization_id, owning_organization_id,plc.displayed_field , past.status Supplier_Status
  FROM po_approved_supplier_list asl,
       po_vendors pov,
       po_vendor_sites_all sites,
       mtl_system_items_b msi,
       hr_operating_units hou,
       po_lookup_codes plc,
       po_asl_statuses past
 WHERE asl.vendor_id = pov.vendor_id
   AND asl.vendor_site_id = sites.vendor_site_id
   AND msi.inventory_item_id = asl.item_id
   AND msi.organization_id = asl.using_organization_id
   AND sites.org_id = hou.organization_id
   and asl.vendor_business_type = plc.lookup_code
   AND asl.asl_status_id = past.status_id
   AND plc.lookup_type = 'ASL_VENDOR_BUSINESS_TYPE'
   and past.status='Approved';

 

4 comments:

pato mwangangi said...
This comment has been removed by the author.
pato mwangangi said...

for mass upload you can pass a cursor and run the procedure anonymously as below shown:


--create or replace PROCEDURE xx_NBK__LOAD_ASLIST AS
DECLARE
x_row_id VARCHAR2(20);
x_row_id1 VARCHAR2(20);
x_asl_id NUMBER;

CURSOR c1 IS select INVENTORY_ITEM_ID from mtl_system_items_b
where ORGANIZATION_ID='101' and SEGMENT1 in ('SP0078')
-- AND INVENTORY_ITEM_ID NOT IN (SELECT ITEM_ID FROM po_approved_supplier_list)
;
-- CURSOR c2 IS
-- select ITEM_ID from po_approved_supplier_list
--where USING_ORGANIZATION_ID='101' AND ITEM_ID --='298' --
--NOT IN (SELECT ITEM_ID FROM PO_ASL_SUPPLIERS_V)
--;
l_INVENTORY_ITEM_ID NUMBER;
--l_ITEM_ID NUMBER;
l_error VARCHAR2(300);
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO
l_INVENTORY_ITEM_ID --,
--l_transaction_step_id
;
EXIT WHEN c1%notfound;
IF
l_INVENTORY_ITEM_ID IS NOT NULL
THEN
apps.po_asl_ths.insert_row --insert ASL HEADER attributes
(x_row_id,
x_asl_id,
101, --using_organization_id
101, --owning_organization_id,
'DIRECT', --vendor_business_type,
2, --status_id,
SYSDATE, --last_updated_date
0, --last_updated_by,
SYSDATE, --creation_date,
0, --created_by,
NULL,
1173, --vendor_id,
l_inventory_item_id,--inventory_item_id
--'367', --inventory_item_id,
NULL,
1050, --vendor_site_id,
null, --primary_vendor_item,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL);

END IF;

IF
l_INVENTORY_ITEM_ID IS NOT NULL
THEN
apps.po_asl_attributes_ths.insert_row

(x_row_id1,
x_asl_id, --asl_id
101, --using_organization_id,
SYSDATE, --last_updated_date
0, --last_updated_by,
SYSDATE, --creation_date
0, --created_by,
'ASL', --document_sourcing_method
'Null', --'CREATE_AND_APPROVE' --release_generation_method
NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,
1173, --vendor_id,
1050, --vendor_site_id,
l_INVENTORY_ITEM_ID, --inventory_item_id,
NULL,
null, --attribute_category
null, --state(attribute1),
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,null, --country_of_origin_code,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL);
END IF;
END LOOP;
CLOSE c1;

COMMIT;

EXCEPTION
WHEN OTHERS THEN
l_error := substr(sqlerrm,1,200);
dbms_output.put_line('Other Error in updating item in ASL- ' || l_error)
;
END; -- xx_NBK__LOAD_ASLIST;

shaik shah said...

Thanks for sharing this informative article ,It would be helpful to all
Oracle Fusion HCM Online Training

Kumar Macha said...

Very good thing to learn about, When I run this query in Apps schema it is working, when I run in user schema it is showing some error. Grants have been given. what could be the problem can anybody help me on this? thanks in advance

Post a Comment

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *