AR Invoice Image Upload Using SOAP Request – Oracle Fusion

 During fusion implementation, Business requirement original receivable invoice image in Oracle Fusion Applications. In such cases, we can use Attachment field option in AR invoices.

The challenge here is, we would do the AR invoice date conversion using FBDI process, but FBDI template doesn’t support image files/Image URL to get uploaded to Fusion Application.

In such cases, the workaround would be to go with REST/SOAP webservices. The below example shows the sample payload of SOAP call to upload images to AR invoices.

End Point URL:
———————
https://serverurl/fscmService/ErpObjectAttachmentService?WSDL

Sample Payload:
———————-
<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:typ=”http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/” xmlns:erp=”http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/”>
<soapenv:Header/>
<soapenv:Body>
<typ:uploadAttachment>
<typ:entityName>RA_CUSTOMER_TRX_ALL</typ:entityName>
<typ:categoryName>CUSTOMER_TRX</typ:categoryName>
<typ:allowDuplicate>Yes</typ:allowDuplicate>
<!–Zero or more repetitions:–>
<typ:attachmentRows>
<!–Business Unit:–>
<erp:UserKeyA>BU Name</erp:UserKeyA>
<!–Transaction Number:–>
<erp:UserKeyB>1000</erp:UserKeyB>
<!–Transaction Source:–>
<erp:UserKeyC>Trx Source</erp:UserKeyC>
<!–CUSTOMER_TRX_ID:–>
<erp:UserKeyD>1</erp:UserKeyD>
<!–AttachmentType:–>
<erp:AttachmentType>FILE</erp:AttachmentType>
<!–File Name:–>
<erp:Title>TESTIMAGE.JPG</erp:Title>
<!–Content: convert the image file to base64 format and input the content below–>
<erp:Content>/9j/4AAQSkZJRgABAQEAYABgAAD/4RCcRXhpZgAATU0AKgAAAAgABAE7AAIAAAAOAAAISodpAAQAAAABAAAIWJydAUUAFFFFABRRRQB/9k=</erp:Content>
</typ:attachmentRows>
</typ:uploadAttachment>
</soapenv:Body>
</soapenv:Envelope>

BPM Approval (Workflow) Table list in Oracle Fusion

BPM(Workflow) Table list in Oracle Fusion

1. POR_AMX_RULES
2. POR_AMX_PARTICIPANTS
3. POR_AMX_DIMENSIONS
4. POR_AMX_TASKS
5. POR_AMX_STAGES
6. POR_AMX_ACTIONS
7. POR_AMX_CONDITIONS

SQL Query

The sql query below extracts information about the BPM approval rules for Purchase requisitions data

SELECT par.rule_id,
       par.rule_number,
       par.rule_name,
       par.DISPLAY_RULE_NAME,
       par.description,
       par.CONDITIONS_STRING,
       par.PRIORITY,
       par.active_flag,
       pat.LOOKUP_CODE pat_LOOKUP_CODE,
       pap.PARTICIPANT_KEY,
       paa.ACTION_CODE,
       paa.APPROVAL_GROUP_NAME,
       pac.condition_number,
       pac.attribute_key cond_key,
       pac.dimension_key,
       pac.operator,
       pac.condition_string
FROM   POR_AMX_RULES PAR,
       POR_AMX_PARTICIPANTS pap,
       POR_AMX_DIMENSIONS   PAD,
       POR_AMX_TASKS PAT,
       (select RULE_NAME max_rul_name, max(OBJECT_VERSION_NUMBER) max_obj 
        from POR_AMX_RULES 
        group by RULE_NAME) rul_max,
       POR_AMX_STAGES   PAS,
       POR_AMX_ACTIONS PAA,
       POR_AMX_CONDITIONS PAC
WHERE  par.active_flag = 'Y'
   and par.PARTICIPANT_ID = pap.PARTICIPANT_ID
   and par.task_id = pap.task_id
   and pas.STAGE_ID = pap.STAGE_ID
   and pas.task_id = pap.task_id
   and pas.stage_id = pap.stage_id
   and PAT.task_id = pad.task_id
   and rul_max.max_obj = par.OBJECT_VERSION_NUMBER
   and rul_max.max_rul_name = par.rule_name
   and paa.rule_id = par.rule_id
   and paa.task_id = par.task_id
   and pac.rule_id = paa.rule_id
   and pac.task_id = paa.task_id
   and pat.task_key = 'ReqApproval'
   and pad.TABLE_NAME  = 'POR_REQUISITION_HEADERS_ALL';

 

Oracle ERP Cloud SQL Query to find Bank Accounts Associated with a Supplier

