, , , , , ,

Complete GL Interface – PL/SQL Procedure

Pre-Requisites:
    ————–
      a) set of books should be defined
      b) Current Conversion rates and accounting periods need to be defines
      c) Source and Category Name Should be defined

    Interface Tables:
    —————-
      GL_INTERFACE
 
    Base Tables:
    ———–
       GL_JE_HEADERS
       GL_JE_LINES
       GL_JE_BATCHES

 Standard Program:
 Go to General Ledger Vision Operations(USA)
   Run =>Import
 Here Give the Source name and Save.

While Click on the save button  Back end One Program Concurrent Program is running.If it is Success the Records are Successfully loaded from interface table to base Table Others wise Some Error are there.
Copy that Request_id and Enter into Generals our Records will be there………..

   Validation Columns:
   ——————
       Source       period_name   currency_code   set_of_books_id
       je_source    je_catregory  accounting_date entered_dr , entered_cr
       accounted_cr accounted_dr  encumberance_type_id

    Source = ‘NEW’
    period need to be open status in  gl_period_statuses
    souce_name defined in gl_je_source table
    category_name defines  gl_je_Category
    currency available in fnd_Currencies
    accounted_cr and accounted_dr total should be same.

Control file for GL_Interface:

LOAD DATA
INFILE *
TRUNCATE INTO TABLE GL_INTERFACE_TEMP
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
TRAILING NULLCOLS
(STATUS,
 SET_OF_BOOKS_ID,
 ACCOUNTING_DATE,
 CURRENCY_CODE,
 DATE_CREATED,
 CREATED_BY,
 ACTUAL_FLAG,
 USER_JE_CATEGORY_NAME,
 USER_JE_SOURCE_NAME,
 SEGMENT1,
 SEGMENT2,
 SEGMENT3,
 SEGMENT4,
 SEGMENT5,
 ENTERED_DR,
 ENTERED_CR,
 ACCOUNTED_DR,
 ACCOUNTED_CR,
 GROUP_ID)

BEGIN DATA
NEW,1,11-AUG-2002,USD,11-AUG-2002,1318,A,Inventory,JETFORMS,01,110,7730,0000,000,555,555,555,555,11
NEW,1,11-AUG-2002,USD,11-AUG-2002,1318,A,Inventory,JETFORMS,01,110,7730,0000,000,554,554,554,554,11

Script
sqlldr apps/apps control=’/apps/aptest/visappl/xxcus/11.5.0/bin/xx_gl.ctl’    log=’/apps/aptest/visappl/xxcus/11.5.0/bin/xx_gl.log’

exit 0

GL Interface Package:

CREATE OR REPLACE package body APPS.xx_gl_int_pkg
is
procedure dis_log(p_msg in varchar2)
is
begin
fnd_file.put_line(fnd_file.log,p_msg);
end;

