How to set as of now filter to OTBI:

Create veriable from prompt and set the prefix
as SET VERIABLE PARAM_EFFECTIVE_DATE='@{AsOfDate'} this is case senstive
please make sure same prompt copy from prompt.

How to get the BPA number against to PO Number in Fusion:


SELECT        
  FromBlanketHeader.SEGMENT1 AS BPANUMBER,
        FromBlanketHeader.attribute1 
  FROM
  PO_LINES_ALL PurchasingDocumentLine,
  PO_HEADERS_ALL PurchasingDocumentHeader,
  PO_HEADERS_ALL ContractHeader,
  PO_HEADERS_ALL FromBlanketHeader
  WHERE
  PurchasingDocumentLine.PO_HEADER_ID = PurchasingDocumentHeader.PO_HEADER_ID
  AND PurchasingDocumentLine.CONTRACT_ID = ContractHeader.PO_HEADER_ID(+)
  AND PurchasingDocumentLine.FROM_HEADER_ID = FromBlanketHeader.PO_HEADER_ID(+)
  AND PurchasingDocumentHeader.SEGMENT1=:P_YOUR_PONUMBER

select * from iby_pay_service_requests

where CALL_APP_PAY_SERVICE_REQ_CODE like 'PPR Fille name'

Best way to get the Payment extract from back end tables in Fusion:

Fusion: Generate IBY PPR Diagnostic Report (Doc ID 1377744.1)

How to configure the Payments  Process Profile in Fusion:

Manage Transmission Configuration to setup all FTP and file format details.

Security details report:

BI Publisher - Fusion Apps - How Can A BI Publisher Report Based On A SQL Query Use The Security Roles In Fusion Applications? (Doc ID 1537196.1)

Print receivable by BU name : View Image » By Business Unit (Doc ID 2188688.1) 

second highest date:

WITH T AS
(select creation_date,DENSE_RANK() OVER (ORDER BY CREATION_DATE asc) AS Rnk from (select unique to_char(RCT.CREATION_DATE,'YYYY-MM-DD') as "CREATION_DATE" from
EGP_SYSTEM_ITEMS_VL ESI,
PO_LINES_ALL PLA,
RCV_TRANSACTIONS RCT
where PLA.item_id=esi.inventory_item_id
and RCT.PO_LINE_ID=pla.PO_LINE_ID
and item_number='92737'
order by CREATION_DATE asc))
select creation_date from T
WHERE Rnk =2

