customer creation through api backend in oracle apps

////////// CREATE CUSTOMER ACCOUNT(PERSON)///////////
_________________________________________________________________________________


DECLARE
   p_cust_account_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
   p_person_rec HZ_PARTY_V2PUB.PERSON_REC_TYPE;
   p_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
   p_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE ;
   x_cust_account_id NUMBER;
   x_account_number  VARCHAR2 ( 2000 ) ;
   x_party_id        NUMBER;
   x_party_number    VARCHAR2 ( 2000 ) ;
   x_profile_id      NUMBER;
   x_return_status   VARCHAR2 ( 2000 ) ;
   x_msg_count       NUMBER;
   x_msg_data        VARCHAR2 ( 2000 ) ;
BEGIN
   p_cust_account_rec.account_name      := 'ABCXYZ';
   p_cust_account_rec.created_by_module := 'TCA_V2_API';
   p_person_rec.person_first_name       := 'ABC';
   p_person_rec.person_last_name        := 'XYZ';
   mo_global.init ( 'AR' ) ;
   mo_global.set_org_context ( 204, NULL, 'AR' ) ;
   fnd_global.set_nls_context ( 'AMERICAN' ) ;
   mo_global.set_policy_context ( 'S', 204 ) ;
   hz_cust_account_v2pub.create_cust_account (  p_init_msg_list           =>    'T'                    ,
                                                p_cust_account_rec        =>    p_cust_account_rec     ,
                                                p_person_rec              =>    p_person_rec           ,
                                                p_customer_profile_rec    =>    p_customer_profile_rec ,
                                                p_create_profile_amt      =>    'F'                    ,
                                                x_cust_account_id         =>    x_cust_account_id      ,
                                                x_account_number          =>    x_account_number       ,
                                                x_party_id                =>    x_party_id             ,
                                                x_party_number            =>    x_party_number         ,
                                                x_profile_id              =>    x_profile_id           ,
                                                x_return_status           =>    x_return_status        ,
                                                x_msg_count               =>    x_msg_count            ,
                                                x_msg_data                =>    x_msg_data
                                             );
   dbms_output.put_line ( 'x_return_status = '||SUBSTR ( x_return_status, 1, 255 ) ) ;
   dbms_output.put_line ( 'x_msg_count = '||TO_CHAR ( x_msg_count ) ) ;
   dbms_output.put_line ( 'Party Id = '||TO_CHAR ( x_party_id ) ) ;
   dbms_output.put_line ( 'Party Number = '||x_party_number ) ;
   dbms_output.put_line ( 'Profile Id = '||TO_CHAR ( x_profile_id ) ) ;
   dbms_output.put_line ( 'x_msg_data = '|| SUBSTR ( x_msg_data, 1, 255 ) ) ;
   IF x_msg_count >1 THEN
      FOR I      IN 1..x_msg_count
      LOOP
         dbms_output.put_line ( I||'.'||SUBSTR ( FND_MSG_PUB.Get ( p_encoded=> FND_API.G_FALSE ), 1, 255 ) ) ;
      END LOOP;
   END IF;
EXCEPTION
WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE ( 'Error: '||SQLERRM ) ;
END;

/

COMMIT;



------------------------------------------------------------------------------------------------------------------------
////////////// FOR VERIFY OF CREATED CUSTOMER//////////////////////////////////////////////////////////

 SELECT  cust_account_id
       , party_id
       , account_number
       , account_name
   FROM  hz_cust_accounts
     WHERE party_id = 554754;


SELECT  party_id
       ,party_number
       ,party_name
       ,party_type
  FROM hz_parties
WHERE party_id = 554754;

SELECT person_profile_id
       ,party_id
       ,person_first_name
       ,person_last_name
  FROM hz_person_profiles
WHERE party_id = 554754;


SELECT cust_account_profile_id
       ,cust_account_id
       ,profile_class_id
       ,collector_id
  FROM hz_customer_profiles
WHERE cust_account_id = 189744;

------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
///////////////////// CREATE LOCATION ///////////////////////////////////////////////////////////////////
_____________________________________________________________________________________________________
DECLARE
      P_location_rec  HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
      X_location_id   NUMBER;
      X_return_status VARCHAR2(2000);
      X_msg_count     NUMBER;
      X_msg_data      VARCHAR2(2000);
