Sunday, July 14, 2024

Saturday, January 20, 2024

Query To Fetch AP Invoice Details From SO Number(Doc ID 2949013.1)

SELECT dh.source_order_number
      ,df.source_line_number as so_line_number
  ,df.fulfill_line_number 
  ,ddr.doc_user_key as po_number
  ,ai.invoice_num
  ,ail.quantity_invoiced
  ,ail.amount
  ,ail.line_number as ap_invoice_line_number
  FROM fusion.ap_invoices_all ai
      ,fusion.ap_invoice_lines_all ail
  ,fusion.doo_document_references ddr
  ,fusion.doo_headers_all dh
  ,fusion.doo_fulfill_lines_all df
 WHERE ai.invoice_id = ail.invoice_id
   AND dh.source_order_number = <sales_order_number>
   AND ddr.doc_id = TO_CHAR(ai.po_header_id)
   AND ddr.fulfill_line_id = df.fulfill_line_id
   AND df.header_id = dh.header_id

Thursday, January 18, 2024

Query to get Item Category Details in Oracle Fusion

SELECT items.item_number
      ,itemcats.category_id
  ,itemcats.sequence_number
  ,itemcats.alt_item_cat_code
  ,ect.description
  ,ect.category_name
  FROM egp_item_cat_assignments itemcats
      ,egp_category_sets_b catsets
  ,egp_system_items_b items
  ,inv_org_parameters org
  ,egp_categories_tl ect
 WHERE catsets.category_set_id = itemcats.category_set_id
   AND items.inventory_item_id = itemcats.inventory_item_id
   AND org.organization_id = items.organization_id
   AND itemcats.category_id=ect.category_id
   AND ( ( catsets.control_level = 1
AND itemcats.organization_id = org.master_organization_id 
     )
  OR ( catsets.control_level = 2
AND itemcats.organization_id = org.organization_id
 
)

Query for AR Sales Representative Name in Oracle Fusion

SELECT hp.party_name sales_representative
  FROM ra_customer_trx_all rcta
      ,jtf_rs_salesreps jrs
  ,hz_parties hp
 WHERE rcta.primary_resource_salesrep_id=jrs.resource_salesrep_id
   AND jrs.resource_id=hp.party_id

Join between Sales Order(SO) and Receivables(AR) in Oracle Fusion

SELECT *
  FROM ra_customer_trx_all rcta
      ,ra_customer_trx_lines_all ra
  ,doo_fulfill_lines_all l
 WHERE ra.interface_line_attribute5 = TO_CHAR(l.fulfill_line_id)
   AND rcta.customer_trx_id = ra.customer_trx_id
   AND rcta.trx_number = '123456789';


You can use INTERFACE_LINE_ATTRIBUTE5 column in RA_CUSTOMER_TRX_LINES_ALL which stores Fulfill Line ID of Sales Order.

Friday, November 24, 2023

Query to get AP Invoice Distribution to Inventory Material Distribution Data

SELECT mmt.transaction_date
          ,(SELECT ml.meaning
              FROM mfg_lookups ml
             WHERE ml.lookup_Type = 'CST_ACCOUNTING_LINE_TYPE'
               AND ml.lookup_code = mta.accounting_line_type
                ) accounting_type
          ,gl_code.concatenated_segments account, mtt.transaction_type_name transaction_type
          ,(SELECT po_hdr.segment1 
              FROM po_headers_all po_hdr 
             WHERE po_hdr.po_header_id = po_dist.po_header_id) transaction_source
          ,mmt.transaction_uom uom
          ,mta.primary_quantity 
          ,mta.base_transaction_value
          ,mmt.transaction_id, mmt.transaction_type_id, items.segment1 item, mmt.inventory_item_id, mmt.organization_id
          ,gl_code.code_combination_id
      FROM ap_invoice_distributions_all inv_dist
          ,po_distributions_all po_dist
          ,rcv_transactions rcv_trx
          ,mtl_material_transactions mmt
          ,mtl_transaction_types mtt
          ,mtl_transaction_accounts mta
          ,gl_code_combinations_kfv gl_code
          ,mtl_system_items_b items
     WHERE 1=1
       --AND inv_dist.po_distribution_id = 10536072
       AND inv_dist.po_distribution_id = po_dist.po_distribution_id
       AND po_dist.po_header_id = rcv_trx.po_header_id
       AND po_dist.po_line_id = rcv_trx.po_line_id
       AND po_dist.line_location_id = rcv_trx.po_line_location_id
       AND po_dist.po_distribution_id = rcv_trx.po_distribution_id
       AND transaction_type = 'DELIVER'
       AND mmt.rcv_transaction_id = rcv_trx.transaction_id
       AND mmt.transaction_type_id = mtt.transaction_type_id
       AND mmt.transaction_id = mta.transaction_id
       AND mta.reference_account = gl_code.code_combination_id
       AND mmt.inventory_item_id = items.inventory_item_id
       AND mmt.organization_id = items.organization_id

Monday, June 5, 2023

Query to get Requisition to PO Details in Oracle Fusion

SELECT DISTINCT PRHA.REQUISITION_NUMBER,
                PHA.SEGMENT1 PO_NUMBER,
                PAH.OBJECT_TYPE_CODE,
                PAH.OBJECT_SUB_TYPE_CODE,
                PAH.SEQUENCE_NUM,
                PPTF.FULL_NAME REQUESTER,
                PAH.ACTION_CODE,
                PAH.ACTION_DATE
  FROM POR_REQUISITION_HEADERS_ALL PRHA,
       POR_REQUISITION_LINES_ALL PRLA,
       POR_REQ_DISTRIBUTIONS_ALL PRDA,
       PO_HEADERS_ALL PHA,
       PO_LINES_ALL PLA,
       PO_DISTRIBUTIONS_ALL PDA,
       PO_ACTION_HISTORY PAH,
       PER_PERSON_NAMES_F PPTF
 WHERE 1 = 1
       AND PRHA.requisition_number IN
              ('REQ-12345')
       AND PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID
       AND PRLA.REQUISITION_LINE_ID = PRDA.REQUISITION_LINE_ID
       AND PRDA.DISTRIBUTION_ID = PDA.REQ_DISTRIBUTION_ID
       AND PDA.PO_HEADER_ID = PHA.PO_HEADER_ID
       AND PDA.PO_LINE_ID = PLA.PO_LINE_ID
       AND PRHA.REQUISITION_HEADER_ID = PAH.OBJECT_ID
       AND PPTF.PERSON_ID = PAH.PERFORMER_ID
       AND PPTF.name_type='GLOBAL'
       AND TRUNC(SYSDATE) BETWEEN PPTF.effective_start_date AND PPTF.effective_end_date

Saturday, March 4, 2023

What is the difference between PER_ALL_ASSIGNMENTS_M and PER_ALL_ASSIGNMENTS_F in Oracle Fusion

PER_ALL_ASSIGNMENTS_M is the core table for assignments.
PER_ALL_ASSIGNMENTS_F is a view which stores effective latest change only.

The definition of the PER_ALL_ASSIGNMENTS_F view:

SELECT *
  FROM per_all_assignments_m
WHERE  EFFECTIVE_LATEST_CHANGE = 'Y';

If you want all the data then use the base table : PER_ALL_ASSIGNMENTS_M

If you want the effective latest change only, please use : PER_ALL_ASSIGNMENTS_F

Wednesday, February 8, 2023

Query to get Employee Leave Balance for Specific Plan in Oracle Fusion

SELECT papf.person_number employee_number
      ,ppn.full_name employee_name
  ,TO_CHAR(acc.procd_date,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN') transaction_date
  ,SUM(acc.value) balance
  FROM per_all_people_f papf
  ,per_person_names_f ppn
  ,anc_per_acrl_entry_dtls acc
  ,anc_absence_plans_vl abpv
WHERE papf.person_id = ppn.person_id
  AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
  AND TRUNC(SYSDATE) BETWEEN ppn.effective_start_date AND ppn.effective_end_date
  AND ppn.name_type = 'GLOBAL'
  AND acc.person_id = papf.person_id
  AND abpv.absence_plan_id = acc.pl_id
  AND abpv.name = 'XX Plan'
  AND papf.person_number = '123456789'--:p_person_number
GROUP BY papf.person_number 
      ,ppn.full_name 

Friday, January 27, 2023

Query to get Customer Details in Oracle Fusion

select party.party_id
      ,party.party_number
      ,party.party_name customer_name
      ,(select party_nm.attribute1 from HZ_PERSON_PROFILES party_nm where party_nm.party_id = party.party_id) person_name_global
      ,party.party_type 
      ,party.orig_system_reference party_orig_system_reference
      ,party.sales_account_id
      ,cust_account.cust_account_id
      ,cust_account.account_number 
      ,cust_account.customer_class_code
      ,cust_account.orig_system_reference cust_orig_system_reference
      ,cust_accts.cust_acct_site_id
      ,cust_accts.party_site_id
      ,cust_accts.orig_system_reference cust_site_orig_system_reference
      ,cust_accts.bill_to_flag
      ,cust_accts.ship_to_flag
      ,party_site.location_id
      ,party_site.party_site_number
      ,party_site.party_site_name
      ,party_site.orig_system_reference party_site_orig_system_reference
      ,site_use.site_use_id
      ,site_use.site_use_code
      ,site_use.primary_flag
      ,site_use.location
      ,site_use.orig_system_reference site_use_orig_system_reference
      ,ref_accts.bu_id
      ,ref_accts.ledger_id
      ,ledger.name ledger_name
      ,ref_accts.rev_ccid
      ,ref_accts.rec_ccid
      ,rev_gcc.segment1||'-'||rev_gcc.segment2||'-'||rev_gcc.segment3||'-'||rev_gcc.segment4||'-'||rev_gcc.segment5||'-'||rev_gcc.segment6||'-'||rev_gcc.segment7||'-'||rev_gcc.segment8 revenue_account
      ,rec_gcc.segment1||'-'||rec_gcc.segment2||'-'||rec_gcc.segment3||'-'||rec_gcc.segment4||'-'||rec_gcc.segment5||'-'||rec_gcc.segment6||'-'||rec_gcc.segment7||'-'||rec_gcc.segment8 receivable_account
from hz_parties party
    ,hz_cust_accounts cust_account
    ,hz_cust_acct_sites_all cust_accts
    ,hz_party_sites party_site
    ,hz_cust_site_uses_all site_use
    ,ar_ref_accounts_all ref_accts
    ,gl_code_combinations rev_gcc
    ,gl_code_combinations rec_gcc
    ,gl_ledgers ledger
where party_name = 'AYMAN ABDULMOHSEN AL-OJAIMI'
  and party.party_id = cust_account.party_id
  and cust_account.cust_account_id = cust_accts.cust_account_id
  and cust_accts.party_site_id = party_site.party_site_id
  and cust_accts.cust_acct_site_id = site_use.cust_acct_site_id
  --AND site_use.primary_flag = 'Y'
  AND ref_accts.source_ref_table(+) = 'HZ_CUST_SITE_USES_ALL'
  and ref_accts.source_ref_account_id(+) = site_use.site_use_id
  and ref_accts.rev_ccid = rev_gcc.code_combination_id(+)
  and ref_accts.rec_ccid = rec_gcc.code_combination_id(+)
  and ref_accts.ledger_id = ledger.ledger_id(+)

Friday, January 6, 2023

Script to update Supplier pay_group_lookup_code in Oracle APPS R12

DECLARE
lc_return_status  VARCHAR2(2000);
ln_msg_count      NUMBER;
ll_msg_data        LONG;
Ln_Vendor_Id      NUMBER;
Ln_Vendor_site_Id NUMBER;
ln_message_int    NUMBER;
Ln_Party_Id        NUMBER;
lrec_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
BEGIN
-- Initialize apps session
fnd_global.apps_initialize(0, 50554, 200);
mo_global.set_policy_context('S',204);
mo_global.init('SQLAP');
 
Ln_Vendor_Id                          := 1;
Lrec_Vendor_Rec.pay_group_lookup_code := 'Employee';
ap_vendor_pub_pkg.update_vendor_public(p_api_version => 1
                                          ,x_return_status => lc_return_status
                                          ,x_msg_count => ln_msg_count
                                          ,x_msg_data => ll_msg_data
                                          ,p_vendor_rec => Lrec_Vendor_Rec
                                          ,p_Vendor_Id => Ln_Vendor_Id
  );
IF (lc_return_status <> 'S') 
THEN
IF ln_msg_count    >= 1 THEN
FOR v_index IN 1..ln_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => v_index
                ,p_encoded => 'F'
                ,p_data => ll_msg_data
,p_msg_index_out => ln_message_int 
);
Ll_Msg_Data := 'UPDATE_VENDOR '||SUBSTR(Ll_Msg_Data,1,3900);
dbms_output.put_line('Ll_Msg_Data - '||Ll_Msg_Data );
END LOOP;
END IF;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('SQLERRM - '||SQLERRM );
ROLLBACK;
END

Script to update Supplier Type(ap_suppliers.vendor_type_lookup_code) in Oracle APPS R12

DECLARE
lc_return_status  VARCHAR2(2000);
ln_msg_count      NUMBER;
ll_msg_data        LONG;
Ln_Vendor_Id      NUMBER;
Ln_Vendor_site_Id NUMBER;
ln_message_int    NUMBER;
Ln_Party_Id        NUMBER;
lrec_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
BEGIN
-- Initialize apps session
fnd_global.apps_initialize(0, 50554, 200);
mo_global.set_policy_context('S',204);
mo_global.init('SQLAP');
 
Ln_Vendor_Id                          := 1;
Lrec_Vendor_Rec.vendor_type_lookup_code := 'LOCAL';
ap_vendor_pub_pkg.update_vendor_public(p_api_version => 1
                                          ,x_return_status => lc_return_status
                                          ,x_msg_count => ln_msg_count
                                          ,x_msg_data => ll_msg_data
                                          ,p_vendor_rec => Lrec_Vendor_Rec
                                          ,p_Vendor_Id => Ln_Vendor_Id
  );
IF (lc_return_status <> 'S') 
THEN
IF ln_msg_count    >= 1 THEN
FOR v_index IN 1..ln_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => v_index
                ,p_encoded => 'F'
                ,p_data => ll_msg_data
,p_msg_index_out => ln_message_int 
);
Ll_Msg_Data := 'UPDATE_VENDOR '||SUBSTR(Ll_Msg_Data,1,3900);
dbms_output.put_line('Ll_Msg_Data - '||Ll_Msg_Data );
END LOOP;
END IF;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('SQLERRM - '||SQLERRM );
ROLLBACK;
END

