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

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

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'