Oracle GL Journal Interface

Using GL_INTERFACE table and Journal Import program in Oracle Applications

--DERIVE LEDGER ID

select * from gl_ledgers;

--CATEGORY NAME IS VALID OR NOT

SELECT 'X'
INTO x_variable
FROM gl_je_categories gjc
WHERE UPPER (gjc.user_je_category_name) = UPPER (p_cat_name);


--SOURCE NAME IS VALID OR NOT

SELECT 'X'
INTO x_variable
FROM gl_je_sources gjs
WHERE UPPER (gjs.user_je_source_name) = UPPER (p_source_name)


CHECK THAT PERTICULAR PERIOD IS OPEN OR NOT BY PASSING PARAMETER AS PERIOD NAME AND LEDGER ID

IF p_transaction_date IS NOT NULL
THEN

SELECT 'X'
INTO x_variable
FROM apps.gl_period_statuses gps,
apps.fnd_application fa,
apps.gl_periods glp
WHERE gps.period_name = glp.period_name
AND gps.closing_status IN ('O', 'F')
AND fa.application_short_name = 'SQLGL'
AND gps.application_id = fa.application_id
AND glp.period_set_name = 'XXX' --period set name
AND p_transaction_date BETWEEN glp.start_date AND glp.end_date
AND gps.ledger_id = v_ledger_id;
END IF;

--INSERT TO INTERFACE TABLE

BEGIN
INSERT INTO gl_interface
(status,
ledger_id,
accounting_date,
transaction_date,
attribute11,
currency_code,
date_created,
created_by,
actual_flag,
user_je_category_name,
user_je_source_name,
currency_conversion_date,
user_currency_conversion_type,
currency_conversion_rate,
code_combination_id, entered_dr,
entered_cr, accounted_dr,
accounted_cr, reference1,
reference2, reference3,
reference4, reference5,
reference6, reference7,
reference8, reference9,
reference10,period_name,
reference_date, GROUP_ID)
VALUES ('NEW',
c_emp_pre.ledger_id,
c_emp_pre.transaction_date,
c_emp_pre.transaction_date,
c_emp_pre.project_id, --c_emp_pre.attribute1,
c_emp_pre.currency_code, SYSDATE, v_user_id,
c_emp_pre.actual_flag,
c_emp_pre.user_je_category_name,
c_emp_pre.user_je_source_name,
c_emp_pre.currency_conversion_date,
c_emp_pre.user_currency_conversion_type,
c_emp_pre.currency_conversion_rate,
c_emp_pre.ccid, c_emp_pre.entered_dr,
c_emp_pre.entered_cr, c_emp_pre.accounted_dr,
c_emp_pre.accounted_cr, c_emp_pre.reference1,
c_emp_pre.reference2, c_emp_pre.reference3,
c_emp_pre.reference4, c_emp_pre.reference5,
c_emp_pre.reference6, c_emp_pre.reference7,
c_emp_pre.reference8, c_emp_pre.reference9,
c_emp_pre.reference10|| ' - '|| c_emp_pre.jgzz_recon_ref,
c_emp_pre.period_name,
c_emp_pre.reference_date, v_request_id
);

After this you can upload this data by log in to application and importing journals from general ledger responsibility using import journal.

--If you want lanch 'Journal Import' program using API FND_REQUEST.SUBMIT_REQUEST to load data from interface table to Oracle GL

--Variables declaration

v_interface_run_id number;
v_je_source_name varchar2(25);
v_status varchar2(1) := 'S';
v_post_error_to_susp_flag varchar2(1) := 'N';
v_from_date date;
v_to_date date;
v_create_sumjourn_flag varchar2(1) := 'N';
v_desc_flex_flag varchar2(1) := 'W'; -- flag set to import with DFF with Validation
v_group_id number;
v_source varchar2(25);
v_sobid number:=1;
v_cr_interval number := 60; -- seconds
v_cr_max_wait number := 0; -- seconds(0 = Will not time out)
v_cr_phase_code varchar2(30);
v_cr_status_code varchar2(30);
v_cr_dev_phase varchar2(30);
v_cr_dev_status varchar2(30);
v_cr_message varchar2(240);
v_jimport_cr_complete boolean;
v_submit_jimport_failed exception;
v_run_jimport_failed exception;
v_records_left number;
v_error_code number;
v_error_text varchar2(250);

SELECT gl_journal_import_s.nextval
INTO v_interface_run_id
FROM dual;

INSERT INTO gl_interface_control
(status, set_of_books_id,interface_run_id, group_id, je_source_name)
VALUES (v_status, v_sobid, v_interface_run_id, v_group_id, v_je_source_name);

COMMIT;

