SupplierSiteAssignmentImport

tables URL:

https://docs.oracle.com/en/cloud/saas/supply-chain-management/18c/oedsc/CMR_XLA_INVOICE_ADJ_TXNS_V-view.html

How to load the supplier's data:

POZ_SITE_ASSIGNMENTS_INT

This summary is not available. Please click here to view the post.

PO Implementation link:


https://docs.oracle.com/en/cloud/saas/procurement/18b/oapro/manage-enterprise-scheduler-job-definitions.html#OAPRO2358293

Getting Banking details:



SELECT b.bank_account_name,
       b.bank_account_num,
       to_char(d.stmt_from_date,'DD-MM-YYYY') transaction_date,  
   c.amount "Amount", 
   decode(c.FLOW_INDICATOR,'DBIT','Debit','CRDT','Credit') item_type,
   d.currency_code "Currency_Code",
     e.trx_code,    
     c.statement_header_id, d.statement_number,--d.currency_code, 
d.stmt_from_date, d.stmt_to_date, d.recon_status_code, d.autorec_process_code, 
     d.statement_entry_type,d.statement_type, c.statement_line_id,c.line_number,c.trx_code_id,
     c.trx_type,c.recon_status, c.amount amount1,
c.addenda_txt reference1, c.customer_reference reference2,
                        (SELECT NVL((FND_FLEX_XML_PUBLISHER_APIS.PROCESS_KFF_COMBINATION_1('FLEXFIELD','GL','GL#',CHART_OF_ACCOUNTS_ID,NULL,CODE_COMBINATION_ID,'ALL','Y','VALUE')),NULL)
                           FROM   gl_code_combinations, ce_external_transactions a
                  WHERE code_combination_id = a.asset_ccid
    AND a.statement_line_id = c.statement_line_id
                    AND a.bank_account_id   = b.bank_account_id
AND a.status = 'REC') AS cash_account, -- Asset_CCID,
                        (SELECT NVL((FND_FLEX_XML_PUBLISHER_APIS.PROCESS_KFF_COMBINATION_1('FLEXFIELD','GL','GL#',CHART_OF_ACCOUNTS_ID,NULL,CODE_COMBINATION_ID,'ALL','Y','VALUE')),NULL)
                   FROM   gl_code_combinations, ce_external_transactions a 
                  WHERE code_combination_id = a.offset_ccid
    AND a.statement_line_id = c.statement_line_id
                    AND a.bank_account_id   = b.bank_account_id
AND a.status = 'REC') AS Offset_Account --offset_ccid
FROM ce_bank_accounts b, ce_statement_lines c, ce_statement_headers d ,ce_transaction_codes e
WHERE d.bank_account_id     = b.bank_account_id    
  AND c.statement_header_id = d.statement_header_id 
  AND  c.trx_code_id=e.transaction_code_id 
  AND b.bank_account_name = NVL(:P_BANK_ACC_NAME,b.bank_account_name)
  AND d.stmt_from_date BETWEEN NVL(:P_FROM_DATE,d.statement_date) AND NVL(:P_TO_DATE,d.statement_date)

ORDER BY b.bank_account_name, transaction_date

BPM Tables in system:


These tables will be available in R12

Fusion Applications BI Publisher : Can we access tables in the XXX_FUSION_SOAINFRA schema in order to report on Workflow Notifications ? (Doc ID 2010398.1)

Until Release 12 of Fusion Application the tables in the XXX_FUSION_SOAINFRA schemas (where XXX can be HCM/CRM/PRC/FIN ) which store information for Workflow Notifications are not accessible from BI Publisher.BI Publisher works through the three ApplicationDB_XXX datasources which are provided out of the box and no additional FusionDB datasources can be defined. As a result access to the XXX_FUSION_SOAINFRA schema would need to be via database grants. At present no such grants have been created and therefore the schema is inaccessible from the FUSION_RUNTIME schema

