assigned suppIier BANK data

 select /*3-22887520491*/ P.PARTY_NAME AS ACCOUNT_OWNER_PARTY_NAME,ACCT.BANK_ACCOUNT_NAME

from fusion.IBY_ACCOUNT_OWNERS OWN,

fusion.IBY_EXT_BANK_ACCOUNTS ACCT,

fusion.hz_parties P

where OWN.ACCOUNT_OWNER_PARTY_ID <> -99

and OWN.EXT_BANK_ACCOUNT_ID=ACCT.EXT_BANK_ACCOUNT_ID

and ACCT.BANK_ACCOUNT_NUM=

and OWN.ACCOUNT_OWNER_PARTY_ID=P.PARTY_ID

 SELECT

            c.conversion_rate,

            c.conversion_date,

            b.user_conversion_type,

            c.to_currency,

            c.from_currency

            --gl.conversion_date

          FROM

            GL_DAILY_CONVERSION_TYPES b,

            GL_DAILY_RATES c

          WHERE

            b.conversion_type = c.conversion_type

            AND b.user_conversion_type = 'MOR'

How to define new profile values in oracle fusion :

Defined new profile options in system using Manage Applications Core Profile Options.

Updated profile values from manage admin profile values.

Then added new FTP values in BIP management.

1. Update Payment Process Profile-->Reporting tab and remove Separate Remittance Format and uncheck 'Automatically submit when payments' option


2. Update Manage Disbursement System Options and disable following SRA settings both at header level and business unit level:

Separate Remittance Advice from Email
Separate Remittance Advice Subject

Sand Box

def  X_org=121;
   def  X_default =1;
  if ( OrganizationId != X_org)  {
  return X_default
  }

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;