Thursday 29 June 2017

OutBound Interface and example

create or replace PACKAGE BODY GL_RPT_01_PKG
 AS
  PROCEDURE JOURNAL_AUDIT_PRC(
      O_ERRBUF OUT VARCHAR2,
      O_RETCODE OUT VARCHAR2,
      I_LEDGER      IN NUMBER,
      I_COMPANY     IN VARCHAR2,
      I_PERIOD_FROM IN VARCHAR2,
      I_PERIOD_TO   IN VARCHAR2
      )
      AS
    x_count          NUMBER:=0;
    X_FILE_NAME      varchar2(100);
    x_directory_name v$parameter.value%type;--VARCHAR2(1000);
    file_handle UTL_FILE.FILE_TYPE;
    x_ledger      NUMBER;
    x_company     VARCHAR2(15);
    x_period_from VARCHAR2(15);
    x_period_to   VARCHAR2(15);
    x_period_year NUMBER;
    x_ledger_name VARCHAR2(100);
    CURSOR cur_journal_entry(i_ledger IN NUMBER, i_company IN VARCHAR2, i_period_from IN VARCHAR2, i_period_to IN VARCHAR2 )
    IS
      SELECT gjh.NAME
        ||gjh.CURRENCY_CODE
        ||gjh.PERIOD_NAME JOURNAL_ENTRY , --Journal Entry Unique Identifier
        gjl.code_combination_id,
        gcc.segment3 ACCOUNT_NUMBER,
        gjh.POSTED_DATE POSTING_DATE,
        gjh.DEFAULT_EFFECTIVE_DATE EFFECTIVE_DATE,
        gjl.ACCOUNTED_DR,
        gjl.ACCOUNTED_CR,
        DECODE(gjl.ACCOUNTED_DR,NULL,'-'
        ||gjl.ACCOUNTED_CR,gjl.ACCOUNTED_DR) GL_AMOUNT,
        gjh.CURRENCY_CODE,
        TO_CHAR(gjh.posted_date,'HH24:MI:SS') posting_time,
        gjh.period_name EFFECTIVE_PERIOD,
        gjh.JE_SOURCE JOURNAL_ENTRY_MA,
        gjh.DESCRIPTION HEADER_DESCR,
        gjl.DESCRIPTION LINE_DESCR,
        (SELECT user_name FROM fnd_user WHERE user_id = gjl.CREATED_BY
        ) CREATED_BY,
      gjh.JE_SOURCE,
      gjb.NAME BATCH_NUMBER,
      gjb.CREATION_DATE BATCH_DATE,
      gjb.DESCRIPTION BATCH_DESCR,
      gjl.JE_LINE_NUM JE_LINE_NUM,
      gcc.segment2 COST_CENTER,
      gjh.JE_CATEGORY DOCUMENT_TYPE,
      gp.period_year FISCAL_YEAR,
      gjh.JE_SOURCE ENTRY_DOCU_TYPE,
      gjh.CURRENCY_CONVERSION_RATE ,
      gjh.ACCRUAL_REV_FLAG REVERSE_IND,
      gjh.ACCRUAL_REV_EFFECTIVE_DATE REVERSE_DATE,
      gjh.CREATION_DATE CREATION_DATE ,
      TO_CHAR(gjh.creation_date,'HH24:MI:SS') CREATION_TIME ,
      (SELECT user_name FROM fnd_user WHERE user_id = gjh.LAST_UPDATED_BY
      ) CHANGED_BY,
      gjh.LAST_UPDATE_DATE CHANGED_BY_DATE,
      TO_CHAR(gjh.last_update_date,'HH24:MI:SS') CHANGED_BY_time,
      (SELECT user_name FROM fnd_user WHERE user_id = gjb.APPROVER_EMPLOYEE_ID
        ) APPROVER_EMPLOYEE_ID,
      gjb.je_batch_id,
      SB_GL_RPT_01_PKG.SB_ApproveDate(gjb.je_batch_id) APPROVED_DATE,
      to_char(SB_GL_RPT_01_PKG.SB_ApproveDate(gjb.je_batch_id),'HH24:MI:SS') APPROVED_TIME
    FROM GL_JE_HEADERS gjh,
      GL_JE_LINES gjl,
      GL_JE_BATCHES gjb,
      GL_CODE_COMBINATIONS gcc,
      gl_periods gp
    WHERE gjh.je_header_id      = gjl.je_header_id
    AND gjh.je_batch_id         = gjb.je_batch_id
    AND gcc.code_combination_id = gjl.code_combination_id
    AND gp.period_name          = gjh.period_name
    AND gjh.ledger_id           = i_ledger
    AND gcc.segment1            = NVL(i_company,gcc.segment1)
    AND gp.start_date           >=
      (SELECT start_date FROM gl_periods WHERE period_name = i_period_from
      )
    AND gp.end_date   <=
      (SELECT end_date FROM gl_periods WHERE period_name = i_period_to
      );

  BEGIN
    -- initializing the variables with null
    x_directory_name:='/usr/tmp';
    x_file_name     :=NULL;
    -- initializing the parameter values into defined variables
    x_ledger      := i_ledger;
    x_company     := i_company;
    x_period_from := i_period_from;
    x_period_to   := i_period_to;
    fnd_file.put_line(fnd_file.log,'starts printing');
    fnd_file.put_line(fnd_file.log,'Ledger value :'||i_ledger);
    fnd_file.put_line(fnd_file.log,'Ledger value :'||x_ledger);
    fnd_file.put_line(fnd_file.output,'Ledger :'||i_ledger);
    fnd_file.put_line(fnd_file.log,'Company :'||x_company);
    fnd_file.put_line(fnd_file.output,'Company :'||i_company);
    fnd_file.put_line(fnd_file.log,'Period From :'||x_period_from);
    fnd_file.put_line(fnd_file.output,'Period From :'||x_period_from);
    fnd_file.put_line(fnd_file.log,'Period to :'||x_period_to);
    fnd_file.put_line(fnd_file.output,'Period to :'||x_period_to);
    --Directory Location
    /*SELECT value
    INTO x_directory_name
    FROM v$parameter
    WHERE name='utl_file_dir' ; */
    fnd_file.put_line(fnd_file.log,'Directory Name :'||x_directory_name);
    fnd_file.put_line(fnd_file.output,'Directory Name :'||x_directory_name);
    -- Retrieving the period year by passing period from to concatenate with file name
    SELECT period_year
    INTO x_period_year
    FROM gl_periods
    WHERE period_name = x_period_from;
    fnd_file.put_line(fnd_file.log,'Period From :'||x_period_from);
    fnd_file.put_line(fnd_file.output,'Period From :'||x_period_from);
    fnd_file.put_line(fnd_file.log,'Period Year :'||x_period_year);
    fnd_file.put_line(fnd_file.output,'Period Year :'||x_period_year);
    -- Retrieving the ledger name US or CA from the gl_sets_of_books table to concatenate with file name
    SELECT SUBSTR(name,1,2)
    INTO x_ledger_name
    FROM gl_sets_of_books
    WHERE set_of_books_id = x_ledger;
    --File name
    x_file_name := x_ledger_name||x_period_from||'-'||x_period_to||'-'||x_period_year||'.txt';
    -- Open file in Write mode
    file_handle := UTL_FILE.FOPEN(x_directory_name,x_file_name,'W',32000);
    fnd_file.put_line(fnd_file.output,'File_name :'||x_file_name);
    fnd_file.put_line(fnd_file.log,'File_name :'||x_file_name);
    BEGIN
      -- open and fetching cursor
      FOR rec_journal_entry IN cur_journal_entry(i_ledger ,i_company , i_period_from , i_period_to )
      LOOP
        x_count  := x_count+1;
        IF x_count>0 THEN
          FND_FILE.PUT_LINE(FND_FILE.LOG,'File Open Successful');
        END IF;
        utl_file.put_line(file_handle,RPAD(rec_journal_entry.JOURNAL_ENTRY,70)||         --1
                              RPAD(rec_journal_entry.ACCOUNT_NUMBER,10)||                --2
                              RPAD(rec_journal_entry.POSTING_DATE,12)||                   --3
                              RPAD(rec_journal_entry.EFFECTIVE_DATE,12)||                 --4
                              RPAD(rec_journal_entry.GL_AMOUNT,9)||                       --5
                              RPAD(rec_journal_entry.CURRENCY_CODE,15)||                  --6
                              RPAD(rec_journal_entry.posting_time,10)||                   --7
                              RPAD(rec_journal_entry.EFFECTIVE_PERIOD,15)||               --8
                              RPAD(rec_journal_entry.JOURNAL_ENTRY_MA,25)||               --9
                              RPAD(rec_journal_entry.HEADER_DESCR,100)||                  --10
                              RPAD(rec_journal_entry.LINE_DESCR,100)||                    --11
                              RPAD(rec_journal_entry.CREATED_BY,50)||                    --12
                              RPAD(rec_journal_entry.JE_SOURCE,25)||                      --13
                              RPAD(rec_journal_entry.BATCH_NUMBER,100)||                  --14
                              RPAD(rec_journal_entry.BATCH_DATE,12)||                     --15
                              RPAD(rec_journal_entry.BATCH_DESCR,100)||                   --16
                              RPAD(rec_journal_entry.JE_LINE_NUM,15)||                    --17
                              RPAD(rec_journal_entry.COST_CENTER,10)||                   --18
                              RPAD(rec_journal_entry.DOCUMENT_TYPE,25)||                  --19
                              RPAD(rec_journal_entry.FISCAL_YEAR,15)||                    --20
                              RPAD(rec_journal_entry.ENTRY_DOCU_TYPE,25)||                --21
                              RPAD(rec_journal_entry.CURRENCY_CONVERSION_RATE,9)||        --22
                              RPAD(rec_journal_entry.REVERSE_IND,2)||                     --23
                              RPAD(rec_journal_entry.REVERSE_DATE,12)||                   --24
                              RPAD(rec_journal_entry.CREATION_DATE,12)||                  --25
                              RPAD(rec_journal_entry.CREATION_TIME,10)||                  --26 
                              RPAD(rec_journal_entry.CHANGED_BY,50)||                    --27
                              RPAD(rec_journal_entry.CHANGED_BY_DATE,12)||                --28
                              RPAD(rec_journal_entry.CHANGED_BY_time,10)||                --29
                              RPAD(rec_journal_entry.APPROVER_EMPLOYEE_ID,50)||          --30
                              RPAD(rec_journal_entry.APPROVED_DATE,12));                  --31
        fnd_file.put_line(fnd_file.output,RPAD(rec_journal_entry.JOURNAL_ENTRY,130)||CHR(9)||
                              RPAD(rec_journal_entry.ACCOUNT_NUMBER,150)||CHR(9)||
                              RPAD(rec_journal_entry.POSTING_DATE,12)||CHR(9)||
                              RPAD(rec_journal_entry.EFFECTIVE_DATE,12)||CHR(9)||
                              RPAD(rec_journal_entry.GL_AMOUNT,9)||CHR(9)||
                              RPAD(rec_journal_entry.CURRENCY_CODE,15)||CHR(9)||
                              RPAD(rec_journal_entry.posting_time,10)||CHR(9)||
                              RPAD(rec_journal_entry.EFFECTIVE_PERIOD,15)||CHR(9)||
                              RPAD(rec_journal_entry.JOURNAL_ENTRY_MA,25)||CHR(9)||
                              RPAD(rec_journal_entry.HEADER_DESCR,240)||CHR(9)||
                              RPAD(rec_journal_entry.LINE_DESCR,240)||CHR(9)||
                              RPAD(rec_journal_entry.CREATED_BY,100)||CHR(9)||
                              RPAD(rec_journal_entry.JE_SOURCE,25)||CHR(9)||
                              RPAD(rec_journal_entry.BATCH_NUMBER,100)||CHR(9)||
                              RPAD(rec_journal_entry.BATCH_DATE,12)||CHR(9)||
                              RPAD(rec_journal_entry.BATCH_DESCR,240)||CHR(9)||
                              RPAD(rec_journal_entry.JE_LINE_NUM,15)||CHR(9)||
                              RPAD(rec_journal_entry.COST_CENTER,150)||CHR(9)||
                              RPAD(rec_journal_entry.DOCUMENT_TYPE,25)||CHR(9)||
                              RPAD(rec_journal_entry.FISCAL_YEAR,15)||CHR(9)||
                              RPAD(rec_journal_entry.ENTRY_DOCU_TYPE,25)||CHR(9)||
                              RPAD(rec_journal_entry.CURRENCY_CONVERSION_RATE,9)||CHR(9)||
                              RPAD(rec_journal_entry.REVERSE_IND,2)||CHR(9)||
                              RPAD(rec_journal_entry.REVERSE_DATE,12)||CHR(9)||
                              RPAD(rec_journal_entry.CREATION_DATE,12)||CHR(9)||
                              RPAD(rec_journal_entry.CREATION_TIME,10)||CHR(9)||
                              RPAD(rec_journal_entry.CHANGED_BY,100)||CHR(9)||
                              RPAD(rec_journal_entry.CHANGED_BY_DATE,12)||CHR(9)||
                              RPAD(rec_journal_entry.CHANGED_BY_time,10)||CHR(9)||
                              RPAD(rec_journal_entry.APPROVER_EMPLOYEE_ID,100)||CHR(9)||
                              RPAD(rec_journal_entry.APPROVED_DATE,12)||CHR(10));
      END LOOP;
    EXCEPTION
    WHEN no_data_found THEN
      fnd_file.put_line(fnd_file.output,'No Data Found'||sqlerrm);
      fnd_file.put_line(fnd_file.log,'No Data Found'||sqlerrm);
    END;
    -- Closing file
    utl_file.fclose(file_handle);
    fnd_file.put_line(fnd_file.output,'No of Records transferred to the data file :'||x_count);
    fnd_file.put_line(fnd_file.output,' ');
    fnd_file.put_line(fnd_file.output,'Submitted User name :'||fnd_profile.value('username'));
    fnd_file.put_line(fnd_file.output,' ');
    fnd_file.put_line(fnd_file.output,'Submitted Responsibility name :'||fnd_profile.value('RESP_NAME'));
    fnd_file.put_line(fnd_file.output,' ');
    fnd_file.put_line(fnd_file.output,'Submission Date :'||SYSDATE);
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    fnd_file.put_line(fnd_file.log , 'NO_DATA_FOUND');
    UTL_FILE.FCLOSE_all;
  WHEN utl_file.invalid_operation THEN
    fnd_file.put_line(fnd_file.log,'invalid operation');
    utl_file.fclose_all;
  WHEN utl_file.invalid_path THEN
    fnd_file.put_line(fnd_file.log,'invalid file path');
    utl_file.fclose_all;
  WHEN utl_file.invalid_mode THEN
    fnd_file.put_line(fnd_file.log,'invalid mode');
    utl_file.fclose_all;
  WHEN utl_file.invalid_filehandle THEN
    fnd_file.put_line(fnd_file.log,'invalid filehandle');
    utl_file.fclose_all;
  WHEN utl_file.read_error THEN
    fnd_file.put_line(fnd_file.log,'read error');
    utl_file.fclose_all;
  WHEN utl_file.internal_error THEN
    fnd_file.put_line(fnd_file.log,'internal error');
    utl_file.fclose_all;
  WHEN OTHERS THEN
    fnd_file.put_line(fnd_file.log,'other error');
    --Closing all file if open
    utl_file.fclose_all;
  end JOURNAL_AUDIT_PRC;