Starting with Release 12 of Fusion Application the FUSION_RUNTIME has been granted with access on the following schemas:

FIN_FUSION_SOAINFRA

PRC_FUSION_SOAINFRA

HCM_FUSION_SOAINFRA

CRM_FUSION_SOAINFRA

List of tables that can be queries through BI Publisher can be found bellow.

FIN_FUSION_SOAINFRA.WFTASK
FIN_FUSION_SOAINFRA.WFASSIGNEE
FIN_FUSION_SOAINFRA.WFCOMMENTS
FIN_FUSION_SOAINFRA.WFATTACHMENT
FIN_FUSION_SOAINFRA.WFTASKASSIGNMENTSTATISTIC
FIN_FUSION_SOAINFRA.WFTASKHISTORY
FIN_FUSION_SOAINFRA.WFMESSAGEATTRIBUTE
FIN_FUSION_SOAINFRA.WFAPPROVALGROUPS
FIN_FUSION_SOAINFRA.WFAPPROVALGROUPMEMBERS
FIN_FUSION_SOAINFRA.WFCOLLECTIONTARGET

PRC_FUSION_SOAINFRA.WFTASK
PRC_FUSION_SOAINFRA.WFASSIGNEE
PRC_FUSION_SOAINFRA.WFCOMMENTS
PRC_FUSION_SOAINFRA.WFATTACHMENT
PRC_FUSION_SOAINFRA.WFTASKASSIGNMENTSTATISTIC
PRC_FUSION_SOAINFRA.WFTASKHISTORY
PRC_FUSION_SOAINFRA.WFMESSAGEATTRIBUTE
PRC_FUSION_SOAINFRA.WFAPPROVALGROUPS
PRC_FUSION_SOAINFRA.WFAPPROVALGROUPMEMBERS
PRC_FUSION_SOAINFRA.WFCOLLECTIONTARGET

HCM_FUSION_SOAINFRA.WFTASK
HCM_FUSION_SOAINFRA.WFASSIGNEE
HCM_FUSION_SOAINFRA.WFCOMMENTS
HCM_FUSION_SOAINFRA.WFATTACHMENT
HCM_FUSION_SOAINFRA.WFTASKASSIGNMENTSTATISTIC
HCM_FUSION_SOAINFRA.WFTASKHISTORY
HCM_FUSION_SOAINFRA.WFMESSAGEATTRIBUTE
HCM_FUSION_SOAINFRA.WFAPPROVALGROUPS
HCM_FUSION_SOAINFRA.WFAPPROVALGROUPMEMBERS
HCM_FUSION_SOAINFRA.WFCOLLECTIONTARGET



CRM_FUSION_SOAINFRA.WFTASK
CRM_FUSION_SOAINFRA.WFASSIGNEE
CRM_FUSION_SOAINFRA.WFCOMMENTS
CRM_FUSION_SOAINFRA.WFATTACHMENT
CRM_FUSION_SOAINFRA.WFTASKASSIGNMENTSTATISTIC
CRM_FUSION_SOAINFRA.WFTASKHISTORY
CRM_FUSION_SOAINFRA.WFMESSAGEATTRIBUTE
CRM_FUSION_SOAINFRA.WFAPPROVALGROUPS
CRM_FUSION_SOAINFRA.WFAPPROVALGROUPMEMBERS
CRM_FUSION_SOAINFRA.WFCOLLECTIONTARGET

 For HCM_FUSION_SOAINFRA tables you can use also the Human Capital Management - Approval Notification Archive Real Time subject area.

An example query is:
Rport showing requisitions and purchase orders where the approver has reassigned the approval.
select distinct title, to_char(updateddate, 'dd-Mon-yyyy'), fromuser, assignees touser
from prc_fusion_soainfra.wftaskhistory where substate='REASSIGNED'
and compositename in ('PrcPorManageReqApprovalComposite', 'PrcPoApprovalComposite')
and createddate >sysdate-400;