p_jimport_cr_id := FND_REQUEST.SUBMIT_REQUEST('SQLGL','GLLEZL',
'Journal Import', '',FALSE,
v_interface_run_id,
v_sobid,
v_post_error_to_susp_flag,
v_from_date,
v_to_date,
v_create_sumjourn_flag,
v_desc_flex_flag);
COMMIT;

-- This is necessary to submit the process to the Concurrent Manager;

--- 'p_jimport_cr_id' is set to the Concurrent Request ID if the request was
--- successful and to 0 if it was not.
---

IF p_jimport_cr_id = 0
THEN
raise v_submit_jimport_failed;
ELSE
v_jimport_cr_complete := FND_CONCURRENT.WAIT_FOR_REQUEST(p_jimport_cr_id,
v_cr_interval,
v_cr_max_wait,
v_cr_phase_code,
v_cr_status_code,
v_cr_dev_phase,
v_cr_dev_status,
v_cr_message);
IF (v_cr_dev_phase = 'COMPLETE' and v_cr_dev_status != 'NORMAL')
THEN
raise v_run_jimport_failed;
END IF;

Base Tables:

After import-

GL_JE_HEADERS

GL_JE_LINES

After posting journals:

GL_BALANCES

 

 

SQL to Derive Concurrent Managers with running process details

SQL to Derive Concurrent Managers list with process details in Oracle Applications

By using below sql, you will be able to derive concurrent managers running in the system.

Also you will be able to collect how many processes are running under each concurrent manager and in which application.

SELECT fat.application_name,
 DECODE(CONCURRENT_QUEUE_NAME,
 'FNDICM','Internal Manager',
 'FNDCRM','Conflict Resolution Manager',
 'AMSDMIN','Marketing Data Mining Manager',
 'C_AQCT_SVC','C AQCART Service',
 'FFTM','FastFormula Transaction Manager',
 'FNDCPOPP','Output Post Processor',
 'FNDSCH','Scheduler/Prereleaser Manager',
 'FNDSM_AQHERP','Service Manager: AQHERP',
 'FTE_TXN_MANAGER', 'Transportation Manager',
 'IEU_SH_CS','Session History Cleanup',
 'IEU_WL_CS', 'UWQ Worklist Items Release for Crashed session',
 'INVMGR','Inventory Manager',
 'INVTMRPM','INV Remote Procedure Manager',
 'OAMCOLMGR','OAM Metrics Collection Manager',
 'PASMGR','PA Streamline Manager',
 'PODAMGR','PO Document Approval Manager',
 'RCVOLTM', 'Receiving Transaction Manager',
 'STANDARD','Standard Manager',
 'WFALSNRSVC', 'Workflow Agent Listener Service',
 'WFMLRSVC','Workflow Mailer Service',
 'WFWSSVC','Workflow Document Web Services Service',
 'WMSTAMGR', 'WMS Task Archiving Manager',
 'XDP_APPL_SVC',
 'SFM Application Monitoring Service',
 'XDP_CTRL_SVC','SFM Controller Service',
 'XDP_Q_EVENT_SVC','SFM Event Manager Queue Service',
 'XDP_Q_FA_SVC','SFM Fulfillment Actions Queue Service',
 'XDP_Q_FE_READY_SVC', 'SFM Fulfillment Element Ready Queue Service',
 'XDP_Q_IN_MSG_SVC', 'SFM Inbound Messages Queue Service',
 'XDP_Q_ORDER_SVC', 'SFM Order Queue Service',
 'XDP_Q_TIMER_SVC','SFM Timer Queue Service',
 'XDP_Q_WI_SVC','SFM Work Item Queue Service',
 'XDP_SMIT_SVC', 'SFM SM Interface Test Service')  "Concurrent Manager",
 Max_Processes  ,                                                                                                                                                                                                                                                                                                                                                                                                                                        
 Running_Processes ,
 fcq.last_update_date,
 fcq.creation_date
From Apps.Fnd_Concurrent_Queues Fcq, Apps.Fnd_Application_Tl Fat
Where Fcq.Application_Id=Fat.Application_Id
and CONCURRENT_QUEUE_NAME IN (
'FNDICM','FNDCRM','AMSDMIN', 'C_AQCT_SVC','FFTM','FNDCPOPP','FNDSCH',
'FNDSM_AQHERP','FTE_TXN_MANAGER','IEU_SH_CS', 'IEU_WL_CS','INVMGR','INVTMRPM',
'OAMCOLMGR','PASMGR','PODAMGR','RCVOLTM', 'STANDARD','WFALSNRSVC','WFMLRSVC',
'WFWSSVC','WMSTAMGR','XDP_APPL_SVC', 'XDP_CTRL_SVC','XDP_Q_EVENT_SVC','XDP_Q_FA_SVC',
'XDP_Q_FE_READY_SVC', 'XDP_Q_IN_MSG_SVC','XDP_Q_ORDER_SVC','XDP_Q_TIMER_SVC',
'XDP_Q_WI_SVC','XDP_SMIT_SVC');
 