procedure main(errbuf out varchar2,
               retcode out varchar2
               )
 is
 cursor c1 is select a.rowid row_id,a.* from GL_INTERFACE_TEMP a;
 v_gl_int    gl_interface%rowtype;
 v_process_flag    varchar2(10);
 v_error_msg   varchar2(100);
 v_tot_err_msg   varchar2(1000);
 begin
 
   dis_log(‘before entering the loop’);
 
 
 for i in c1 loop
                v_error_msg :=null;
                v_process_flag:=’S’;
                v_tot_err_msg:=null;
                v_gl_int:=null;
              –currency_code validation
                begin
                select  currency_code into v_gl_int.currency_code
                                      from fnd_currencies
                                     where currency_code=i.currency_code;
                 exception
                 when no_data_found then
                    v_process_flag:=’E’;
                    v_error_msg  := ‘Invalid Currency Code =>’||i.currency_code;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                 when others then
                    v_process_flag:=’E’;
                    v_error_msg   := ‘ Exception at Currency Code =>’||i.currency_code;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                end;    
               
               
                –user_je_source_name validation
               
                begin
               
                  select user_je_source_name into v_gl_int.user_je_source_name
                                             from gl_je_sources
                                            where user_je_source_name=i.user_je_source_name;
                  exception
                 when no_data_found then
                    v_process_flag:=’E’;
                    v_error_msg  := ‘Invalid Sourec Name =>’||i.user_je_source_name;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                 when others then
                    v_process_flag:=’E’;
                    v_error_msg   := ‘ Exception at Sourec Name =>’||i.user_je_source_name;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                end;    
               
                –category_name  validation
                begin
                     select user_je_category_name into v_gl_int.user_je_category_name
                     from gl_je_categories
                     where user_je_category_name=i.user_je_category_name;
                  exception
                  when no_data_found then
                    v_process_flag:=’E’;
                    v_error_msg  := ‘Invalid category_name =>’||i.user_je_category_name;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                  when others then
                    v_process_flag:=’E’;
                    v_error_msg   := ‘ Exception at category_name =>’||i.user_je_category_name;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;  
                 
                end;
               
                 –user id validation
               
                begin
                     select user_id into v_gl_int.created_by from fnd_user
                                   where  user_id = i.created_by;
                  exception
                  when no_data_found then
                    v_process_flag:=’E’;
                    v_error_msg  := ‘Invalid user id =>’||i.created_by;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                  when others then
                    v_process_flag:=’E’;
                    v_error_msg   := ‘ Exception at user id =>’||i.created_by;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                   
                end;
               
                 — set of books id validation
               
                begin
               
                      SELECT SET_OF_BOOKS_ID INTO v_gl_int.set_of_books_id
                      FROM GL_SETS_OF_BOOKS WHERE SET_OF_BOOKS_ID=i.set_of_books_id;
                   exception
                  when no_data_found then
                    v_process_flag:=’E’;
                    v_error_msg  := ‘Invalid set of books id =>’||i.set_of_books_id;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                  when others then
                    v_process_flag:=’E’;
                    v_error_msg   := ‘ Exception atset of books id =>’||i.set_of_books_id;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                end;
             
                         v_gl_int.status                    :=i.status;
                        — v_gl_int.set_of_books_id           :=i.set_of_books_id;
                         v_gl_int.accounting_date           :=i.accounting_date;
                        — v_gl_int.currency_code             :=i.currency_code;
                         v_gl_int.date_created              :=i.date_created;
                         –v_gl_int.created_by                :=i.created_by;
                         v_gl_int.actual_flag               :=i.actual_flag ;
                         –v_gl_int.user_je_category_name     :=i.user_je_category_name;
                        –v_gl_int.user_je_source_name       :=i.user_je_source_name;
                         v_gl_int.segment1                  :=i.segment1;
                         v_gl_int.segment2                  :=i.segment2;
                         v_gl_int.segment3                  :=i.segment3;
                         v_gl_int.segment4                  :=i.segment4;
                         v_gl_int.segment5                  :=i.segment5 ;
                         v_gl_int.entered_dr                :=i.entered_dr;
                         v_gl_int.entered_cr                :=i.entered_cr;
                         v_gl_int.accounted_dr               :=i.accounted_dr;
                         v_gl_int.accounted_cr              :=i.accounted_cr;
                         v_gl_int.group_id                  :=i.group_id;
                       
             
               
                 dis_log(‘before inserting the loop’);  
               
                   if v_process_flag = ‘S’ then    
               
                    insert into gl_interface values v_gl_int;
               
                   end if;
           update GL_INTERFACE_TEMP set process_flag=v_process_flag,
                                           error_message=v_tot_err_msg
                       where rowid=i.row_id;
                 
                 dis_log(‘after inserting the loop’);    
 end loop;
 exception
 when others then
 dis_log(‘exception occured at main loop’);
 end main;
 end xx_gl_int_pkg;

/by
, ,

How is data deleted from the interface tables after it has been loaded ?

After loading the data from the interface tables into the system successfully,  the data is not being removed (cleaned) from the interfaces.
They will have process_code = ‘ACCEPT’ in the interface tables.

To remove the processed data from the interface, a concurrent program is available.  The program name is : purge purchasing open interface processed data(POXPOIPR)

Run this program with paramenter purge accepted data = Yes
Then the process_code = ‘ACCEPTED’ records will be removed from the interface tables.