SELECT /*+ index(mmtt MTL_MATERIAL_TRANS_TEMP_N13) */
'MMTT_RECORD' record_source
,mmtt.pick_slip_line_number pick_slip_line -- use a temporary holder for Pick Slip Line Number
,mol.LINE_NUMBER
,mol.QUANTITY mol_quantity -- quantity requested
,mol.REQUIRED_QUANTITY
,mol.QUANTITY_DELIVERED
,mol.QUANTITY_DETAILED
,mol.UOM_CODE mol_uom_code
,mol.SECONDARY_QUANTITY mol_secondary_quantity
,mol.SECONDARY_QUANTITY_DELIVERED
,mol.SECONDARY_QUANTITY_DETAILED
,mol.SECONDARY_REQUIRED_QUANTITY
,mol. SECONDARY_UOM_CODE mol_secondary_uom_code
,moh.REQUEST_NUMBER
,mol.DATE_REQUIRED mol_date_required
,moh.MOVE_ORDER_TYPE -- pick wage, repleneshment or request
,moh.DATE_REQUIRED moh_date_required
,moh.CREATED_BY moh_created_by
,moh.TRANSACTION_TYPE_ID moh_transaction_type_id
,wdd.source_header_number
,wdd.source_line_number
,wdd.ship_to_location_id -- need to join to TCA
,wdd.ship_to_party_id -- need to join to TCA
,wdd.shipment_priority_code -- need to join to FND_LOOKUPS
,wdd.date_requested
,wdd.ship_from_location_id
,wdd.ship_method_code -- need to join to FND_LOOKUPS
,wdd.ship_to_party_id customer_id -- need to join to TCA
,wnd.delivery_id wnd_delivery_id
,wnd.delivery_name delivery_number
,wpb.batch_id
,wpb.picking_batch_name wave_number
, mmtt.transaction_header_id
, mmtt.transaction_temp_id transaction_id
, mmtt.source_code
, mmtt.source_line_id
, mmtt.transaction_mode
, mmtt.lock_flag
, mmtt.last_update_date
, mmtt.last_updated_by
, mmtt.creation_date
, mmtt.created_by
, mmtt.last_update_login
, mmtt.request_id
, mmtt.inventory_item_id
, mmtt.revision
, mmtt.organization_id
, mmtt.subinventory_code
, mmtt.locator_id
, transaction_quantity
, mmtt.primary_quantity
, transaction_uom
, transaction_cost
, mmtt.transaction_type_id
, mmtt.transaction_action_id
, mmtt.transaction_source_type_id
, mmtt.transaction_source_id
, mmtt.transaction_source_name
, mmtt.transaction_date
, distribution_account_id
, transaction_reference
, mmtt.requisition_line_id
, mmtt.requisition_distribution_id
, mmtt.reason_id
, receiving_document
, demand_id
, rcv_transaction_id
, move_transaction_id
, completion_transaction_id
, wip_entity_type
, schedule_id
, repetitive_line_id
, employee_code
, primary_switch
, schedule_update_code
, setup_teardown_code
, item_ordering
, negative_req_flag
, mmtt.operation_seq_num
, picking_line_id
, trx_source_line_id
, trx_source_delivery_id
, physical_adjustment_id
, cycle_count_id
, rma_line_id
, customer_ship_id
, mmtt.currency_code
, currency_conversion_rate
, currency_conversion_type
, currency_conversion_date
, ussgl_transaction_code
, vendor_lot_number
, ship_to_location
, shipment_number
, transfer_cost
, transportation_cost
, containers
, waybill_airbill
, expected_arrival_date
, transfer_subinventory
, nvl(transfer_organization, mmtt.organization_id) transfer_organization
, transfer_to_location
, new_average_cost
, value_change
, percentage_change
, material_allocation_temp_id
, demand_source_header_id
, demand_source_line
, demand_source_delivery
, item_segments
, mmtt.item_description
, item_trx_enabled_flag
, item_location_control_code
, item_restrict_subinv_code
, item_restrict_locators_code
, item_revision_qty_control_code
, item_primary_uom_code
, item_uom_class
, item_shelf_life_code
, item_shelf_life_days
, item_lot_control_code
, item_serial_control_code
, item_inventory_asset_flag
, allowed_units_lookup_code
, department_id
, department_code
, mmtt.wip_supply_type
, mmtt.supply_subinventory
, mmtt.supply_locator_id
, valid_subinventory_flag
, valid_locator_flag
, locator_segments
, current_locator_control_code
, number_of_lots_entered
, wip_commit_flag
, next_lot_number
, lot_alpha_prefix
, next_serial_number
, serial_alpha_prefix
, shippable_flag
, posting_flag
, required_flag
, process_flag
, ERROR_CODE
, error_explanation
, mmtt.movement_id
, reservation_quantity
, mmtt.shipped_quantity
, transaction_line_number
, mmtt.task_id
, mmtt.to_task_id
, mmtt.project_id
, mmtt.to_project_id
, final_completion_flag
, transfer_percentage
, flow_schedule
, overcompletion_transaction_id
, overcompletion_primary_qty
, overcompletion_transaction_qty
, line_type_code
, parent_transaction_temp_id parent_transaction_id
, mmtt.put_away_strategy_id
, mmtt.put_away_rule_id
, mmtt.pick_strategy_id
, mmtt.pick_rule_id
, common_bom_seq_id
, common_routing_seq_id
, mmtt.move_order_line_id
, task_group_id
, mmtt.pick_slip_number
, reservation_id
, transaction_status
, mmtt.pick_slip_date
, secondary_transaction_quantity -- INVCONV change
, mmtt.secondary_uom_code -- INVCONV change
, mmtt.picked_quantity
, mmtt.secondary_picked_quantity
, mmtt.lot_number
, nvl(mmtt.orig_transaction_quantity, mmtt.transaction_quantity) orig_transaction_quantity
, nvl(mmtt.orig_subinventory_code, mmtt.subinventory_code) orig_subinventory_code
, nvl(mmtt.orig_locator_id, mmtt.locator_id) orig_locator_id
, nvl(mmtt.orig_lot_number, mmtt.lot_number) orig_lot_number
, mmtt.PJC_CONTEXT_CATEGORY
, mmtt.PJC_PROJECT_ID
, mmtt.PJC_TASK_ID
, mmtt.PJC_EXPENDITURE_TYPE_ID
, mmtt.PJC_EXPENDITURE_ITEM_DATE
, mmtt.PJC_BILLABLE_FLAG
, mmtt.PJC_CAPITALIZABLE_FLAG
, mmtt.PJC_WORK_TYPE_ID
, mmtt.PJC_CONTRACT_ID
, mmtt.PJC_CONTRACT_LINE_ID
, mmtt.PJC_FUNDING_ALLOCATION_ID
, mmtt.PJC_RESERVED_ATTRIBUTE1
, mmtt.PJC_RESERVED_ATTRIBUTE2
, mmtt.PJC_RESERVED_ATTRIBUTE3
, mmtt.PJC_RESERVED_ATTRIBUTE4
, mmtt.PJC_RESERVED_ATTRIBUTE5
, mmtt.PJC_RESERVED_ATTRIBUTE6
, mmtt.PJC_RESERVED_ATTRIBUTE7
, mmtt.PJC_RESERVED_ATTRIBUTE8
, mmtt.PJC_RESERVED_ATTRIBUTE9
, mmtt.PJC_RESERVED_ATTRIBUTE10
, mmtt.PJC_USER_DEF_ATTRIBUTE1
, mmtt.PJC_USER_DEF_ATTRIBUTE2
, mmtt.PJC_USER_DEF_ATTRIBUTE3
, mmtt.PJC_USER_DEF_ATTRIBUTE4
, mmtt.PJC_USER_DEF_ATTRIBUTE5
, mmtt.PJC_USER_DEF_ATTRIBUTE6
, mmtt.PJC_USER_DEF_ATTRIBUTE7
, mmtt.PJC_USER_DEF_ATTRIBUTE8
, mmtt.PJC_USER_DEF_ATTRIBUTE9
, mmtt.PJC_USER_DEF_ATTRIBUTE10
, mmtt.PJC_ORGANIZATION_ID
, mmtt.ATTRIBUTE_CATEGORY
, mmtt.ATTRIBUTE1
, mmtt.ATTRIBUTE2
, mmtt.ATTRIBUTE3
, mmtt.ATTRIBUTE4
, mmtt.ATTRIBUTE5
, mmtt.ATTRIBUTE6
, mmtt.ATTRIBUTE7
, mmtt.ATTRIBUTE8
, mmtt.ATTRIBUTE9
, mmtt.ATTRIBUTE10
, mmtt.ATTRIBUTE11
, mmtt.ATTRIBUTE12
, mmtt.ATTRIBUTE13
, mmtt.ATTRIBUTE14
, mmtt.ATTRIBUTE15
, mmtt.ATTRIBUTE16
, mmtt.ATTRIBUTE17
, mmtt.ATTRIBUTE18
, mmtt.ATTRIBUTE19
, mmtt.ATTRIBUTE20
, mmtt.ATTRIBUTE_NUMBER1
, mmtt.ATTRIBUTE_NUMBER2
, mmtt.ATTRIBUTE_NUMBER3
, mmtt.ATTRIBUTE_NUMBER4
, mmtt.ATTRIBUTE_NUMBER5
, mmtt.ATTRIBUTE_NUMBER6
, mmtt.ATTRIBUTE_NUMBER7
, mmtt.ATTRIBUTE_NUMBER8
, mmtt.ATTRIBUTE_NUMBER9
, mmtt.ATTRIBUTE_NUMBER10
, mmtt.ATTRIBUTE_DATE1
, mmtt.ATTRIBUTE_DATE2
, mmtt.ATTRIBUTE_DATE3
, mmtt.ATTRIBUTE_DATE4
, mmtt.ATTRIBUTE_DATE5
, mmtt.ATTRIBUTE_TIMESTAMP1
, mmtt.ATTRIBUTE_TIMESTAMP2
, mmtt.ATTRIBUTE_TIMESTAMP3
, mmtt.ATTRIBUTE_TIMESTAMP4
, mmtt.ATTRIBUTE_TIMESTAMP5
, mmtt.transaction_temp_id transaction_temp_id
, wdd.SALES_ORDER_NUMBER
, wdd.SALES_ORDER_LINE_NUMBER
, wdd.SALES_ORDER_SHIPMENT_NUMBER
, wdd.ship_set_name ship_set
, wdd.SOURCE_SHIPMENT_NUMBER
, wsp.OVERPICK_ENABLED
,moh.REQUEST_NUMBER newN,
 moh.HEADER_STATUS,
 moh.HEADER_ID
