Thursday, 14 July 2011

FAQ's For Interviews

Answer sheet (Test No.1)

SET OF BOOKS


1.What are the different types of value set (or) validation types? Explain each in brief?

The validation types are 8 they are
  1. Independent                      : the nature of segment is independent
2.    Dependent                        : this segment depends on independent segment
  1. None                               : none is free field   
  2. Pair                                  : pair means range of segments
  3. Table                               : table means calculation purpose  
  4. Special                             : special means based on conditions
  5. Translatable independent     : we use it other than English language
  6. Translatable dependent       : we use it other than English language

2.Can I increase/decrease the size of value set?

No, only we can increase the value set size but not decrease

3.What are the difference between KFF and DFF?

Sl. No.

Key Flex field

Descriptive Flex field
1
This field is Intelligent Flex field and Mandatory and there are 33 KFF in Oracle finance out of which accounting structure is the one of the KFF in GL module
This flex field is optional to capture the additional information. There is no such limit
2
KFF is application Specific
DFF is form Specific
3.
Cross Validation Rules are Specific to KFF
Cross validation are not applicable to DFF
4.
Aliases are applicable to KFF
Not applicable to DFF
5.
KFF is Inbuilt (Oracle Provides)
We have to define DFF.

4.How can we identify whether DFF is allowed for a specific form or not?

The DFF is available to a form where the symbol  “ [ ] ” (square bracket) is displayed

5.Explain the difference between flex field qualifiers and segment qualifiers.

Flex Field Qualifier identifies the segment (to which it is assigned) with a specific property. There are 4 types of flexfield qualifiers that can be assigned to segments, namely,    
  1. Balancing segment Qualifier
  2. Cost center segment Qualifier
  3. Natural account segment Qualifier
  4. Intercompany segment Qualifier
è1 & 3 are mandatory flexfield qualifiers
èOne Segment can have more than one qualifier not vice versa.
   
Segment Qualifier identifies the segment values with a specific property.
There are five types of segment qualifiers assigned to Natural account segment qualifier, namely,    
  1. Allow budgeting
  2. Allow posting
  3. Control account
  4. Account type
  5. Reconciliation flag

Other then above flexfield qualifier others show only two segment qualifiers i.e.
  1. Allow budgeting
  2. Allow posting

6.What are the components of set of books?

The components of set of books are 3C’s + 6 mandatory accounts + future period
     The 3c’s are
  1. Chart of accounts
  2. Calendar
  3. Currency

     The 6 mandatory accounts are
  1. Retained earnings
  2. Net income
  3. Suspense account
  4. Translation adjustment account
  5. Rounding difference
  6. Reserve for encumbrance
     
 And Future period 

7.What are the different types of set of books?

Two types of set of books
  1. Primary set of books
  2. Reporting set of books 

8.What are different Year types?

Two types of Year types
  1. Calendar year
  2. Fiscal year

9.What are the different statuses of accounting periods?

The statuses of accounting periods are
  1. Open
  2. Closed
  3. Permanently closed
  4. Future enterable
  5. Never open

10.Why does an accounting period does not appear in the accounting periods list of values on the journal entry form?

The periods which have the status of closed, permanently closed and/or never open will not appear in accounting periods list of values on the Journal entry form.

11.Whether is it allowed for entering negative amount in journal lines?

A) Yes, it is possible but after posting it will be shown in brackets “< >”

12.Is it allowed to reverse, a reversed journal entry?

Yes, it is allowed to reverse journal entry. We can reverse a Journal entry only once.

13.Can I post the reversal entry in before period of actual journal entry?

No, reversal entry should be in current date or future date in the accounting periods of actual Journal entry.

14.What is the formula for funds available?

Funds available = Budget – Encumbrance - Actual

15.Whether system allows the reversing journal to posted even if the original journal is posted or not?

Yes, The original journal is posted or not. The reversal entry can be posted. Reversal meaning is to nullify the particular account balance.

16.How will it display if I use my segment separator in description?

It will appear as “?“ mark
Ex: my segment values are company1, dept1, A/C
We can select the segment separator is “ / “ in value set at the time of journal entry   
posting the A/C in between “/ “ is appear ? in description.

17.What do you mean by Translation?