/by
, , , , , ,

How to view AP Invoice Interface from the Oracle R12 Front end?

Let’s say Invoices are imported from External Systems – irrespective of the transportation layer / method. Instead of viewing the Interface tables in the backend – say AP_INVOICES_INTERFACE , AP_INVOICE_LINES_INTERFACE, they can be viewed in the front-end.
Responsibility – Payables Manager
Navigation – Invoices – Entry – Open Interface Invoices

Clicking on the above link, opens the below form ..

Open the Query-mode to view the Invoice in the Interface table

Above is the header line. Click on Lines to view the lines information.

This would avoid looking for Invoices by querying in the back-end.
/by
, , , , ,

Payables Open Interface’s Control File

load data infile ‘AP_INV.TXT’
append into table    AP_INVOICES_INTERFACE
fields terminated by ‘^’ optionally enclosed by ‘”‘
trailing nullcols
    (INVOICE_NUM              CHAR “RTRIM(:INVOICE_NUM)”,
     INVOICE_TYPE_LOOKUP_CODE   CHAR “RTRIM(:INVOICE_TYPE_LOOKUP_CODE)”,
     INVOICE_DATE               DATE “DD-MON-RRRR”,
     VENDOR_NUM                 CHAR “RTRIM(:VENDOR_NUM)”,
     VENDOR_SITE_CODE           FILLER,
     INVOICE_AMOUNT             CHAR “RTRIM(:INVOICE_AMOUNT)”,
     TERMS_NAME                 CHAR “RTRIM(:TERMS_NAME)”,
     DESCRIPTION                CHAR “RTRIM(:DESCRIPTION)”,
     DUMMY1 FILLER,
     DUMMY2 FILLER,
     DUMMY3 FILLER,
     DUMMY4 FILLER,
     GL_DATE  DATE “TO_DATE((TO_CHAR(:GL_DATE,’DD-‘)||DECODE(TO_CHAR(:GL_DATE,’MON’),’AUG’,’SEP’,’SEP’,’SEP’,’OCT’,’OCT’,TO_CHAR(:GL_DATE,’MON’))||TO_CHAR(:GL_DATE,’-YYYY’)))”,
     VOUCHER_NUM                CHAR “RTRIM(:VOUCHER_NUM)”,
     INVOICE_RECEIVED_DATE      DATE,
     AMOUNT_APPLICABLE_TO_DISCOUNT,
     TERMS_DATE                 DATE,
     SOURCE                     CONSTANT ‘MAXCIM INVOICE’,
     PAYMENT_CURRENCY_CODE      CONSTANT ‘USD’,
     PAYMENT_METHOD_LOOKUP_CODE CONSTANT ‘CHECK’,
     CALC_TAX_DURING_IMPORT_FLAG CONSTANT ‘N’,
     ORG_ID                     CONSTANT ‘166’,
     SHIP_TO_LOCATION           CONSTANT ‘Materials Warehouse’,
     INVOICE_CURRENCY_CODE      CONSTANT ‘USD’,
     creation_date              SYSDATE,
     last_updated_by            CONSTANT 1093,
     last_update_date           SYSDATE,
     created_by                 CONSTANT 1093,
     INVOICE_ID “AP_INVOICES_INTERFACE_S.NEXTVAL”)