Script to update Supplier Tax Payer ID(AP_SUPPLIERS.NUM_1099) in Oracle APPS R12

DECLARE
lc_return_status  VARCHAR2(2000);
ln_msg_count      NUMBER;
ll_msg_data        LONG;
Ln_Vendor_Id      NUMBER;
Ln_Vendor_site_Id NUMBER;
ln_message_int    NUMBER;
Ln_Party_Id        NUMBER;
lrec_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
BEGIN
-- Initialize apps session
fnd_global.apps_initialize(0, 50554, 200);
mo_global.set_policy_context('S',204);
mo_global.init('SQLAP');
 
Ln_Vendor_Id                          := 1;
Lrec_Vendor_Rec.jgzz_fiscal_code := 'X123456789X';
ap_vendor_pub_pkg.update_vendor_public(p_api_version => 1
                                          ,x_return_status => lc_return_status
                                          ,x_msg_count => ln_msg_count
                                          ,x_msg_data => ll_msg_data
                                          ,p_vendor_rec => Lrec_Vendor_Rec
                                          ,p_Vendor_Id => Ln_Vendor_Id
  );
IF (lc_return_status <> 'S') 
THEN
IF ln_msg_count    >= 1 THEN
FOR v_index IN 1..ln_msg_count
LOOP
fnd_msg_pub.get (p_msg_index     => v_index
                ,p_encoded       => 'F'
,p_data          => ll_msg_data
,p_msg_index_out => ln_message_int 
);
Ll_Msg_Data := 'UPDATE_VENDOR '||SUBSTR(Ll_Msg_Data,1,3900);
dbms_output.put_line('Ll_Msg_Data - '||Ll_Msg_Data );
END LOOP;
END IF;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('SQLERRM - '||SQLERRM );
ROLLBACK;
END