function ApproveDate(i_batch_id number) return date
as
x_date date;
x_batch varchar2(100);
begin

SELECT DISTINCT gjb.NAME batch,
wn.end_date approval_end_date
into x_batch,x_date
FROM wf_notifications wn, wf_items wi, gl_je_batches gjb
WHERE wn.CONTEXT LIKE
'GLBATCH%'
|| (SELECT item_key
FROM wf_items a
WHERE a.user_key = wi.user_key
AND a.item_type = 'GLBATCH'
AND ROWNUM = 1)
|| '%'
AND wi.item_type = wn.MESSAGE_TYPE
AND wi.user_key = gjb.NAME
AND gjb.je_batch_id = i_batch_id; --54626862

return (x_date);
exception
WHEN NO_DATA_FOUND THEN
  return null;
  fnd_file.put_line(fnd_file.log ,'NO_DATA_FOUND'||SQLERRM);
WHEN OTHERS THEN
  return null;
  fnd_file.put_line(fnd_file.log,'OTHER ERROR'||SQLERRM);
end ApproveDate;
end GL_RPT_01_PKG;
Posted by at No comments:

Tuesday 13 June 2017

rvtptcontrol failed RVTTH-445: Subroutine Please define all applicable accounts. Cause: Accounts have not been correctly defined. Please verify that all accounts have been defined on the receiving parameter fo
This error is occured