BEGIN
      p_location_rec.country            := 'US';
      p_location_rec.address1           := '1229';
      p_location_rec.address2           := 'South Harlem Ave';
      p_location_rec.city               := 'Berwyn';
      p_location_rec.postal_code        := '60169';
      p_location_rec.state              := 'IL';
      p_location_rec.created_by_module  := 'TCA_V2_API';
      hz_location_v2pub.create_location(
                                          p_init_msg_list  =>   'T'               ,
                                          p_location_rec   =>   p_location_rec    ,
                                          x_location_id    =>   x_location_id     ,
                                          x_return_status  =>   x_return_status   ,
                                          x_msg_count      =>   x_msg_count       ,
                                          x_msg_data       =>   x_msg_data
                                       );
      dbms_output.put_line('x_return_status = '||SUBSTR(x_return_status,1,255));
      dbms_output.put_line('x_msg_count = '    ||TO_CHAR(x_msg_count));
      dbms_output.put_line('Location Id = '    ||TO_CHAR(x_location_id));
      dbms_output.put_line('Country = '        || SUBSTR(p_location_rec.country,1,255));
      dbms_output.put_line('Address1 = '       || SUBSTR(p_location_rec.Address1,1,255));
      dbms_output.put_line('State = '          || SUBSTR(p_location_rec.state,1,255));
      dbms_output.put_line('Created By = '     || SUBSTR(p_location_rec.created_by_module,1,255));
      dbms_output.put_line('x_msg_data = '     || SUBSTR(x_msg_data,1,255));
      dbms_output.put_line('x_location_id = '||x_location_id  );
      IF x_msg_count > 1
      THEN
         FOR I IN 1..x_msg_count
         LOOP
            dbms_output.put_line(I||'.'||SUBSTR( FND_MSG_PUB.Get(
                                                                  p_encoded => FND_API.G_FALSE )
                                                                  ,1, 255
                                                                 )
                                                );
         END LOOP;
      END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE('Error: '||SQLERRM);

END;
/
COMMIT;

------------------------------------------------------------------------------------------------------------------------
////////////// FOR VERIFY OF CREATED LOCATION /////////////////////////////////////////////////////////////

SELECT address1,
address2,
county ,
object_version_number
FROM hz_locations
WHERE location_id = 29266;

-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------

//////////////////// CREATE PARTY SITE ////////////////////////////////////////////

DECLARE
p_party_site_rec                                HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
x_party_site_id                                 NUMBER;
x_party_site_number                             VARCHAR2(2000);
x_return_status                                 VARCHAR2(2000);
x_msg_count                                     NUMBER;
x_msg_data                                      VARCHAR2(2000);
BEGIN
p_party_site_rec.party_id := 409691;
p_party_site_rec.location_id := 26478;
p_party_site_rec.identifying_address_flag := 'Y';
p_party_site_rec.created_by_module := 'TCA_V2_API';

hz_party_site_v2pub.create_party_site(
'T',
p_party_site_rec,
x_party_site_id,
x_party_site_number,
x_return_status,
x_msg_count,
x_msg_data);