Roles Query from :

SELECT CFT.ENTERPRISE_ID,
 CFT.TREE_STRUCTURE_CODE,
 CFT.TREE_CODE,
 CFT.TREE_VERSION_ID,
 CFT.CF_TREE_NODE_ID,
 CFT.DEP0_PK1_VALUE,
 CFT.DEP0_PK2_VALUE,
 CFT.DEP0_TREE_NODE_ID,
 CFT.DEP1_TREE_NODE_ID,
 CFT.DEP2_TREE_NODE_ID,
 CFT.DEP3_TREE_NODE_ID,
 CFT.DEP4_TREE_NODE_ID,
 CFT.DEP5_TREE_NODE_ID,
 CFT.DEP6_TREE_NODE_ID,
 CFT.DEP7_TREE_NODE_ID,
 CFT.DEP8_TREE_NODE_ID,
 CFT.DEP9_TREE_NODE_ID,
 CFT.DEP10_TREE_NODE_ID,
 CFT.DEP11_TREE_NODE_ID,
 CFT.DEP12_TREE_NODE_ID,
 CFT.DEP13_TREE_NODE_ID,
 CFT.DEP14_TREE_NODE_ID,
 CFT.DEP15_TREE_NODE_ID,
 CFT.DEP16_TREE_NODE_ID,
 CFT.DEP17_TREE_NODE_ID,
 CFT.DEP18_TREE_NODE_ID,
 CFT.DEP19_TREE_NODE_ID,
 CFT.DEP20_TREE_NODE_ID,
 CFT.DEP21_TREE_NODE_ID,
 CFT.DEP22_TREE_NODE_ID,
 CFT.DEP23_TREE_NODE_ID,
 CFT.DEP24_TREE_NODE_ID,
 CFT.DEP25_TREE_NODE_ID,
 CFT.DEP26_TREE_NODE_ID,
 CFT.DEP27_TREE_NODE_ID,
 CFT.DEP28_TREE_NODE_ID,
 CFT.DEP29_TREE_NODE_ID,
 CFT.DEP30_TREE_NODE_ID,
 CFT.DEP31_TREE_NODE_ID,
 DSDEP0_1.VALUE_ID AS DEP0_VALUE_ID,
 DSDEP0_1.VALUE AS DEP0_VALUE FROM GL_SEG_VAL_HIER_CF CFT,
 FND_VS_TYPED_VALUES_VL DSDEP0_1
 WHERE (CFT.DEP0_PK1_VALUE = DSDEP0_1.VALUE(+)
 AND CFT.DEP0_PK2_VALUE = DSDEP0_1.VALUE_SET_CODE(+))
 AND ( ( (( ( CFT.TREE_CODE='Company Country Biz'
 AND (1=2) ) ) OR (CFT.TREE_CODE NOT IN ('Company Curr Country',
 ) ) ))
 AND (CFT.TREE_CODE in (select A.tree_code from fnd_tree_data_source_params A,
 fnd_ts_data_source_params B where A.tree_structure_code = 'GL_ACCT_FLEX'
 and B.tree_structure_code = 'GL_ACCT_FLEX' and A.parameter_id = B.parameter_id
 and B.parameter_name = 'Bind_ValueSetCode' and A.parameter_value in ('COMPANY_CODES'))) ) 

Query to find the suppliers in fusion:


SELECT DISTINCT NULL business_level1,
   NULL business_level2,
   NULL business_level3,
   NULL business_level4,
   c.bu_name buname,
   to_char(assa.vendor_site_id) vendor_site_id,
   to_char(aps.vendor_id) vendor_id,
   assa.attribute1 AS GSL,
   (SELECT hp2.party_name
        FROM hz_parties hp2
        WHERE hp2.party_id = aps.party_id) AS vendor_name,