Translation means we can translate actual and budget account balances from functional currency to Reporting currency. Actual we perform translation for reporting purpose.  We need to define average, period end and historical rates. Average rate will be applicable to expenses and revenues, Period end rate will be applicable to Assets and Liabilities and Historical rate for Owners equity. Run translation after we have completed all journal activity for an accounting period. If you post additional journal entries or change your translation rates after running translation for a period, you must retranslate. Additionally, if you change the account type for an account Segment value and want to retranslate your actual account balances, you must re-enter or change the period-end and average exchange rates for the periods you want to retranslate.




18.What are the rules for Translation?

Rule for translation
  1. Prior period and future period should be open
  2. Define Average and Period end rates
We cannot perform translation for the first period.

19.How can I deactivate the segment value?

We can deactivate the segment value by uncheck the enable box of the particular segment value in value set of the particular segment.

20.What type of accounts will display in list of values while entering retained earnings account in set of books?

Only Balance sheet items are display like Assets, Liabilities and Owner’s equity.

21.Briefly list the steps for foreign currency journals.

Before entering the foreign currency journals we need
  1. Conversion type
  2. Conversion rate
  3. Conversion Effective date

22.An entire batch was reversed and posted, while trying to reverse and post just one journal entry in the batch. How can this be corrected?

Leave the wrong reverse entry and reverse all other reversed entries manually and post them.

23.Can a Flex field Qualifies be changed after it has been created?

Cannot change.

24.Can different set of books share the same value set?

Yes, the value set is an independent because the value set is share different set of books.

25.What are the three options available in oracle application with respect to foreign currencies?

Three options are
  1. Conversion
  2. Translation
  3. Revaluation



Posted by at No comments:

GL interface using bulk collect

Hi Friends,

These are the mandatory columns in gl interface....



1)Once we get the data into the stage table we have to transfer from stage table into
 Interface table by using PL/SQL Program
 Inside this program we write
 1)Cursor   (to select data from stage table)
 2)Validate Data
 3)Insert Statement(To Insert Into Interface table.

Note:1) If Record is valid record then we will insert into interface table other wise
        we will insert into Error tables.
     2)Before Inserting the Data into Interface tables first we have to understand the
       Interface table structure and we should know waht data is valid and what data
      is not valid for the all Mandatroy columns.

2)Once the data is Inserted into the Interface table . Then we will submit concurrent
  program to transfer the data from interface table to Base Tables.

In this Interface Interface table is GL_INTERFACE
Mandatroy Columns:
==================
1)STATUS
2)ACCOUNTING_DATE
3)CURRENCY_CODE
4)CREATED_BY
5)CREATION_DATE
6)USER_JE_SOURCE_NAME
7)USER_JE_CATEGORY_NAME
8)ACTUAL_FLAG
9)ENTERED_DR
10)ENTERED_CR
11)GROUP_ID
12)PERIOD_NAME
13)SET_OF_BOOKS_ID


1)Status Column will accept any Data. but we will insert always standard string called
         "NEW". It means that we are bringing new data into Oracle Applications.

2)ACCOUNTING_DATE  : Column will accept valid acc_date as per the accounting Periods
3)CURRENCY_CODE    : from FND_CURRENCIES table we can find out wether CUrrency Code is
            valid or Not if Currecny code is available in the Table and enabled it is
            valid.otherwise Invalid
4)CREATED_BY  : IS nothing but UserID we have to find wether  USerID is valid or not
               By using FND_USER table we can find out wether it is valid or not.
5)CREATION_DATE : Should be valid date
                   date should be <= SYSDATE
6)USER_JE_SOURCE_NAME: Valid Source name
                 By using GL_JE_SOURCES table  we can find wether valid source or not.

7)USER_JE_CATEGORY_NAME : Will accept valid cvategory name
        By using GL_JE_CATEGORIES table we can find out wether valid category or Not.
8)ACTUL_FLAG    : This Column will accept single Character
                  Either 'A' 'B' 'E'
                  A=Actuval Amounts
                  B=Budeget Amounts
                  E=Encumbrance Amounts
9)ENTERED_DR
10)ENTERED_CR : Both Columns we accept Numbers Only but same number we have to insert
                into both the columns.
      Always ENTERED_CR = ENTERED_DR . Otherwise Suspense Account will be
               Created.
11)GROUP_ID   : Will accept any Number
12)period_name : Will Accept valid period and that period should be in the Open status
                By using GL_PERIODS we can find wether Period is there or not
                         GL_PERIOD_STATUSES table we can find wether it is in "Open"
                status or Not.
13)SET_OF_BOOKS_ID  : This column will accept valid set of Books ID. By using
              GL_SETS_OF_BOOKS table we can fnd out wether valuid set_of_books_id or
              not.