dbms_output.put_line(SubStr('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255));
dbms_output.put_line('PARTY_SITE_ID = '||x_party_site_id  );

IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;

END;

/
COMMIT;

--------------------------------------------------------------------------------

//////////  VERIFY FOR CREATED PARTY SITE ///////////////


SELECT * FROM hz_party_sites
WHERE PARTY_ID=409691;

--------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------

/////////////////////////////// CREATE party_site_use_rec //////////////////////////////////


------------------------------------------------------------------

DECLARE
p_party_site_use_rec         HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE;
x_party_site_use_id          NUMBER;
x_return_status              VARCHAR2(2000);
x_msg_count                  NUMBER;
x_msg_data                   VARCHAR2(2000);
BEGIN
p_party_site_use_rec.site_use_type := 'SHIP_TO';
p_party_site_use_rec.party_site_id := 225634;
p_party_site_use_rec.created_by_module := 'TCA_V2_API';

hz_party_site_v2pub.create_party_site_use(
'T',
p_party_site_use_rec,
x_party_site_use_id,
x_return_status,
x_msg_count,
x_msg_data);

dbms_output.put_line(SubStr('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255));
dbms_output.put_line('x_party_site_use_id ='||x_party_site_use_id);

IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;

END;

COMMIT;
-----------------------------------------------------------------------------------------------------
///////////////////// VERIFY FOR CREATED party_site_use_rec ///////////////////////

select party_site_use_id, party_site_id, site_use_type, primary_per_type
from hz_party_site_uses
where party_site_use_id = 235055;


-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
//////////////////////  create contact point //////////////////////////////////////

DECLARE
p_contact_point_rec HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;
p_edi_rec           HZ_CONTACT_POINT_V2PUB.EDI_REC_TYPE;
p_email_rec         HZ_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE;
p_phone_rec         HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE;
p_telex_rec         HZ_CONTACT_POINT_V2PUB.TELEX_REC_TYPE;
p_web_rec           HZ_CONTACT_POINT_V2PUB.WEB_REC_TYPE;
x_return_status     VARCHAR2(2000);
x_msg_count         NUMBER;
x_msg_data          VARCHAR2(2000);
x_contact_point_id  NUMBER;

BEGIN
-- Setting the Context --
mo_global.init('AR');
fnd_global.apps_initialize ( user_id      => 1013455
                            ,resp_id      => 21623
                            ,resp_appl_id => 222);
mo_global.set_policy_context('S',204);
fnd_global.set_nls_context('AMERICAN');

-- Initializing the Mandatory API parameters
p_contact_point_rec.contact_point_type    := 'PHONE';
p_contact_point_rec.owner_table_name      := 'HZ_PARTIES';
p_contact_point_rec.owner_table_id        := 409691; --THIS IS PARTY_ID
p_contact_point_rec.primary_flag          := 'Y';
p_contact_point_rec.contact_point_purpose := 'BUSINESS';
p_contact_point_rec.created_by_module     := 'TCA_V2_API';

p_phone_rec.phone_area_code               :=  NULL;
p_phone_rec.phone_country_code            := '1';
p_phone_rec.phone_number                  := '856-784-521';
p_phone_rec.phone_line_type               := 'MOBILE';

DBMS_OUTPUT.PUT_LINE('Calling the API hz_contact_point_v2pub.create_contact_point');

HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT
                   (
                     p_init_msg_list      => FND_API.G_TRUE,
                     p_contact_point_rec  => p_contact_point_rec,
                     p_edi_rec            => p_edi_rec,
                     p_email_rec          => p_email_rec,
                     p_phone_rec          => p_phone_rec,
                     p_telex_rec          => p_telex_rec,
                     p_web_rec            => p_web_rec,
                     x_contact_point_id   => x_contact_point_id,
                     x_return_status      => x_return_status,
                     x_msg_count          => x_msg_count,
                     x_msg_data           => x_msg_data
                           );
                       

IF  x_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of Contact Point is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');
    DBMS_OUTPUT.PUT_LINE('x_contact_point_id = '||x_contact_point_id);
   
ELSE
    DBMS_OUTPUT.put_line ('Creation of Contact Point got failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
END;

-------------------------------------------------------------------------------------------------------------
///////////////////////// VERIFY CONTACT POINT ////////////////////////////

select *
from hz_contact_points
where contact_point_id = 262892;
-------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------

////////////////////////// CREATE CUSTOMER ACCOUNT SITE ID ////////////////////////////////////////
DECLARE
   p_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
   x_return_status VARCHAR2 ( 2000 ) ;
   x_msg_count                                                  NUMBER;
   x_msg_data                                                   VARCHAR2 ( 2000 ) ;
   x_cust_acct_site_id                                          NUMBER;
BEGIN
   p_cust_acct_site_rec.cust_account_id   := 118742;
   p_cust_acct_site_rec.party_site_id     := 225635;
   --p_cust_acct_site_rec.language          := 'US';
   --The Above Language Column is Obsolete in R12
   p_cust_acct_site_rec.created_by_module := 'TCA_V2_API';
   mo_global.init ( 'AR' ) ;
   mo_global.set_org_context ( 204, NULL, 'AR' ) ;
   fnd_global.set_nls_context ( 'AMERICAN' ) ;
   mo_global.set_policy_context ( 'S', 204 ) ;
   hz_cust_account_site_v2pub.create_cust_acct_site (    'T'                   ,
                                                         p_cust_acct_site_rec  ,
                                                         x_cust_acct_site_id   ,
                                                         x_return_status       ,
                                                         x_msg_count           ,
                                                         x_msg_data
                                                    ) ;
   dbms_output.put_line ( 'x_return_status = '||SUBSTR ( x_return_status, 1, 255 ) ) ;
   dbms_output.put_line ( 'x_msg_count = '||TO_CHAR ( x_msg_count ) ) ;
   dbms_output.put_line ( 'Customer Account Site Id is = '||TO_CHAR ( x_cust_acct_site_id ) ) ;
   dbms_output.put_line ( 'x_msg_data = '|| SUBSTR ( x_msg_data, 1, 255 ) ) ;
   IF x_msg_count >1 THEN
      FOR I      IN 1..x_msg_count
      LOOP
         dbms_output.put_line ( I||'.'||SUBSTR ( FND_MSG_PUB.Get ( p_encoded=> FND_API.G_FALSE ), 1, 255 ) ) ;
      END LOOP;
   END IF;
EXCEPTION
WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE ( 'Error: '||SQLERRM ) ;
END;

/
COMMIT;
---------------------------------------------------------------------------------------------------------------------
///////////////////////////////////// VERIFY ACCOUNT SITE ID //////////////////////////////////////////////
SELECT *
  FROM hz_cust_acct_sites_all
WHERE cust_acct_site_id = 11297;
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------

//////////////////////////////////////TCA Create Location Assignment API /////////////////////////////////

Name of the API : UPDATE CUSTOMER PROFILE
Base Tables Affected : HZ_LOC_ASSIGNMENTS
PL/SQL Procedure used : CREATE_LOC_ASSIGNMENT
Package Used : HZ_TAX_ASSIGNMENT_V2PUB
--------------------------------------------
DECLARE
   p_location_id       NUMBER;
   p_created_by_module VARCHAR2 ( 500 ) ;
   p_application_id    NUMBER;
   x_return_status     VARCHAR2 ( 2000 ) ;
   x_msg_count         NUMBER;
   x_msg_data          VARCHAR2 ( 2000 ) ;
   x_loc_id            NUMBER;
BEGIN
   p_location_id       := 26478;
   p_created_by_module := 'TCA_V2_API';
   mo_global.init ( 'AR' ) ;
   mo_global.set_org_context ( 204, NULL, 'AR' ) ;
   fnd_global.set_nls_context ( 'AMERICAN' ) ;
   mo_global.set_policy_context ( 'S', 204 ) ;
   hz_tax_assignment_v2pub.create_loc_assignment ( 'T', p_location_id, 'T', p_created_by_module, p_application_id, x_return_status, x_msg_count, x_msg_data, x_loc_id );
   dbms_output.put_line ( 'x_return_status = '||SUBSTR ( x_return_status, 1, 255 ) ) ;
   dbms_output.put_line ( 'Location Id = '||TO_CHAR ( p_location_id ) ) ;
   dbms_output.put_line ( 'Created By Moudle = '|| SUBSTR ( p_created_by_module, 1, 30 ) ) ;
   dbms_output.put_line ( 'x_msg_count = '||TO_CHAR ( x_msg_count ) ) ;
   dbms_output.put_line ( 'x_msg_data = '|| SUBSTR ( x_msg_data, 1, 255 ) ) ;
   IF x_msg_count >1 THEN
      FOR I      IN 1..x_msg_count
      LOOP
         dbms_output.put_line ( I||'.'||SUBSTR ( FND_MSG_PUB.Get ( p_encoded=> FND_API.G_FALSE ), 1, 255 ) ) ;
      END LOOP;
   END IF;
EXCEPTION
WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE ( 'Error: '||SQLERRM ) ;
END;


-------------------------------------------------------------------------------------------
//////////////////////// VERIFY LOCATION ASSIGNMENT //////////////////////////////////////

SELECT *
  FROM hz_loc_assignments
WHERE location_id = 26478;
--------------------------------------------------------------------------

Query to get the wip quantity from backend

Query to get the wip quantity for an item from backend

SELECT SUM(A.B)
              FROM
                (SELECT sum(nvl(wdj.start_quantity,0) - (nvl(wdj.quantity_scrapped,0) + nvl(wdj.quantity_completed, 0)))  B
                FROM MTL_SUPPLY ms ,
                  PO_DISTRIBUTIONS_ALL pod,
                  WIP_ENTITIES WE,
                  wip_discrete_jobs wdj
                WHERE ms.po_header_id     = pod.po_header_id
                AND ms.po_line_id         = pod.po_line_id
                AND pod.WIP_ENTITY_ID     = WE.WIP_ENTITY_ID
                AND ms.po_distribution_id = pod.po_distribution_id
                AND item_id               = 787036
                AND we.wip_entity_id = wdj.wip_entity_id
                AND wdj.status_type = 3 -- Released
                UNION ALL
                SELECT SUM(DECODE (s.supply_type_code, 'SHIPMENT', s.to_org_primary_quantity, s.to_org_primary_quantity )) B
                FROM mtl_system_items i,
                  mtl_parameters p,
                  bom_calendar_dates c,
                  mtl_supply s,
                  mfg_lookups ml,
                  (SELECT DECODE (ms.po_header_id, NULL, DECODE (ms.supply_type_code, 'REQ', DECODE ( ms.from_organization_id, NULL, 18, 20), 12 ), DECODE (ms.supply_type_code, 'SHIPMENT', 35, 'RECEIVING', 36, 1) ) supply_demand_source_type,
                    poh.segment1 Identifier,
                    supply_source_id
                  FROM mtl_supply ms,
                    po_headers_all poh
                  WHERE 1              =1
                  AND poh.po_header_id = ms.po_header_id
                  ) sx
                WHERE 1                     = 1
                AND s.supply_source_id      = sx.supply_source_id
                AND ml.lookup_type          = 'MRP_SUPPLY_DEMAND_SOURCE_TYPE'
                AND ml.lookup_code          = sx.supply_demand_source_type
                AND ( ( s.req_header_id    IS NULL
                AND s.po_header_id         IS NULL )
                OR ( s.req_header_id        = s.req_header_id
                AND s.from_organization_id IS NOT NULL )
                OR ( s.supply_type_code     = 'REQ'
                AND s.from_organization_id IS NULL )
                OR s.po_header_id           = s.po_header_id )
                AND s.to_organization_id    = 4
                AND s.item_id               = 787036
                AND s.destination_type_code = 'INVENTORY'
                AND ( s.to_subinventory    IS NULL
                OR EXISTS
                  (SELECT 'X'
                  FROM mtl_secondary_inventories s2
                  WHERE s2.organization_id  = s.to_organization_id
                  AND s.to_subinventory     = s2.secondary_inventory_name
                  AND s2.inventory_atp_code = 1
                  AND s2.availability_type  = s2.availability_type
                  ) )
                AND i.organization_id           = s.to_organization_id
                AND i.inventory_item_id         = s.item_id
                AND p.organization_id           = s.to_organization_id
                AND p.calendar_code             = c.calendar_code
                AND p.calendar_exception_set_id = c.exception_set_id
                AND NOT EXISTS
                  (SELECT 'X'
                  FROM oe_drop_ship_sources odss
                  WHERE DECODE (s.po_header_id, NULL, s.req_line_id, s.po_line_location_id ) = DECODE (s.po_header_id, NULL, odss.requisition_line_id, odss.line_location_id )
                  )
                AND c.calendar_date = TRUNC (s.expected_delivery_date)
                ) A;

Inbound Interface complete process using sql loader for gl interface and Purchase Order interface in oracle apps

Inbound Interface

Process Steps:

)    Stage Table Creation

)    Control file development

)    PL/SQL Program

)    Submit Standard Program

   ·         Inbound interface will be used to upload the data from legacy system in to Oracle applications base tables.

·         We will receive the flat file from the client then we will create staging table upload the data from flat file in to stage table.

·         Develop the PL/SQL program to validate the data weather it is valid or not, if it is valid we will insert in to interface table, if it is not valid we will insert in to error table.

·         Once the data is available in interface table submit the standard program from SRS window, and we will transfer the data from interface table to base tables.

·        GL_INTERFACE_TABLE

STATUS: This column will accept any string but we will always insert standards string called new it indicates that we are brining new data in to General Ledge Applications.

SET_OF_BOOKS_ID:  We have to enter the appropriate set of books ID, it should be valid set of books id is available in GL_SETS_BOOKS table it is valid, otherwise i8t is invalid.

USER_JE_SOURCES_NAME: We have to enter the journal sources name for the transaction we can find all the valid source names in the tabled called GL_JE_SOURCES.

USER_JE_CATEGORY_NAME: WE have to find out weather journal category is available in the GL_JE_CATEGORIES table.  It is is available then we will insert, otherwise we will reject.

CURRENCY_CODE:  We have to enter the valid currency code in FND_CURREINCES table we can find out weather it is valid or not.

ACCOUNTING_DATE and CREATION_DATE: Both columns will accept valid date but that date should be less than or equal to System date.

CREATED_BY: WE have to enter valid user_id from FND_USER table we can identify weather it is valid user_id or not.

PERIOD_NAME: We have to enter valid period name and period should be in the open status from GL_PERIODS table we can find out weather it is valid period or not,  from GL_PERIOD_STATUS table we can find out period is in the open status or not.

ENTERED_DR and ENTERED_CR:  Both columns will accept positive number Debit and credit amount, both debit and credit should be equal otherwise account will be imported as suspense account.

GROUP_ID: WE will enter unique group number while importing from interface table to base table it will be used as parameter.

 ACTUAL_FLAG:  This column will accept single character either ‘A’ or ‘B’ or ‘E’ a- Actual amounts, B- Budget Amounts, E- Encumbrance Amounts

Reference 1                                        -           Batch Name

Reference 2                                        -           Batch Description

Reference 3                                        -           Don’t Enter any value

Reference 4                                        -           Journal Entry Name

Reference 5                                        -           Journal Entry Description

Reference 11 to 20                 -           Don’t enter any values.

Chart_of_accounts_id                        -           Don’t enter any values.

Transationc_date                    -           Don’t enter any values.

Je-Batch_id                                         -           Don’t enter any values.

Je_header_id                          -           Don’t enter any values.

 Pre requisites for GL Interface:

   1)    Set of books should be defined (Currency, Calendar, Chart of Accounts)

   2)    2) Currency Conversion Rates needs to be defined.

   3)    Accounting Period should be defined and also opened

   4)    Source name and as well as category name should be defined.

 Process Steps:

    1)    We have received flat file from client

    2)    We have created Staging table as per flat file structure

   3)    Developed Control file and uploaded data

   4)    Developed PL/SQL Program to upload the data from stage into interface table

  i.  declare Cursor