(SELECT taddn.party_name
FROM hz_addtnl_party_names taddn
WHERE taddn.party_id = aps.party_id
AND taddn.party_name_type = 'PHONETIC' 
AND taddn.preferred_flag = 'Y') AS vendor_name_alt,
(SELECT pii.income_tax_id 
FROM poz_suppliers_pii pii
WHERE  pii.vendor_id = aps.vendor_id) AS tax_identification_number,
aps.organization_type_lookup_code tax_classification,
hl.country country,
    hl.address1 address_line_1,
    hl.address2 address_line_2,
    hl.address3 address_line_3,
    hl.address4 address_line_4,
    hl.city city,
    hl.county county,
    hl.state state_province_region,
    hl.postal_code postal_code,
to_char(assa.prc_bu_id) ORG_ID,
aps.attribute1 GSL6,
assa.vendor_site_code vendor_site_code,
    iepm.payment_method_code payment_method_code,
iepm.payment_method_code remittance_type,
iepa.remit_advice_fax remit_advice_fax,
    iepa.remit_advice_email remit_advice_email,
(select tt.name
          from ap_terms_tl tt
         where tt.term_id = assa.terms_id
           and tt.language = 'US') as PAYMENT_TERMS,
     (select tt.description
          from ap_terms_tl tt
         where tt.term_id = assa.terms_id
           and tt.language = 'US') as PAYMENT_TERM_DESC,
    assa.SUPPLIER_NOTIF_METHOD,
assa.EMAIL_ADDRESS,
    assa.FOB_LOOKUP_CODE INCOTERM,
    assa.purchasing_site_flag,
    assa.pay_site_flag,
    ipiua.primary_flag primary_bank_flag,
    ieba.bank_account_name,
    ieba.bank_account_num,
    ieba.currency_code BANK_ACCOUNT_CURRENCY,
b.bank_name,
    br.bank_branch_name,
    br.branch_number,
b.HOME_COUNTRY country1,
br.eft_swift_code swift_code,
ieba.iban,
to_char(pscv.vendor_contact_id) vendor_contact_id,
pscv.first_name,
    pscv.last_name,
    pscv.email_address CONTACT_EMAIL_ADDRESS,
    pscv.phone CONTACT_PHONE,
b.BANK_NAME_ALT,
ieba.BANK_ACCOUNT_NAME_ALT,
iepm.primary_flag primary_payment_method_flag,
assa.pay_group_lookup_code,
aps.last_update_date SUPP_LAST_UPDATE_DATE,
assa.last_update_date SITE_LAST_UPDATE_DATE,
ieba.last_update_date BANK_LAST_UPDATE_DATE,
pscv.last_update_date CONTACT_LAST_UPDATE_DATE,
aps.segment1 SUPPLIER_NUMBER
FROM
    poz_supplier_sites_all_m assa,
    hz_parties hp,
    iby_ext_bank_accounts ieba,
    iby_external_payees_all iepa,
    iby_pmt_instr_uses_all ipiua,
    poz_suppliers aps,
    hz_parties hp1,
    fun_all_business_units_v c,
    ce_banks_v b,
    ce_bank_branches_v br,
hz_locations hl,
poz_supplier_contacts_v pscv,
iby_ext_party_pmt_mthds iepm
WHERE assa.vendor_site_id = iepa.supplier_site_id
    AND assa.location_id = hl.location_id
    AND hp.party_id = ieba.bank_id
    AND ipiua.instrument_id = ieba.ext_bank_account_id
    AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
AND iepa.ext_payee_id = iepm.ext_pmt_party_id(+) --Added
    AND assa.vendor_id = aps.vendor_id
AND aps.vendor_id = pscv.vendor_id(+)
    AND ieba.branch_id = hp1.party_id
    AND assa.prc_bu_id = c.bu_id
     AND b.bank_party_id = ieba.bank_id
    AND br.bank_party_id = b.bank_party_id
    AND br.branch_party_id = ieba.branch_id
AND aps.end_date_active IS NULL
AND assa.inactive_date IS NULL
   and aps.segment1 in('XXXX')