Oracle GL Budget Interface

 How to create Budget using GL_BUDGET_INTERFACE table in Oracle General Ledger

 

BUSINESS VALIDATIONS

 

FISCAL_YEAR IS NOT NULL

 

 

VALIDATE CODE COMBINATIONS

 

SELECT gcc.code_combination_id
INTO x_ccid
FROM gl_code_combinations gcc
WHERE gcc.segment1 = p_segment1
AND gcc.segment2 = p_segment2
AND gcc.segment3 = p_segment3
AND gcc.segment4 = p_segment4
AND gcc.segment5 = '000'
AND gcc.segment6 = '00000';
           

PERIOD_AMOUNT IS NOT NULL  

      

 

DERIVE BUDGET_VERSION_ID FOR BUDGET_NAME 

 

SELECT budget_version_id
INTO r_budget_line.budget_id
FROM gl_budget_versions
WHERE budget_name = p_budget_name;
                  

DERIVING BUDGET_ENTITY_ID FOR  ENTITY NAME   

 

SELECT budget_entity_id
INTO r_budget_line.entity_id
FROM gl_budget_entities
WHERE NAME = p_entity_name;

 

              

INSERT INTO GL_BUDGET_INTERFACE

--p_budget_int_row is cursor created on budget temp table

 

INSERT INTO gl_budget_interface
                        (budget_name,
                         budget_entity_name,
                         currency_code,
                         fiscal_year,
                         update_logic_type,                        
                         segment1,
                         segment2,
                         segment3,
                         segment4,
                         segment5,
                         segment6,
                         period1_amount,
                         period2_amount,
                         period3_amount,
                         period4_amount,
                         period5_amount,
                         period6_amount,
                         period7_amount,
                         period8_amount,
                         period9_amount,
                         period10_amount,
                         period11_amount,
                         period12_amount,
                         period13_amount,
                         last_update_date,
                         last_updated_by)
VALUES (       p_budget_int_row.budget_name,
                         p_budget_int_row.entity_name,
                         p_budget_int_row.currency_code,
                         p_budget_int_row.fiscal_year,
                         'R',  --A for add , R for replace                       
                         p_budget_int_row.business_unit,
                         p_budget_int_row.entity,
                         p_budget_int_row.cost_centre,
                         p_budget_int_row.ACCOUNT,
                         p_budget_int_row.icp,
                         p_budget_int_row.c4,
                         NVL(p_budget_int_row.period1_amount,0),
                         NVL(p_budget_int_row.period2_amount,0),
                         NVL(p_budget_int_row.period3_amount,0),
                         NVL(p_budget_int_row.period4_amount,0),
                         NVL(p_budget_int_row.period5_amount,0),
                         NVL(p_budget_int_row.period6_amount,0),
                         NVL(p_budget_int_row.period7_amount,0),
                         NVL(p_budget_int_row.period8_amount,0),
                         NVL(p_budget_int_row.period9_amount,0),
                         NVL(p_budget_int_row.period10_amount,0),
                         NVL(p_budget_int_row.period11_amount,0),
                         NVL(p_budget_int_row.period12_amount,0),
                         NVL(p_budget_int_row.period13_amount,0), SYSDATE,
                         fnd_profile.VALUE ('USER_ID')
                        );

 

Upload a budget:

1. Navigate to the Upload Budget window.

General Ledger Super User Responsibility

Budgets> Enter > Upload

2. Enter the Budget and Budget Organization that will receive the uploaded budget amounts. You can upload budgets for all of your budget organizations by entering the name of your general,all–inclusive budget organization.

3. Choose Upload.

General Ledger submits a concurrent process to upload budget information from the GL_BUDGET_INTERFACE table.

4. Review the Budget Spreadsheet Upload Execution Report for the status of your uploaded budget information.

 

Base Table: GL_BUDGETS

 

 

Oracle AP Invoice Interface

How to use Payables Open Interface Import program (APXIIMPT) to create invoices using AP Invoice Interface tables

Oracle Applications 11i, R12

Here I will explain flow to create invoices using AP invoice Interface tables and launching Payables Open Interface Import program (APXIIMPT) using fnd_request.submit_request API-

Derive Supplier and site details-
SELECT asu.vendor_name, asu.vendor_id, asus.vendor_site_id
INTO   v_vendor_name, v_vendor_id, v_vendor_site_id
FROM ap_suppliers asu
    ,ap_supplier_sites_all asus