ii. open cursor

iii.                Validate each record

iv.               If no invalid record then insert into interface table.

    

      Temporary Table Creation

Create Table GL_INTERF_TEMP (

STATUS                                                Varchar2(10),

SET_OF_BOOKS_ID         Number(8),

ACCOUNTING_DATE,     Date,

CURRENCY                        Number(8),

DATE_CREATED                              Date,

CREATED_BY                    Number(8),

ACTUAL_FLAG                 Varchar2(1),

CATEGORY                         Varchar2(10),

SOURCE                                               Varchar2(10),

CURR_CONVERSION      Number(8),

SEGMENT1                         Varchar2(100),

SEGMENT2                         Varchar2(100),

SEGMENT3                         Varchar2(100),   

SEGMENT4                         Varchar2(100),

SEGMENT5                         Varchar2(100),

ENTERED_DR                    Number(8),

ENTERED_CR                    Number(8), 

ACCOUNTED_DR                             Number(8),

ACCOUNTED_CR                             Number(8),

GROUP_ID                           Number(8) );

  

 Control File Creation

 

 OPTIONS (SKIP = 1 )

LOAD DATA

INFILE '&1'

INSERT into TABLE GL_INTERF_TEMP 

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS

TRAILING NULLCOLS

(STATUS,

 SET_OF_BOOKS_ID,

 ACCOUNTING_DATE,

 CURRENCY,

 DATE_CREATED,

 CREATED_BY,

 ACTUAL_FLAG,

 CATEGORY,

 SOURCE,

 CURR_CONVERSION,

 SEGMENT1,

 SEGMENT2,

 SEGMENT3,

 SEGMENT4,

 SEGMENT5,

 ENTERED_DR,

 ENTERED_CR, 

 ACCOUNTED_DR,

 ACCOUNTED_CR,

 GROUP_ID)