--AND iepm.primary_flag = 'Y' --primary_payment_method_flag
--AND ipiua.primary_flag = 'Y' --primary_bank_flag

ORDER BY c.bu_name

Security Table in Fusion:

If you need the company values description you may find it in : fnd_vs_values_tl table
For data access set table you can use : GL_ACCESS_SETS

Query to get customer Extract:


SELECT 1 "KEY",
  TRIM(HCA.ATTRIBUTE1) SFDC_ID,
  HP.PARTY_NUMBER Oracle_ERP_Customer_Id,
  HCA.ACCOUNT_NUMBER Oracle_ERP_Customer_Account_no,
  HPS.PARTY_SITE_NUMBER Oracle_ERP_Customer_Site_no,
  HCA.CUST_ACCOUNT_ID Oracle_ERP_Customer_Account_id,
  HPS.PARTY_SITE_ID Oracle_ERP_Customer_Site_Id,
  HCPF.EFFECTIVE_START_DATE ORACLE_ERP_CREATE_DATE,
  HCPF.LAST_UPDATE_DATE ORACLE_ERP_UPDATE_DATE,
  HCPF.CREDIT_RATING CREDIT_RATING,
  HCPF.CREDIT_CLASSIFICATION CREDIT_CLASSIFICATION,
  HCPF.CREDIT_CURRENCY_CODE CREDIT_CURRENCY_CODE, --- Rel 006
  HCPF.CREDIT_LIMIT CREDIT_LIMIT,
  TRIM(HCAS.ATTRIBUTE3) ORACLE_BUC_CODE,
  ZPTP.REP_REGISTRATION_NUMBER ORACLE_VAT_TAX_CODE,
  NULL ORACLE_TAX_JURISDICTION_CODE
FROM HZ_PARTIES HP,
  HZ_PARTY_SITES HPS,
  HZ_CUST_ACCOUNTS HCA,
  HZ_CUST_ACCT_SITES_ALL HCAS,
  HZ_CUSTOMER_PROFILES_F HCPF,
  ZX_PARTY_TAX_PROFILE ZPTP
WHERE 1                 =1
AND HCA.PARTY_ID        = HP.PARTY_ID
AND HP.PARTY_ID         = HPS.PARTY_ID
AND HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID
AND HCA.CUST_ACCOUNT_ID = HCPF.CUST_ACCOUNT_ID
AND HCAS.PARTY_SITE_ID  = HPS.PARTY_SITE_ID
AND ZPTP.PARTY_ID(+)    = HPS.PARTY_SITE_ID
AND HCA.ATTRIBUTE1     IS NOT NULL
AND HCPF.SITE_USE_ID   IS NULL
AND HCA.STATUS          = 'A'
AND HP.STATUS           = 'A'
AND HCAS.STATUS         = 'A'
AND HPS.STATUS          = 'A'
AND HCPF.STATUS         = 'A'

UNION
SELECT 1 "KEY",
  TRIM(HCA.ATTRIBUTE1) SFDC_ID,
  HP.PARTY_NUMBER Oracle_ERP_Customer_Id,
  HCA.ACCOUNT_NUMBER Oracle_ERP_Customer_Account_no,
  HPS.PARTY_SITE_NUMBER Oracle_ERP_Customer_Site_no,
  HCA.CUST_ACCOUNT_ID Oracle_ERP_Customer_Account_id,
  HPS.PARTY_SITE_ID Oracle_ERP_Customer_Site_Id,
  HCPF.EFFECTIVE_START_DATE ORACLE_ERP_CREATE_DATE,
  HCPF.LAST_UPDATE_DATE ORACLE_ERP_UPDATE_DATE,
  HCPF.CREDIT_RATING CREDIT_RATING,
  HCPF.CREDIT_CLASSIFICATION CREDIT_CLASSIFICATION,
  HCPF.CREDIT_CURRENCY_CODE CREDIT_CURRENCY_CODE, --- Rel 006
  HCPF.CREDIT_LIMIT CREDIT_LIMIT,
  TRIM(HCAS.ATTRIBUTE3) ORACLE_BUC_CODE,
  ZPTP.REP_REGISTRATION_NUMBER ORACLE_VAT_TAX_CODE,
  NULL ORACLE_TAX_JURISDICTION_CODE
