User Cannot Manually Create Tax For A Credit Memo Yet They Are Able To Create Tax For An Invoice

To implement the solution, please execute the following steps:

1. Navigate to Setup and Maintenance > Setup: Financials > Functional Area: Transaction Tax

2. Task "Manage Configuration Owner Tax Options"

3. Search by the Configuration Owner (Business Unit or Legal Entity depending on your setup) and Event Class as "Credit Memo". If not present create a new entry for the Configuration Owner/Event Class.

4. Verify if the checkbox "Allow entry of manual tax lines" is checked. If not, user will need to end date the current record and create a new one with this checkbox as checked.

5. Retest the issue

How to Default Input VAT tax or any tax from Receivable Transaction Fusion Cloud

How to Default Input VAT tax or any tax from Receivable Transaction 

 

The Manage Application Tax Option is set for product Receivables, for this specific BU, to default the tax classification code S4, based on Receivable system options.

But the setup is not complete: in Manage Receivables System options the Item Validation Organization is snot set.

However, the Tax Classification information is changed and it is trying to default a new one when the transaction line Item/Memo Line/ Description is changed, because the used Transaction Type is checked the Default Tax Classification Code option.

If the business requirement is to not default the Tax Classification code for transaction line, then please uncheck this option for used transaction type:

1. Got to Setup and Maintenance > Financials Setup > Customer billing

2. Manage transaction types task > Search by issue transaction type name

3. Ensure the flag - Default tax classification code is disabled

4. Retest the issue

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>

Detail SQL Query Using Workflow/BPM tables in oracle fusion

Query to extract information related to the PR Purchase requisition BPM approval rules.

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 pap.DIMENSION_ID = pad.DIMENSION_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'

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';

SQL query to extract data Pending approval transaction for employee transactions data

 select

txnd.transaction_id,

txnd.status,

txnd.state,

txnd.created_by,

txnd.creation_date,

txnd.last_update_date,

txnd.last_updated_by,

txnh.module_identifier,

txnh.subject,

txnh.subject_id,

txnh.object,

txnh.object_id,

txne.Fault_Date,

txne.Error_Details,

wft.TITLE,

wft.OUTCOME as WF_OUTCOME,

wft.STATE as WF_STATE,

Wft.Assignees,

wft.Assigneddate,

wft.Approvers,

wft.compositeinstanceid

from

Fusion.Hrc_Txn_Header Txnh, Fusion.Hrc_Txn_Data Txnd,

fa_fusion_soainfra.wftask wft,

fusion.hrc_txn_error txne

Where txnh.Transaction_Id = txnd.Transaction_Id

and txnh.transaction_id = txne.transaction_id(+)

And To_Char(Txnh.Transaction_Id) = Wft.Identificationkey(+)

and ((txnh.subject_id in (select distinct person_id from per_all_people_f

where person_number in ('1234')

))

or (txnh.subject_id in (select assignment_id from per_all_assignments_m

where assignment_type ='E' and person_id in

(select distinct person_id from per_all_people_f where person_number in

('1234')

)))

or (txnh.subject_id in (select period_of_service_id from

per_periods_of_service where person_id in

(select distinct person_id from per_all_people_f where person_number in

('1234')

))))

SQL query to get current approver and future or next approver for resignation transactions

 SELECT wt.ASSIGNEESDISPLAYNAME next_approver,--hrops_name

wt.FROMUSERDISPLAYNAME current_approver,

txnh.object_id assignment_id,

(

SELECT DISTINCT papf.person_number

FROM per_all_people_f papf,

per_all_assignments_f paaf

WHERE 1 = 1

AND papf.person_id = paaf.person_id

AND sysdate BETWEEN paaf.effective_start_date

AND paaf.effective_end_date

AND paaf.assignment_id = txnh.object_id

) person_number,

(

SELECT to_date(EXTRACTVALUE(xmltype.createxml(DATA_CACHE), '/TRANSACTION/BUSINESSDATA/AM/TXN/EO/PeriodOfServiceEORow/NotifiedTerminationDate/DATA'), 'rrrr-mm-dd')

FROM hrc_txn_data txnd

WHERE 1 = 1

AND LENGTHB(TO_CHAR(SUBSTR(DATA_CACHE, 1, 4000))) <> 0

AND txnd.transaction_id = wt.identificationkey

) NTD,