CREATE A CONCURRENT PROGRM FROM SRS WINDOW THE EXCECUTABLE NAME MUST BE SQL LOADER .

ADD A PARAMETER TO DYNAMICALLY LOAD THE DATA USING CSV FILE OR .txt FILE.

AFTER LOADING THE DATA IN TO STAGING TABLE i.e in GL_INTERF_TEMP 

Creation of PL/SQL procedure – to transfer the data from staging table to interface table after validation

CREATE OR REPLACE PROCEDURE  GL_IN_PRO(              Errbuf  OUT VARCHAR2,

                                                                                                                Retcode OUT VARCHAR2)

IS

  -- cursor declaration

  CURSOR gl_cur IS

    SELECT 

        status    ,

        set_of_books_id   ,

        accounting_date   ,

        currency   ,

        date_created   ,

        created_by    ,

        actual_flag    ,

        category       ,

        source     ,

        curr_conversion     ,

          segment1     ,

          segment2        ,

          segment3        ,

          segment4        ,

          segment5        ,

          entered_dr      ,

          entered_cr      ,

          accounted_dr    ,

          accounted_cr    ,

          group_id       

          FROM GL_INT_TEMP;

l_currencycode    VARCHAR2(25);

l_set_of_books_id              NUMBER(3);

l_flag                                      VARCHAR2(2);