WHERE asu.vendor_name='XXX'
  AND asus.vendor_site_code = 'XXX'
  AND asus.org_id = (SELECT organization_id
                      FROM hr_operating_units
                     WHERE NAME = 'XXX')
  AND asu.vendor_id = asus.vendor_id;
        
DERIVE SET OF BOOKS AND CHART OF ACCOUNTS FOR THT OU

SELECT hou.organization_id, hou.set_of_books_id,gl.chart_of_accounts_id
INTO x_org_id, l_set_of_bks_id ,l_chart_of_acct_id
FROM hr_operating_units hou,gl_ledgers gl
WHERE UPPER (hou.NAME) = UPPER ('XXX')
AND hou.set_of_books_id = gl.ledger_id;
        
CHECK INVOICE IS ALREADY EXIST FOR THT SUPPLIER AND OU

SELECT COUNT (1)
INTO x_count
FROM ap_invoices_all aia
WHERE aia.invoice_num = p_invoice_number
AND aia.vendor_id = v_vendor_id
AND aia.vendor_site_id = v_vendor_site_id
AND aia.org_id = (SELECT organization_id
                    FROM hr_operating_units
                   WHERE NAME = 'XXX');
        
CHECK INVOICE AMOUNT IS EQUAL TO ITEM  AND TAX AMOUNT

CHECK CURRENCY CODE EXIST OR NOT

TAX CODE IS NOT NULL

PAYMENT TERM VALIDATION

SELECT term_id
INTO l_terms_id
FROM ap_terms
WHERE UPPER (NAME) = UPPER (p_term_name) AND enabled_flag = 'Y';
      
POPULATE CODE COMBINATION ID

STANDARD ORACLE API FOR VALIDATING CODE COMBINATIONS AGAINSTRULES CONFIGURED IN ORALCE GL
FND_FLEX_EXT.GET_COMBINATION_ID
SELECT code_combination_id, enabled_flag
INTO p_ccid, l_enabled_flag
FROM gl_code_combinations gcc,gl_sets_of_books gsb
WHERE gcc.chart_of_accounts_id = gsb.chart_of_accounts_id
  AND segment1 = p_segment1
  AND segment2 = p_segment2
  AND segment3 = p_segment3
  AND segment4 = p_segment4
  AND segment5 = p_segment5
  AND segment6 = p_segment6
  AND set_of_books_id = p_set_of_book_id;
        
DERIVE SOURCE VALUE

SELECT flv.lookup_code
INTO v_source
FROM fnd_lookup_values flv
,fnd_lookup_types flt
,fnd_application_tl fat
WHERE flt.lookup_type = 'SOURCE'
AND UPPER (fat.application_name) = 'PAYABLES'
AND flt.application_id = fat.application_id
AND flt.lookup_type = flv.lookup_type
AND flv.enabled_flag = 'Y'
AND flv.meaning = 'XXX';


Insert data into interface tables-

c_apinv_main is cursor type variables of cursor created on invoice temp table
          
INSERT INTO ap_invoices_interface
                    (invoice_id
                    ,invoice_num
                    ,invoice_type_lookup_code
                    ,invoice_date
                    ,vendor_id
                    ,vendor_name
                    ,vendor_site_id
                    ,invoice_amount
                    ,invoice_currency_code
                    ,terms_id
                    ,terms_name
                    ,attribute_category
                    ,attribute1
                    ,attribute2
                    ,attribute3
                    ,attribute5
                    ,attribute7
                    ,SOURCE
                    ,org_id
                    ,creation_date
                    ,created_by
                    ,last_update_date
                    ,last_updated_by
                    ,last_update_login
                    ,payment_method_code
                    ,calc_tax_during_import_flag
                    ,description
                    ,accts_pay_code_combination_id
                    )
VALUES (ap_invoices_interface_s.NEXTVAL
                    ,c_apinv_main.invoice_num
                    ,'STANDARD'
                    ,c_apinv_main.invoice_date
                    ,v_vendor_id
                    ,v_vendor_name
                    ,v_vendor_site_id
                    ,c_apinv_main.invoice_amount
                    ,c_apinv_main.invoice_currency_code
                    ,l_terms_id
                    ,'Immediate'
                    ,'OTS01'
                    ,c_apinv_main.attribute1
                    ,c_apinv_main.attribute2
                    ,c_apinv_main.attribute3
                    ,c_apinv_main.attribute5
                    ,'0'
                    ,v_source
                    ,x_org_id
                    ,SYSDATE
                    ,l_user_id
                    ,SYSDATE
                    ,l_user_id
                    ,l_login_id
                    ,'JLP_Cheque'
                    ,'Y'
                    ,c_apinv_main.reason_description
                    ,NULL
                    );