Sunday, December 18, 2022

API to Create Online Accounting for AP Payment in Oracle APPS R12 (ap_drilldown_pub_pkg.payment_online_accounting)

    PROCEDURE xx_payment_accounting(errbuf OUT VARCHAR2
   ,retcode           OUT VARCHAR2
   ,p_invoice_id    IN  NUMBER
   )
IS
CURSOR c1 
IS
SELECT DISTINCT aca.check_id, aca.check_number        
              FROM ap_invoices_all xlpi
      ,ap_invoice_payments_all aipa
  ,ap_checks_all aca
             WHERE xlpi.invoice_id   = aipa.invoice_id
               AND xlpi.invoice_id   = pin_invoice_id
               AND aipa.check_id     = aca.check_id
               AND ap_checks_pkg.get_posting_status(aca.check_id) = 'N';
   -- This condition is to pick invoices which are paid and unaccounted
   ;  
ln_processed_cnt    NUMBER := 0;
ln_failed_cnt       NUMBER := 0;  
lv_error_buf        VARCHAR2(4000);
ln_retcode          NUMBER; 
BEGIN

FOR rec_create_pmt_accounting IN c1
LOOP
BEGIN
fnd_file.put_line(fnd_file.log,'Check ID: '||rec_create_pmt_accounting.check_id);
ln_retcode   := NULL;
lv_error_buf := NULL;
ap_drilldown_pub_pkg.payment_online_accounting(p_check_id           => rec_create_pmt_accounting.check_id
  ,p_accounting_mode    => 'P'      
  ,p_errbuf             => lv_error_buf
  ,p_retcode            => ln_retcode
  ,p_calling_sequence   => 'XX_PAYMENT_ACCOUNTING'
  );