Program :

CREATE OR REPLACE PACKAGE GL_INTERFACE_PKG
AS
PROCEDURE GL_INTERFACE_PROC(ERRBUF OUT VARCHAR2, RETCODE OUT VARCHAR2)
END GL_INTERFACE_PKG;

/

CREATE OR REPLACE PACKAGE BODY APPS.gl_interface_pkg
AS
   PROCEDURE gl_interface_proc (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
   IS
      -- cursor declaration
      CURSOR gl_cur
      IS
         SELECT *
           FROM xtg_gl_interface_stg;

      TYPE gl_data_tbl IS TABLE OF xtg_gl_interface_stg%ROWTYPE
         INDEX BY BINARY_INTEGER;

      rec_cur             gl_data_tbl;
      l_currencycode      VARCHAR2 (25);
      l_set_of_books_id   NUMBER (5);
      l_flag              VARCHAR2 (2);
      l_error_msg         VARCHAR2 (100);
      l_err_flag          VARCHAR2 (10);
      l_category          VARCHAR2 (100);
      l_userid            NUMBER (10);
      l_count             NUMBER (9)     DEFAULT 0;
   BEGIN
      DELETE FROM gl_interface;

      COMMIT;

      OPEN gl_cur;
                          
      FETCH gl_cur
      BULK COLLECT INTO rec_cur;
    
      CLOSE gl_cur;

      FOR rec_cur IN gl_cur
      LOOP
         l_count := l_count + 1;
         l_flag := 'A';

--Category Column Validation
         BEGIN
            SELECT user_je_category_name
              INTO l_category
              FROM gl_je_categories
             WHERE user_je_category_name = rec_cur.user_je_category_name;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_flag := 'E';
               l_error_msg := 'Category does not exist ';
               fnd_file.put_line
                                (fnd_file.LOG,
                                    'Inserting data into the Interface TABLE'
                                 || '-'
                                 || l_count
                                 || ' '
                                 || l_error_msg
                                );
         END;

         --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_flag := 'E';
               l_error_msg := 'User ID does not exist ';
               fnd_file.put_line
                                (fnd_file.LOG,
                                    'Inserting data into the Interface TABLE'
                                 || '-'
                                 || l_count
                                 || ' '
                                 || l_error_msg
                                );
         END;

         --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_flag := 'E';
               l_error_msg := 'set of Books ID does not exist ';
               fnd_file.put_line
                                (fnd_file.LOG,
                                    'Inserting data into the Interface TABLE'
                                 || '-'
                                 || l_count
                                 || ' '
                                 || l_error_msg
                                );
         END;

--Cuurency Code Validation
         BEGIN
            SELECT currency_code
              INTO l_currencycode
              FROM fnd_currencies
             WHERE currency_code = rec_cur.currency_code
               AND currency_code = 'USD';
         EXCEPTION
            WHEN OTHERS
            THEN
               l_flag := 'E';
               l_error_msg := 'currency code does not exists';
               fnd_file.put_line
                                (fnd_file.LOG,
                                    'Inserting data into the Interface TABLE'
                                 || '-'
                                 || l_count
                                 || ' '
                                 || l_error_msg
                                );
         END;

         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,
                         reference1, reference2,
                         reference4, reference5
                        )
                 VALUES (rec_cur.status, rec_cur.set_of_books_id,
                         rec_cur.accounting_date, rec_cur.currency_code,
                         SYSDATE,fnd_global.user_id,
                         rec_cur.actual_flag, rec_cur.user_je_category_name,
                         rec_cur.user_je_source_name,
                         rec_cur.user_currency_conversion_type,
                         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,
                         rec_cur.reference1, rec_cur.reference2,
                         rec_cur.reference4, rec_cur.reference5
                        );
         END IF;

         l_flag := NULL;
         l_error_msg := NULL;
      END LOOP;

      COMMIT;
   END;
END;
/
Please give ur comments to this post 
thank you
sandeep
Posted by at 8 comments:

Monday, 11 July 2011

Vendor Po Shipment Details Report


Source Code:



SELECT PV.segment1 "Vendor Number",
         PV.vendor_name "Vendor Name",
         PVSA.address_line1 "Vendor Address",
         PVSA.city "City",
         PHA.segment1 "PO Number",
         PHA.type_lookup_code "PO Type",
         PHA.creation_date "Creation Date",
         PHA.currency_code "Currency",
         HL1.location_code "Bill To Location",
         HL2.location_code "Ship To Location",
         HE.full_name "Buyer Name"
    FROM po_vendors PV,
         po_vendor_sites_all PVSA,
         po_headers_all PHA,
         po_line_locations_all PLA,
         hr_locations HL1,
         hr_locations HL2,
         hr_employees HE
   WHERE     PV.vendor_id = PVSA.vendor_id
         AND PHA.po_header_id = PLA.po_header_id
         AND PLA.ship_to_location_id = HL1.ship_to_location_id
         AND PLA.ship_to_location_id = HL2.ship_to_location_id
         AND PHA.bill_to_location_id = HL1.location_id
         AND PHA.ship_to_location_id = HL2.location_id
         AND PV.bill_to_location_id = HL1.location_id
         AND PV.bill_to_location_id = HL2.location_id
         AND HE.employee_id = PHA.agent_id
         AND PV.segment1 BETWEEN NVL (:P_PO_NUM_FROM, PV.segment1)
         AND NVL (:P_PO_NUM_TO, PV.segment1)
GROUP BY PV.segment1,
         PV.vendor_name,
         PVSA.address_line1,
         PVSA.city,
         PHA.segment1,
         PHA.type_lookup_code,
         PHA.creation_date,
         PHA.currency_code,
         HL1.location_code,
         HL2.location_code,
         HE.full_name
Posted by at 1 comment:

PO Conversion using bulk collect

Validations and Data Sample its your wish

1.Lines:

a. CREATE TABLE XXPO_LINES_STG
    (
     INTERFACE_HEADER_ID                 NUMBER
    ,INTERFACE_LINE_ID                   NUMBER
    ,LINE_NUM                            NUMBER
    ,LIST_PRICE_PER_UNIT                 NUMBER
    ,SHIPMENT_NUM                        NUMBER    
    ,LINE_TYPE                           VARCHAR2(100)  
    ,ITEM                                VARCHAR2(240)
    ,ITEM_DESCRIPTION                    VARCHAR2(240)
    ,item_id                             NUMBER          
    ,UOM_CODE                            VARCHAR2(30)
    ,QUANTITY                            NUMBER
    ,UNIT_PRICE                          NUMBER
    ,SHIP_TO_ORGANIZATION_CODE           VARCHAR2(40)
    ,SHIP_TO_LOCATION                    VARCHAR2(60)          
      );


b. load data
   infile '--------------------------------------------------'
   truncate into table  XXPO_LINES_STG
   fields terminated by ','
   optionally enclosed by '"'
   trailing nullcols
   (INTERFACE_HEADER_ID              
    ,INTERFACE_LINE_ID                
    ,LINE_NUM
    ,LIST_PRICE_PER_UNIT
    ,SHIPMENT_NUM                
    ,LINE_TYPE                              
    ,ITEM                                
    ,ITEM_DESCRIPTION          
    ,ITEM_ID                                  
    ,UOM_CODE                      
    ,QUANTITY
    ,UNIT_PRICE
    ,SHIP_TO_LOCATION                            
    )


c.  CREATE OR REPLACE PROCEDURE xxpo_lines_conv(x_errbuf  OUT VARCHAR2,
                   x_retcode OUT NUMBER)
                   IS
     TYPE test_array IS TABLE OF xxpo_lines_stg%ROWTYPE  INDEX BY BINARY_INTEGER;
  v_vendor_id                               VARCHAR2(100);
  v_interface_header_id                     VARCHAR2(100);
  v_item                                    VARCHAR2(150);
  v_vendors                                 test_array ;
BEGIN
SELECT *  BULK COLLECT INTO v_vendors FROM xxpo_lines_stg;
FOR i IN  1..v_vendors.COUNT
  LOOP
  INSERT INTO po_lines_interface
  (
    interface_line_id
        ,interface_header_id
  ,ship_to_location_id
  ,line_num
  ,shipment_num
  ,line_type
  ,item
  ,item_description
  ,item_id
  ,uom_code
  ,unit_of_measure
  ,quantity
  ,unit_price
  ,ship_to_organization_code
  --,ship_to_location
  ,need_by_date
  ,promised_date
  ,list_price_per_unit
)
VALUES
(  v_vendors(i).interface_line_id  --po_lines_interface_s.nextval
  , v_vendors(i).interface_header_id    --po_headers_interface_s.CURRVAL--v_interface_header_id
  ,144
  ,v_vendors(i).line_num
  ,v_vendors(i).shipment_num
  ,v_vendors(i).line_type
  ,v_vendors(i).item
  ,v_vendors(i).item_description
  ,v_vendors(i).item_id
  ,v_vendors(i).uom_code
  ,'Each'
  ,v_vendors(i).quantity
  ,v_vendors(i).unit_price
  ,v_vendors(i).ship_to_organization_code
  --,v_vendors(i).SHIP_TO_LOCATION
  ,SYSDATE                              --done
  ,SYSDATE                              --done
  ,v_vendors(i).list_price_per_unit
);
END LOOP;
COMMIT;
END;
/

