Wednesday 6 April 2022

query to get Expense report number invoice number and payment number in Oracle Fusion

 select 
i.invoice_number,
c.check_number,
p.payment_number,
eer.expense_report_num
from
AP_INVOICES_ALL i,
AP_CHECKS_ALL c,
AP_INVOICE_PAYMENTS_ALL p,
EXM_EXPENSE_REPORTS eer
where p.invoice_id = i.invoice_id
and c.check_id = p.check_id
and i.REFERENCE_KEY1=eer.EXPENSE_REPOrt_id
and i.PRODUCT_TABLE='EXM_EXPENSE_REPORTS'
Posted by at No comments:

Tuesday 5 April 2022

Rest API to create Supplier in Oracle Fusion

 Rest API:  https://HOST/fscmRestApi/resources/11.13.18.05/suppliers

Method: POST

Request Payload:

{
  "Supplier" : "Test Supplier",
  "TaxOrganizationType" : "Corporation",
  "SupplierType" : "Supplier",
  "BusinessRelationship" : "Spend Authorized",
  "DUNSNumber" : "221132556",
  "OneTimeSupplierFlag" : false,
  "TaxpayerCountry" : "United States",
  "TaxpayerId" : "98-01234567"
}

Response :

{
    "SupplierId": 300000127675805,
    "SupplierPartyId": 300000127675806,
    "Supplier": "Test Supplier",
    "SupplierNumber": "20029",
    "AlternateName": null,
    "TaxOrganizationTypeCode": "CORPORATION",
    "TaxOrganizationType": "Corporation",
    "SupplierTypeCode": "SUPPLIER_GH",
    "SupplierType": "Supplier",
    "InactiveDate": null,
    "Status": "ACTIVE",
    "BusinessRelationshipCode": "SPEND_AUTHORIZED",
    "BusinessRelationship": "Spend Authorized",
    "ParentSupplierId": null,
    "ParentSupplier": null,
    "ParentSupplierNumber": null,
    "CreationDate": "2022-04-05T17:49:39.001+00:00",
    "CreatedBy": "pinky.mukherjee@pwc.com",
    "LastUpdateDate": "2022-04-05T17:49:46.911+00:00",
    "LastUpdatedBy": "pinky.mukherjee@pwc.com",
    "CreationSourceCode": "WEB_SERVICE",
    "CreationSource": "Web Service",
    "DataFoxScore": null,
    "DataFoxScoringCriteria": null,
    "Alias": null,
    "DUNSNumber": "221132556",
    "OneTimeSupplierFlag": false,
    "RegistryId": "SUP_540989",
    "CustomerNumber": null,
    "StandardIndustryClass": null,
    "IndustryCategory": null,
    "IndustrySubcategory": null,
    "NationalInsuranceNumber": null,
    "NationalInsuranceNumberExistsFlag": false,
    "CorporateWebsite": null,
    "YearEstablished": null,
    "MissionStatement": null,
    "YearIncorporated": null,
    "ChiefExecutiveTitle": null,
    "ChiefExecutiveName": null,
    "PrincipalTitle": null,
    "PrincipalName": null,
    "FiscalYearEndMonthCode": null,
    "FiscalYearEndMonth": null,
    "CurrentFiscalYearPotentialRevenue": null,
    "PreferredFunctionalCurrencyCode": null,
    "PreferredFunctionalCurrency": null,
    "TaxRegistrationCountryCode": null,
    "TaxRegistrationCountry": null,
    "TaxRegistrationNumber": null,
    "TaxpayerCountryCode": "US",
    "TaxpayerCountry": "United States",
    "TaxpayerId": "98-01234567",
    "TaxpayerIdExistsFlag": true,
    "FederalReportableFlag": false,
    "FederalIncomeTaxTypeCode": null,
    "FederalIncomeTaxType": null,
    "StateReportableFlag": false,
    "TaxReportingName": null,
    "NameControl": null,
    "VerificationDate": null,
    "UseWithholdingTaxFlag": false,
    "WithholdingTaxGroupId": null,
    "WithholdingTaxGroup": null,
    "BusinessClassificationNotApplicableFlag": false,
    "DataFoxId": null,
    "DataFoxCompanyName": null,
    "DataFoxLegalName": null,
    "DataFoxCompanyPrimaryURL": null,
    "DataFoxNAICSCode": null,
    "DataFoxCountry": null,
    "DataFoxEIN": null,
    "DataFoxLastSyncDate": null,
    "OBNEnabledFlag": null,
    "links": [
        {
            "rel": "self",
            "href": "https://Host:443/fscmRestApi/resources/11.13.18.05/suppliers/300000127675805",
            "name": "suppliers",
            "kind": "item",
            "properties": {
                "changeIndicator": "ACED0005737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A65787000000007770400000007737200116A6176612E6C616E672E496E746567657212E2A0A4F781873802000149000576616C7565787200106A6176612E6C616E672E4E756D62657286AC951D0B94E08B0200007870000000017371007E0002000000017372001B6F7261636C652E6A626F2E646F6D61696E2E4E756C6C56616C75655899C1C58DAABEEB02000149000A6D53514C54797065496478700000000C7371007E00020000000171007E00077371007E00020000000171007E000778"
            }
        },
        {
            "rel": "canonical",
            "href": "https://Host:443/fscmRestApi/resources/11.13.18.05/suppliers/300000127675805",
            "name": "suppliers",
            "kind": "item"
        },
        {
            "rel": "lov",
            "href": "https://Host:443/fscmRestApi/resources/11.13.18.05/suppliers/300000127675805/lov/CountryLookup",
            "name": "CountryLookup",
            "kind": "collection"
        },
        {
            "rel": "lov",
            "href": "https://Host:443/fscmRestApi/resources/11.13.18.05/suppliers/300000127675805/lov/FiscalYearEndMonthLookup",
            "name": "FiscalYearEndMonthLookup",
            "kind": "collection"
        },
        {
            "rel": "lov",
            "href": "https://Host:443/fscmRestApi/resources/11.13.18.05/suppliers/300000127675805/lov/ParentSupplierLookup",
            "name": "ParentSupplierLookup",
            "kind": "collection"
        },
        {
            "rel": "lov",
            "href": "https://Host:443/fscmRestApi/resources/11.13.18.05/suppliers/300000127675805/lov/CurrencyLookup",
            "name": "CurrencyLookup",
            "kind": "collection"
        },
        {
            "rel": "lov",
            "href": "https://Host:443/fscmRestApi/resources/11.13.18.05/suppliers/300000127675805/lov/BusinessRelationshipLookup",
            "name": "BusinessRelationshipLookup",
            "kind": "collection"
        },
        {
            "rel": "lov",
            "href": "https://Host:443/fscmRestApi/resources/11.13.18.05/suppliers/300000127675805/lov/TaxOrganizationTypeLookup",
            "name": "TaxOrganizationTypeLookup",
            "kind": "collection"
        },
        {
            "rel": "lov",
            "href": "https://Host:443/fscmRestApi/resources/11.13.18.05/suppliers/300000127675805/lov/SupplierTypeLookup",
            "name": "SupplierTypeLookup",
            "kind": "collection"
        },
        {
            "rel": "lov",
            "href": "https://Host:443/fscmRestApi/resources/11.13.18.05/suppliers/300000127675805/lov/FederalIncomeTaxTypeLookup",
            "name": "FederalIncomeTaxTypeLookup",
            "kind": "collection"
        },
        {
            "rel": "lov",
            "href": "https://Host:443/fscmRestApi/resources/11.13.18.05/suppliers/300000127675805/lov/WithholdingTaxGroupLookup",
            "name": "WithholdingTaxGroupLookup",
            "kind": "collection"
        },
        {
            "rel": "child",
            "href": "https://Host:443/fscmRestApi/resources/11.13.18.05/suppliers/300000127675805/child/DFF",
            "name": "DFF",
            "kind": "collection"
        },
        {
            "rel": "child",
            "href": "https://Host:443/fscmRestApi/resources/11.13.18.05/suppliers/300000127675805/child/addresses",
            "name": "addresses",
            "kind": "collection"
        },
        {
            "rel": "child",
            "href": "https://Host:443/fscmRestApi/resources/11.13.18.05/suppliers/300000127675805/child/attachments",
            "name": "attachments",
            "kind": "collection"
        },
        {
            "rel": "child",
            "href": "https://Host:443/fscmRestApi/resources/11.13.18.05/suppliers/300000127675805/child/businessClassifications",
            "name": "businessClassifications",
            "kind": "collection"
        },
        {
            "rel": "child",
            "href": "https://Host:443/fscmRestApi/resources/11.13.18.05/suppliers/300000127675805/child/contacts",
            "name": "contacts",
            "kind": "collection"
        },
        {
            "rel": "child",
            "href": "https://Host:443/fscmRestApi/resources/11.13.18.05/suppliers/300000127675805/child/globalDFF",
            "name": "globalDFF",
            "kind": "collection"
        },
        {
            "rel": "child",
            "href": "https://Host:443/fscmRestApi/resources/11.13.18.05/suppliers/300000127675805/child/productsAndServices",
            "name": "productsAndServices",
            "kind": "collection"
        },
        {
            "rel": "child",
            "href": "https://Host:443/fscmRestApi/resources/11.13.18.05/suppliers/300000127675805/child/sites",
            "name": "sites",
            "kind": "collection"
        },
        {
            "rel": "action",
            "href": "https://Host:443/fscmRestApi/resources/11.13.18.05/suppliers/300000127675805/action/scores",
            "name": "scores",
            "kind": "other"
        },
        {
            "rel": "action",
            "href": "https://Host:443/fscmRestApi/resources/11.13.18.05/suppliers/300000127675805/action/signals",
            "name": "signals",
            "kind": "other"
        },
        {
            "rel": "action",
            "href": "https://Host:443/fscmRestApi/resources/11.13.18.05/suppliers/300000127675805/action/submitSpendAuthorizationRequest",
            "name": "submitSpendAuthorizationRequest",
            "kind": "other"
        }
    ]
}
Posted by at No comments:

Rest API to Get Legal entity details

 Method: Get

Rest API: https://<Host>/fscmRestApi/resources/11.13.18.05/legalEntitiesLOV/{legal_entity}

Response :
{
    "LegalEntityId": 300000000000000,
    "Name": "Legal Entity",
    "LegalEntityIdentifier": "000",
    "EffectiveFrom": null,
    "EffectiveTo": null,
    "PartyId": 300000055721140,
    "links": [
        {
            "rel": "self",
            "href": "https://Server:443/fscmRestApi/resources/11.13.18.05/legalEntitiesLOV/300000000000000",
            "name": "legalEntitiesLOV",
            "kind": "item"
        },
        {a
            "rel": "canonical",
            "href": "https://Server:443/fscmRestApi/resources/11.13.18.05/legalEntitiesLOV/300000000000000",
            "name": "legalEntitiesLOV",
            "kind": "item"
        }
    ]
}



Posted by at No comments:

Rest API to Get Business unit Details

Method: Get

Rest API: https://<Host>/hcmRestApi/resources/11.13.18.05/hcmBusinessUnitsLOV/{BusinessUnitId}

Response:

{
    "BusinessUnitId": 300000000000000,
    "Name": "Business Unit",
    "Status": "BU",
    "links": [
        {
            "rel": "self",
            "href": "https://Server/hcmRestApi/resources/11.13.18.05/hcmBusinessUnitsLOV/300000000000000",
            "name": "hcmBusinessUnitsLOV",
            "kind": "item"
        },
        {
            "rel": "canonical",
            "href": "https://Server/hcmRestApi/resources/11.13.18.05/hcmBusinessUnitsLOV/300000000000000",
            "name": "hcmBusinessUnitsLOV",
            "kind": "item"
        }
    ]
}
Posted by at No comments:

Query to get Purchase Order and requisition in Oracle Fusion

SELECT
POH.PO_HEADER_ID,
POH.SEGMENT1  ,
PRHA.REQUISITION_NUMBER
FROM
PO_HEADERS_ALL POH,
PO_DISTRIBUTIONS_ALL PDA ,
POR_REQ_DISTRIBUTIONS_ALL PRDA ,
POR_REQUISITION_LINES_ALL PRLA ,
POR_REQUISITION_HEADERS_ALL PRHA
WHERE 1=1
AND POH.PO_HEADER_ID = PDA.PO_HEADER_ID
AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID
AND PRDA.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID
AND PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID
AND PRHA.REQUISITION_NUMBER = <Requsition_number>
Posted by at No comments:
Subscribe to: Posts (Atom)