IF ln_retcode = 0 
THEN
fnd_file.put_line(fnd_file.log,'Invoice Check ID: '
||rec_create_pmt_accounting.check_id 
||'. Payment ('
||rec_create_pmt_accounting.check_number
||') Accounted Sucessfully'
);
ln_processed_cnt := ln_processed_cnt+1;
ELSIF ln_retcode = 1 THEN
fnd_file.put_line(fnd_file.log,'Invoice Check ID: '
||rec_create_pmt_accounting.check_id
||'. Payment ('
||rec_create_pmt_accounting.check_number
||') Accounting ended in WARNING. Errbuf : '
||lv_error_buf
);
ln_processed_cnt := ln_processed_cnt+1;
ELSIF ln_retcode = 2 THEN
fnd_file.put_line(fnd_file.log,'Invoice Check ID: '
||rec_create_pmt_accounting.check_id 
||'. Payment ('
||rec_create_pmt_accounting.check_number
||') Accounting ended in ERROR. Errbuf : '
||lv_error_buf
);       
ln_failed_cnt := ln_failed_cnt +1;          
ELSE
fnd_file.put_line(fnd_file.log,'Invoice Check ID: '
||rec_create_pmt_accounting.check_id 
||'. Payment ('
||rec_create_pmt_accounting.check_number
||') Accounting ended in ERROR. Errbuf : '
||lv_error_buf
||' Retcode: '
||ln_retcode
);
ln_failed_cnt := ln_failed_cnt +1;
END IF; 
COMMIT;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Invoice Check ID: ' 
||rec_create_pmt_accounting.check_id
||'. Payment Accounting failed with Unhandled Exception. Error Message:' 
||SQLERRM);
ln_failed_cnt := ln_failed_cnt + 1;
END;
END LOOP;
fnd_file.put_line ( fnd_file.log,'Processed Record Count: '
||ln_processed_cnt
||' Failed Record Count: '
||ln_failed_cnt);
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line ( fnd_file.log,'Main Error in xx_payment_accounting. Error Message: '||SQLERRM);
END xx_payment_accounting;

ap_checks_pkg.get_posting_status(check_id) to pick AP Invoice Payments which are not acconted.

Pass check_id from ap_checks_all (aca) to ap_checks_pkg.get_posting_status to pick invoice payments which are not accounted.

ap_checks_pkg.get_posting_status(aca.check_id)

Saturday, December 17, 2022

INVALID_PATH: File location or filename was invalid(UTL_FILE) in Oracle APPS

1. Create Directory using below command

CREATE OR REPLACE directory XXDIR AS '/home/erp';

--Here change directory path(/home/erp) according to your path.

Give the required permissions to above path.

2. Connect to WinSCP/Putty and Create directory.

3. Now use the directory name in utl_file 

UTL_FILE.FOPEN('XXDIR ','file_name','R');


Wednesday, November 9, 2022

Package jdr_utils in OAF

Jdr_utils.listcustomizations:

 begin 
    dbms_output.put_line('***START***');
    Jdr_utils.listcustomizations('/oracle/apps/ce/bankaccounttransfer/webui/BankAccountTransferPG');
    dbms_output.put_line('***STOP***'); 
end;
  • Searches Personalization’s for the OAF Page. We can find the Page URL (path) from about this page.

jdr_utils.listDocuments:- Similar to list customizations, it searches recursively, we do not specify the page name, but path.

exec jdr_utils.listDocuments('/oracle/apps/ce/bankaccounttransfer/webui/', true)


Jdr_utils.listContents:- Recursively finds all personalization's from a given path.

exec Jdr_utils.listContents ('/oracle/apps/ce/bankaccounttransfer/webui', true)


jdr_utils.DeleteDocument:- It deletes Personalization.
exec jdr_utils.DeleteDocument('/oracle/apps/eam/workorder/webui/customizations/site/0/EAM_EW_START_PAGE') 

Thursday, November 3, 2022

Invalid customer transaction identifier.APPLY : ORA-01403: no data foundORA-01403: no data found in Package AR_RECEIPT_API_PUB Procedure Apply

One of the possible reason is Invoice already paid.

You can validate with below query. 

select * from ra_customer_trx_all where trx_number = '1'

note customer_trx_id from the above query.

select * from ar_receivable_applications_all

where applied_customer_trx_id = <pass customer trx id>

note cash_receipt_id from above query 

select * from ar_cash_receipts_all

where cash_receipt_id = <pass cash receipt id>


Tuesday, October 25, 2022

Is there any API to End Date GL Code Combinations from Backend in Oracle APPS

As per the Oracle as of now there is no API to End Date GL Code Combinations from Backend. Please refer below notes from Oracle Support.

  • Is Effective Date From / 'Start Date Active' Mandatory for GL Code Combinations? Can it Be Changed to "null" Value? (Doc ID 2763034.1)
    • Check if these details could have been ported from the segment values, in that case verify if you want to have Start/End Date for the segment values left in the system while the same details would be removed for the code combinations
    • Any sql update on gl_code_combinations table will not be supported in any way. You must perform the changes in the code combinations form, manually.
    • To port changes from segment values to code combinations, you may use Program - Inherit Segment Value Attributes (see referenced documents)
  • GLNSVI: What is the Purpose of the Segment Value Inheritance Program/ Program - Inherit Segment Value Attributes ? (Doc ID 170569.1)

AR Receipt Reversal Categories(REVERSAL_CATEGORY_TYPE) in Oracle APPS

SELECT lookup_code
      ,meaning
      ,description
  FROM fnd_lookup_values 
WHERE lookup_type = 'REVERSAL_CATEGORY_TYPE'
    AND LANGUAGE = USERENV('LANG')
    AND lookup_code = 'REV'

Friday, October 21, 2022

Script to Update Liability Account, Prepay Account, Invoice Withholding Tax Group and Tax Classification Code at Supplier Site Level in Oracle APPS

Table Script:

DROP TABLE XXSCHEMA.XX_VENDOR_MASTER_DATA CASCADE CONSTRAINTS;

CREATE TABLE XXSCHEMA.XX_VENDOR_MASTER_DATA
(
  VENDOR_NAME                    VARCHAR2(250 BYTE),
  VENDOR_ID                      NUMBER,
  VENDOR_SITE_CODE               VARCHAR2(15 BYTE),
  ORG_ID                         NUMBER,
  LIABILITY_ACCOUNT              VARCHAR2(207 BYTE),
  NEW_LIABILITY_ACCOUNT          VARCHAR2(207 BYTE),
  PRE_PAYMENT_ACCOUNT            VARCHAR2(207 BYTE),
  NEW_PRE_PAYMENT_ACCOUNT        VARCHAR2(207 BYTE),
  TAX_CLASSIFICATION_CODE        VARCHAR2(207 BYTE),
  NEW_TAX_CLASSIFICATION_CODE    VARCHAR2(30 BYTE),
  INVOICE_WITHHOLDING_TAX_GROUP  VARCHAR2(25 BYTE),
  NEW_INVOICE_WHT_TAX_GROUP      VARCHAR2(25 BYTE),
  SUPPLIER_TYPE                  VARCHAR2(80 BYTE),
  NEW_SUPPLIER_TYPE              VARCHAR2(80 BYTE),
  TAX_APPLICABILITY              VARCHAR2(80 BYTE),
  SUPP_STATUS_FLAG               VARCHAR2(1 BYTE),
  SUPP_STATUS_MSG                VARCHAR2(2000 BYTE),
  SUPP_SITE_STATUS_FLAG          VARCHAR2(1 BYTE),
  SUPP_SITE_STATUS_MSG           VARCHAR2(2000 BYTE)
);