l_error_msg                           VARCHAR2(100);

l_err_flag               VARCHAR2(10);

l_category             VARCHAR2(100);

L_USERID           NUMBER(10);

BEGIN

 DELETE FROM  gl_interface;

 COMMIT;

FOR rec_cur IN gl_cur LOOP

     l_flag:='A';

     l_err_flag:='A';

--This  PL/SQL Block will do the currency validation

 --end of the currency validation

--Category Column Validation

BEGIN

 SELECT USER_JE_CATEGORY_NAME

 INTO   l_CATEGORY

 FROM   GL_JE_CATEGORIES

 WHERE  USER_JE_CATEGORY_NAME = REC_CUR.Category;

 EXCEPTION

     WHEN OTHERS THEN

            l_category:=NULL;

            l_flag:='E';

            l_error_msg:='Category does not exist ';

 END;

Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);

 --End Category Column Validation

 --User ID column validation

BEGIN

SELECT USER_ID

INTO   L_USERID

FROM   FND_USER

WHERE  USER_ID = REC_CUR.created_by;

EXCEPTION

WHEN OTHERS THEN

            L_userid:=NULL;

            l_flag:='E';

            l_error_msg:='User ID does not exist ';

 END;

 Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);

 --End of Created_by OR UserID column Validation

 --Set of  books Validation

 BEGIN

     SELECT set_of_books_id

     INTO   l_set_of_books_id

     FROM   GL_SETS_OF_BOOKS

     WHERE set_of_books_id=rec_cur.set_of_books_id;

     EXCEPTION

     WHEN OTHERS THEN

            l_set_of_books_id:=NULL;

            l_flag:='E';

            l_error_msg:='set of Books ID does not exist ';

 END;

 Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);

-- End Set of  books Validation

 --Status Column validation

