Tuesday, March 19, 2024
SOAP API to FindPerson (Contact Details) using FirstName and LastName
Thursday, March 14, 2024
SOAP API to Create Receipt Reversal Transaction (Oracle Fusion Receivables Cloud)
SOAP API
Sample Payload
Sample Response
SOAP to Create Debit memo (Oracle Fusion Receivables Cloud Service)
===========
Tuesday, March 12, 2024
Update Credit Card "Assignment Inactive On" for a Customer in Oracle Fusion (Using SOAP/REST API)
Use the below WSDL to get the Instrument assignment id for all available Credit Cards on a Customer
Service Name: findAssignedInstrumentsByPayer
==================
<soapenv:Header/>
<soapenv:Body>
<typ:findAssignedInstrumentsByPayer>
<typ:partyId>100000014531116</typ:partyId>
<typ:customerAccountId>100000032507573</typ:customerAccountId>
<typ:paymentFunction>CUSTOMER_PAYMENT</typ:paymentFunction>
<typ:instrumentType>CREDITCARD</typ:instrumentType>
</typ:findAssignedInstrumentsByPayer>
</soapenv:Body>
</soapenv:Envelope>
<env:Header>
<wsa:Action>http://xmlns.oracle.com/apps/financials/payments/shared/payeePayerInstruments/externalPayerServiceV2/PayerDetailService/findAssignedInstrumentsByPayerResponse</wsa:Action>
<wsa:MessageID>urn:uuid:d1447dc5-20e8-4f7e-9fa3-f90ee4f2e9df</wsa:MessageID>
</env:Header>
<env:Body>
<ns0:findAssignedInstrumentsByPayerResponse xmlns:ns0="http://xmlns.oracle.com/apps/financials/payments/shared/payeePayerInstruments/externalPayerServiceV2/types/">
<ns2:result xsi:type="ns1:AssignedPmtInstrument" xmlns:ns0="http://xmlns.oracle.com/adf/svc/types/" xmlns:ns1="http://xmlns.oracle.com/apps/financials/payments/shared/payeePayerInstruments/externalPayerServiceV2/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns2="http://xmlns.oracle.com/apps/financials/payments/shared/payeePayerInstruments/externalPayerServiceV2/types/">
<ns1:InstrAssignmentId>300000055014</ns1:InstrAssignmentId>
<ns1:ExternalPayerId>300000055013</ns1:ExternalPayerId>
<ns1:PaymentFunction>CUSTOMER_PAYMENT</ns1:PaymentFunction>
<ns1:PartyId>100000011116</ns1:PartyId>
<ns1:CustomerAccountId>100000007573</ns1:CustomerAccountId>
<ns1:AccountSiteUseId xsi:nil="true"/>
<ns1:InstrumentType>CREDITCARD</ns1:InstrumentType>
<ns1:InstrumentId>31300900</ns1:InstrumentId>
<ns1:CurrencyCode xsi:nil="true"/>
<ns1:CardNumber>7809</ns1:CardNumber>
<ns1:CardExpirydate>2025-08-31</ns1:CardExpirydate>
<ns1:CardIssuerCode>MASTERCARD</ns1:CardIssuerCode>
<ns1:CardIssuerName>MasterCard</ns1:CardIssuerName>
<ns1:PurchasecardSubtype xsi:nil="true"/>
<ns1:CardHolderName>ABC Corp</ns1:CardHolderName>
<ns1:CardBillingAddressId>300000019901</ns1:CardBillingAddressId>
<ns1:CardFiName>300000008684</ns1:CardFiName>
<ns1:CardSingleUseFlag>false</ns1:CardSingleUseFlag>
<ns1:CardInformationOnlyFlag>false</ns1:CardInformationOnlyFlag>
<ns1:AccountNumber xsi:nil="true"/>
<ns1:BankName xsi:nil="true"/>
<ns1:BankNumber xsi:nil="true"/>
<ns1:BranchName xsi:nil="true"/>
<ns1:BranchNumber xsi:nil="true"/>
<ns1:BICNumber xsi:nil="true"/>
<ns1:Description xsi:nil="true"/>
<ns1:AssignmentLastUpdate>2022-12-16T11:49:26Z</ns1:AssignmentLastUpdate>
<ns1:AssignmentStartDate>2022-09-30</ns1:AssignmentStartDate>
<ns1:AssignmentEndDate>4712-12-31</ns1:AssignmentEndDate>
<ns1:CreatedBy>xyz</ns1:CreatedBy>
<ns1:CreationDate>2022-12-16T11:49:25.047Z</ns1:CreationDate>
<ns1:LastUpdatedBy>xyv</ns1:LastUpdatedBy>
<ns1:LastUpdateDate>2022-12-16T11:49:26Z</ns1:LastUpdateDate>
<ns1:LastUpdateLogin>EFF17E6849E8E0539560310A7831</ns1:LastUpdateLogin>
<ns1:ObjectVersionNumber>2</ns1:ObjectVersionNumber>
<ns1:PrimaryFlag>true</ns1:PrimaryFlag>
</ns2:result>
<ns2:result xsi:type="ns1:AssignedPmtInstrument" xmlns:ns0="http://xmlns.oracle.com/adf/svc/types/" xmlns:ns1="http://xmlns.oracle.com/apps/financials/payments/shared/payeePayerInstruments/externalPayerServiceV2/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns2="http://xmlns.oracle.com/apps/financials/payments/shared/payeePayerInstruments/externalPayerServiceV2/types/">
<ns1:InstrAssignmentId>300000063931</ns1:InstrAssignmentId>
<ns1:ExternalPayerId>300000055013</ns1:ExternalPayerId>
<ns1:PaymentFunction>CUSTOMER_PAYMENT</ns1:PaymentFunction>
<ns1:PartyId>100000011116</ns1:PartyId>
<ns1:CustomerAccountId>100000007573</ns1:CustomerAccountId>
<ns1:AccountSiteUseId xsi:nil="true"/>
<ns1:InstrumentType>CREDITCARD</ns1:InstrumentType>
<ns1:InstrumentId>2479025</ns1:InstrumentId>
<ns1:CurrencyCode xsi:nil="true"/>
<ns1:CardNumber>XXXXXXXXXXXX5100</ns1:CardNumber>
<ns1:CardExpirydate>2030-03-31</ns1:CardExpirydate>
<ns1:CardIssuerCode>MASTERCARD</ns1:CardIssuerCode>
<ns1:CardIssuerName>MasterCard</ns1:CardIssuerName>
<ns1:PurchasecardSubtype xsi:nil="true"/>
<ns1:CardHolderName>ABC Corp</ns1:CardHolderName>
<ns1:CardBillingAddressId>300000013928</ns1:CardBillingAddressId>
<ns1:CardFiName>300000008683</ns1:CardFiName>
<ns1:CardSingleUseFlag>false</ns1:CardSingleUseFlag>
<ns1:CardInformationOnlyFlag>false</ns1:CardInformationOnlyFlag>
<ns1:AccountNumber xsi:nil="true"/>
<ns1:BankName xsi:nil="true"/>
<ns1:BankNumber xsi:nil="true"/>
<ns1:BranchName xsi:nil="true"/>
<ns1:BranchNumber xsi:nil="true"/>
<ns1:BICNumber xsi:nil="true"/>
<ns1:Description xsi:nil="true"/>
<ns1:AssignmentLastUpdate>2023-08-28T14:52:29.542Z</ns1:AssignmentLastUpdate>
<ns1:AssignmentStartDate>2023-08-28</ns1:AssignmentStartDate>
<ns1:AssignmentEndDate>4712-12-31</ns1:AssignmentEndDate>
<ns1:CreatedBy>xyz</ns1:CreatedBy>
<ns1:CreationDate>2023-08-28T14:52:12.09Z</ns1:CreationDate>
<ns1:LastUpdatedBy>xyz</ns1:LastUpdatedBy>
<ns1:LastUpdateDate>2023-08-28T14:52:29.542Z</ns1:LastUpdateDate>
<ns1:LastUpdateLogin>013AE5810B5E063E262310ABDD1</ns1:LastUpdateLogin>
<ns1:ObjectVersionNumber>1</ns1:ObjectVersionNumber>
<ns1:PrimaryFlag>false</ns1:PrimaryFlag>
</ns2:result>
</ns0:findAssignedInstrumentsByPayerResponse>
</env:Body>
</env:Envelope>
Thursday, June 23, 2022
FA:OM:SQL: To get the RMA Order receipt details along with the Serial numbers
WITH sn_tbl as ( SELECT dha.header_id
, dla.line_id
, dfla.fulfill_line_id
, dla.display_line_number | | '.' | | dfla.fulfill_line_number line_num
, LISTAGG(iut.serial_number, ',' on overflow truncate) WITHIN GROUP (
ORDER BY iut.serial_number
) Serial_numbers
FROM fusion.inv_material_txns imt
, fusion.inv_org_parameters iop
, fusion.egp_system_items_b esi
, fusion.inv_transaction_types_vl ittv
, fusion.fnd_lookup_values_vl flva
, fusion.inv_txn_source_types_vl itst
, fusion.inv_unit_transactions iut
, fusion.rcv_shipment_headers rsh
, fusion.rcv_shipment_lines rsl
, fusion.rcv_transactions rt
, fusion.doo_fulfill_line_details dfld
, fusion.doo_fulfill_lines_all dfla
, fusion.doo_lines_all dla
, fusion.doo_headers_all dha
WHERE imt.organization_id = iop.organization_id
AND imt.organization_id = esi.organization_id
AND imt.inventory_item_id = esi.inventory_item_id
AND imt.transaction_type_id = ittv.transaction_type_id
AND ittv.transaction_action_id = flva.lookup_code
AND flva.lookup_type = 'INV_TRANSACTION_ACTION'
AND flva.enabled_flag = 'Y'
AND ittv.transaction_source_type_id = itst.transaction_source_type_id
AND imt.transaction_id = iut.transaction_id(+)
AND ittv.transaction_type_name = 'RMA Receipt'
AND itst.transaction_source_type_name = 'RMA'
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsh.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_line_id = rt.shipment_line_id
AND rt.transaction_id = imt.rcv_transaction_id
AND imt.rcv_transaction_id = dfld.rma_receipt_transaction_id
AND dfld.fulfill_line_id = dfla.fulfill_line_id
AND dfla.line_id = dla.line_id
AND dla.header_id = dha.header_id
AND dfla.header_id = dha.header_id
AND dha.submitted_flag = 'Y'
GROUP BY dla.display_line_number | | '.' | | dfla.fulfill_line_number
, dha.header_id
, dla.line_id
, dfla.fulfill_line_id)
SELECT hauft.NAME BusinessUnit
, a.order_type_code
, o.organization_code | | ' ' | | flv.meaning warehouse
, a.order_number
, f.source_line_number
, i.item_number
, b.ordered_qty l_qty
, f.rma_delivered_qty f_del_qty
, f.shipped_qty s_qty
, f.CANCELED_QTY
, initcap(f.status_code) f_status_code
, decode(f.canceled_flag, 'Y', 'Line Canceled', 'Not Canceled') Line_cancel_status
, a.creation_date
, i.description
, A.SOURCE_ORDER_SYSTEM | | ':' | | A.SOURCE_ORDER_ID ORDER_KEY
, a.ordered_date
, DECODE(i.serial_number_control_code, 1, 'No', 'Yes') serial_number_control_code
, sn_tbl.Serial_numbers
FROM fusion.doo_headers_all a
, fusion.doo_lines_All b
, fusion.doo_fulfill_lines_all f
, fusion.egp_system_items_v i
, fusion.inv_org_parameters o
, fusion.fnd_lookup_values_vl flv
, fusion.hr_org_unit_classifications_f houcf
, fusion.hr_all_organization_units_f haouf
, fusion.hr_organization_units_f_tl hauft
, sn_tbl
WHERE 1 = 1
AND a.submitted_flag = 'Y'
AND a.header_id = b.header_id
AND b.header_id = f.header_id
AND b.line_id = f.line_id
AND a.order_type_code = flv.lookup_code
AND flv.lookup_type = 'ORA_DOO_ORDER_TYPES'
AND flv.enabled_flag = 'Y'
AND f.fulfill_org_id = i.organization_id
AND f.fulfill_org_id = o.organization_id
AND f.inventory_item_id = i.inventory_item_id
AND haouf.organization_id = houcf.organization_id
AND haouf.organization_id = hauft.organization_id
AND hauft.organization_id = a.org_id
AND hauft.language = 'US'
AND sysdate BETWEEN houcf.effective_start_date AND NVL(houcf.effective_end_date, SYSDATE + 1)
AND sysdate BETWEEN haouf.effective_start_date AND NVL(haouf.effective_end_date, SYSDATE + 1)
AND sysdate BETWEEN hauft.effective_start_date AND NVL(hauft.effective_end_date, SYSDATE + 1)
AND hauft.effective_start_date = haouf.effective_start_date
AND hauft.effective_end_date = haouf.effective_end_date
AND houcf.classification_code = 'FUN_BUSINESS_UNIT'
AND f.header_id = sn_tbl.header_id
AND f.line_id = sn_tbl.line_id
and f.fulfill_line_id = sn_tbl.fulfill_line_id
AND a.order_number = :p_order_number
ORDER BY a.creation_date
, LPAD(f.source_line_number, 2)