Sunday, 5 January 2025

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