FROM inv_material_txns_temp mmtt
,inv_txn_request_lines mol
,inv_txn_request_headers moh
,wsh_delivery_details wdd
,wsh_delivery_assignments wda
,wsh_new_deliveries wnd
,wsh_picking_batches wpb
,wsh_shipping_parameters wsp
where mmtt.move_order_line_id = mol.line_id and
mol.header_id = moh.header_id and
mmtt.trx_source_line_id = wdd.delivery_detail_id (+) and
wdd.batch_id = wpb.batch_id (+) and
wdd.delivery_detail_id = wda.delivery_detail_id (+) and
wda.delivery_id = wnd.delivery_id (+) and
mmtt.organization_id = wsp.organization_id (+)
--and mmtt.PICK_SLIP_NUMBER=275039
union all
SELECT /*+ index(mmt MTL_MATERIAL_TRANSACTIONS_N16) */
'MMT_RECORD' record_source
,mmt.pick_slip_line_number pick_slip_line
,mol.LINE_NUMBER
,mol.QUANTITY -- quantity requested
,mol.REQUIRED_QUANTITY
,mol.QUANTITY_DELIVERED
,mol.QUANTITY_DETAILED
,mol.UOM_CODE
,mol.SECONDARY_QUANTITY
,mol.SECONDARY_QUANTITY_DELIVERED
,mol.SECONDARY_QUANTITY_DETAILED
,mol.SECONDARY_REQUIRED_QUANTITY
,mol. SECONDARY_UOM_CODE
,moh.REQUEST_NUMBER
,mol.DATE_REQUIRED mol_date_required
,moh.MOVE_ORDER_TYPE -- pick wage, repleneshment or request
,moh.DATE_REQUIRED moh_date_required
,moh.CREATED_BY moh_created_by
,moh.TRANSACTION_TYPE_ID moh_transaction_type_id
,wdd.source_header_number
,wdd.source_line_number
,wdd.ship_to_location_id -- need to join to TCA
,wdd.ship_to_party_id -- need to join to TCA
,wdd.shipment_priority_code -- need to join to FND_LOOKUPS
,wdd.date_requested
,wdd.ship_from_location_id
,wdd.ship_method_code -- need to join to FND_LOOKUPS
,wdd.ship_to_party_id customer_id -- need to join to TCA
,wnd.delivery_id wnd_delivery_id
,wnd.delivery_name delivery_number
,wpb.batch_id
,wpb.picking_batch_name wave_number
,mmt.parent_transaction_id transaction_header_id
, mmt.transaction_id
, mmt.source_code
, mmt.source_line_id
,null transaction_mode -- transaction mode stored as string in MMTT
, null lock_flag
, mmt.last_update_date
, mmt.last_updated_by
, mmt.creation_date
, mmt.created_by
, mmt.last_update_login
, mmt.request_id
, mmt.inventory_item_id
, mmt.revision
, mmt.organization_id
, mmt.subinventory_code
, mmt.locator_id
, ABS(mmt.transaction_quantity) transaction_quantity
, ABS(mmt.primary_quantity) primary_quantity
, transaction_uom
, transaction_cost
, mmt.transaction_type_id
, null transaction_action_id -- transaction action stored as string in MMTT
, mmt.transaction_source_type_id
, mmt.transaction_source_id
, mmt.transaction_source_name
, mmt.transaction_date
, distribution_account_id
, transaction_reference
, null requisition_line_id
, null requisition_distribution_id
, mmt.reason_id
, receiving_document
, null demand_id
, rcv_transaction_id
, move_transaction_id
, completion_transaction_id
, null wip_entity_type
, null schedule_id
, repetitive_line_id
, employee_code
, null primary_switch
, null schedule_update_code --master_schedule_update_code
, null setup_teardown_code
, null item_ordering
, null negative_req_flag
, mmt.operation_seq_num
, picking_line_id
, trx_source_line_id
, trx_source_delivery_id
, physical_adjustment_id
, cycle_count_id
, rma_line_id
, null customer_ship_id
, mmt.currency_code
, currency_conversion_rate
, currency_conversion_type
, currency_conversion_date
, ussgl_transaction_code
, vendor_lot_number
, mmt.ship_to_location_id ship_to_location
, shipment_number
, transfer_cost
, transportation_cost
, null containers
, waybill_airbill
, null expected_arrival_date
, transfer_subinventory
, transfer_organization_id transfer_organization
, transfer_locator_id transfer_to_location
, null new_average_cost
, value_change
, percentage_change
, null material_allocation_temp_id
, null demand_source_header_id -- may be replaced by transaction_source_id
, null demand_source_line -- may be replaced by trx_source_line_id
, null demand_source_delivery -- may be replaced by trx_source_delivery_id
, null item_segments
, null item_description
, null item_trx_enabled_flag
, null item_location_control_code
, null item_restrict_subinv_code
, null item_restrict_locators_code
, null item_revision_qty_control_code
, null item_primary_uom_code
, null item_uom_class
, null item_shelf_life_code
, null item_shelf_life_days
, null item_lot_control_code
, null item_serial_control_code
, null item_inventory_asset_flag
, null allowed_units_lookup_code
, department_id
, department_code
, null wip_supply_type
, null supply_subinventory
, null supply_locator_id
, null valid_subinventory_flag
, null valid_locator_flag
, null locator_segments
, null current_locator_control_code
, null number_of_lots_entered
, null wip_commit_flag
, null next_lot_number
, null lot_alpha_prefix
, null next_serial_number
, null serial_alpha_prefix
, null shippable_flag
, null posting_flag
, null required_flag
, null process_flag
, ERROR_CODE
, error_explanation
, mmt.movement_id
, null reservation_quantity
, null shipped_quantity
, null transaction_line_number
, mmt.task_id
, mmt.to_task_id
, mmt.project_id
, mmt.to_project_id
, final_completion_flag
, transfer_percentage
, flow_schedule
, overcompletion_transaction_id
, overcompletion_primary_qty
, overcompletion_transaction_qty
, null line_type_code
, parent_transaction_id
, mmt.put_away_strategy_id
, mmt.put_away_rule_id
, mmt.pick_strategy_id
, mmt.pick_rule_id
, common_bom_seq_id
, common_routing_seq_id
, mmt.move_order_line_id
, task_group_id
, mmt.pick_slip_number
, reservation_id
, null transaction_status
, mmt.pick_slip_date
, ABS(secondary_transaction_quantity) secondary_transaction_quantity -- INVCONV change
, mmt.secondary_uom_code -- INVCONV change
, ABS(mmt.transaction_quantity) picked_quantity
, ABS(mmt.secondary_transaction_quantity) secondary_picked_quantity
, null lot_number
, mmt.orig_transaction_quantity
, mmt.orig_subinventory_code
, mmt.orig_locator_id
, mmt.orig_lot_number
, mmt.PJC_CONTEXT_CATEGORY
, mmt.PJC_PROJECT_ID
, mmt.PJC_TASK_ID
, mmt.PJC_EXPENDITURE_TYPE_ID
, mmt.PJC_EXPENDITURE_ITEM_DATE
, mmt.PJC_BILLABLE_FLAG
, mmt.PJC_CAPITALIZABLE_FLAG
, mmt.PJC_WORK_TYPE_ID
, mmt.PJC_CONTRACT_ID
, mmt.PJC_CONTRACT_LINE_ID
, mmt.PJC_FUNDING_ALLOCATION_ID
, mmt.PJC_RESERVED_ATTRIBUTE1
, mmt.PJC_RESERVED_ATTRIBUTE2
, mmt.PJC_RESERVED_ATTRIBUTE3
, mmt.PJC_RESERVED_ATTRIBUTE4
, mmt.PJC_RESERVED_ATTRIBUTE5
, mmt.PJC_RESERVED_ATTRIBUTE6
, mmt.PJC_RESERVED_ATTRIBUTE7
, mmt.PJC_RESERVED_ATTRIBUTE8
, mmt.PJC_RESERVED_ATTRIBUTE9
, mmt.PJC_RESERVED_ATTRIBUTE10
, mmt.PJC_USER_DEF_ATTRIBUTE1
, mmt.PJC_USER_DEF_ATTRIBUTE2
, mmt.PJC_USER_DEF_ATTRIBUTE3
, mmt.PJC_USER_DEF_ATTRIBUTE4
, mmt.PJC_USER_DEF_ATTRIBUTE5
, mmt.PJC_USER_DEF_ATTRIBUTE6
, mmt.PJC_USER_DEF_ATTRIBUTE7
, mmt.PJC_USER_DEF_ATTRIBUTE8
, mmt.PJC_USER_DEF_ATTRIBUTE9
, mmt.PJC_USER_DEF_ATTRIBUTE10
, mmt.PJC_ORGANIZATION_ID
, mmt.ATTRIBUTE_CATEGORY
, mmt.ATTRIBUTE1
, mmt.ATTRIBUTE2
, mmt.ATTRIBUTE3
, mmt.ATTRIBUTE4
, mmt.ATTRIBUTE5
, mmt.ATTRIBUTE6
, mmt.ATTRIBUTE7
, mmt.ATTRIBUTE8
, mmt.ATTRIBUTE9
, mmt.ATTRIBUTE10
, mmt.ATTRIBUTE11
, mmt.ATTRIBUTE12
, mmt.ATTRIBUTE13
, mmt.ATTRIBUTE14
, mmt.ATTRIBUTE15
, mmt.ATTRIBUTE16
, mmt.ATTRIBUTE17
, mmt.ATTRIBUTE18
, mmt.ATTRIBUTE19
, mmt.ATTRIBUTE20
, mmt.ATTRIBUTE_NUMBER1
, mmt.ATTRIBUTE_NUMBER2
, mmt.ATTRIBUTE_NUMBER3
, mmt.ATTRIBUTE_NUMBER4
, mmt.ATTRIBUTE_NUMBER5
, mmt.ATTRIBUTE_NUMBER6
, mmt.ATTRIBUTE_NUMBER7
, mmt.ATTRIBUTE_NUMBER8
, mmt.ATTRIBUTE_NUMBER9
, mmt.ATTRIBUTE_NUMBER10
, mmt.ATTRIBUTE_DATE1
, mmt.ATTRIBUTE_DATE2
, mmt.ATTRIBUTE_DATE3
, mmt.ATTRIBUTE_DATE4
, mmt.ATTRIBUTE_DATE5
, mmt.ATTRIBUTE_TIMESTAMP1
, mmt.ATTRIBUTE_TIMESTAMP2
, mmt.ATTRIBUTE_TIMESTAMP3
, mmt.ATTRIBUTE_TIMESTAMP4
, mmt.ATTRIBUTE_TIMESTAMP5
, mmt.original_transaction_temp_id transaction_temp_id
, wdd.SALES_ORDER_NUMBER
, wdd.SALES_ORDER_LINE_NUMBER
, wdd.SALES_ORDER_SHIPMENT_NUMBER
, wdd.ship_set_name ship_set
, wdd.SOURCE_SHIPMENT_NUMBER
, wsp.OVERPICK_ENABLED
,moh.REQUEST_NUMBER newN
,moh.HEADER_STATUS,
moh.HEADER_ID
FROM inv_material_txns mmt
,inv_txn_request_lines mol
,inv_txn_request_headers moh
,wsh_delivery_details wdd
,wsh_delivery_assignments wda
,wsh_new_deliveries wnd
,wsh_picking_batches wpb
,wsh_shipping_parameters wsp
where mmt.move_order_line_id = mol.line_id and
mol.header_id = moh.header_id and
mmt.trx_source_line_id = wdd.delivery_detail_id (+) and
wdd.batch_id = wpb.batch_id (+) and
wdd.delivery_detail_id = wda.delivery_detail_id (+) and
wda.delivery_id = wnd.delivery_id (+) and
mmt.organization_id = wsp.organization_id (+)
--and mmt.PICK_SLIP_NUMBER=275039
and mmt.transaction_quantity < 0

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.

Manage Transmission Configurations To do the setups related to payments.

Accounts Payable Payment Supervisor role for creating payments

Accounts Payable Specialist for creating invoices


Transmission Configuration to configure the server details.

Communicate Purchasing Documents to send the emails


Disable the profile option at the site level.

Here is the navigation:
1. Setup and Maintenance
2. Search 'Manage Administrator Profile Values', & click.
3. Enter 'Control Supplier Communication' in the field 'Profile Display Name'
4. Click on that record in the search result
5. Change Profile value to 'Disable' at the 'Site' Profile level.
6. Save and Close.
7. Sign out and sign back in.