solution :
Run below query and you will the PO distribution for which "Accrual Account" and "Variance Account" is null

select poh.segment1 PO_NUMBER, por.release_num, poll.shipment_num, ood.organization_name ORG_NAME,ood.organization_id,ood.organization_code,
pod.org_id OPERATING_UNIT, pod.po_distribution_id,
pod.accrual_account_id, pod.variance_account_id
from apps.po_headers_all poh,
apps.po_line_locations_all poll,
apps.po_distributions_all pod,
apps.po_releases_all por,
apps.org_organization_definitions ood
where (accrual_account_id is NULL
OR variance_account_id is NULL)
and poh.po_header_id = <PO_HEADER_ID>
and poh.po_header_id = pod.po_header_id
and poh.po_header_id = por.po_header_id
and poh.po_header_id = poll.po_header_id
and poll.line_location_id = pod.line_location_id
and por.po_release_id = poll.po_release_id
and ood.organization_id= pod.destination_organization_id  --org_id
and poh.approved_flag = 'Y';

Now open PO-Release
Navigation : Purchasing Super User > Purchase Orders > Purchase Order Summary
Enter PO number and Release number
Click on OPEN
then verify the distributions and enter the "Accrual Account" and "Variance Account" wherever it is null.
Posted by at No comments:
Subscribe to: Posts (Atom)