2. Headers:

a. CREATE TABLE XXPO_HEADERS_STG
(
    INTERFACE_HEADER_ID            NUMBER                                          
    ,BATCH_ID                       NUMBER                                
    ,ACTION                         VARCHAR2(25)                              
    ,ORG_ID                         NUMBER      
    ,DOCUMENT_TYPE_CODE             VARCHAR2(25)
    ,CURRENCY_CODE                  VARCHAR2(15)
    ,AGENT_NAME                     VARCHAR2(240)      
    ,VENDOR_NAME                    VARCHAR2(240)
    ,VENDOR_SITE_CODE               VARCHAR2(15)
    ,SHIP_TO_LOCATION               VARCHAR2(60)  
    ,BILL_TO_LOCATION               VARCHAR2(60)
    ,FREIGHT_CARRIER                VARCHAR2(25)
    ,FOB                            VARCHAR2(25)
    ,FREIGHT_TERMS                  VARCHAR2(25)
  );

b. load data
infile '------------------------------------'
insert into table XXPO_HEADERS_STG
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)


c. CREATE OR REPLACE PROCEDURE xxpo_header_conv(x_errbuf  OUT VARCHAR2,
                   x_retcode OUT NUMBER)
                   IS
TYPE test_array IS TABLE OF xxpo_headers_stg%ROWTYPE  INDEX BY BINARY_INTEGER;
  v_vendor_id                          VARCHAR2(100);
  v_vendor_int_id                      VARCHAR2(100);
  v_vendors                            test_array ;
BEGIN
SELECT *  BULK COLLECT INTO v_vendors FROM  xxpo_headers_stg;
FOR i IN  1..v_vendors.COUNT
  LOOP                
    INSERT INTO po_headers_interface
    (
    interface_header_id
  ,batch_id
  ,action
    ,org_id
  ,document_type_code
  ,currency_code
  ,agent_id
  ,agent_name
  ,vendor_name
  ,vendor_site_code
  ,vendor_site_id
  ,ship_to_location
  ,bill_to_location
  ,creation_date
  ,approval_status
  ,approved_date
  ,freight_carrier
  ,fob
  ,freight_terms
)
VALUES
(
  v_vendors(i).interface_header_id--po_headers_interface_s.NEXTVAL
  ,v_vendors(i).batch_id       --po_headers_interface_s.CURRVAL
  ,v_vendors(i).action
  ,v_vendors(i).org_id                                    --"Puffer-Sweiven"
  ,v_vendors(i).document_type_code                            --done
  ,v_vendors(i).currency_code  
  ,61                                          --done
  ,v_vendors(i).agent_name                  --done
  ,v_vendors(i).vendor_name
  ,v_vendors(i).vendor_site_code
  ,1120
  ,v_vendors(i).ship_to_location
  ,v_vendors(i).bill_to_location
  ,SYSDATE-10                            --done
  ,v_vendors(i).approval_status
  ,SYSDATE                               --done
  ,v_vendors(i).freight_carrier
  ,v_vendors(i).fob
  ,v_vendors(i).freight_terms
  );
END LOOP;

COMMIT;
END;
/

3.  Distributions:

a. CREATE TABLE XXPO_DISTRIBUTION_STG
(
  INTERFACE_HEADER_ID                      NUMBER,
  INTERFACE_LINE_ID                        NUMBER,
  INTERFACE_DISTRIBUTION_ID                NUMBER,
  DISTRIBUTION_NUM                         NUMBER,
  QUANTITY_ORDERED                         NUMBER,  
  QTY_DELIVERED                            NUMBER,
  QTY_BILLED                               NUMBER,
  QTY_CANCELLED                            NUMBER,
  DELIVER_TO_LOCATION_ID                   NUMBER,
  DELIVER_TO_PERSON_ID                     NUMBER,
  SET_OF_BOOKS                             VARCHAR2(50),
  CHARGE_ACCT                              VARCHAR2(2000),
  AMOUNT_BILLED                            NUMBER
);