/******************************************************************* *PURPOSE: ERP Cloud SQL Query to find Bank Accounts Associated with a Supplier * *AUTHOR: Shailender Thallam * *******************************************************************/ SELECT ps.segment1 AS vendor_num , hzp.party_name AS vendor_name , pssm.attribute20 AS legacy_vendor_number , pssm.vendor_site_code , bank.party_name bank_name , branch.party_name branch_name , ieb.bank_account_name , ieb.bank_account_num , ieb.currency_code , ieb.Bank_Account_Name_alt AS TRANSIT_NUMBER , ieb.Secondary_Account_Reference AS ROUTING_NUMBER FROM poz_suppliers ps , poz_supplier_sites_all_m pssm , iby_external_payees_all payee , iby_pmt_instr_uses_all uses , iby_ext_bank_accounts ieb , hz_parties bank , hz_parties branch , HZ_PARTIES HZP WHERE ps.vendor_id = pssm.vendor_id AND ps.party_id = payee.payee_party_id AND payee.supplier_site_id = pssm.vendor_site_id AND uses.instrument_type = 'BANKACCOUNT' AND payee.ext_payee_id = uses.ext_pmt_party_id AND uses.payment_function = 'PAYABLES_DISB' AND uses.instrument_id = ieb.ext_bank_account_id AND ieb.bank_id = bank.party_id(+) AND ieb.branch_id = branch.party_id(+) AND hzp.party_id = ps.party_id AND SYSDATE BETWEEN NVL(uses.start_date,SYSDATE) AND NVL(uses.end_date,SYSDATE) AND SYSDATE BETWEEN NVL(ieb.start_date,SYSDATE) AND NVL(ieb.end_date,SYSDATE) AND NVL(ps.end_date_active,SYSDATE+1) > TRUNC (SYSDATE) AND NVL(pssm.inactive_date,SYSDATE+1) > TRUNC (SYSDATE)

SQL Query to get the Supplier Contact Address Detail Information

Here are the tables that we can refer supplier Information. Suppliers - POZ_SUPPLIERS Contacts - HZ_PARTIES Addresses - HZ_PARTY_SITES Contact Addresses - POZ_SUPPLIER_CONTACTS (Join to HZ_PARTIES on per_party_id - POZ_SUPPLIER_CONTACTS.PER_PARTY_ID = HZ_PARTIES.PARTY_ID ) (Join to HZ_PARTY_SITES on party_site_id - POZ_SUPPLIER_CONTACTS.PARTY_SITE_ID = HZ_PARTY_SITES.PARTY_SITE_ID)

Payment Process Request Query in Fusion

 Payment Process Request in Oracle fusion or E business suit is created to gather all the applicable invoices in P2P Cycle in order to pay the suppliers. ( Come on, they would want their money once they have delivered you the item or the services). Here is the query you can use to get the details:

SELECT aisca.CHECKRUN_ID,
aisca.checkrun_name,
tmplt.template_name ,
aisca.check_date,
aisca.period_name,
aisca.pay_thru_date,
Pmnt_mthd_tl.payment_method_name Payment_Method,
hp.party_name supplier_name,
bank_detail.party_name bank_name,
bank_brach.party_name bank_branch_name,
bank_acct.bank_account_name,
bank_acct.bank_account_num,
bank_acct.masked_account_num,
bank_acct.account_holder_name,
pmnt_doc.payment_document_name,
Pmnt_Prof.payment_profile_name Payment_Profile,
aisca.status
FROM ap_inv_selection_criteria_all aisca,
poz_suppliers supp,
hz_parties hp,
ap_payment_templates tmplt,
ce_payment_documents pmnt_doc,
IBY_ACCT_PMT_PROFILES_TL Pmnt_Prof,
ce_bank_accounts bank_Acct,
hz_parties bank_detail,
hz_parties bank_brach,
iby_payment_methods_tl Pmnt_mthd_tl
WHERE 1=1
AND supp.vendor_id(+) = aisca.vendor_id
and hp.party_id(+) = supp.party_id
AND tmplt.template_id(+) = aisca.template_id
AND pmnt_doc.payment_document_id(+) = aisca.payment_document_id
AND Pmnt_Prof.PAYMENT_PROFILE_ID(+) = aisca.PAYMENT_PROFILE_ID
AND Pmnt_prof.language = USERENV(‘LANG’)
AND bank_Acct.bank_account_id(+) = aisca.bank_account_id
AND Bank_Detail.party_id(+) = bank_Acct.BANK_ID
AND Bank_Brach.party_id(+) = bank_Acct.Bank_branch_id
AND Pmnt_mthd_tl.payment_method_code = aisca.payment_method_code
AND Pmnt_mthd_tl.LANGUAGE = USERENV(‘LANG’)
Order by aisca.creation_date desc

How To Fetch (Query) The Payment Details In Fusion

 Payment: The final leg of P2P Cycle in Oracle. Once you run the payment process Request in Fusion, depending on the criteria you have chosen Invoices will be selected for payment. Once selected, you’ll also get the option to remove the invoices you don’t want to pay. Once you confirm the final list of the invoices to be paid, and take the payment process request to the conclusion, Invoices will be paid (at least in the system. You might still need to send the checks to the vendor or EFT file to the bank)

At high level, this is how the relationship among Payment Process Request, Payment, Checks and Invoices sample looks like

Oracle Fusion Payment structure

To give the further details, every payment process request can have multiple invoices paid against. So relationship between Payment Process Request and Check is 1:N.

Again, 1 Check can be used to pay multiple invoices. At the same time one invoice can be paid by multiple checks. So relationship between review and the Invoices is M:N. Table that holds the relationship is AP_INVOICE_PAYMENTS_ALL.