1) Remove "Complete" action button on AR Transactions for few user on the basis of assigned Roles
2) Provide access to few users to "complete" action for AR Transactions .
================
I have used below Edit Page component through Sandbox >>EL but "Complete" Button is getting disabled for all users
#{securityContext.userInRole['AZM_LOG_COM_ACTION']}
AZM_LOG_COM_ACTION - this is dummy role assigned to 2 users only.
Oracle FAW provides four role types:
Duty roles => Define the duties of a job as an entitlement to perform a particular action, such as access to an AP Transactions subject area.
Data roles => Provide access to the transactional data in the tables. Data roles group the users based on the functional access they have through a particular job role and a particular dimension of data. For example, a group of users based on invoices relevant only to their business unit.
System roles => Provide a set of privileges that allows users to perform system tasks after signing into Oracle Fusion Analytics Warehouse, such as administering system settings, performing functional setup, managing security, and modeling data.
Job roles => Inherit duty roles, data roles, and application roles that are assigned to users. A job role defines a user business function such as Vice President of Sales, Human Resources Analyst, and Procurement Buyer. Job roles and users are synchronized from Oracle Applications Cloud to Oracle Identity Cloud Service. The job roles are mapped as groups in Oracle Identity Cloud Service. You can also create custom job roles based on your business requirements
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
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
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>
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';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')
))))
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
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
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
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>;
/******************************************************************* *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
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(+)
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(+);
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
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
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 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.
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.
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
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 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
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’