Getting the Tax Rate Codes by Business Unit and Reference Set

Use the Below QUERY to get the Tax Rate Codes corresponding to each Business Unit and Reference Data Set.



SELECT A.INTERNAL_ORGANIZATION_ID,
       BU.BU_NAME,
       R.TAX_RATE_CODE,
       RM.COUNTRY_CODE,
       T.TAX_TYPE_CODE,
       RM.TAX_REGIME_CODE,
       RM.REGIME_TYPE_FLAG,
       LC.LOOKUP_TYPE,
       LC.LOOKUP_CODE,
       LC.DISPLAY_SEQUENCE,
       LC.ENABLED_FLAG,
       LC.START_DATE_ACTIVE,
       LC.END_DATE_ACTIVE,
       LC.MEANING,
       LC.DESCRIPTION,
       LC.TAG,
       LC.SET_ID LookUpSetID
  FROM ZX_RATES_B               R,
       ZX_TAXES_B               T,
       ZX_ACCOUNTS              A,
       FUN_FIN_BUSINESS_UNITS_V BU,
       ZX_REGIMES_B             RM,
       FND_LOOKUP_VALUES_VL     LC,
       FND_SETID_ASSIGNMENTS    SA
WHERE     R.TAX_RATE_ID = A.TAX_ACCOUNT_ENTITY_ID
       AND A.TAX_ACCOUNT_ENTITY_CODE = 'RATES'
       AND NVL (R.EFFECTIVE_TO, SYSDATE) >= SYSDATE
       AND NVL (RM.EFFECTIVE_TO, SYSDATE) >= SYSDATE
       AND R.ACTIVE_FLAG = 'Y'
       AND A.INTERNAL_ORGANIZATION_ID = BU.BU_ID
       AND R.TAX_REGIME_CODE = RM.TAX_REGIME_CODE
       AND (   (    LC.LOOKUP_TYPE = 'ZX_INPUT_CLASSIFICATIONS'
                AND RM.REGIME_TYPE_FLAG = 'I')
            OR (    LC.LOOKUP_TYPE = 'ZX_WHT_TAX_CLASSIFICATION_CODE'
                AND RM.REGIME_TYPE_FLAG = 'W'))
       AND LC.LOOKUP_CODE = R.TAX_RATE_CODE
       AND LC.ENABLED_FLAG = 'Y'
       AND SA.DETERMINANT_VALUE = BU.BU_ID
       AND LC.SET_ID = SA.SET_ID
       AND SA.REFERENCE_GROUP_NAME = 'ZX_TAX_CLASSIFICATION_CODES'
       AND R.TAX = T.TAX
       AND RM.TAX_REGIME_CODE = T.TAX_REGIME_CODE
       AND NVL (T.EFFECTIVE_TO, SYSDATE) >= SYSDATE

Comments

Post a Comment

Popular posts from this blog

How to Add an Attachment to the Payables Invoice using the REST API

How to Add an Attachment to the Payables Invoice using the REST API Uploading file type attachment can be achieved by using a combination of FIN services.  You will need to create a custom java program/process to do the following: 1) Call uploadFileToUcm() web service to upload the attachment file to a UCM account. This API returns the document ID in UCM. ERP Integration Services is the Business Object Services for this Operation. 2) Construct document URL in UCM by using document ID returned in step 1. The UCM doc URL has the format of "https://efops-rel8-gadebug-external-fs.us.oracle.com/cs/idcplg?IdcService=GET_FILE&dID=document ID". Replace with correct UCM server name and substitute document ID with value from step 1. 3) Call AP web service 'createURLAttachment' to create URL attachment for invoice by using the document URL constructed in step 2.
Read more

How to Create Procurement Agent in Bulk or using REST API

Image
How to Create Procurement Agent in Bulk or using REST API Step1:  Identify the User for which you want to Create/Assign the procurement Agent. Step2:  Identify the Business Units you want to Assign to the Procurement Agent. Step3:  Use the Below query to get the Agent ID which is nothing but the Person ID from the Query and the Business Unit ID which is needed for creating the Procurement Agent. SELECT U.USERNAME,U.PERSON_ID,B.BU_ID,B.BU_NAME FROM PER_USERS U,FUN_ALL_BUSINESS_UNITS_V B WHERE USERNAME IN (:UserID) --AND B.BU_NAME IN (:BusinessUnit_Name) AND NOT EXISTS (SELECT 'X' FROM PO_AGENT_ASSIGNMENTS A   WHERE A.AGENT_ID = U.PERSON_ID AND B.BU_ID = A.PRC_BU_ID ) Step4:  Download the Macro File attached to this and follow the instruction given in the Instruction Sheet. Procurement Agent Step5:  Populate the data in the ProcurementAgent sheet.  Sample data is there, make sure to use the static values a
Read more

Oracle Cloud ERP - AP - How to get the Status of the Invoice

Use the Below Query to get the Status of the Invoice. SELECT DECODE(AP_INVOICES_UTILITY_PKG.GET_APPROVAL_STATUS(AIA.INVOICE_ID,AIA.INVOICE_AMOUNT,AIA.PAYMENT_STATUS_FLAG,AIA.INVOICE_TYPE_LOOKUP_CODE),                 'FULL'            , 'Fully Applied',                 'NEVER APPROVED'  , 'Never Validated',                 'NEEDS REAPPROVAL', 'Needs Revalidation',                 'CANCELLED'       , 'Cancelled',                 'UNPAID'          , 'Unpaid',                 'AVAILABLE'       , 'Available',                 'UNAPPROVED'      , 'Unvalidated',                 'APPROVED'        , 'Validated',                 'PERMANENT'       , 'Permanent Prepayment',                 NULL) "INSTATUS_SUMMARY" ,AIA.*         FROM   AP_INVOICES_ALL AIA         WHERE   AIA.INVOICE_ID = '300000283970076'
Read more