Supplier Interface Program

------------------------------ Supplier Interface Program ----------------------------------


-------------Supplier Interface-------------

1. Required Data  ( From Staging or Direct )
2. Data Validation
3. Data Insert into Interface Table
4. Import Program.
Supplier Open Interface Import
Supplier Sites Open Interface Import
Supplier Site Contacts Open Interface Import
5. Checking



--===================================================================

DECLARE

CURSOR SUP_INT IS

select  'Oragnic Pvt. Ltd.'vendor_name

, 'SELLERS' vendor_type_lookup_code

, 'NEW' status FROM DUAL;

BEGIN

--SUPPLIER Creation--

        FOR I IN SUP_INT LOOP

           

            IF I.VENDOR_NAME is not null AND I.VENDOR_TYPE_LOOKUP_CODE is not null THEN 

            /* this is kind of Validation only you can put your own validation here*/

               

                INSERT INTO apps.ap_suppliers_int

                (vendor_interface_id

                , vendor_name

                , vendor_type_lookup_code

                , status

                )

                VALUES (apps.ap_suppliers_int_s.NEXTVAL , I.VENDOR_NAME, I.VENDOR_TYPE_LOOKUP_CODE, I.STATUS  );

               

            COMMIT;

            END IF;

        END LOOP;

   

EXCEPTION WHEN OTHERS THEN

dbms_output.put_line (SQLERRM ||' - '||SQLCODE);

END;

/* -- Submit Standard Import Program ---

Name:- Supplier Open Interface Import

*/

--===================================================================

DECLARE

V_ACCTS_PAY number;

V_PREPAY_CODE number;

CURSOR SUP_SITE_INT IS

select  '1874261' VENDOR_ID,

'DELHI' vendor_site_code , 

'Buildin 123' address_line1 , 'Near Cyber HUB' address_line2, 'Delhi' city , 'Delhi' state, '100191' zip, 'IN' country ,

'1021' accts_pay_code_combination_id , 

'1022' prepay_code_combination_id,

'NEW' status,

82 org_id 

FROM DUAL;

BEGIN

--SUPPLIER Site Creation--

        FOR I IN SUP_SITE_INT LOOP

           

            IF I.VENDOR_ID is not null AND I.VENDOR_SITE_CODE is not null and I.accts_pay_code_combination_id is not null  and I.prepay_code_combination_id is not null and I.org_id is not null THEN 

            /* this is kind of Validation only you can put your own validation here*/

               

                select COUNT(*) INTO V_ACCTS_PAY

                from gl_code_combinations_KFV

                where CODE_COMBINATION_ID in ( I.accts_pay_code_combination_id );

                select COUNT(*) INTO V_PREPAY_CODE

                from gl_code_combinations_KFV

                where CODE_COMBINATION_ID in ( I.prepay_code_combination_id );

                IF V_ACCTS_PAY > 0 AND  V_PREPAY_CODE > 0  THEN /* validation */

                   

                INSERT INTO apps.ap_supplier_sites_int (

                vendor_site_interface_id,

                vendor_site_code ,

                vendor_id,

                address_line1 , address_line2, city , state, zip, country ,

                accts_pay_code_combination_id ,

                prepay_code_combination_id,

                status,

                org_id 

                )

                VALUES (ap_supplier_sites_int_s.NEXTVAL ,

                I. vendor_site_code ,

                I. vendor_id,

                I. address_line1 , I. address_line2, I. city , I. state, I. zip, I. country ,

                I. accts_pay_code_combination_id ,

                I. prepay_code_combination_id,

                I. status,

                I. org_id    );

                   

                COMMIT;

                ELSE dbms_output.put_line ('Account Information is not proper');

                END IF;

            ELSE dbms_output.put_line ('Required information is not complete');

               

            END IF;

        END LOOP;

EXCEPTION WHEN OTHERS THEN dbms_output.put_line (SQLERRM ||' - '||SQLCODE);

END;

/* -- Submit Standard Import Program ---

Name:- Supplier Sites Open Interface Import

*/

--===================================================================

DECLARE

CURSOR SUP_CONTACT_INT IS

select

 '2016734' vendor_site_id 

, '1874261' vendor_id

, 'Kuldeep' first_name

, 'Kumar' last_name

, 'oragnic@gmail.com' email_address

, 'NEW' status 

, 82 org_id

FROM DUAL;

BEGIN

--SUPPLIER Creation--

        FOR I IN SUP_CONTACT_INT LOOP

           

            IF I.VENDOR_ID is not null AND I.VENDOR_SITE_ID is not null  AND I.FIRST_NAME is not null  AND I.LAST_NAME is not null AND I.EMAIL_ADDRESS is not null  THEN 

            /* this is kind of Validation only you can put your own validation here*/

                       

            INSERT INTO apps.ap_sup_site_contact_int

            (vendor_contact_interface_id

            , vendor_site_id 

            , vendor_id

            , first_name

            , last_name

            , email_address

            , status 

            , org_id

            )

            VALUES

            (ap_sup_site_contact_int_s.NEXTVAL

            , I.vendor_site_id 

            , I.vendor_id

            , I.first_name

            , I.last_name

            , I.email_address

            , I.status 

            , I.org_id

            );

               

            COMMIT;

            END IF;

        END LOOP;

EXCEPTION WHEN OTHERS THEN dbms_output.put_line (SQLERRM ||' - '||SQLCODE);

END;

/* -- Submit Standard Import Program ---

Name:- Supplier Site Contacts Open Interface Import

*/

--===================================================================

Checking

--===================================================================

select A.STATUS, A.* from ap_suppliers_int A where  VENDOR_NAME = 'Oragnic Pvt. Ltd.'

select A.STATUS, A.*  from ap_supplier_sites_int  A where vendor_id in( 1874261 )

select A.STATUS, A.*  from ap_sup_site_contact_int  A where vendor_id in( 1874261 )

select * from AP_SUPPLIER_INT_REJECTIONS

where PARENT_ID = 1208438

select * from ap_suppliers where VENDOR_NAME = 'Oragnic Pvt. Ltd.'

where vendor_id = 1874261

select * from ap_supplier_sites_all