DROP SYNONYM APPS.XX_VENDOR_MASTER_DATA;

CREATE OR REPLACE SYNONYM APPS.XX_VENDOR_MASTER_DATA FOR XXSCHEMA.XX_VENDOR_MASTER_DATA;

API Script:
-----------------------------------------------------------------------------------------------
DECLARE
    v_api_version              NUMBER;
    v_init_msg_list            VARCHAR2(200);
    v_commit                    VARCHAR2(200);
    v_validation_level          NUMBER;
    x_return_status            VARCHAR2(200);
    x_msg_count                NUMBER;
    v_vendor                    NUMBER;
    x_msg_data                  VARCHAR2(200);
    l_vendor_site_rec          apps.ap_vendor_pub_pkg.r_vendor_site_rec_type;
    l_existing_vendor_site_rec ap_supplier_sites_all%rowtype;
    l_vendor_site_id            NUMBER;
l_liability_ccid gl_code_combinations_kfv.code_combination_id%TYPE;
l_prepay_ccid gl_code_combinations_kfv.code_combination_id%TYPE;
l_ap_awt_group_id ap_awt_groups.group_id%TYPE;
l_allow_awt_flag ap_suppliers.allow_awt_flag%TYPE;
    v_calling_prog              VARCHAR2(200);
    
    l_msg                      VARCHAR2(200);
    l_status_flag         VARCHAR(1):='N';
    l_status_msg     VARCHAR2(2000) := NULL;
        
    CURSOR c 
IS
SELECT DISTINCT 
            vendor_id
               ,vendor_site_code
   ,new_tax_classification_code
                   ,new_invoice_wht_tax_group
                   ,new_liability_account
                   ,new_pre_payment_account
          FROM xx_vendor_master_data
         WHERE NVL(supp_status_flag, 'N') = 'S'
   AND NVL(supp_site_status_flag, 'N') IN ('N', 'E')
   --AND vendor_id = 7216
   --AND vendor_id IN (5041)--5059)
   ;

BEGIN
    FOR i IN c 
LOOP
l_status_flag     := 'N';
l_status_msg      := NULL;
l_liability_ccid  := NULL;
l_prepay_ccid   := NULL;
l_ap_awt_group_id := NULL;
l_allow_awt_flag  := 'N';
BEGIN
SELECT vendor_site_id 
  INTO l_vendor_site_id
  FROM ap_supplier_sites_all
WHERE vendor_id = i.vendor_id
   AND vendor_site_code = i.vendor_site_code
   AND org_id = 101
   ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_vendor_site_id := null;
l_status_flag := 'E';
l_status_msg := 'Unable to find vendor_site_code. '; 
WHEN OTHERS THEN
l_vendor_site_id := NULL;
l_status_flag := 'E';
l_status_msg := 'Error while validating vendor_site_code. error message: ' || SQLERRM;  
END;
BEGIN
SELECT code_combination_id 
  INTO l_liability_ccid
  FROM gl_code_combinations_kfv
WHERE concatenated_segments = TRIM(i.new_liability_account)
   ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_liability_ccid := NULL;
l_status_flag := 'E';
l_status_msg := l_status_msg||'-Unable to find new_liability_account. '; 
WHEN OTHERS THEN
l_liability_ccid := NULL;
l_status_flag := 'E';
l_status_msg := l_status_msg||'-Error while validating new_liability_account. error message: ' || SQLERRM;  
END;
BEGIN
SELECT code_combination_id 
  INTO l_prepay_ccid
  FROM gl_code_combinations_kfv
WHERE concatenated_segments = TRIM(i.new_pre_payment_account)
   ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_prepay_ccid := NULL;
l_status_flag := 'E';
l_status_msg := l_status_msg||'-Unable to find new_pre_payment_account. '; 
WHEN OTHERS THEN
l_prepay_ccid := NULL;
l_status_flag := 'E';
l_status_msg := l_status_msg||'-Error while validating new_pre_payment_account. error message: ' || SQLERRM;  
END;
IF TRIM(i.new_invoice_wht_tax_group) IS NOT NULL THEN
BEGIN
SELECT group_id 
  INTO l_ap_awt_group_id
  FROM ap_awt_groups
WHERE name = TRIM(i.new_invoice_wht_tax_group)
   ;
l_allow_awt_flag := 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_ap_awt_group_id := NULL;
l_status_flag := 'E';
l_status_msg := l_status_msg||'-Unable to find new_invoice_wht_tax_group. '; 
WHEN OTHERS THEN
l_ap_awt_group_id := NULL;
l_status_flag := 'E';
l_status_msg := l_status_msg||'-Error while validating new_invoice_wht_tax_group. error message: ' || SQLERRM;  
END;
END IF;
dbms_output.put_line('l_status_flag = ' || l_status_flag);
dbms_output.put_line('l_status_msg = ' || l_status_msg);
        IF l_status_flag <> 'E' THEN
fnd_global.apps_initialize(0, 50240, 200);
mo_global.init('SQLAP');
mo_global.set_policy_context('S', 101);
v_api_version := 1.0;
v_init_msg_list := fnd_api.g_true;
v_commit := fnd_api.g_true;
v_validation_level := fnd_api.g_valid_level_full;
--l_vendor_site_id := j.vendor_site_id; -- to be end dated
v_calling_prog := 'Supplier Tax code update';
l_vendor_site_rec.vendor_site_id := l_existing_vendor_site_rec.vendor_site_id;
l_vendor_site_rec.last_update_date := sysdate;
--l_vendor_site_rec.last_updated_by := 6147023;
l_vendor_site_rec.vendor_id := l_existing_vendor_site_rec.vendor_id;
l_vendor_site_rec.org_id := l_existing_vendor_site_rec.org_id;
l_vendor_site_rec.accts_pay_code_combination_id := l_liability_ccid;
l_vendor_site_rec.prepay_code_combination_id := l_prepay_ccid;
l_vendor_site_rec.vat_code := i.new_tax_classification_code;
l_vendor_site_rec.awt_group_id := l_ap_awt_group_id;
l_vendor_site_rec.allow_awt_flag := l_allow_awt_flag;
--l_vendor_site_rec.auto_tax_calc_flag := 'y';
--l_vendor_site_rec.vat_registration_num := i.tax_registration_no;
ap_vendor_pub_pkg.update_vendor_site_public(p_api_version => v_api_version
                                           ,p_init_msg_list => v_init_msg_list
       ,p_commit => v_commit
                                           ,p_validation_level  => v_validation_level
       ,x_return_status => x_return_status
       ,x_msg_count => x_msg_count
       ,x_msg_data     => x_msg_data
       ,p_vendor_site_rec   => l_vendor_site_rec
       ,p_vendor_site_id    => l_vendor_site_id
       ,p_calling_prog      => v_calling_prog
       );