b. load data
infile '------------------------------------------'
truncate into table XXPO_DISTRIBUTION_STG
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(INTERFACE_HEADER_ID
,INTERFACE_LINE_ID
,INTERFACE_DISTRIBUTION_ID
  ,DISTRIBUTION_NUM                      
  ,QUANTITY_ORDERED                      
  ,QTY_DELIVERED                          
  ,QTY_BILLED                            
  ,QTY_CANCELLED                          
  ,DELIVER_TO_LOCATION_ID                
  ,DELIVER_TO_PERSON_ID                  
  ,SET_OF_BOOKS                          
  ,CHARGE_ACCT                            
  ,AMOUNT_BILLED)


c. CREATE OR REPLACE PROCEDURE xxpo_distribution_conv(x_errbuf  OUT VARCHAR2,
                   x_retcode OUT NUMBER)
                   IS
TYPE test_array IS TABLE OF xxpo_distribution_stg%ROWTYPE  INDEX BY BINARY_INTEGER;
    v_interface_header_id                     VARCHAR2(100);
    v_interface_line_id                       VARCHAR2(150);
    v_item                                    VARCHAR2(150);
    v_vendors                                 test_array ;
BEGIN
SELECT *  BULK COLLECT INTO v_vendors FROM  xxpo_distribution_stg;
FOR i IN  1..v_vendors.COUNT
  LOOP      
INSERT INTO po_distributions_interface
(
interface_header_id,
interface_line_id,
interface_distribution_id,
distribution_num,
quantity_ordered,
charge_account,
charge_account_id,
variance_account_id,
accrual_account_id,
quantity_delivered,
quantity_billed,
quantity_cancelled,
deliver_to_location_id,
deliver_to_person_id,
org_id
)
VALUES
(
v_vendors(i).interface_header_id  ,--po_headers_interface_s.currval,--v_interface_header_id,
  v_vendors(i).interface_line_id ,--po_lines_interface_s.currval,--v_interface_line_id,     --nvl(v_interface_line_id,1),
v_vendors(i).interface_distribution_id,  --distributions_interface_s.nextval,
v_vendors(i).distribution_num,
v_vendors(i).quantity_ordered,
v_vendors(i).charge_acct,
1001,
1000,
1006,
v_vendors(i).qty_delivered,
v_vendors(i).qty_billed,
v_vendors(i).qty_cancelled,
v_vendors(i).deliver_to_location_id,
v_vendors(i).deliver_to_person_id,
104
);
END LOOP;
COMMIT;
END;
/
Posted by at No comments:

AP Supplier Conversion using bulk collect

Hi friends this procedure is on supplier conversion using bulk collect

Create table for supplier site

create table XXAP_SUPPLIER_SITE_STAGE
(
SUPPLIER_NAME                      VARCHAR2(100)
,SUPP_SITE_CODE                    VARCHAR2(100)
,ADDRESS1                          VARCHAR2(100)
,ADDRESS2                          VARCHAR2(100)
,CITY                              VARCHAR2(50)
,COUNTRY                           VARCHAR2(50)
,CURRENCY_CODE                     VARCHAR2(50)
,PHONE_NUMBER                      VARCHAR2(50)
,FAX                               VARCHAR2(50)
,EMAIL                             VARCHAR2(50)
,VENDOR_INTERFACE_ID               NUMBER(15)
,AREA_CODE                         VARCHAR2(15)
,FIRST_NAME_ALT                    VARCHAR2(200)
,LAST_NAME_ALT                     VARCHAR2(200)
,FIRST_NAME                        VARCHAR2(20)
,LAST_NAME                         VARCHAR2(20)
);


load data
infile '------------------------------------------------------'
insert into table XXAP_SUPPLIER_SITE_STAGE
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(SUPPLIER_NAME
   ,SUPP_SITE_CODE
   ,ADDRESS1
   ,ADDRESS2
   ,CITY
   ,COUNTRY
   ,CURRENCY_CODE
   ,PHONE_NUMBER
   ,FAX
   ,EMAIL
   ,VENDOR_INTERFACE_ID
   ,AREA_CODE
   ,FIRST_NAME_ALT
   ,LAST_NAME_ALT
   ,FIRST_NAME
   ,LAST_NAME)

Create a procedure for SUPPLIER LOAD:

CREATE OR REPLACE PROCEDURE XXAP_SUPPLIER_LOAD(x_errbuf OUT VARCHAR2,
                                                    x_retcode OUT NUMBER,
                                                    p_array_size IN PLS_INTEGER DEFAULT 300)