FROM HZ_PARTIES HP,
  HZ_PARTY_SITES HPS,
  HZ_CUST_ACCOUNTS HCA,
  HZ_CUST_ACCT_SITES_ALL HCAS,
  HZ_CUSTOMER_PROFILES_F HCPF,
  ZX_PARTY_TAX_PROFILE ZPTP
WHERE 1                                       =1
AND HCA.PARTY_ID                              = HP.PARTY_ID
AND HP.PARTY_ID                               = HPS.PARTY_ID
AND HCA.CUST_ACCOUNT_ID                       = HCAS.CUST_ACCOUNT_ID
AND HCA.CUST_ACCOUNT_ID                       = HCPF.CUST_ACCOUNT_ID
AND HCAS.PARTY_SITE_ID                        = HPS.PARTY_SITE_ID
AND ZPTP.PARTY_ID(+)                          = HPS.PARTY_SITE_ID
AND HCA.ATTRIBUTE1                           IS NOT NULL
AND HCPF.SITE_USE_ID                         IS NULL
AND HCA.STATUS                                = 'A'
AND HP.STATUS                                 = 'A'
AND HCAS.STATUS                               = 'A'
AND HPS.STATUS                                = 'A'
AND HCPF.STATUS                               = 'A'
UNION
SELECT 1 "KEY",
  TRIM(HCA.ATTRIBUTE1) SFDC_ID,
  HP.PARTY_NUMBER Oracle_ERP_Customer_Id,
  HCA.ACCOUNT_NUMBER Oracle_ERP_Customer_Account_no,
  HPS.PARTY_SITE_NUMBER Oracle_ERP_Customer_Site_no,
  HCA.CUST_ACCOUNT_ID Oracle_ERP_Customer_Account_id,
  HPS.PARTY_SITE_ID Oracle_ERP_Customer_Site_Id,
  HCPF.EFFECTIVE_START_DATE ORACLE_ERP_CREATE_DATE,
  HCPF.LAST_UPDATE_DATE ORACLE_ERP_UPDATE_DATE,
  HCPF.CREDIT_RATING CREDIT_RATING,
  HCPF.CREDIT_CLASSIFICATION CREDIT_CLASSIFICATION,
  HCPF.CREDIT_CURRENCY_CODE CREDIT_CURRENCY_CODE, --- Rel 006
  HCPF.CREDIT_LIMIT CREDIT_LIMIT,
  TRIM(HCAS.ATTRIBUTE3) ORACLE_BUC_CODE,
  ZPTP.REP_REGISTRATION_NUMBER ORACLE_VAT_TAX_CODE,
  NULL ORACLE_TAX_JURISDICTION_CODE
FROM HZ_PARTIES HP,
  HZ_PARTY_SITES HPS,
  HZ_CUST_ACCOUNTS HCA,
  HZ_CUST_ACCT_SITES_ALL HCAS,
  HZ_CUSTOMER_PROFILES_F HCPF,
  ZX_PARTY_TAX_PROFILE ZPTP
WHERE 1                                       =1
AND HCA.PARTY_ID                              = HP.PARTY_ID
AND HP.PARTY_ID                               = HPS.PARTY_ID
AND HCA.CUST_ACCOUNT_ID                       = HCAS.CUST_ACCOUNT_ID
AND HCA.CUST_ACCOUNT_ID                       = HCPF.CUST_ACCOUNT_ID
AND HCAS.PARTY_SITE_ID                        = HPS.PARTY_SITE_ID
AND ZPTP.PARTY_ID(+)                          = HPS.PARTY_SITE_ID
AND HCA.ATTRIBUTE1                           IS NOT NULL
AND HCPF.SITE_USE_ID                         IS NULL
AND HCA.STATUS                                = 'A'
AND HP.STATUS                                 = 'A'
AND HCAS.STATUS                               = 'A'
AND HPS.STATUS                                = 'A'
AND HCPF.STATUS                               = 'A'

