Oracle Mania

Thursday, 27 March 2025

Query to get Charts Of Accounts structure – Segments and Value Set

select hou.name BU_NAME,
led.NAME Ledger_Name,
Str.STRUCTURE_CODE CHART_OF_ACCOUNTS_Name,
SegInSt.SEGMENT_CODE COA_SEGMENT_NAME,
vs.value_set_code,
vs.description value_set_desc
from hr_operating_units hou,
xla_gl_ledgers led ,
fnd_kf_structures_b Str,
fnd_kf_str_instances_b StrInSt,
fnd_kf_segment_instances SegInSt,
fnd_vs_value_sets vs
where 1=1
and led.ledger_id = hou.set_of_books_id
AND led.CHART_OF_ACCOUNTS_ID = Str.Structure_id
AND Str.KEY_FLEXFIELD_CODE = ‘GL#’
AND Str.Structure_id = StrInSt.Structure_id
and SegInSt.Structure_inStance_id = StrInSt.Structure_inStance_id
and vs.VALUE_SET_ID = SegInSt.VALUE_SET_ID

Thursday, 20 February 2025

Query to Get List of Procurement Agents and Access In oracle Fusion

SELECT

       ppf.display_name
     , pu.username
     , hou.name BU_NAME
     , paa.access_action_code
     , paa.active_flag
     , paa.access_others_level_code
FROM
       po_agent_accesses     paa
     , per_person_names_f_v  ppf
     , per_users             pu
     , hr_organization_units hou
WHERE
       1                       = 1
       AND paa.agent_id        = ppf.person_id
       AND pu.person_id        = ppf.person_id
       AND hou.organization_id = paa.prc_bu_id

Sunday, 5 January 2025

Query to get Logged IN user name in Oracle fuson

SELECT sys_context ('USERENV', 'CLIENT_IDENTIFIER') FROM dual

Query to get Chart Of Account Structure Name and Segments in Oracle fusion

select
b.FORM_LEFT_PROMPT "Chart Of Account Segment Name",
b.description,
a.segment_name "CCA_VALUSET",
c.ID_FLEX_STRUCTURE_NAME "Chart Of Account Structure Name",
b.application_column_name "Internal Segment"
from
FND_ID_FLEX_SEGMENTS a,
FND_ID_FLEX_SEGMENTS_TL b,
FND_ID_FLEX_STRUCTURES_VL c
where
b.language = 'US'
and c.ID_FLEX_NUM = b.ID_FLEX_NUM
and c.enabled_flag = 'Y'
and b.application_id = a.application_id
and b.id_flex_num = a.id_flex_num
and b.application_column_name = a.application_column_name
and a.application_id = 101

Tuesday, 16 April 2024

Rest API to Create a AP Invoice in Oracle Fusion

Rest API :

https://<Host>/fscmRestApi/resources/11.13.18.05/invoices

example URI:

https://abcd123-test.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/invoices

Method : Post

Json payload

{
    "InvoiceNumber": "AND_Unmatched_Invoice",
    "InvoiceCurrency": "USD",
    "InvoiceAmount": 2212.75,
    "InvoiceDate": "2019-02-01",
    "BusinessUnit": "Testing BU",
    "Supplier": "INV Global Market Intelligence LLC",
    "SupplierSite": "Main",
    "InvoiceGroup": "01Feb2019",
    "Description": "Office Supplies",
    "invoiceLines": [
        {
            "LineNumber": 1,
            "LineAmount": 2112.75,
            "invoiceDistributions": [
                {
                    "DistributionLineNumber": 1,
                    "DistributionLineType": "Item",
                    "DistributionAmount": 2112.75,
                    "DistributionCombination": "653-83300000-610-100-000-0000"
                }
            ]
        },
        {
            "LineNumber": 2,
            "LineType": "Freight",
            "LineAmount": 100,
            "ProrateAcrossAllItemsFlag": true
        }
    ]
}


Post response 201Created

Thursday, 25 January 2024

Rest API Payload to create a Party or a organization and site

Rest URL: https://<Host>/crmRestApi/resources/11.13.18.05/hubOrganizations

Payload:

{
    "PartyNumber": "98791",
    "OrganizationName": "Test_98791",
    "PartyUsageCode": "EXTERNAL_LEGAL_ENTITY",
    "SourceSystemReference": [
        {
            "SourceSystem": "SalenticaEngageCRM",
            "SourceSystemReferenceValue": "98791",
            "StartDate": "2023-09-09"
        }
    ],
    "Address": [
        {
            "StartDateActive": "2012-09-09",
            "AddressType": "BILL_TO",
            "Address1": "123 Mayfield Road",
            "City": "Cleveland",
            "Country": "US",
            "PostalCode": "44113",
            "State": "OH"
        }
    ]
}

Monday, 2 October 2023

Query to Retrive Purchasing mapping set in oracle fusion

select
XMST.name,
DeliverLoc.LOCATION_NAME,
gcc.segment1||'.'||
gcc.segment2||'.'||
gcc.segment3||'.'||
gcc.segment4||'.'||
gcc.segment5||'.'||
gcc.segment6||'.'||
gcc.segment7 code_combination,
from XLA_MAPPING_SETS_TL XMST,
XLA_MAPPING_SET_VALUES xmSV,
gl_code_combinations gcc,
HR_LOCATIONS_ALL DeliverLoc
where XMST.application_id =201 -- Application id for purchasing
and XMST.name ='XX Procure Purchaseing CC MP' -- Name of Mapping Set
and XMST.application_id= xmSV.application_id
and XMST.language ='US'
and xmSV.mapping_set_code=XMST.mapping_set_code
and xmSV.VALUE_CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
and xmSV.INPUT_VALUE_CONSTANT1=DeliverLoc.location_id

Sunday, 30 July 2023

Res API to create a Party, Party site and Party Site Usage In Oracle Fusion

URL: https://host/crmRestApi/resources/11.13.18.05/hubOrganizations/

Method: POST

JSON payload

{
"PartyNumber": "TEST_Party_001",
"OrganizationName": "Testing Organization Creation",
"PartyUsageCode": "PARTY_OF_INTEREST",
"SourceSystemReference": [
{
"SourceSystem": "CRM",
"SourceSystemReferenceValue": "1234567"
}
],
"Address": [
{
"AddressType": "BILL_TO",
"Address1": "200 Hill Top Building",
"City": "hillington",
"Country": "US",
"PostalCode": "44023",
"State": "OH"
},
{
"AddressType": "SHIP_TO",
"Address1": "200 Hill Top Building",
"City": "hillington",
"Country": "US",
"PostalCode": "44023",
"State": "OH"
}
]
}

Wednesday, 30 November 2022

Rest API to Activate and Deactivate the Integration in OIC

URL: https:/Host/ic/api/integration/v1/integrations/Id

Headers

Content-Type : application/json; charset=utf-8

X-HTTP-Method-Override: PATCH

Method : POST

URL: https:/Host/ic/api/integration/v1/integrations/TestINT|01.00.0000

To deactivate a Integration.

{

"status":"CONFIGURED"

}

To Activate a integration

{

"status":"ACTIVATED"

}

Response :

{

"type": "integrationRs",

"links": [],

"status": "ACTIVATED",

"compatible": false,

"lockedFlag": false,

"scheduleApplicableFlag": false,

"scheduleDefinedFlag": false,

"tempCopyExists": false,

"totalEndPoints": 0,

"stopScheduleForDeactivation": false

}

Query to retrieve User Roles Assigned to a user in Oracle Fusion

SELECT pu.username,prdt.role_id,

prdt.role_name,

prd.role_common_name

FROM per_user_roles pur,

per_users pu,

per_roles_dn_tl prdt,

per_roles_dn prd

WHERE pu.user_id = pur.user_id

AND pu.username = :P_USERNAME

AND prdt.role_id = pur.role_id

AND prdt.role_id = prd.role_id

AND prdt.language = 'US'

AND pu.active_flag = 'Y'

ORDER BY pu.username, prdt.role_name

Tuesday, 7 June 2022

Query to get AP Invoice lines Tax details in Oracle Fusion

