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

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

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

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' 

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