dbms_output.put_line('x_return_status = ' || x_return_status);
dbms_output.put_line('x_msg_count = ' || x_msg_count);
dbms_output.put_line('x_msg_data = ' || x_msg_data);
IF (x_return_status <> fnd_api.g_ret_sts_success) 
THEN
FOR i1 IN 1 .. fnd_msg_pub.count_msg 
LOOP
l_msg := fnd_msg_pub.get(p_msg_index => i1,
  p_encoded   => fnd_api.g_false);
DBMS_OUTPUT.put_line('The API call failed with error ' || l_msg);
UPDATE xx_vendor_master_data 
   SET supp_site_status_flag = 'E'
      ,supp_site_status_msg = l_msg 
     WHERE vendor_id = i.vendor_id
   AND vendor_site_code = i.vendor_site_code;
END LOOP;
ELSE
   DBMS_OUTPUT.put_line('The API call ended with SUCESSS status');
   UPDATE xx_vendor_master_data 
  SET supp_site_status_flag = 'S'
,supp_site_status_msg = 'Update Successful' 
WHERE vendor_id = i.vendor_id
  AND vendor_site_code = i.vendor_site_code
  ;
END IF;
ELSE
UPDATE xx_vendor_master_data 
   SET supp_site_status_flag = 'E'
      ,supp_site_status_msg = l_status_msg 
WHERE vendor_id = i.vendor_id
   AND vendor_site_code = i.vendor_site_code
   ;
END IF;
COMMIT;
    END LOOP;
END;


Script to Update Supplier Vendor Type and Invoice Withholding Tax Group at Supplier Level in Oracle APPS

Table Script:

DROP TABLE XXSCHEMA.XX_VENDOR_MASTER_DATA CASCADE CONSTRAINTS;

CREATE TABLE XXSCHEMA.XX_VENDOR_MASTER_DATA
(
  VENDOR_NAME                    VARCHAR2(250 BYTE),
  VENDOR_ID                      NUMBER,
  VENDOR_SITE_CODE               VARCHAR2(15 BYTE),
  ORG_ID                         NUMBER,
  LIABILITY_ACCOUNT              VARCHAR2(207 BYTE),
  NEW_LIABILITY_ACCOUNT          VARCHAR2(207 BYTE),
  PRE_PAYMENT_ACCOUNT            VARCHAR2(207 BYTE),
  NEW_PRE_PAYMENT_ACCOUNT        VARCHAR2(207 BYTE),
  TAX_CLASSIFICATION_CODE        VARCHAR2(207 BYTE),
  NEW_TAX_CLASSIFICATION_CODE    VARCHAR2(30 BYTE),
  INVOICE_WITHHOLDING_TAX_GROUP  VARCHAR2(25 BYTE),
  NEW_INVOICE_WHT_TAX_GROUP      VARCHAR2(25 BYTE),
  SUPPLIER_TYPE                  VARCHAR2(80 BYTE),
  NEW_SUPPLIER_TYPE              VARCHAR2(80 BYTE),
  TAX_APPLICABILITY              VARCHAR2(80 BYTE),
  SUPP_STATUS_FLAG               VARCHAR2(1 BYTE),
  SUPP_STATUS_MSG                VARCHAR2(2000 BYTE),
  SUPP_SITE_STATUS_FLAG          VARCHAR2(1 BYTE),
  SUPP_SITE_STATUS_MSG           VARCHAR2(2000 BYTE)
);

DROP SYNONYM APPS.XX_VENDOR_MASTER_DATA;

CREATE OR REPLACE SYNONYM APPS.XX_VENDOR_MASTER_DATA FOR XXSCHEMA.XX_VENDOR_MASTER_DATA;


API Script:
-----------------------------------------------------------------------------------------------
--SET serveroutput on;
DECLARE
p_api_version          NUMBER;
p_init_msg_list        VARCHAR2(200);
p_commit                VARCHAR2(200);
p_validation_level      NUMBER;
x_return_status        VARCHAR2(200);
x_msg_count            NUMBER;
x_msg_data              VARCHAR2(200);
lr_vendor_rec          apps.ap_vendor_pub_pkg.r_vendor_rec_type;
lr_existing_vendor_rec ap_suppliers%ROWTYPE;
l_msg                  VARCHAR2(200);
p_vendor_id            NUMBER;
l_new_supplier_type fnd_lookup_values.lookup_code%type;
l_allow_awt_flag ap_suppliers.allow_awt_flag%TYPE;
l_ap_awt_group_id     ap_awt_groups.group_id%TYPE;
l_status_flag VARCHAR(1):='N';
l_status_msg VARCHAR2(2000) := NULL;

CURSOR c_vendor_type IS
SELECT DISTINCT vendor_id
               ,supplier_type
   ,new_supplier_type
                       ,new_invoice_wht_tax_group    
  FROM xx_vendor_master_data
WHERE NVL(supp_status_flag, 'N') IN ('N', 'E')
   --AND vendor_id = 7216
   AND vendor_id IN (5041)--5059)
   ;
    BEGIN
-- Initialize apps session
fnd_global.apps_initialize(0, 50240, 200); --user_id, 
mo_global.init('SQLAP');
fnd_client_info.set_org_context(101);
-- Assign Basic Values
p_api_version      := 1.0;
p_init_msg_list    := fnd_api.g_true;
p_commit           := fnd_api.g_true;
p_validation_level := fnd_api.g_valid_level_full;
     
FOR j in c_vendor_type 
        LOOP
            p_vendor_id        := j.vendor_id;