INSERT INTO ap_invoice_lines_interface
                    (invoice_id
                    ,invoice_line_id
                    ,line_type_lookup_code
                    ,amount
                    ,accounting_date
                    ,dist_code_combination_id
                    ,org_id
                    ,tax_classification_code
                    ,creation_date
                    ,created_by
                    ,last_update_date
                    ,last_updated_by
                    ,last_update_login
                    )
VALUES (ap_invoices_interface_s.CURRVAL
                    ,ap_invoice_lines_interface_s.NEXTVAL
                    ,'ITEM'
                    ,c_apinv_main.line_amount
                    ,SYSDATE
                    ,c_apinv_main.ccid
                    ,x_org_id
                    ,c_apinv_main.tax_code
                    ,SYSDATE
                    ,l_user_id
                    ,SYSDATE
                    ,l_user_id
                    ,l_login_id
                    );
                   
After inserting into interface tables, launch Payables Open Interface Import program using fnd_request.submit_request


mo_global.set_policy_context ('S', l_org_id);
l_standard_request_id :=fnd_request.submit_request (application                  => 'SQLAP'
                                  ,program                      => 'APXIIMPT'
                                  ,description                  => NULL
                                  ,start_time                   => NULL
                                  ,sub_request                  => FALSE
                                 ,argument1                    => l_org_id
                                 ,argument2                    => v_source
                                 ,argument3                    => NULL                                                         
                                 ,argument4                    => NULL                                                         
                                 ,argument5                    => NULL                  
                                 ,argument6                    => NULL                                                           
                                 ,argument7                    => NULL
                                 ,argument8                    => 'Y'
                                 );

l_completed :=
      fnd_concurrent.wait_for_request (l_standard_request_id
                                      ,1
                                      ,NULL
                                      ,l_phase
                                      ,l_vstatus
                                      ,l_dev_phase
                                      ,l_dev_status
                                      ,l_message
                                      ); 
Check l_dev_phase and l_dev_status. it should be complete and normal

Base Table for invoices:

AP_INVOICES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL

AP/AR Netting Sample SQL query

Oracle Applications R12 AP/AR Netting Sample SQL Queries

 

Netting beween Oracle Accounts Payables and Oracle Account Receivables

 

AP/AR Netting:

 

AP/AR NETTING AUTOMATICALLY COMPARES PAYABLES TO RECEIVABLES AND CREATES THE APPROPRIATE TRANSACTION IN EACH SYSTEM    TO NET SUPPLIER INVOICES AND CUSTOMER INVOICES

 

 

1. Create Netting control Account in segment values. Account code 2111.
2. Create 01 Netting bank
3. Create 01 Netting Branch.
4. Create 02 Netting Account.
5. Query AP/AR receipt class (seeded) and attach the Netting Bank Account in AR.
6. Create “01 Netting Agreement”, for this navigation is Receipt>Netting>netting Agreement.
7. Create a standard invoice in AP module for the supplier Advance network Devices .my invoice number is rb1567.
8. Create a invoice in AR for the customer AT & T universal card.invoice number 10953.
9. Create netting Batch “netting batch”, navigation receipt>netting >netting batch.
10. Query your netting batch in netting batch window and click the view report icon, it will take you to view netting report page, click the run button.it will run a request.

11. Again query your batch, click view report icon and again press Run button it will show the proposed netting report.

 

Sample Sql-

 

REQUIREMENTS-   

1.ONLY THOSE SUPPLIERS FOR WHICH A VALID NETTING AGREEMENT EXIST WITH A CUSTOMER.
 2.THE PAYABLES INVOICES SELECTED FOR THE REPORT SHOULD INCLUDE ONLY VALIDATED AND APPROVED INVOICES.
 3.THE RECEIVABLES INVOICES SELECTED FOR THE REPORT SHOULD ONLY INCLUDE COMPLETED INVOICES.

To pull data from Oracle Account Payables of netting :