load data infile ‘AP_LINE_TEST.TXT’
append into table    AP_INVOICE_LINES_INTERFACE
fields terminated by ‘^’ optionally enclosed by ‘”‘
trailing nullcols
    (REFERENCE_2              CHAR “RTRIM(:REFERENCE_2)”,
     TYPE              FILLER,
     ACCOUNTING_DATE         DATE “DD-MON-RRRR”,
     DESCRIPTION          CHAR “RTRIM(:DESCRIPTION)”,
     DIST_CODE_COMBINATION_ID          CHAR “RTRIM(:DIST_CODE_COMBINATION_ID)”,
     LAST_UPDATED_BY          CHAR “RTRIM(:LAST_UPDATED_BY)”,
     AMOUNT                  CHAR “RTRIM(:AMOUNT)”,
     CREATED_BY          CHAR “RTRIM(:CREATED_BY)”,
     CREATION_DATE          DATE “DD-MON-RRRR”,
     ATTRIBUTE1          CHAR “RTRIM(:ATTRIBUTE1)”,
     ATTRIBUTE2          CHAR “RTRIM(:ATTRIBUTE2)”,
     ATTRIBUTE3          CHAR “RTRIM(:ATTRIBUTE3)”,
     TYPE_1099          CHAR “RTRIM(:TYPE_1099)”,
     UNIT_OF_MEAS_LOOKUP_CODE      CHAR “RTRIM(:UNIT_OF_MEAS_LOOKUP_CODE)”,
     TAX_RATE              CHAR “RTRIM(:TAX_RATE)”,
     QUANTITY_INVOICED      CHAR “RTRIM(:QUANTITY_INVOICED)”,
     UNIT_PRICE          CHAR “RTRIM(:UNIT_PRICE)”,
     INVOICE_ID “AP_INVOICE_LINES_INTERFACE_S.NEXTVAL”)

/by
, , , , , ,

Payables Open Interface for Project based Invoices

Following scripts used to test Payable open Interface for Invoices contains Project Data:

 Query for to get PO details:
 –select * from po_headers_all  where  PO_HEADER_ID=63845
–AND segment1=’NEX060378-002′
 –select * from po_lines_all where po_header_id=63845
–select * from po_vendors where vendor_id=11813
 –select * from po_line_locations_all where po_header_id=63845
  –select *  from PO_DISTRIBUTIONS_ALL
 –select * from ap_invoice_lines_interface
 –commit
Query to Set Org_id: 
–begin
–dbms_application_info.set_client_info(‘169’);
–end;

–select * from ap_invoices_interface where invoice_num like ‘COL%’
–select * from ap_invoice_lines_interface
— select * from  AP_TERMS
–select * from PO_DISTRIBUTIONS_ALL

DECLARE
    p_invoice_id       NUMBER;
    i                  NUMBER;
BEGIN

Query to Generate invoice_id —

    select AP_INVOICES_INTERFACE_S.nextval
    into p_invoice_id
    from dual;

Query to  Insert an invoice header —
 insert into ap_invoices_interface(INVOICE_ID,
                 INVOICE_NUM,
                   INVOICE_TYPE_LOOKUP_CODE,
                  INVOICE_DATE,
                 PO_NUMBER,
                 VENDOR_ID,
                 VENDOR_SITE_ID,
                   INVOICE_AMOUNT,
                 INVOICE_CURRENCY_CODE,
                  –TERMS_ID,
                   SOURCE,
                   GROUP_ID,
                PAYMENT_METHOD_LOOKUP_CODE,
                ORG_ID)
        VALUES  (p_invoice_id,         –INVOICE_ID,
                  ‘COL012’,              –INVOICE_NUM,
                  ‘STANDARD’,            –INVOICE_TYPE_LOOKUP_CODE,
                  sysdate,              –INVOICE_DATE,
                 ‘NEX060378-002’,                    –v_PO_NUMBER,
                11934,                 –v_VENDOR_ID, 11790
                14667,                 –v_VENDOR_SITE_ID, 14506
                10,                       –v_INVOICE_AMOUNT,  (Sanjiv Wrong Amt)10
               ‘USD’,                  — v_INVOICE_CURRENCY_CODE, usd
                –10011,                  –v_TERMS_ID, (Sanjiv)
                ‘CONVERSION’,–‘INVOICE GATEWAY’,       –v_SOURCE,
                 ‘USM’,                  — v_GROUP_ID,
               ”,                     — v_PAYMENT_METHOD_LOOKUP_CODE, CHECK
               169);                   –v_ORG_ID
    — Insert invoice line —

    for i in 1..1 loop