IS
TYPE test_array IS TABLE OF xxap_supplier_site_stage%ROWTYPE  INDEX BY BINARY_INTEGER;  
      --v_vendor_interface_id                         test_array;
     -- v_supplier_name                                 test_array;
 v_vendors                              test_array ;
      v_vendor_type                     VARCHAR2(30);
      v_interface_id                         NUMBER;

  BEGIN  
 SELECT *  BULK COLLECT INTO v_vendors FROM  xxap_supplier_site_stage ORDER BY supp_site_code;
  FOR i IN  1..v_vendors.COUNT
 
  LOOP
  SELECT ap_suppliers_int_s.NEXTVAL INTO v_interface_id  FROM dual;

  INSERT INTO ap_suppliers_int (vendor_interface_id,vendor_name) VALUES (v_interface_id ,INITCAP(v_vendors(i).supplier_name));
  END LOOP;
       
  END ;
/

create a procedure for SUPPLIERS CONTACT LOAD:

CREATE OR REPLACE PROCEDURE XXAP_SUPPLIERS_CONTACT_LOAD(x_errbuf OUT VARCHAR2, x_retcode OUT NUMBER)
IS
TYPE test_array IS TABLE OF xxap_supplier_site_stage%ROWTYPE  INDEX BY BINARY_INTEGER;
  v_vendor_id                          VARCHAR2(30);
  v_vendor_int_id                      VARCHAR2(30);
   v_vendors                              test_array ;
   --v_vendor_site_id                       number;

 
BEGIN
--open c1;
SELECT *  BULK COLLECT INTO v_vendors FROM  xxap_supplier_site_stage ORDER BY supp_site_code;
FOR i IN  1..v_vendors.COUNT
 LOOP
                    BEGIN
                    SELECT vendor_site_code
                    INTO   v_vendor_int_id
                                        FROM   ap_supplier_sites_int a
                    WHERE a. vendor_site_code = v_vendors(I).supp_site_code;
                              EXCEPTION WHEN OTHERS THEN
                        NULL;
                   END;
                   --SELECT ap_sup_site_contact_int_seq.NEXTVAL INTO v_vendor_site_id  FROM dual;
  INSERT INTO ap_sup_site_contact_int
(vendor_site_code
,first_name
,last_name
,area_code
,phone
,first_name_alt
,last_name_alt
,email_address
,fax
,org_id
--,vendor_id
--creation_date
)
VALUES
(
  v_vendor_int_id
  ,v_vendors(i).first_name                    
  ,v_vendors(i).last_name                      
  ,v_vendors(i).area_code                      
  ,v_vendors(i).phone_number                        
  ,v_vendors(i).first_name_alt                  
  ,v_vendors(i).last_name_alt                  
  ,v_vendors(i).email                
  ,v_vendors(i).fax
  ,104
  --,v_vendor_id    
  --SYSDATE
  );
  END LOOP;
  END;
/

create a procedure for SUPPLIER SITE LOAD:


CREATE OR REPLACE PROCEDURE XXAP_SUPPLIER_SITE_LOAD(x_errbuf OUT VARCHAR2, x_retcode OUT NUMBER)
IS
TYPE test_array IS TABLE OF xxap_supplier_site_stage%ROWTYPE  INDEX BY BINARY_INTEGER;
  v_vendor_id                          VARCHAR2(100);
  v_vendor_int_id                      VARCHAR2(100);
  v_vendors                            test_array ;

  --vv_vendor_interface_id                number;
 -- CURSOR C1
 -- IS
 -- SELECT *
 -- FROM    xxap_supplier_site_stage
 -- order by SUPP_SITE_CODE;
BEGIN
--open c1;
SELECT *  BULK COLLECT INTO v_vendors FROM  xxap_supplier_site_stage ORDER BY supp_site_code;
FOR i IN  1..v_vendors.COUNT
 LOOP
                   BEGIN
                    SELECT   vendor_id
                    INTO     v_vendor_id
                    FROM     po_vendors
                    WHERE    UPPER(vendor_name) = UPPER(v_vendors(i).supplier_name);
                   EXCEPTION WHEN OTHERS THEN
                         NULL;
                 
                   END;
                 
                  /* BEGIN
                    SELECT vendor_interface_id
                    INTO   v_vendor_int_id
                    FROM   ap_suppliers_int
                    WHERE  UPPER(vendor_name) = UPPER(v_vendors(I).supplier_name);
                    EXCEPTION WHEN OTHERS THEN
                        NULL;
                   END;*/