/* BEGIN

 IF rec_cur.status = 'NEW' THEN

 l_flag:= 'A';

 ELSE

 l_flag:= 'E';

 Fnd_File.put_line (Fnd_File.LOG,'Status column has got invalid data');

 END IF;

 END;*/

 BEGIN

     SELECT currency_code

     INTO   l_currencycode

     FROM   fnd_currencies

     WHERE currency_code=rec_cur.currency

     AND currency_code='USD';

     EXCEPTION

      WHEN OTHERS THEN

            l_currencycode:=NULL;

            l_flag:='E';

            l_error_msg:='currency code does not exists';

 END;

Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);

Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);

--End of Actual Flag Column validation

IF   l_flag!='E' THEN

Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE');

INSERT INTO gl_interface(status,

      set_of_books_id,

      accounting_date,

      currency_code,

      date_created,

      created_by,

      actual_flag,

      user_je_category_name,

      user_je_source_name,

      user_currency_conversion_type,

      segment1,

      segment2,

      segment3,

      segment4,

      segment5,

      entered_dr,

      entered_cr,

      accounted_dr,

      accounted_cr,

      group_id)

      VALUES

      (rec_cur.status    ,

       rec_cur.set_of_books_id   ,

       rec_cur.accounting_date   ,

       rec_cur.currency    ,

       rec_cur.date_created   ,

       rec_cur.created_by    ,

       rec_cur.actual_flag    ,

       rec_cur.category       ,

       rec_cur.source     ,

       rec_cur.curr_conversion                ,

       rec_cur.segment1  ,

       rec_cur.segment2  ,

       rec_cur.segment3  ,

       rec_cur.segment4  ,

       rec_cur.segment5  ,

       rec_cur.entered_dr   ,

       rec_cur.entered_cr   ,

       rec_cur.accounted_dr  ,

       rec_cur.accounted_cr  ,

       rec_cur.group_id);

 END IF;

 l_flag:=NULL;

 l_error_msg:=NULL;

  END LOOP;

COMMIT;

END  GL_IN_PRO;

Purchase Order Inbound Interface

Pre Requisites

   1)    Supplier sites contact details should be defined

   2)    Organization structure should be defined

   3)    Ship to bill to locations needs to be defined

   4)    Set of books needs to be defined

   5)    Items, item categories, UOM needs to be defined

   6)    Employee creation, buyer setup should be defined.

Process Steps

   1)    Create the Staging tables

   2)    Develop the Control files and register as concurrent program

   3)    Develop the PL/SQL Program and write the validations and insert into interface table

   4)    Run the standard program called Import Standard Purchase orders from PO Responsibility

                    Parameter :         Default Buyer                          : Null

                                                Create or update items           : No

                                                 PO Status                              : APPROVED

                                                 Batch ID                                 : 13

                                                            (We can get from headers interface table)

   5)    Take the Request ID execute following query we can get the PO numbers

                      select segment1 Ponumber

                      from   po_headers_all

                      where  request _id = 145233;

    6)    Go to the PO Application and Query the PO from as per the PO number.

Control File: - PO_HEADERS_INTERFACE

load data

infile *

insert  into table XX_HEADERS

fields terminated by ","  optionally enclosed by '"'

TRAILING NULLCOLS

( INTERFACE_HEADER_ID

  ,BATCH_ID          

  ,ACTION              

  ,ORG_ID                   

  ,DOCUMENT_TYPE_CODE       

  ,CURRENCY_CODE            

  ,AGENT_NAME                

  ,VENDOR_NAME              

  ,VENDOR_SITE_CODE         

  ,SHIP_TO_LOCATION         

  ,BILL_TO_LOCATION

  ,APPROVAL_STATUS

  ,FREIGHT_CARRIER

  ,FOB

  ,FREIGHT_TERMS

)

Control File: - PO_LINES_INTERFACE

load data

infile *

insert into table  XX_LINES

fields terminated by ","  optionally enclosed by '"'

TRAILING NULLCOLS

( interface_header_id

  ,interface_line_id

  ,LINE_NUM                       

  ,SHIPMENT_NUM                   

  ,LINE_TYPE                       

  ,ITEM

  ,ITEM_DESCRIPTION

  ,item_id                          

  ,UOM_CODE                       

  ,QUANTITY                       

  ,UNIT_PRICE                     

  ,SHIP_TO_ORGANIZATION_CODE      

  ,SHIP_TO_LOCATION               

   ,list_price_per_unit)

Procedure for Insertions of data from Interface tables to Base tables after validating.

CREATE OR REPLACE PROCEDURE PO_Int1(Errbuf  OUT VARCHAR2,

                                     Retcode OUT VARCHAR2) AS

CURSOR c1 IS SELECT * FROM PO_HEADER_TABLE;

CURSOR c2 IS SELECT * FROM PO_LINE_TABLE;

l_vendor_id number(10);

l_item      varchar2(150);

l_flag     varchar2(4) default 'A';

l_msg      varchar2(200);

l_site_code     varchar2(100);

l_curr_code     varchar2(10);

l_org_id     number(6);

BEGIN

DELETE FROM PO_HEADERS_INTERFACE;