l_status_flag     := 'N';
l_status_msg      := NULL;
l_ap_awt_group_id := NULL;
l_allow_awt_flag  := 'N';
    
            -- gather vendor details
            BEGIN
                SELECT *
                  INTO lr_existing_vendor_rec
                  FROM ap_suppliers asa
                 WHERE asa.vendor_id = p_vendor_id;
            EXCEPTION
    WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Unable to derive the supplier  information for vendor id:' ||
                                      p_vendor_id);
END;
            
IF j.supplier_type <> j.new_supplier_type THEN
BEGIN
SELECT lookup_code
  INTO l_new_supplier_type
  FROM fnd_lookup_values
WHERE meaning = j.new_supplier_type
   AND lookup_type = 'VENDOR TYPE'
   AND enabled_flag = 'Y'
   AND language = userenv('LANG');
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_new_supplier_type := null;
l_status_flag := 'E';
l_status_msg := 'Supplier Type does not exists in VENDOR TYPE Lookup: ' || SQLERRM; 
WHEN OTHERS THEN
l_new_supplier_type := null;
l_status_flag := 'E';
l_status_msg := 'Error while validating new supplier type. error message: ' || SQLERRM;  
END;
ELSE
NULL;
END IF;
IF TRIM(j.new_invoice_wht_tax_group) IS NOT NULL THEN
BEGIN
SELECT group_id 
  INTO l_ap_awt_group_id
  FROM ap_awt_groups
WHERE name = TRIM(j.new_invoice_wht_tax_group)
   ;
l_allow_awt_flag := 'Y';
EXCEPTION
WHEN OTHERS THEN
l_ap_awt_group_id := NULL;
l_status_flag := 'E';
l_status_msg := l_status_msg||'-Error while validating new_invoice_wht_tax_group. error message: ' || SQLERRM;  
END;
END IF;
dbms_output.put_line('l_status_flag = ' || l_status_flag);
dbms_output.put_line('l_status_msg = ' || l_status_msg);
    
             lr_vendor_rec.vendor_id       := lr_existing_vendor_rec.vendor_id;
-----------------------------------------------
--Deactivate Vendor
             -- lr_vendor_rec.end_date_active := SYSDATE;
             -- lr_vendor_rec.enabled_flag    := 'N';
-----------------------------------------------
             lr_vendor_rec.vendor_type_lookup_code := l_new_supplier_type;
lr_vendor_rec.allow_awt_flag := l_allow_awt_flag;
lr_vendor_rec.awt_group_id := l_ap_awt_group_id;
            IF l_status_flag != 'E'
THEN
ap_vendor_pub_pkg.update_vendor(p_api_version      => p_api_version
   ,p_init_msg_list    => p_init_msg_list
   ,p_commit           => p_commit
   ,p_validation_level => p_validation_level
   ,x_return_status    => x_return_status
   ,x_msg_count        => x_msg_count
   ,x_msg_data         => x_msg_data
   ,p_vendor_rec       => lr_vendor_rec
   ,p_vendor_id        => p_vendor_id
   );
DBMS_OUTPUT.put_line('X_RETURN_STATUS = ' || x_return_status);
DBMS_OUTPUT.put_line('X_MSG_COUNT = ' || x_msg_count);
                DBMS_OUTPUT.put_line('X_MSG_DATA = ' || x_msg_data);
    
IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
FOR i IN 1 .. fnd_msg_pub.count_msg 
LOOP
l_msg := fnd_msg_pub.get(p_msg_index => i
                        ,p_encoded   => fnd_api.g_false
);
DBMS_OUTPUT.put_line('The API call failed with error ' || l_msg);
UPDATE xx_vendor_master_data 
                           SET supp_status_flag = 'E'
      ,supp_status_msg = l_msg 
     WHERE vendor_id = j.vendor_id;
                    END LOOP;
                ELSE
DBMS_OUTPUT.put_line('The API call ended with SUCESSS status');
    UPDATE xx_vendor_master_data 
   SET supp_status_flag = 'S'
  ,supp_status_msg = 'Update Successful' 
WHERE vendor_id = j.vendor_id;
END IF;
ELSE
UPDATE xx_vendor_master_data 
   SET supp_status_flag = 'E'
      ,supp_status_msg = l_status_msg 
WHERE vendor_id = j.vendor_id;
            END IF;
        END LOOP;
    END;
/

AP_SUPPLIER_SITES_ALL.AUTO_TAX_CALC_FLAG in Oracle APPS(AP_TAX_CALCULATION_METHOD)

SELECT *
  FROM fnd_lookup_values
 WHERE lookup_type = 'AP_TAX_CALCULATION_METHOD'
 AND language = USERENV('LANG')

Here AP_SUPPLIER_SITES_ALL.AUTO_TAX_CALC_FLAG will be fnd_lookup_values.lookup_code



Wednesday, October 19, 2022

API call failed with error ORA-01403: no data found in Package AP_VENDOR_PUB_PKG Procedure Validate_Vendor_Site

We had a requirement to update Tax Classification Code, Invoice Withholding Tax Group at supplier level. While using ap_vendor_pub_pkg.update_vendor_site API we got below error message:

The API call failed with error ORA-01403: no data found in Package AP_VENDOR_PUB_PKG Procedure Validate_Vendor_Site

But when used ap_vendor_pub_pkg.update_vendor_site_public the update was successful.

CE_PAYMENT_TRANSACTIONS.SETTLE_BY_SYSTEM_FLAG in Oracle Cash Management

CE_PAYMENT_TRANSACTIONS.SETTLE_BY_SYSTEM_FLAG Determines whether the settlement is required through oracle Payments.

Tuesday, October 18, 2022

AP_CHECKS_ALL.PAYMENT_METHOD_LOOKUP_CODE and AP_CHECKS_ALL.PAYMENT_METHOD_CODE in Oracle APPS

Below is from Oracle Metalink ID 1919051.1

PAYMENT_METHOD_LOOKUP_CODE is obsolete in R12 hence it should not be referred in R12. If you want to refer payment method code from Invoice header then PAYMENT_METHOD_CODE should be used. During upgrade to R12 we populate PAYMENT_METNHOD_CODE with PAYMENT_METHOD_LOOKUP_CODE for migrated invoices.

SELECT payment_method_code, payment_method_name, description
  FROM iby_payment_methods_tl
 WHERE payment_method_code IN
          (SELECT DISTINCT PAYMENT_METHOD_CODE
              FROM AP_CHECKS_ALL
              )
