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 

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

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

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

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"

        }

    ]

}

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