DELETE FROM PO_LINES_INTERFACE;

COMMIT;

FOR x1 IN c1 LOOP

 BEGIN

   SELECT vendor_id

   INTO   l_vendor_id

   FROM po_vendors

   WHERE vendor_name = x1.VENDOR_NAME;

--   AND   ORG_ID = Fnd_Profile.Value('ORG_ID');

 EXCEPTION

   WHEN OTHERS THEN

    l_flag        := 'E';

    l_vendor_id := 0;

    l_msg       := 'Vendor id is Not in SYSTEM';

 END;

 --Vendor Site code  Validation

/*  begin

    select  vendor_site_code

    into    l_site_code

    from    po_vendor_sites_all

    where   vendor_site_code = x1.vendor_site_code;

    EXCEPTION

       WHEN OTHERS THEN

        l_flag        := 'E';

     l_site_code   := 0;

                 l_msg         := 'Vendor Site Code is Not in SYSTEM';

    END;*/

--End of Site Code Validation

--Currency Code Validation

  Begin

            select currency_code

   into   l_curr_code

   from   fnd_currencies

   where  currency_code = x1.CURRENCY_CODE;

   EXCEPTION

      WHEN OTHERS THEN

        l_flag        := 'E';

     l_curr_code   := 0;

                 l_msg         := 'Currency Code is Invalid';

    END;

--End of te Currency Validation

--Operating Unit ID Validation

Begin

   select organization_id

   into   l_org_id

   from   hr_operating_units

   where  organization_id = x1.org_id;

   EXCEPTION

      WHEN OTHERS THEN

        l_flag        := 'E';

     l_org_id      := 0;

                 l_msg         := 'Invalid Organization ID';

    END;

--End of the ORG ID Validation

  IF l_flag != 'E' THEN

     INSERT INTO po_headers_interface

   (

   INTERFACE_HEADER_ID

  ,BATCH_ID

  ,ACTION

   ,ORG_ID

  ,DOCUMENT_TYPE_CODE

  ,CURRENCY_CODE

  ,AGENT_NAME

  ,VENDOR_NAME

  ,VENDOR_SITE_CODE

  ,SHIP_TO_LOCATION

  ,BILL_TO_LOCATION

  ,creation_date

  ,APPROVAL_STATUS

  ,APPROVED_DATE

  ,FREIGHT_TERMS

)

VALUES

(

   x1.INTERFACE_HEADER_ID

  ,x1.batch_id

  ,x1.action

  ,x1.org_id                                  

  ,x1.document_type_code                           

  ,x1.CURRENCY_CODE                                

  ,x1.AGENT_NAME                

  ,x1.VENDOR_NAME

  ,x1.VENDOR_SITE_CODE

  ,x1.SHIP_TO_LOCATION

  ,x1.BILL_TO_LOCATION

  ,SYSDATE-10                            

  ,x1.APPROVAL_STATUS

  ,SYSDATE                              

 ,x1.FREIGHT_TERMS

 );

end if;

END LOOP;

FOR x2 IN c2  LOOP

l_flag := 'A';

--Item Validation

begin

  select segment1

  into   l_item

  from   mtl_system_items_b

     where  segment1        = x2.item

     AND    ORGANIZATION_ID = fnd_profile.value('ORG_ID');

exception

when others then

    l_flag        := 'E';

    l_vendor_id := 0;

    l_msg       := 'Item is not valid Item';

 END;

--End of the Item Validation

 if  l_flag != 'E' then

 INSERT INTO PO_LINES_INTERFACE

 (

  INTERFACE_LINE_ID

  ,INTERFACE_HEADER_ID

  ,LINE_NUM

  ,SHIPMENT_NUM

  ,LINE_TYPE

  ,ITEM

  ,ITEM_DESCRIPTION

  ,item_id

  ,UOM_CODE

  ,QUANTITY

  ,UNIT_PRICE

  ,SHIP_TO_ORGANIZATION_CODE

  ,SHIP_TO_LOCATION

  ,NEED_BY_DATE

  ,PROMISED_DATE

  ,list_price_per_unit

)

VALUES

(

   x2.INTERFACE_LINE_ID

  ,x2.INTERFACE_HEADER_ID

  ,x2.LINE_NUM

  ,x2.SHIPMENT_NUM

  ,x2.LINE_TYPE

  ,x2.ITEM

  ,x2.ITEM_DESCRIPTION

  ,x2.item_id

  ,x2.UOM_CODE

  ,x2.QUANTITY,

   X2.UNIT_PRICE,

  X2.SHIP_TO_ORGANIZATION_CODE,

  X2.SHIP_TO_LOCATION,

  X2.NEED_BY_DATE,

  X2.PROMISED_DATE,

  X2.LIST_PRICE_PER_UNIT);

END IF;

END LOOP;

COMMIT;

END PO_INT1;