(

SELECT to_date(EXTRACTVALUE(xmltype.createxml(DATA_CACHE), '/TRANSACTION/BUSINESSDATA/AM/TXN/EO/PeriodOfServiceEORow/ActualTerminationDate/DATA'), 'rrrr-mm-dd')

FROM hrc_txn_data txnd

WHERE 1 = 1

AND LENGTHB(TO_CHAR(SUBSTR(DATA_CACHE, 1, 4000))) <> 0

AND txnd.transaction_id = wt.identificationkey

) ATD,

(

SELECT DISTINCT ppnf.display_name

FROM per_person_names_f ppnf,

per_all_people_f papf,

per_all_assignments_f paaf

WHERE 1 = 1

AND ppnf.person_id = papf.person_id

AND sysdate BETWEEN ppnf.effective_start_date

AND ppnf.effective_end_date

AND ppnf.NAME_TYPE = 'GLOBAL'

AND papf.person_id = paaf.person_id

AND sysdate BETWEEN paaf.effective_start_date

AND paaf.effective_end_date

AND paaf.assignment_id = txnh.object_id

) DISPLAY_NAME,

trunc(CREATEDDATE) trx_date

FROM fa_fusion_soainfra.wftask wt,

hrc_txn_header txnh

WHERE 1 = 1

AND wt.identificationkey = txnh.transaction_id

AND ((ASSIGNMENTCONTEXT LIKE '%Above%'

AND wt.PUSHBACKSEQUENCE <> 'INITIAL_ASSIGNEES;1')

OR (ASSIGNMENTCONTEXT LIKE '%Below%'))

and wt.TASKDEFINITIONNAME = 'ResignationApproval'

AND Txnh.OBJECT = 'PER_ALL_ASSIGNMENTS_M'

AND Txnh.module_identifier IN ('Resignation','Terminations')

AND wt.COMPONENTNAME IN ('ResignationApproval','TerminationsApproval')

AND trunc(wt.UPDATEDDATE) = trunc(sysdate)

AND wt.ENDDATE IS NULL

SQL query to extract from data_cache field on HRC_TRX_DATA with transaction status

 

SELECT

person.person_number,