SELECT hou.NAME organization_name,
aia.invoice_currency_code invoice_currency,
asa.vendor_name supplier, asa.segment1 supplier_number,
assa.vendor_site_code vendor_site,
aia.invoice_num invoice_number,
NVL ((aia.invoice_amount - NVL (aia.amount_paid, 0)),NULL) invoice_due,
null party_site_name,
NULL trx_number,
NULL amount_due_remaining,
DECODE (aia.invoice_currency_code,'GBP', 'AAA',aia.invoice_currency_code )
FROM fun_net_suppliers_all fnsa,
ap_suppliers asa,
ap_invoices_all aia,
ap_supplier_sites_all assa,
hr_operating_units hou
WHERE asa.vendor_id = fnsa.supplier_id
AND asa.vendor_id = aia.vendor_id
AND asa.vendor_id(+) = assa.vendor_id
AND assa.vendor_site_id = NVL (fnsa.supplier_site_id, assa.vendor_site_id)
AND assa.vendor_id(+) = fnsa.supplier_id
AND fnsa.supplier_id = aia.vendor_id
AND hou.organization_id = aia.org_id(+)
AND assa.org_id = hou.organization_id(+)
--AND hou.name in (XXX)-- operating unit
AND asa.vendor_name = NVL (:p_vendor, asa.vendor_name)
AND aia.wfapproval_status in ('WFAPPROVED','MANUALLY APPROVED','APPROVED')
AND NOT EXISTS(SELECT 'N'
FROM ap_invoice_distributions_all
WHERE NVL(match_status_flag, 'C') IN ('T', 'C', 'N', 'S')
AND invoice_id=aia.invoice_id)
AND aia.invoice_date BETWEEN TO_DATE(:p_from_date,'YYYY/MM/DD HH24:MI:SS') and TO_DATE(:p_to_date,'YYYY/MM/DD HH24:MI:SS') ;

To pull data from Oracle Account Receivables of netting:

SELECT hou.NAME organization_name,

       rcta.invoice_currency_code invoice_currency,

       asa.vendor_name supplier, asa.segment1 supplier_number,

       null vendor_site, NULL invoice_number,

       NVL (NULL, NULL) invoice_due,hps.party_site_name party_site_name,rcta.trx_number trx_number,

       NVL (apsa.amount_due_remaining, NULL) amount_due_remaining,

       DECODE (rcta.invoice_currency_code,'GBP', 'AAA',rcta.invoice_currency_code)

  FROM ap_suppliers asa,                                                      

       ap_supplier_sites_all assa,

       hz_cust_accounts_all hcaa,

       ar_payment_schedules_all apsa,

       ra_customer_trx_all rcta,

       hz_cust_site_uses_all hcsua,

       hz_cust_acct_sites_all hcasa,

       hr_operating_units hou,

       fun_net_suppliers_all fnsa,

       fun_net_customers_all fnca,

       hz_party_sites hps

 WHERE asa.vendor_id(+) = assa.vendor_id

   AND fnca.cust_account_id = hcaa.cust_account_id

   AND hcaa.cust_account_id=rcta.bill_to_customer_id

   AND rcta.customer_trx_id = apsa.customer_trx_id

   AND hcaa.cust_account_id = hcasa.cust_account_id

   AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id(+)

   and hps.party_site_id=hcasa.party_site_id

   AND hcsua.site_use_id = NVL (rcta.bill_to_site_use_id, hcsua.site_use_id)                                               

   AND fnca.agreement_id = fnsa.agreement_id

   AND fnsa.supplier_id = asa.vendor_id

   AND fnsa.supplier_site_id=assa.vendor_site_id

   AND hou.organization_id(+) = rcta.org_id

-- AND hou.name in ('XXX')--operating unit

   AND asa.vendor_name = NVL (:p_vendor, asa.vendor_name)

   AND rcta.complete_flag='Y'

   AND rcta.trx_date BETWEEN TO_DATE(:p_from_date,'YYYY/MM/DD HH24:MI:SS')

                                                    and TO_DATE(:p_to_date,'YYYY/MM/DD HH24:MI:SS')      

 

Oracle API to validate gl code combinations in Oracle General Ledger

Oracle API to finds combination_id for given segment values

Oracle Applications

Cross Validation Rule API- CCID in Oracle GL


p_ccid number number ;
l_ccid_number number;
l_set_of_books_id number;

 /* Find the Chart Of Accounts ID */

    SELECT  gls.chart_of_accounts_id,
            gls.set_of_books_id
    INTO    l_ccid_number,
            l_set_of_books_id
    FROM    gl_sets_of_books gls
    WHERE   gls.set_of_books_id = p_sob_id;

 /* Get the delimiter for the code combination */

   l_delimiter := fnd_flex_ext.get_delimiter(
                     application_short_name  => 'SQLGL' ,
                     key_flex_code           => 'GL#' ,
                     structure_number        => l_ccid_number);

 /* Concatenate the segment values */

    l_segments :=p_segment1 || l_delimiter || p_segment2 || l_delimiter ||
                 p_segment3 || l_delimiter || p_segment4 || l_delimiter ||
                 p_segment5 || l_delimiter || p_segment6 || l_delimiter ||
                 p_segment7 || l_delimiter || p_segment8 || l_delimiter ||
                 p_segment9 || l_delimiter || p_segment10;