AND language = USERENV('LANG')

Monday, October 17, 2022

Query to get Asset Categories with GL Codes in Oracle APPS

SELECT fcb.book_type_code
       ,fct.segment1||'.'||fct.segment2 category
       ,fct.enabled_flag enabled
       ,fct.description
       ,fct.capitalize_flag  capitalize
       ,flt.meaning category_type
       ,fct.inventorial physical_inventory
       ,flt1.meaning ownership 
       ,fct.property_type_code property_type
       ,gcck.concatenated_segments asset_cost
       ,gcck1.concatenated_segments asset_clearing
       ,gcck2.concatenated_segments depreciation_expense
       ,gcck3.concatenated_segments accumulated_depreciation
       ,gcck4.concatenated_segments bonus_expense
       ,gcck5.concatenated_segments bonus_reserve
       ,gcck6.concatenated_segments revaluation_reserve
       ,gcck7.concatenated_segments revaluation_amortization
       ,gcck8.concatenated_segments cip_cost
       ,gcck9.concatenated_segments cip_clearing
       ,gcck10.concatenated_segments impairment_expense
       ,gcck11.concatenated_segments accumulated_impairment
       ,gcck12.concatenated_segments unplanned_depreciatn_exp
       ,gcck13.concatenated_segments alternate_asset_cost
       ,gcck14.concatenated_segments write_off_expense
       ,fcbd.start_dpis placed_in_service_from
       ,fcbd.end_dpis placed_in_service_to
       ,fcbd.depreciate_flag depreciate
       ,fcbd.deprn_method 
       ,fcbd.life_in_months
       ,FLOOR(fcbd.life_in_months/12) life_in_years
       ,fcbd.bonus_rule
       ,fcbd.prorate_convention_code
       ,fcbd.retirement_prorate_convention
       ,fcbd.percent_salvage_value
       ,fcbd.ceiling_name
       ,fcbd.price_index_name
       ,fcbd.subcomponent_life_rule
       ,fcbd.minimum_life_in_months
       ,FLOOR(fcbd.minimum_life_in_months/12) minimum_life_in_years
       ,fcbd.use_stl_retirements_flag
       ,fcbd.stl_method_code 
       ,FLOOR(fcbd.stl_life_in_months/12) slr_life_years
       ,fcbd.use_deprn_limits_flag
       ,fcbd.allowed_deprn_limit
       ,fcbd.special_deprn_limit_amount
       ,FLOOR(fcbd.capital_gain_threshold/12) cgt_in_years
       ,MOD(fcbd.capital_gain_threshold,12) cgt_in_months
       ,fcbd.itc_eligible_flag       
       ,fcbd.use_itc_ceilings_flag
       ,fcbd.mass_property_flag
       ,fcbd.group_asset_id group_asset
       ,fcbd.recognize_gain_loss
       ,fcbd.terminal_gain_loss
       ,fcbd.recapture_reserve_flag 
       ,fcbd.limit_proceeds_flag
       ,fcbd.tracking_method
       ,fcbd.allocate_to_fully_rsv_flag
       ,fcbd.excess_allocation_option distribute_or_reduce_excess
   FROM apps.fa_categories_vl fct
       ,apps.fa_lookups_tl flt
       ,apps.fa_lookups_tl flt1
       ,apps.fa_category_books fcb
       ,apps.gl_code_combinations_kfv gcck
       ,apps.gl_code_combinations_kfv gcck1
       ,apps.gl_code_combinations_kfv gcck2
       ,apps.gl_code_combinations_kfv gcck3
       ,apps.gl_code_combinations_kfv gcck4
       ,apps.gl_code_combinations_kfv gcck5
       ,apps.gl_code_combinations_kfv gcck6
       ,apps.gl_code_combinations_kfv gcck7
       ,apps.gl_code_combinations_kfv gcck8
       ,apps.gl_code_combinations_kfv gcck9
       ,apps.gl_code_combinations_kfv gcck10
       ,apps.gl_code_combinations_kfv gcck11
       ,apps.gl_code_combinations_kfv gcck12
       ,apps.gl_code_combinations_kfv gcck13
       ,apps.gl_code_combinations_kfv gcck14
       ,apps.fa_category_book_defaults fcbd
  WHERE 1=1
    AND fct.category_type = flt.lookup_code(+)
    AND flt.lookup_type(+) = 'CATEGORY TYPE'
    AND flt1.lookup_code(+) = fct.owned_leased
    AND flt1.lookup_type(+) ='OWNLEASE'
    AND fct.category_id = fcb.category_id
    AND gcck.code_combination_id(+) = fcb.asset_cost_account_ccid
    AND gcck1.code_combination_id(+) = fcb.asset_clearing_account_ccid
    AND gcck2.code_combination_id(+) = fcb.deprn_expense_account_ccid
    AND gcck3.code_combination_id(+) = fcb.reserve_account_ccid
    AND gcck4.code_combination_id(+) = fcb.bonus_expense_account_ccid
    AND gcck5.code_combination_id(+) = fcb.bonus_reserve_acct_ccid
    AND gcck6.code_combination_id(+) = fcb.reval_reserve_account_ccid
    AND gcck7.code_combination_id(+) = fcb.reval_amort_account_ccid
    AND gcck8.code_combination_id(+) = fcb.wip_cost_account_ccid
    AND gcck9.code_combination_id(+) = fcb.wip_clearing_account_ccid
    AND gcck10.code_combination_id(+) = fcb.impair_expense_account_ccid
    AND gcck11.code_combination_id(+) = fcb.impair_reserve_account_ccid
    AND gcck12.code_combination_id(+) = fcb.unplan_expense_account_ccid
    AND gcck13.code_combination_id(+) = fcb.alt_cost_account_ccid
    AND gcck14.code_combination_id(+) = fcb.write_off_account_ccid
    AND fcb.category_id = fcbd.category_id
    AND fct.category_id = fcb.category_id
    AND fcb.book_type_code = fcbd.book_type_code
    AND fcb.book_type_code = :p_book_type_code
    

SupplierAddressImportTemplate.xlsm South Africa Suburb Field mapping in POZ_SUPPLIER_ADDRESSES_INT

Suburb mpping in Supplier Address Import Template will be mapped to Address Element Attribute2 (HZ_LOCATIONS. ADDR_ELEMENT_ATTRIBUTE2)