SELECT aia.invoice_num,
xep.name Legal_Entity_Name,
psv.vendor_name party,
aila.line_number Line_Ref,
aila.line_type_lookup_code,
gcc.segment1 Distribution_entity,
zl.tax_amt Tax_amount,
zl.tax_regime_code Tax_regime,
zl.tax_jurisdiction_code Tax_Juridiction,
zl.taxable_amt TaxableAmt,
ZE.exception_reason_code Exemption_Reason,
ZE.tax_rate_code Exemption_Rate,
zrb.country_code Country_of_taxation,
zl.tax_line_number Tax_Line_Ref,
zl.tax,
zl.tax_rate_code,
zl.tax_rate
FROM ap_invoices_all aia,
poz_suppliers_v psv,
xle_entity_profiles xep,
ap_invoice_lines_all aila,
gl_code_combinations gcc,
ap_invoice_distributions_all aida,
zx_lines zl,
zx_status_b zsb,
zx_exceptions ze,
zx_regimes_b zrb
WHERE 1 = 1
AND psv.vendor_id = aia.vendor_id
AND aia.legal_entity_id = xep.legal_entity_id(+)
AND aia.invoice_id = aila.invoice_id
AND aila.invoice_id = aida.invoice_id
AND aila.line_number = aida.invoice_line_number
AND aia.invoice_id = zl.trx_id(+)
AND aila.line_number = zl.trx_line_number(+)
AND zl.application_id(+) = 200
AND ze.tax_exception_id(+) = zl.tax_exception_id
AND zsb.tax_status_id = zl.tax_status_id
AND zl.tax_regime_id = zrb.tax_regime_id
AND aida.dist_code_combination_id = gcc.code_combination_id
AND ( aida.line_type_lookup_code = 'MISCELLANEOUS'
OR aida.line_type_lookup_code = 'FREIGHT'
OR aida.line_type_lookup_code = 'ITEM' )
AND zsb.tax_status_code = 'EXEMPT'

Thursday, 19 May 2022

Query to Get Purchase Category hierarchy In oracle Fusion

SELECT (SELECT category_name

FROM por_browse_categories_tl pbct

WHERE pbct.category_id = picpl.level1_cat_id) Level_1_Name,

(SELECT category_name

FROM por_browse_categories_tl pbct

WHERE pbct.category_id = picpl.level2_cat_id) Level_2_Name,

(SELECT category_name

FROM por_browse_categories_tl pbct

WHERE pbct.category_id = picpl.level3_cat_id) Level_3_Name

FROM por_item_cat_parent_levels picpl

WHERE picpl.type = 'SOURCING'

ORDER BY level_1_name,

level_2_name,

level_3_name

Wednesday, 4 May 2022

Sample payload To Create a Payables Invoice using Rest API in Oracle Fusion

Rest API : https://Host/fscmRestApi/resources/latest/invoices

Method: Post

Sample payload

{
    "InvoiceNumber": "Test_Rest_007",
    "InvoiceCurrency": "USD",
    "InvoiceAmount": 2112.75,
    "InvoiceDate": "2022-05-02",
    "BusinessUnit": "Business Unit",
      "InvoiceSource": "Manual invoice entry",
        "InvoiceType": "Standard",
  "PayGroup":"Refund",
    "Supplier": "Test Supplier ",
    "SupplierSite": "MAIN",
       "LiabilityDistribution": "100.00000.20501.00000.000",
    "invoiceLines": [
        {
            "LineNumber": 1,
            "LineAmount": 2112.75,
            "Description": null,
            "invoiceDistributions": [
                {
                    "DistributionLineNumber": 1,
                    "DistributionLineType": "Item",
                    "DistributionAmount": 2112.75,
                    "DistributionCombination": "100.00000.60005.00000.000"
                }
            ]
        }
    ]
}

Query to get Account Segment Description

SELECT gl_flexfields_pkg.get_description_sql

(chart_of_accounts_id,--- chart of account id

1,----- Position of segment

segment1 ---- Segment value

)

FROM gl_code_combinations

Query to get concatenated Code combination of a code combination id

SELECT gl_flexfields_pkg.get_concat_description

(chart_of_accounts_id,

code_combination_id)

FROM gl_code_combinations

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'

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"
        }
    ]
}

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"
        }
    ]
}

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"
        }
    ]
}

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>