INSERT into ap_invoice_lines_interface (INVOICE_ID,
                                INVOICE_LINE_ID,
                                LINE_NUMBER,
                                LINE_TYPE_LOOKUP_CODE,
                                AMOUNT,
                                ACCOUNTING_DATE,
                                FINAL_MATCH_FLAG,
                                PO_HEADER_ID,
                                PO_NUMBER,
                                 PO_LINE_ID,
                                 PO_LINE_NUMBER,
                                PO_LINE_LOCATION_ID,
                                PO_SHIPMENT_NUM,
                                INVENTORY_ITEM_ID,
                                ITEM_DESCRIPTION,
                                QUANTITY_INVOICED,
                                SHIP_TO_LOCATION_CODE,
                                UNIT_PRICE,
                                PO_RELEASE_ID,
                                RELEASE_NUM,
                                PRICE_CORRECTION_FLAG,
                                RECEIPT_NUMBER,
                                MATCH_OPTION,
                                RCV_TRANSACTION_ID,
                                DIST_CODE_COMBINATION_ID,
                                PO_DISTRIBUTION_ID,
                                PO_DISTRIBUTION_NUM,
                                PROJECT_ID,
                                TASK_ID,
                                EXPENDITURE_TYPE,
                                EXPENDITURE_ORGANIZATION_ID,
                                PA_QUANTITY,
                                EXPENDITURE_ITEM_DATE
                                )
                        VALUES (
                                 p_invoice_id,     –v_INVOICE_ID,
                                 AP_INVOICE_LINES_INTERFACE_S.nextval,       –v_INVOICE_LINE_ID,
                                     i,               –v_LINE_NUMBER,  1
                                  ‘ITEM’,           — v_LINE_TYPE_LOOKUP_CODE,
                                   10,            –v_AMOUNT, (Sanjiv Wrong Amt, PO Line=82.27) 10
                                  sysdate,           — v_ACCOUNTING_DATE,
                                  ”,                –v_FINAL_MATCH_FLAG,
                                  63845,                –v_PO_HEADER_ID,
                                 ‘NEX060378-002’,             — v_PO_NUMBER,
                                 63846,              –v_PO_LINE_ID, (Sanjiv Wrong POLineID) 47245
                                  1,               –v_PO_LINE_NUMBER,
                                  63846,           –v_PO_LINE_LOCATION_ID, (Sanjiv Wrong POLineLocID)
                                    ”,                –v_PO_SHIPMENT_NUM,
                                  ”,                –v_INVENTORY_ITEM_ID,
                                  ”,                –v_ITEM_DESCRIPTION,
                                  1,                –v_QUANTITY_INVOICED,
                                  ”,                –v_SHIP_TO_LOCATION_CODE,
                                 ”,                 –v_UNIT_PRICE,
                                ”,                 –v_PO_RELEASE_ID,
                                ”,                 –v_RELEASE_NUM,
                                   ”,                 –v_PRICE_CORRECTION_FLAG,
                                ”,                 –v_RECEIPT_NUMBER,
                                   ”,                –v_MATCH_OPTION,
                                   ”,                 –v_RCV_TRANSACTION_ID
                                ”,           –v_DIST_CODE_COMBINATION_ID
                                64302,            –PO_DISTRIBUTION_ID = 64302
                                1,                  –PO_DISTRIBUTION_NUM
                                ”,–1087,               –PROJECT_ID, (Sanjiv Wrong ProjID) 1172 1087
                                ”,–1551,                –TASK_ID,
                                ”,–‘Awrd Ovation Point Redemption’,–EXPENDITURE_TYPE,
                                 ”,–169,                –EXPENDITURE_ORGANIZATION_ID,
                                 ”,–”,             –PA_QUANTITY, (Sanjiv Wrong Qty)10
                              ” );– sysdate );           –EXPENDITURE_ITEM_DATE
      end loop;

 commit;
END;

Queries to debugged and trace Interface errors: 

–select * from ap_invoices_interface where invoice_id=13634
–select * from ap_invoice_lines_interface where invoice_id=13634
–select * from user_errors where name like’USM%’;
— select * from pa_projects_all where project_id=1172 –1087
–select * from pa_projects_all where project_id in (select project_id from pa_tasks where task_number=’2.4′)
–10437
–select * from usm_po_codes
–select * from pa_projects_all where segment1 =’10437′

/by