EXTRACTVALUE (

xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/PreRowAsgCalcAttributeHash/Department')

Department_old,

EXTRACTVALUE (xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/asgCalcAttributeHash/Department')

Department_new,

INITCAP (names.display_name) display_name,

EXTRACTVALUE (

xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/PreRowAsgCalcAttributeHash/BusinessUnit')

BusinessUnit_old,

EXTRACTVALUE (

xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/asgCalcAttributeHash/BusinessUnit')

BusinessUnit_new,

EXTRACTVALUE (

xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/PreRowAsgCalcAttributeHash/Location')

Location_old,

EXTRACTVALUE (xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/asgCalcAttributeHash/Location')

Location_new,

'InProgress' STATUS,

(SELECT per_all_people_f.person_number||'-'||INITCAP (per_person_names_f.display_name)

FROM PER_ASSIGNMENT_SUPERVISORS_F,

per_person_names_f,

per_all_people_f

WHERE PER_ASSIGNMENT_SUPERVISORS_F.manager_id =

per_person_names_f.person_id

AND per_person_names_f.person_id =

per_all_people_f.person_id

AND PER_ASSIGNMENT_SUPERVISORS_F.MANAGER_TYPE =

'LINE_MANAGER'

AND TRUNC (SYSDATE) BETWEEN TRUNC (

PER_ASSIGNMENT_SUPERVISORS_F.EFFECTIVE_START_DATE)

AND TRUNC (

PER_ASSIGNMENT_SUPERVISORS_F.EFFECTIVE_END_DATE)

AND TRUNC (SYSDATE) BETWEEN TRUNC (

per_person_names_f.EFFECTIVE_START_DATE)

AND TRUNC (

per_person_names_f.EFFECTIVE_END_DATE)

AND TRUNC (SYSDATE) BETWEEN TRUNC (

per_all_people_f.EFFECTIVE_START_DATE)

AND TRUNC (

per_all_people_f.EFFECTIVE_END_DATE)

and PER_ASSIGNMENT_SUPERVISORS_F.assignment_id=asg.assignment_id

AND per_person_names_f.name_type = 'GLOBAL'

)curr_reporting,

/* EXTRACTVALUE (xmltype ('' || replace(txnD.data_cache,',','') || ''),

'TRANSACTION/BUSINESSDATA/AM/TXN/EO/PeriodOfServiceEORow/CEO/EO/EmployeeTermsDEORow/CEO/EO/EmployeeAssignmentDEORow/CEO/EO/AssignmentSupervisorDEORow/ManagerName/DATA[1]')

*/null prapose_mgr,

asg.effective_start_date doj,

EXTRACTVALUE (xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/basicInfoAttributes/ReasonName')REASON

FROM hrc_txn_header txnH,

per_all_people_f person,

per_all_assignments_m asg,

per_person_names_f names,

hrc_txn_data txnD,

FA_FUSION_SOAINFRA.WFTASK wft,

per_persons ppos

WHERE UPPER (txnH.Object) IN ('PER_ALL_ASSIGNMENTS_M')

AND txnH.object_id = asg.assignment_id

AND SYSDATE BETWEEN asg.effective_start_date

AND asg.effective_end_date

AND asg.person_id = person.person_id

AND SYSDATE BETWEEN person.effective_start_date

AND person.effective_end_date

AND names.person_id = person.person_id

AND person.person_id = ppos.person_id

AND TO_CHAR (txnh.transaction_id) = wft.identificationkey(+)

AND SYSDATE BETWEEN names.effective_start_date

AND names.effective_end_date

AND names.name_type = 'GLOBAL'

AND txnH.transaction_id = txnD.transaction_id

AND txnD.status = 'PENDING'

AND txnH.MODULE_IDENTIFIER = 'Transfers'

AND person.person_number =nvl(:L_PERSON_NUM,person.person_number)

and (EXTRACTVALUE (

xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/PreRowAsgCalcAttributeHash/BusinessUnit')) =nvl(:L_BU_NAME,(EXTRACTVALUE (

xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/PreRowAsgCalcAttributeHash/BusinessUnit')))

and wft.ASSIGNEESDISPLAYNAME is not null

SQL query to search for Pending Transaction of Employee Assignments changes

 

SELECT
person.person_number||'-'||INITCAP (names.display_name) pp,person.person_number
FROM hrc_txn_header txnH,
per_all_people_f person,
per_all_assignments_m asg,
per_person_names_f names,
hrc_txn_data txnD,
FA_FUSION_SOAINFRA.WFTASK wft,
per_persons ppos
WHERE UPPER (txnH.Object) IN ('PER_ALL_ASSIGNMENTS_M')
AND txnH.object_id = asg.assignment_id
AND SYSDATE BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.person_id = person.person_id
AND SYSDATE BETWEEN person.effective_start_date
AND person.effective_end_date
AND names.person_id = person.person_id
AND person.person_id = ppos.person_id
AND TO_CHAR (txnh.transaction_id) = wft.identificationkey(+)
AND SYSDATE BETWEEN names.effective_start_date
AND names.effective_end_date
AND names.name_type = 'GLOBAL'
AND txnH.transaction_id = txnD.transaction_id
AND txnD.status = 'PENDING'
AND txnH.MODULE_IDENTIFIER = 'Transfers'
and wft.ASSIGNEESDISPLAYNAME is not null

Link Tables Between Workflow Table (FA_FUSION_SOAINFRA.WFTASK) and Projects Table (PJF_PROJECTS_ALL_B)

 1. PROJECT STATUS NOTIFICATION APPROVAL: 

select * from FA_FUSION_SOAINFRA.WFTASK where componentname ='ProjectStatusNotificationApproval'
and identificationkey = (select project_id from pjf_projects_all_b where segment1 = <Project Number>);

2. FORECAST APPROVAL: 

select * from FA_FUSION_SOAINFRA.WFTASK where componentname ='ApprovePlanVersion'
and identificationkey = (select plan_version_id from PJO_PLAN_VERSIONS_B where project_id = (select project_id from pjf_projects_all_b where segment1 = <Project Number>));

 

3. CONTRACT APPROVAL: 

select * from FA_FUSION_SOAINFRA.WFTASK where componentname ='ContractsApproval' and taskid in (select task_id from OKC_K_HEADERS_ALL_B where contract_number = <Contract Number>); 

 

To join the contracts table with the projects table, you need the following SQL:

SELECT OKH.CONTRACT_NUMBER
,OKHT.COGNOMEN AWARD_NAME
,PPAB.SEGMENT1 PROJ_NUM
FROM fusion.OKC_K_HEADERS_ALL_B OKH
, fusion.OKC_K_HEADERS_TL OKHT
, fusion.GMS_AWARD_HEADERS_B GAH
, fusion.GMS_AWARD_PROJECTS GAP
, fusion.PJF_PROJECTS_ALL_B PPAB
WHERE GAH.ID = OKH.ID
AND OKH.ID = OKHT.ID
AND OKH.ID = GAP.AWARD_ID
AND GAP.PROJECT_ID = PPAB.PROJECT_ID
AND OKH.CONTRACT_NUMBER = <Contract Number>;

 

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)

Supplier bank account query in fusion

 Supplier bank account query in fusion

 This SQL query will help to extract the complete Supplier Banking information's in oracle fusion. 

 Top 10 Important Tables used by Supplier bank account query in fusion

1.POZ_SUPPLIERS_V

2.POZ_SUPPLIER_SITES_V

3.IBY_EXTERNAL_PAYEES_ALL

4.IBY_PMT_INSTR_USES_ALL

5.IBY_EXT_BANK_ACCOUNTS

6.CE_BANK_BRANCHES_V

7.IBY_EXTERNAL_PAYEES_ALL

8.POZ_SUPPLIER_ADDRESS_V

9.POZ_SITE_ASSIGNMENTS_ALL_M

10.IBY_EXT_PARTY_PMT_MTHDS

Detail Supplier bank account query in fusion

SELECT PSV.VENDOR_NAME ,

  PSV.SEGMENT1 ,

  PSV.VENDOR_TYPE_LOOKUP_CODE ,

  PSV.ORGANIZATION_TYPE_LOOKUP_CODE " Organization Type" ,

  TAX_PRF.COUNTRY_CODE "TAXPAYER_COUNTRY",

  PSP.INCOME_TAX_ID "TAXPAYER_ID",

  PSV.FEDERAL_REPORTABLE_FLAG,

  PSV.TYPE_1099 "FEDERAL_INCOME_TAX_TYPE",

  PSV.STATE_REPORTABLE_FLAG STATE_REPORTABLE ,

  TAX_PRF.TAX_CLASSIFICATION_CODE "TAX_CLASSIFICATION_CODE",

  ZXR.TAX_REGIME_CODE "TAX_REGIME_CODE",

  ZXR.TAX "TAX",

  TAX_PRF.REP_REGISTRATION_NUMBER "REGISTRATION_NUMBER",

  ZXR.TAX_JURISDICTION_CODE "TAX_JURISDICTION_CODE",

  PSV.TAX_REPORTING_NAME,

  PSAV.PARTY_SITE_NAME "Supplier Address Name",

  PSAV.ADDRESS1 ,

  PSAV.ADDRESS2 ,

  PSAV.ADDRESS3 ,

  PSAV.ADDRESS4 ,

  PSAV.CITY ,

  PSAV.STATE ,

  PSAV.COUNTY ,

  PSAV.POSTAL_CODE ,

  PSAV.COUNTRY ,

  PSAV.EMAIL_ADDRESS ,

  PSSV.VENDOR_SITE_CODE ,

  TERMS.NAME "PAYMENT_TERMS",

  PSSV.INVOICE_CURRENCY_CODE ,

  PSSV.PAYMENT_CURRENCY_CODE ,

  PSSV.TAX_REPORTING_SITE_FLAG ,

  HP.PARTY_NAME BANK_PARTY_NAME,

  IEBA.COUNTRY_CODE BANK_COUNTRY,

  IEBA.FOREIGN_PAYMENT_USE_FLAG ALLOW_INT,

 IEBA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM,

  IEBA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME,

  IEBA.CURRENCY_CODE BANK_ACCT_CURRENCY_CODE,

  IEBA.IBAN ,

  EBB.BANK_BRANCH_NAME ,

  EBB.BRANCH_NUMBER 

FROM POZ_SUPPLIERS_V PSV ,

  POZ_SUPPLIERS_PII PSP,

  ZX_PARTY_TAX_PROFILE TAX_PRF,

  FND_LOOKUP_VALUES_TL REGT,

  HZ_CODE_ASSIGNMENTS HCA,

  FND_LOOKUP_VALUES_TL HCAVAL,

  ZX_REGISTRATIONS ZXR,

  POZ_SUPPLIER_SITES_V PSSV,

  POZ_ALL_SUPPLIER_CONTACTS_V PSC,

  HZ_RELATIONSHIPS hr,

  HZ_ORG_CONTACTS hoc,

  IBY_EXTERNAL_PAYEES_ALL IEPA,

  IBY_EXT_PARTY_PMT_MTHDS IEPPM,

  IBY_PMT_INSTR_USES_ALL IPIUA ,

  IBY_EXT_BANK_ACCOUNTS IEBA,

  CE_BANK_BRANCHES_V EBB,

  HZ_PARTIES HP,

  IBY_EXTERNAL_PAYEES_ALL BANK,

  POZ_SUPPLIER_ADDRESS_V PSAV,

  AP_TERMS_TL TERMS,

  POZ_SITE_ASSIGNMENTS_ALL_M PSAA,

  HR_OPERATING_UNITS HRO,

  HR_OPERATING_UNITS HRO1,

  IBY_EXT_PARTY_PMT_MTHDS PAY_METHOD

WHERE PSP.VENDOR_ID(+)               =PSV.VENDOR_ID

AND TAX_PRF.PARTY_ID(+)                =PSV.PARTY_ID

AND TAX_PRF.PARTY_TAX_PROFILE_ID       = HCA.OWNER_TABLE_ID(+)

AND (HCA.OWNER_TABLE_NAME          = 'ZX_PARTY_TAX_PROFILE'

OR HCA.OWNER_TABLE_NAME           IS NULL)

AND HCAVAL.LOOKUP_CODE(+)          =HCA.CLASS_CODE

AND (REGT.LOOKUP_TYPE              ='ZX_REGISTRATIONS_TYPE'

OR REGT.LOOKUP_TYPE               IS NULL)

AND TAX_PRF.REGISTRATION_TYPE_CODE     =REGT.LOOKUP_CODE(+)

AND TAX_PRF.PARTY_TAX_PROFILE_ID       =ZXR.PARTY_TAX_PROFILE_ID(+)

AND PSSV.VENDOR_ID(+)              =PSV.VENDOR_ID

AND PSSV.VENDOR_SITE_ID            = IEPA.SUPPLIER_SITE_ID(+)

AND PSC.SUP_PARTY_ID(+)            = PSV.PARTY_ID

AND hr.subject_id(+)               =PSV.PARTY_ID

AND( hr.relationship_code          = 'CONTACT'

OR hr.relationship_code           IS NULL)

AND (hr.object_table_name          = 'HZ_PARTIES'

OR hr.object_table_name           IS NULL)

AND hoc.party_relationship_id(+)   = hr.relationship_id

AND IEPA.EXT_PAYEE_ID              = IEPPM.EXT_PMT_PARTY_ID(+)

AND ((IEPPM.INACTIVE_DATE         IS NULL)

OR (IEPPM.INACTIVE_DATE            > SYSDATE) )

AND IPIUA.EXT_PMT_PARTY_ID (+)     = IEPA.EXT_PAYEE_ID

AND ( IPIUA.INSTRUMENT_TYPE        = 'BANKACCOUNT'

OR IPIUA.INSTRUMENT_TYPE          IS NULL)

AND ( IPIUA.PAYMENT_FLOW           = 'DISBURSEMENTS'

OR IPIUA.PAYMENT_FLOW             IS NULL)

AND ( IPIUA.ORDER_OF_PREFERENCE    = 1

OR IPIUA.ORDER_OF_PREFERENCE      IS NULL)

AND IPIUA.INSTRUMENT_ID            = IEBA.EXT_BANK_ACCOUNT_ID(+)

AND HP.PARTY_ID (+)                = IEBA.BANK_ID

AND IEBA.BRANCH_ID                 = EBB.BRANCH_PARTY_ID(+)

AND BANK.EXT_PAYEE_ID(+)           =PSV.VENDOR_ID

AND PSAV.LOCATION_ID(+)            =PSSV.LOCATION_ID

AND TERMS.TERM_ID(+)               =PSSV.TERMS_ID

AND PSAA.VENDOR_SITE_ID(+)         =PSSV.VENDOR_SITE_ID

AND HRO.ORGANIZATION_ID(+)         =PSAA.BU_ID

AND HRO1.ORGANIZATION_ID(+)        =PSAA.BILL_TO_BU_ID

AND PAY_METHOD.EXT_PMT_PARTY_ID(+) =BANK.EXT_PAYEE_ID

AND BANK.EXT_PAYEE_ID              =PAY_METHOD.EXT_PMT_PARTY_ID(+)

SQL Query To Fetch Supplier Bank Details In Oracle Apps R12

 This query will help you to get Supplier bank details like Supplier bank name, bank address, bank account number, branch type etc in Oracle R12 Application

SELECT   VENDOR_NAME,IEBA.BANK_ACCOUNT_NUM,
         IEBA.BANK_ACCOUNT_NAME,
         cbv.BANK_NAME,
         cbv.ADDRESS_LINE1 BANK_ADDRESS_1,
         CBV.COUNTRY BANK_COUNTRY,
         CBV.CITY BANK_CITY,
         CBBV.BANK_BRANCH_NAME,
         CBBV.ADDRESS_LINE1 BRANCH_ADDRESS_1,
         CBBV.CITY BRANCH_CITY,
         CBBV.COUNTRY BRANCH_COUNTRY,
         CBBV.BRANCH_NUMBER,
         CBBV.EFT_SWIFT_CODE Swift_Code ,
         FOREIGN_PAYMENT_USE_FLAG
 FROM    apps.AP_SUPPLIERS APS,
         apps.IBY_EXTERNAL_PAYEES_ALL IEPA,
         apps.IBY_PMT_INSTR_USES_ALL IPIUA,
         APPS.IBY_EXT_BANK_ACCOUNTS IEBA,
         apps.ce_banks_v cbv, 
         apps.ce_bank_BRANCHES_V CBBV
 WHERE   1=1
     AND APS.VENDOR_ID IN (select VENDOR_ID from apps.AP_SUPPLIER_SITES_ALL ass)
     AND IEPA.PAYEE_PARTY_ID=APS.PARTY_ID
     AND PARTY_SITE_ID IS NULL
     AND SUPPLIER_SITE_ID IS NULL
     AND IPIUA.EXT_PMT_PARTY_ID(+)=IEPA.EXT_PAYEE_ID
     AND IEBA.EXT_BANK_ACCOUNT_ID(+)=IPIUA.INSTRUMENT_ID
     AND IEBA.BANK_ID=cbv.BANK_PARTY_ID(+)
     AND IEBA.BRANCH_ID=CBBV.BRANCH_PARTY_ID(+);

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.

Vendor Site – Invoicing & Payments Query

 Vendor Site – Invoicing & Payments Query

Vendor Site query of invoicing and payment tabs is something you can’t run away in Fusion query. in this post we’ll discuss the query to fetch the details of both the sections. So first thing first and let me give you the invoicing query. This query will get you the payment method and default flag which means you’ll be able to tell which all payment methods are available at a particular vendor site and which of them is default.

Vendor Site Invoicing Query.

SELECT ste.vendor_site_code,
pm.payment_method_code,
pm.primary_flag Default_flag
FROM poz_supplier_sites_all_m ste,
iby_ext_party_pmt_mthds pm,
iby_external_payees_all payee
WHERE 1=1
AND payee.supplier_site_id = ste.vendor_site_id
AND pm.ext_pmt_party_id = payee.ext_payee_id
AND ste.vendor_site_code =:vendor_site_code

Here :vendor_site_code is the vendor site code for which you want to fetch the details for.

Payments Query

And here is the query to fetch the bank details like bank name, branch, account number of a supplier site.

select ste.vendor_site_code,
cebranch.bank_name bank_name,
cebranch.bank_number bank_number,
cebranch.bank_branch_name branch_name,
bankacct.bank_account_num
from poz_supplier_sites_all_m ste,
iby_external_payees_all payee,
iby_pmt_instr_uses_all instr_assign,
iby_ext_bank_accounts bankacct,
ce_all_bank_branches_v cebranch
where 1=1
AND payee.supplier_site_Id = ste.VENDOR_SITE_ID
AND instr_assign.ext_pmt_party_id=payee.ext_payee_id
AND bankacct.ext_bank_account_id =instr_assign.instrument_id
AND Bankacct.branch_id=cebranch.branch_party_id(+)
AND instr_assign.instrument_type=’BANKACCOUNT’
AND instr_assign.payment_flow=’DISBURSEMENTS’
AND ste.vendor_site_code = :vendor_site_code

Supplier Site Query ( SQL) – General in Fusion

Supplier site is one of the most important set ups in Oracle Fusion Finance. if you have implemented P2P, vendor site and reports fetching the data are inevitable. in this blog, let me share the general query for supplier site. Of course there will be posts as the follow up to this post to find other important fields:

Supplier Site Query Output:

This query will return you following columns:
1. Vendor Site Code
2. Procurement Business Unit
3. Alternate Vendor Site Code
4. Customer Number
5. Purchasing Site Flag
6. Pay Site Flag
7. Procurement Site Flag
8. Primary Pay Site Flag
9. Tax Reporting Site Flag
10. Document Category
11. Site Address
12. Site Status

Supplier Site Query:

SELECT ste.vendor_site_code,
hou.name procurement_bu,
ste.vendor_site_code_alt alternate_site_name,
ste.customer_num customer_number,
ste.purchasing_site_flag,
ste.pay_site_flag,
ste.pcard_site_flag procurement_site_flag ,
ste.primary_pay_site_flag,
ste.tax_reporting_site_flag income_tax_reporting_site,
b2b_site_code b2b_supplier_site_code ,
dsc.name document_category,
hl.address1 ||’, ‘|| hl.address2||’, ‘||hl.city||’, ‘||hl.state||’, ‘|| hl.postal_code|| ‘, ‘||hl.country site_address,
CASE
WHEN NVL(ste.INACTIVE_DATE, SYSDATE +1) > SYSDATE
THEN
‘Active’
ELSE
‘Inactive’
END status
FROM poz_supplier_sites_all_m ste,
hz_party_sites hps,
hz_locations hl,
hr_operating_units hou,
fnd_doc_sequence_categories dsc
WHERE 1=1
AND hps.party_site_id = ste.party_site_id
AND hl.location_id = ste.location_id
AND hou.organization_id = ste.PRC_BU_ID
AND dsc.code(+) = ste.global_attribute1
AND ste.vendor_site_code= :vendor_site_code

Here :vendor_site_code is the input parameter. But in case you want to get all the sites of the supplier, you can add POZ_SUPPLIERS table in the above query and connect POZ_SUPPLIER_SITES_ALL_M table using vendor_id.

Supplier Details Query in Oracle Fusion

Supplier or Vendor Details is something you are bound to query if you have implemented Purchasing/ Payable modules. After all supplier would have been created to supply the material which you would consume in your business and in return you will have to pay the supplier. So most probably you will need to query the vendor from Purchasing side or Payable Side.

Base table to store the supplier information is POZ_Suppliers. Query to fetch the supplier details is given below. In case you are looking to get the details for Supplier Sites or the payment details, following posts can be helpful 

Below is the query you can use. Following are the columns query will fetch:

1. Vendor Name
2. Vendor Number
3. Vendor Type
4. Tax Organization Type
5. Business Relationship
6. DUNS Number
7. Customer Number
8. SIC
9. Registry Id
10. Year Established
11. Mission Statement
12. Taxpayer Id
13. Party Site Name
14. Party Site Address
15. Contact Person

Supplier Detail Query:

SELECT 

PS.VENDOR_NAME,

PS.SEGMENT1 SUPPLIER_NUMBER,

NVL2(PS.TAX_REPORTING_NAME, 'Secure', NULL) TAX_REPORTING_NAME,

ps.vendor_type_lookup_Code supplier_type,

ps.organization_type_lookup_code Tax_Organization_Type,

ps.business_relationship ,

ps.standard_industry_class sic,

hzl.country country,

pvs.vendor_site_spk_id, 

pvs.vendor_site_id, 

pvs.effective_end_date,

pvs.effective_start_date, 

pvs.effective_sequence,

pvs.object_version_number, 

pvs.inactive_date, 

pvs.vendor_id,

pvs.prc_bu_id, 

pvs.location_id, 

pvs.party_site_id,

pvs.vendor_site_code, 

pvs.purchasing_site_flag, 

pvs.rfq_only_site_flag,

pvs.pay_site_flag, pvs.tp_header_id, 

pvs.services_tolerance_id,

pvs.tolerance_id, pvs.terms_id, 

pvs.exclude_freight_from_discount,

pvs.bank_charge_bearer, 

pvs.pay_on_code, 

pvs.default_pay_site_id,

pvs.pay_on_receipt_summary_code, 

pvs.ece_tp_location_code,

pvs.pcard_site_flag, 

pvs.match_option, 

pvs.country_of_origin_code,

pvs.create_debit_memo_flag, 

pvs.supplier_notif_method,

pvs.email_address, 

pvs.primary_pay_site_flag, 

pvs.shipping_control,

pvs.selling_company_identifier, 

pvs.gapless_inv_num_flag,

pvs.retainage_rate, 

pvs.auto_calculate_interest_flag, 

pvs.hold_by,

pvs.hold_date, pvs.hold_flag, 

pvs.purchasing_hold_reason,

pvs.vendor_site_code_alt, 

pvs.attention_ar_flag, 

pvs.area_code,

pvs.phone, pvs.customer_num, 

pvs.ship_via_lookup_code,

pvs.freight_terms_lookup_code, 

pvs.fob_lookup_code, pvs.fax,

pvs.fax_area_code, pvs.telex, 

pvs.terms_date_basis,

pvs.pay_group_lookup_code, 

pvs.payment_priority,

pvs.invoice_amount_limit, 

pvs.pay_date_basis_lookup_code,

pvs.always_take_disc_flag, 

pvs.invoice_currency_code,

pvs.payment_currency_code, 

pvs.hold_all_payments_flag,

pvs.hold_future_payments_flag, 

pvs.hold_reason,

pvs.hold_unmatched_invoices_flag, 

pvs.payment_hold_date,

pvs.tax_reporting_site_flag, 

pvs.last_update_date, 

pvs.last_updated_by,

pvs.last_update_login, 

pvs.creation_date, 

pvs.created_by,

pvs.aging_period_days,

pvs.aging_onset_point, 

pvs.consumption_advice_frequency,

pvs.consumption_advice_summary, 

pvs.pay_on_use_flag,

pvs.mode_of_transport, 

pvs.service_level, 

hzl.address1 address_line1,

hzl.address2 address_line2, 

hzl.address3 address_line3,

hzl.address4 address_line4, 

hzl.address_style address_style,

hzl.address_lines_phonetic address_lines_alt, 

hzl.city city,

hzl.state state, 

hzl.county county, 

hzl.country country,

hzl.postal_code zip, 

hzl.province province,

hzl.location_language language_code, 

hps.duns_number_c duns_number

FROM 

poz_supplier_sites_all_m pvs, 

hz_locations hzl, 

hz_party_sites hps,

POZ_SUPPLIERS_V PS

WHERE hzl.location_id = pvs.location_id

AND hps.party_site_id = pvs.party_site_id

AND pvs.vendor_id=PS.VENDOR_ID

and PS.SEGMENT1 in ('508643','508411')

or try this

select hp.party_name Vendor_Name,
ps.segment1 supplier_number,
ps.vendor_type_lookup_Code supplier_type,
ps.organization_type_lookup_code Tax_Organization_Type,
ps.business_relationship ,
hop.DUNS_NUMBER_C DUNS_NUMBER,
ps.customer_num,
ps.standard_industry_class sic,
hop.party_number Registry_id,
hop.year_established,
hop.mission_statement,
psp.INCOME_TAX_ID Taxpayer_ID,
hps.PARTY_SITE_NAME Address_Name,
hp.address1,
hp.address2,
hp.city ,
hp.state,
hp.county,
hp_contact.person_last_name||’, ‘||hp_contact.PERSON_PRE_NAME_ADJUNCT||’ ‘||hp_contact.person_first_name Contact_Person
from poz_suppliers ps,
hz_parties hp ,
hz_organization_profiles hop,
POZ_SUPPLIERS_PII psp,
hz_party_sites hps,
hz_parties hp_contact
where hp.party_id = ps.party_id
AND hop.party_id = ps.party_id
AND psp.vendor_id(+) = ps.vendor_id
AND hps.party_site_id(+) = hp.iden_addr_party_site_id
AND hp_contact.party_id(+) = hp.preferred_contact_person_id
and hp.party_name = ‘ABC’