--v_vendor_id(C_REC):=vv_vendor_id;
--v_VENDOR_INT_ID(C_REC):= vv_VENDOR_INT_ID;  

 --end loop;

                   --BEGIN
                  -- open C_REC;
                  -- FETCH  C_REC  BULK COLLECT INTO v_vendor_id,v_VENDOR_INT_ID;
                  -- for i in 1 .. v_VENDOR_INT_ID.count
                  -- LOOP
       -- SELECT ap_supplier_sites_int_s.NEXTVAL INTO v_vendor_int_id  FROM dual;
 
    INSERT INTO ap_supplier_sites_int
    (vendor_interface_id
    --,vendor_site_interface_id
    ,vendor_id
    ,vendor_site_code
    ,address_line1
    ,address_line2
    ,city
    ,country
    ,phone
    ,fax
    ,email_address
    ,invoice_currency_code
    ,payment_currency_code
    ,org_id
    ,pay_site_flag
    ,purchasing_site_flag
    )
    VALUES
    ( v_vendors(i).VENDOR_INTERFACE_ID                                                            
    --,ap_suppliers_int_s.nextval
    ,v_vendor_id
    ,v_vendors(i).supp_site_code
    ,v_vendors(i).address1
    ,v_vendors(i).address2
    ,v_vendors(i).city
    ,v_vendors(i).country
    ,SUBSTR(v_vendors(i).phone_number,1,15)
    ,SUBSTR(v_vendors(i).fax,1,15)
    ,v_vendors(i).email
    ,'USD'                                          --C_REC.CURRENCY_CODE
    ,'USD'                               --C_REC.CURRENCY_CODE
    ,104
    ,'Y'
    ,'Y'
    );
    END LOOP;
   -- close C1;  
  COMMIT;
  EXCEPTION
  WHEN too_many_rows THEN
      v_vendor_id :=NULL;
  WHEN no_data_found THEN
    v_vendor_id :=NULL;
   -- END LOOP;
  --  END;
END;
/
Posted by at No comments:

General ledger report

SELECT
GJS.JE_SOURCE_NAME     "Source",
GJC.JE_CATEGORY_NAME  "Category",
GJB.NAME              "Batch Name",
GJH.NAME          "JE Name",
GJH.DATE_CREATED  "GL Date",
--ROWNUM            "Doc Seq No",
GJB.DESCRIPTION   "Short Descr Of Acc",
GJH.DESCRIPTION   "Full Details",
GJL.ACCOUNTED_DR  "Debit",
GJL.ACCOUNTED_CR  "Credit",
GJL.CODE_COMBINATION_ID "GL Code,Description"
FROM
GL_JE_HEADERS           GJH,
GL_JE_BATCHES             GJB,
GL_JE_LINES               GJL,
GL_PERIODS                GP,
GL_CODE_COMBINATIONS_KFV  GCK,
GL_JE_SOURCES             GJS,
GL_JE_CATEGORIES         GJC,
GL_SETS_OF_BOOKS GSB
WHERE
    GJH.JE_BATCH_ID=GJB.JE_BATCH_ID
AND GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJL.PERIOD_NAME=GP.PERIOD_NAME
AND GJH.SET_OF_BOOKS_ID=GSB.SET_OF_BOOKS_ID
AND GJL.CODE_COMBINATION_ID=GCK.CODE_COMBINATION_ID
AND GSB.SET_OF_BOOKS_ID=GJL.SET_OF_BOOKS_ID
AND GJS.JE_SOURCE_NAME =GJH.JE_SOURCE
AND GJC.JE_CATEGORY_NAME=GJH.JE_CATEGORY
AND GCK.CHART_OF_ACCOUNTS_ID=GSB.CHART_OF_ACCOUNTS_ID
AND GSB.SET_OF_BOOKS_ID=NVL(:P_SETOF_ID,GSB.SET_OF_BOOKS_ID)
AND GP.PERIOD_NAME=NVL(:PERIOD_NAME,GP.PERIOD_NAME)
AND GCK.CONCATENATED_SEGMENTS BETWEEN
NVL(:P_FLEXFROM,GCK.CONCATENATED_SEGMENTS) AND NVL(:P_FLEXTO,GCK.CONCATENATED_SEGMENTS)


01.000.6130.2270.000
1001-6001-2000-2110-1001-6636-21130-333001
Posted by at No comments:
Subscribe to: Posts (Atom)