ORDER BY ORACLE_ERP_CUSTOMER_ID DESC

Query to find the GL Status:


select
gl.name,
gps.PERIOD_NAME,
fa.application_SHORT_name,
gps.CLOSING_STATUS,
gps.START_DATE,
gps.application_id,
gps.END_DATE,
gps.last_updated_by  from GL_PERIOD_STATUSES gps,
GL_LEDGERS gl,
FND_application fA
where 
gl.ledger_id=gps.ledger_id
and gl.name in ('GL')
and fa.application_id=gps.application_id
and PERIOD_NAME like ('%16%')

and fa.application_SHORT_name like 'GL'

Fusion Subject area vs Role:

FIN-OTBI-Sub-Area-Doc-Master-R13.18B
R13.18A_SCM-and-PRC-OTBI-Sub-Area-Doc-R13

 

https://cloudcustomerconnect.oracle.com/.

**************************** How to find the DFF based on some table name *******************************
select distinct
fdfc.application_column_name attribute_no,
fdfc.end_user_column_name column1,
fdf.title,
fdfc.descriptive_flex_context_code dff_code,
fdf.application_table_name,
fdfc.display_flag Displayed,
fdfc.enabled_flag Enabled,
fdfc.required_flag Required1
from
apps.fnd_descriptive_flexs_vl fdf,
apps.fnd_descr_flex_col_usage_vl fdfc
where 1=1
and fdf.descriptive_flexfield_name=fdfc.descriptive_flexfield_name
and fdf.title like '%Vendor%Sites%'
---and fdf.descriptive_flexfield_name like '%PER_PEOPLE%'
and FDFC.DESCRIPTIVE_FLEX_CONTEXT_CODE='11565'
and FDFC.ENABLED_FLAG = 'Y'
---and fdfc.end_user_column_name like '%APPROVAL%STATUS%'
---order by FDFC.LAST_UPDATE_DATE desc

Hash Map in OAF:


            Hashtable params = new Hashtable (1);
             params.put ("param1", pageName);
             Hashtable paramsWithBinds = new Hashtable(3);
           
             paramsWithBinds.put ("param2", new OADataBoundValueFireActionURL(ActualFlag, "{$PoHeaderId}"));
             paramsWithBinds.put ("param3", new OADataBoundValueFireActionURL(ActualFlags, "{$LINE_LOCATION_ID}"));
           
             paramsWithBinds.put ("param4", new OADataBoundValueFireActionURL(ActualFlag, "{$ActualFlag}"));
             FireAction fireAction = OAWebBeanUtils.getFireActionForSubmit (MHDDate, "MHDDateEvent", params, paramsWithBinds,false, false);
               ActualFlag.setFireActionForSubmit("ActualFlagEvent",params, paramsWithBinds,true,true);

How to change VO Query dynamically in OAF:

Dynamically Changing the VO query in OAF:

Generally while creating the View Objects we define a query to it. While rendering the OAF page these view objects get executed (either by framework or by the java code).

In some scenarios we might have a requirement to change the query of the VO, this query can be modified dynamically.

Example: Lets say a VO(EMPVO) has the query as
SELECT empno,ename,sal
FROM EMP

Now I want to change the query as below
SELECT empno,ename,sal+nvl(comm,0)
FROM EMP

Sample code:

String query = “SELECT empno,ename,sal+nvl(comm,0) FROM EMP”;
try
{
OAApplicationModule oam = oapagecontext.getApplicationModule(oawebbean);

// get the handle for your view object

EMPVOImpl voimpl = (EMPVOImpl)oam.findViewObject(“EMPVO”); 

voimpl.setFullSqlMode(voimpl.FULLSQL_MODE_AUGMENTATION);
voimpl.setQuery(query);

// setQuery only sets the new query to the View Object, in order to effect the changes of the query we need to execute the equery using below statement.

voimpl.executeQuery();
}
catch (Exception e)
{

}

Always need to call setFullSqlMode(voimpl.FULLSQL_MODE_AUGMENTATION) before executing the query, If you won’t call this OA Framework will not append the where clause and Order by clauses correctly.
The above code is written in controller, you can write the code in either processRequest method or in processFormRequest based on your requirement or in AM.

(Note: The number of columns and the order of the column data types must be same as the original query, so this code cann’t be used for adding new columns to the original query, for this we need to go for extension of VO)

How to get the payload file from payments:


SELECT DOCUMENT FROM IBY_TRXN_DOCUMENTS WHERE PAYMENT_INSTRUCTION_ID =<document number from payment>

How to get the payment file information:

Transmit Payment File log  for file name

Format Payment Files for xml file

How to define a AR template for each BU :

Transaction « View Image » By Business Unit (Doc ID 2188688.1)

<?if:AmountChangeDisplay !=''?>Net Order Amount Changed :<?AmountChangeDisplay?> <?end if?>


<?if:OneTimeLocationFlag>0?><?ONETIMEADD?>  <?end if?>


<?if:RevisionNum <>''?>Revision :<?RevisionNum?> <?end if?>

<?if:OrderedDisplay=''?>Ordered :<?RevisionNum?> <?end if?>

<?if:RevisionNum=''?>Revision :<?RevisionNum?> <?end if?>

<?if:RevisionNum=''?>Revision :<?RevisionNum?> <?end if?>

<?if:RevisionNum !=''?>Revision:<?RevisionNum?> <?end if?>

<?if:ChangeOrderDateDisplay !=''?>Change Order Date::<?ChangeOrderDateDisplay?> <?end if?>

Ordered: <?OrderedDisplay?> <?CurrencyCode?>

<?if:OrderedDisplay !=''?>Ordered:<?OrderedDisplay?> <?CurrencyCode?> <?end if?>


Order Amount Before Change: <?OriginalOrderAmountDisplay?>



<?if:AmountChangeDisplay !=''?>Net Order Amount Changed :<?AmountChangeDisplay?> <?end if?>

<?if:RevisionNum !=''?>Revision:<?RevisionNum?> <?end if?>

<?if:RevisionNum !=''?>Revision:<?RevisionNum?> <?end if?>








<?if: INVOICE_CURRENCY_CODE ='CAD'?> 'USD Cu' <?end if?>

<?if:OneTimeLocationFlag>0?><?ONETIMEADD?>  <?end if?>


Net Order Amount Changed : <?AmountChangeDisplay?>


<?ChangeOrderDateDisplay?>

How to upload bank statements in Fusion:

1) Get the BAI2 files
2)go to schedule process and submit a program with below details.

load interface file for import 

parameters : Process BAI2 Format Bank Statements and select data file.

Once this program is complete you will able to see the transactions in system.

From:

 Manage Bank Statements in Cash Management role

3)Then you have to create a external transaction for the uploaded statements.

go to schedule process and submit a program with below details.

Create Bank statement Transactions 

Parameters are Bank account details and from and to date of statements.

Once this is done you are able to see these details in
Manage external Transactions in Cash Management role


4) Final process is need to move the transactions to accounting .

Submit a program called create accounting 

With below parameters:

Sub ledger as Cash management.

Ledger Name: you ledger Name.

Process Catagory as : External Transactions.

end date:

accounting mode: Final

Process Events: All

Report Style: Summery

Transfer to GL: Yes

Post GL: Yes

Journal Batch:

Include User transaction identifiers: Yes

Once it is done loading statement process is done.