--API to validate account
/*      Finds combination_id for given segment values.                      */
/*      If validation date is NULL checks all cross-validation rules.       */
/*      Returns TRUE if combination valid, or FALSE and sets error message  */
/*      on server using FND_MESSAGE if invalid.                             */

   x_status :=
      fnd_flex_ext.get_combination_id (application_short_name       => 'SQLGL'
                                      ,key_flex_code                => 'GL#'
                                      ,structure_number           => l_ccid_number
                                      ,validation_date               => SYSDATE
                                      ,n_segments                     => 10 --number of segments
                                      ,segments                         => l_segments
                                      ,combination_id               => p_ccid
                                      ,data_set                          => -1
                                      );

    IF NOT x_status
    THEN
      p_err_msg := 'Invalid Account Segment ' || p_ccid;
      ROLLBACK;
      RETURN 1;
    ELSE
      COMMIT;
      RETURN 0;
    END IF;

--Another API
/* Validate the code combination also, this API will return ccid, if valid and already exists. Else creates a new ccid for a valid code combination.*/

    x_cao_ccid := fnd_flex_ext.get_ccid (
                            application_short_name  => 'SQLGL' ,
                            key_flex_code           => 'GL#' ,
                            structure_number        => l_ccid_number ,
                            validation_date         => to_char(sysdate, 'DD-MON-YYYY') ,
                            concatenated_segments   => l_segments);
    /* If the above API returns a non-zero value, cross-validation successful. */
    IF (x_cao_ccid = 0) THEN
        --Error
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Validation Error' );
        FND_FILE.PUT_LINE(FND_FILE.LOG, SUBSTR(fnd_flex_ext.get_message, 0, 240));
    ELSE
        --success
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Validation Successful' );
       
    END IF;

You can see that below where segments has been defined and segment separator

 

Important SQL queries and tables related to Oracle General Ledger

Important SQL queries and tables related to Oracle GL

Oracle Applications
--sql to find code combinitions

 SELECT code_combination_id, enabled_flag
         FROM gl_code_combinations gcc
             ,gl_sets_of_books gsb
        WHERE gcc.chart_of_accounts_id = gsb.chart_of_accounts_id
          AND segment1 = p_segment1  --pass segment details as your acct structure
          AND segment2 = p_segment2
          AND segment3 = p_segment3
          AND segment4 = p_segment4
          AND segment5 = p_segment5
          AND segment6 = p_segment6
           AND set_of_books_id = p_set_of_book_id; --pass set of books id


--SQL to derive gl CATEGORY NAME

 SELECT *
               FROM gl_je_categories gjc
             WHERE UPPER (gjc.user_je_category_name) = UPPER (:p_cat_name);
          
          
            
--SQL to derive  gl SOURCE NAME

  SELECT *
              FROM gl_je_sources gjs
             WHERE UPPER (gjs.user_je_source_name) = UPPER (:p_source_name);

-- sql to derive gl ledger id

SELECT ledger_id
           FROM gl_ledgers
          WHERE UPPER (NAME) = UPPER (:name);


--SQL to check THAT PERTICULAR PERIOD IS OPEN OR NOT BY PASSING PARAMETER AS PERIOD NAME AND LEDGER ID

SELECT 'X'
               FROM apps.gl_period_statuses gps,
                    apps.fnd_application fa,
                    apps.gl_periods glp
              WHERE gps.period_name = glp.period_name
                AND gps.closing_status IN ('O', 'F')
                AND fa.application_short_name = 'SQLGL'
                AND gps.application_id = fa.application_id
                AND glp.period_set_name = 'JLP_GL_CALENDER' --pass period set name
                AND p_transaction_date BETWEEN glp.start_date AND glp.end_date
                AND gps.ledger_id = v_ledger_id; --set of books id


--To find balances of all Assets, Liabilities & Shareholders Equity accounts as of Dec '13

SELECT * FROM (
SELECT
GL.GL_CODE_COMBINATIONS.SEGMENT1 LEGAL_ENTITY,
GL.GL_CODE_COMBINATIONS.SEGMENT2 ACCOUNT,
GL.GL_CODE_COMBINATIONS.SEGMENT5 ENTRY_IND,
GL.GL_BALANCES.PERIOD_NAME, GL_CODE_COMBINATIONS.ACCOUNT_TYPE ,
SUM((NVL(BEGIN_BALANCE_DR,0)-
NVL(BEGIN_BALANCE_CR,0))+
(NVL(PERIOD_NET_DR,0)-
NVL(PERIOD_NET_CR,0))) YTD_ACTUAL_AMOUNT
FROM
GL.GL_BALANCES,
GL.GL_CODE_COMBINATIONS
WHERE GL.GL_BALANCES.CODE_COMBINATION_ID=GL.GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
AND GL.GL_CODE_COMBINATIONS.ACCOUNT_TYPE IN ('O','L','A')
AND GL.GL_BALANCES.SET_OF_BOOKS_ID = 1
AND GL.GL_BALANCES.PERIOD_NAME = 'DEC-13'
AND GL.GL_BALANCES.CURRENCY_CODE = 'USD'
AND GL.GL_BALANCES.ACTUAL_FLAG ='A'
GROUP BY SEGMENT1, SEGMENT2, SEGMENT5, PERIOD_NAME , ACCOUNT_TYPE
ORDER BY ACCOUNT_TYPE,SEGMENT1 ) WHERE YTD_ACTUAL_AMOUNT <>0 ;

API to create bank branch in Oracle Account Payables

API to create bank branch in Oracle AP R12

Important Note: Please visit my post
'API to create bank in Oracle Applications R12' in this blog to know how to create bank  in Oracle Applications.
'API to create bank account in Oracle Applications R12' in this blog to know how to create bank account in Oracle Applications.
'API to associate bank account to supplier site in Oracle Applications R12' in this blog to know how to associate created bank account to supllier site.

--Declaration
x_msg_count NUMBER;
x_bank_id NUMBER;
v_return_status_account VARCHAR2 (100);
x_response_rec iby_fndcpt_common_pub.result_rec_type;
x_msg_data VARCHAR2 (2000);
x_error_message VARCHAR2 (2000);
--r_ext_bank_branch_rec this is record type

--CHECK BRANCH OF THAT BANK OR NOT by passing branch number

SELECT   COUNT (1), branch_party_id,bank_party_id
INTO x_bank_branch_count, v_branch_party_id,v_bank_party_id
FROM iby_ext_bank_branches_v
WHERE UPPER (branch_number) = UPPER(c_validate_vendor_site_rec.branch_number )
GROUP BY branch_party_id, bank_party_id;
                       
iby_ext_bankacct_pub.create_ext_bank_branch
                               (p_api_version              => 1.0,
                                p_init_msg_list            => fnd_api.g_true,
                                p_ext_bank_branch_rec      => r_ext_bank_branch_rec,
                                x_branch_id                => x_branch_id,
                                x_return_status            => v_return_status,
                                x_msg_count                => x_msg_count,
                                x_msg_data                 => x_msg_data,
                                x_response                 => x_response_rec
                              );
COMMIT;
                      
IF v_return_status_account <> 'S'
THEN
--error
--print error message or use x_msg_data
x_error_message:=fnd_msg_pub.get_detail (x_msg_count, 'F' );
FOR i IN x_msg_count .. 1
LOOP
fnd_msg_pub.delete_msg (i);
END LOOP;
else
--success
end if;            

API to create bank in Oracle Account Payables

API to create bank in Oracle AP

Important Note: Please visit my post
'API to create bank branch in Oracle Applications R12' in this blog to know how to create bank branch in Oracle Applications.
'API to create bank account in Oracle Applications R12' in this blog to know how to create bank account in Oracle Applications.
'API to associate bank account to supplier site in Oracle Applications R12' in this blog to know how to associate created bank account to supllier site.


--Declaration
x_msg_count NUMBER;
x_bank_id NUMBER;
v_return_status_account VARCHAR2 (100);
x_response_rec iby_fndcpt_common_pub.result_rec_type;
x_msg_data VARCHAR2 (2000);
x_error_message VARCHAR2 (2000);
--r_ext_bank_rec this is record type

--CHECK THT BANK EXIST OR NOT by passing bank name

SELECT COUNT (bank_party_id)
INTO x_bank_count
FROM iby_ext_banks_v
WHERE UPPER (bank_name) = UPPER (c_supp_site_pre_rec.bank_name)
AND home_country = 'GB' --for testing, change as per req
AND bank_institution_type = 'BANK';
                 
            

iby_ext_bankacct_pub.create_ext_bank
                                          (p_api_version        => 1.0,
                                           p_init_msg_list      => fnd_api.g_true,
                                           p_ext_bank_rec       => r_ext_bank_rec,
                                           x_bank_id            => x_bank_id,
                                           x_return_status      => v_return_status,
                                           x_msg_count          => x_msg_count,
                                           x_msg_data           => x_msg_data,
                                           x_response           => x_response_rec
                                         );
 
COMMIT;
                         
IF v_return_status_account <> 'S'
THEN
--error
--print error message or use x_msg_data
x_error_message:=fnd_msg_pub.get_detail (x_msg_count, 'F' );
FOR i IN x_msg_count .. 1
LOOP
fnd_msg_pub.delete_msg (i);
END LOOP;
else
--success
end if;