SELECT
       fu.user_name                            CP_RUNBY
     , to_char(fcr.request_date,'DD-Mon-YYYY') CP_RUNDATE
FROM
       fnd_concurrent_requests fcr
     , fnd_user                fu
WHERE
       fcr.requested_by   =fu.user_id
       AND fcr.request_id = fnd_global.conc_request_id

 SELECT DISTINCT
       PAPF.FULL_NAME APPROVED_BY
     , AIAH.APPROVER_COMMENTS
     , TO_CHAR(AIAH.LAST_UPDATE_DATE,'DD-MON-YYYY') APPROVAL_DATE
     , APPROVAL_HISTORY_ID
FROM
       AP_INV_APRVL_HIST_ALL AIAH
     , FND_USER              FUS
     , PER_ALL_PEOPLE_F      PAPF
WHERE
       INVOICE_ID          = :INVOICE_ID
       AND RESPONSE        = 'APPROVED'
       AND AIAH.CREATED_BY = FUS.USER_ID
       AND FUS.EMPLOYEE_ID = PAPF.PERSON_ID

 Select distinct
       AIA.INVOICE_NUM
     , AIA.INVOICE_DATE
     , JTD.TAX_CATEGORY_NAME GST_TAX_CAT
     , JTD.LINE_AMT
from
       APPS.JAI_TAX_DET_FCT_LINES_V JTD
     , AP_INVOICES_ALL              AIA
where
       AIA.INVOICE_ID               =:P_INVOICE_ID
       and JTD.TRX_NUMBER           = AIA.INVOICE_NUM
       and JTD.TAX_CATEGORY_NAME like 'GST%'

 SELECT
       JAW.REGIME_CODE
     , JAW.ACTUAL_SECTION_CODE
     , JAI.TDS_TAX_RATE
     , JAW.TAX_AMOUNT
     , JAW.PROCESS_MESSAGE
     , (
              SELECT
                     CONCATENATED_SEGMENTS
              FROM
                     GL_CODE_COMBINATIONS_KFV
              WHERE
                     CODE_COMBINATION_ID=DIST_CODE_COMBINATION_ID
       )
       ACCOUNT
     , JTC.TAX_CATEGORY_NAME
     , JCT.TAX_NAME
FROM
       JAI_AP_WTHLD_INV_TAXES JAW
     , JAI_TAX_CATEGORIES     JTC
     , JAI_CMN_TAXES_ALL      JCT
     , JAI_AP_TDS_INVOICES    JAI
WHERE
       JAI.INVOICE_ID         =JAW.INVOICE_ID
       AND JAI.INVOICE_ID     = :P_INVOICE_ID
       AND JAW.TAX_CATEGORY_ID=JTC.TAX_CATEGORY_ID
       AND JCT.TAX_ID         =JAW.TAX_TYPE_ID

SELECT DISTINCT

       hold_date

     , hold_lookup_code

     , hold_reason

FROM

       ap_holds_all

WHERE

       invoice_id     = :P_INVOICE_ID

       and STATUS_FLAG='S'

SELECT DISTINCT

       AILA.LINE_TYPE_LOOKUP_CODE LINE_TYPE

     , AILA.DESCRIPTION           INV_LINE_DESCRIPTION

     , (

              SELECT DISTINCT

                     SEGMENT1

              FROM

                     PO_HEADERS_ALL PHA

              WHERE

                     AILA.PO_HEADER_ID=PHA.PO_HEADER_ID

       )

       PO_NUM

     , (

              SELECT DISTINCT

                     TYPE_LOOKUP_CODE

              FROM

                     PO_HEADERS_ALL PHA

              WHERE

                     AILA.PO_HEADER_ID=PHA.PO_HEADER_ID

       )

       PO_TYPE

     , (

              SELECT DISTINCT

                     COMMENTS

              FROM

                     PO_HEADERS_ALL PHA

              WHERE

                     AILA.PO_HEADER_ID=PHA.PO_HEADER_ID

       )

       PO_DESCRIPTION

     , (

              SELECT DISTINCT

                     CREATION_DATE

              FROM

                     PO_HEADERS_ALL PHA

              WHERE

                     AILA.PO_HEADER_ID=PHA.PO_HEADER_ID

       )

            PO_CREATION_DATE

     , NULL NULL_RECEIPT_NUM

     , (

              SELECT DISTINCT

                     LOCATION_CODE

              FROM

                     HR_LOCATIONS HL

              WHERE

                     AILA.SHIP_TO_LOCATION_ID=HL.LOCATION_ID

       )

                                  SHIP_TO

     , NULL                       QTY_AP_LINES

     , NULL                       RATE_AP_LINES

     , AILA.AMOUNT                LINE_AMOUNT

     , APDA.LINE_TYPE_LOOKUP_CODE DIST_TYPE

     , APDA.DESCRIPTION           DIST_DESCRIPTION

     , (

              SELECT DISTINCT

                     CONCATENATED_SEGMENTS

              FROM

                     GL_CODE_COMBINATIONS_KFV

              WHERE

                     APDA.DIST_CODE_COMBINATION_ID=CODE_COMBINATION_ID

       )

                   CHARGE_ACCOUNT

     , APDA.AMOUNT DIST_AMOUNT

     , (

              SELECT DISTINCT

                     SEGMENT1

              FROM

                     PA_PROJECTS

              WHERE

                     PROJECT_ID=APDA.PROJECT_ID

       )

       PROJECT

     , (

              SELECT DISTINCT

                     NAME

              FROM

                     PA_PROJECTS

              WHERE

                     PROJECT_ID=APDA.PROJECT_ID

       )

       PROJECT_NAME

     , (

              SELECT DISTINCT

                     DESCRIPTION

              FROM

                     PA_PROJECTS

              WHERE

                     PROJECT_ID=APDA.PROJECT_ID

       )

       PROJECT_DESC

     , (

              SELECT DISTINCT

                     TASK_NAME

              FROM

                     PA_TASKS

              WHERE

                     TASK_ID=APDA.TASK_ID

       )

       TASK

     , APDA.EXPENDITURE_TYPE

     , (

              SELECT DISTINCT

                     NAME

              FROM

                     HR_ORGANIZATION_UNITS

              WHERE

                     APDA.EXPENDITURE_ORGANIZATION_ID=ORGANIZATION_ID

       )

                                                   EXPENDITURE_ORGANIZATION

     , TO_CHAR(APDA.ACCOUNTING_DATE,'DD-MON-YYYY') ACCOUNTING_DATE

FROM

       AP_INVOICES_ALL              AIA

     , AP_INVOICE_LINES_ALL         AILA

     , AP_INVOICE_DISTRIBUTIONS_ALL APDA

WHERE

       APDA.INVOICE_ID(+) = AILA.INVOICE_ID

       AND AIA.INVOICE_ID =AILA.INVOICE_ID

       AND AIA.INVOICE_ID =NVL(:P_INVOICE_NUM,AIA.INVOICE_NUM)

       AND AIA.INVOICE_DATE BETWEEN NVL(TO_DATE(:P_DATE_FROM,'YYYY/MM/DD HH24:MI:SS'),AIA.INVOICE_DATE) AND NVL(TO_DATE(:P_DATE_TO,'YYYY/MM/DD HH24:MI:SS'),AIA.INVOICE_DATE)

SELECT DISTINCT  TO_CHAR(SYSDATE,'DD-MON-YYYY') RUNDATE,
    AIA.INVOICE_ID,
    AIA.INVOICE_NUM,
    ASU.VENDOR_NAME,
    ASSA.VENDOR_SITE_CODE,
    ASU.SEGMENT1                   SUPPLIER_NUM,
    AIA.INVOICE_ID                 VOUCHER,
    AIA.ATTRIBUTE5                 INV_CATEGORY,
    AT.NAME                        PAYYMT_TERM,
    AIA.SOURCE,
    AIA.DESCRIPTION INV_DESCRIPTION,
    AIA.INVOICE_TYPE_LOOKUP_CODE   INVOICE_TYPE,
    TO_CHAR(AIA.INVOICE_DATE,'DD-MON-YYYY') INVOICE_DATE,
    TO_CHAR(AIA.TERMS_DATE,'DD-MON-YYYY')TERMS_DATE
    ,TO_CHAR(AIA.GL_DATE,'DD-MON-YYYY')GL_DATE,
    AIA.INVOICE_AMOUNT,
    AIA.AMOUNT_PAID,
    ( AIA.INVOICE_AMOUNT - AIA.AMOUNT_PAID ) BALANCE,
    NVL(JAI.TDS_AMOUNT,0),
    TO_CHAR(APSA.DUE_DATE,'DD-MON-YYYY') DUE_DATE,
    APSA.GROSS_AMOUNT,
    APSA.AMOUNT_REMAINING,
    APSA.HOLD_FLAG,
    APSA.IBY_HOLD_REASON           HOLD_REASON,
    APSA.PAYMENT_METHOD_CODE PAYMENT_METHOD,
    APSA.PAYMENT_STATUS_FLAG       PAYMENT_STATUS
,DECODE (AIA.WFAPPROVAL_STATUS,'WFAPPROVED','APPROVED','MANUALLY APPROVED','APPROVED','NOT REQUIRED','APPROAL NOT REQUIRED', 'REQUIRED', 'NEED APPROVAL', 'NOT REQUIRED', AIA.WFAPPROVAL_STATUS
) APPROVAL_STATUS
   ,DECODE (AP_INVOICES_PKG.GET_POSTING_STATUS( AIA.INVOICE_ID),'N','NO','Y','YES') ACCOUNTED
   ,DECODE(APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS(AIA.INVOICE_ID,AIA.INVOICE_AMOUNT,AIA.PAYMENT_STATUS_FLAG,AIA.INVOICE_TYPE_LOOKUP_CODE),
                 'APPROVED',     'VALIDATED',
                 'FULL',         'FULLY VALIDATD',
                 'UNAPPROVED',   'UNVALIDATED',
                 'NEEDS REAPPROVAL','NEEDS REVALIDATION',
                 'NEVER APPROVED','NEVER VALIDATED',
                 'CANCELLED',    'CANCELLED',
                 'UNPAID',       'UNPAID',
                 'AVAILABLE',    'AVAILABLE'
                 )VALIDATED 
    FROM
    AP_SUPPLIERS               ASU,
    AP_SUPPLIER_SITES_ALL      ASSA,
    AP_INVOICES_ALL            AIA,
    AP_INVOICE_LINES_ALL       AILA,
    AP_TERMS                   AT,
    JAI_AP_TDS_INVOICES        JAI,
    AP_PAYMENT_SCHEDULES_ALL   APSA
    ,AP_INVOICE_DISTRIBUTIONS_ALL APDA
WHERE
    1 = 1
    AND ASU.VENDOR_ID = ASSA.VENDOR_ID
    AND AILA.INVOICE_ID = AIA.INVOICE_ID
    AND AIA.VENDOR_ID = ASU.VENDOR_ID
    AND AIA.VENDOR_SITE_ID = ASSA.VENDOR_SITE_ID
    AND AT.TERM_ID = AIA.TERMS_ID
    AND JAI.INVOICE_ID (+) = AIA.INVOICE_ID
    AND APSA.INVOICE_ID = AIA.INVOICE_ID
    AND APDA.INVOICE_ID(+) = AIA.INVOICE_ID
    AND AIA.INVOICE_NUM = NVL(:P_INVOICE_NUM,AIA.INVOICE_NUM)
AND AIA.INVOICE_DATE BETWEEN NVL(TO_DATE(:P_DATE_FROM,'YYYY/MM/DD HH24:MI:SS'),AIA.INVOICE_DATE) AND NVL(TO_DATE(:P_DATE_TO,'YYYY/MM/DD HH24:MI:SS'),AIA.INVOICE_DATE)

TCA
Sr. No.	Detail	API Name	Module
1	Create Customer Account	HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT	TCA
2	Create a Customer Account Relationship API	HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE	TCA
3	API to update a Customer Account Relationship TCA R12	HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCT_RELATE	TCA
4	API to Update a Customer Account TCA R12	HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCOUNT	TCA
5	Create Customer Profile	HZ_CUSTOMER_PROFILE_V2PUB.CREATE_CUSTOMER_PROFILE	TCA
6	API to Create a Customer Site TCA R12	HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE	TCA
7	API to Create a Customer Site Use TCA R12	HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE	TCA
8	API to Create a Party Site TCA R12	HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE	TCA
9	API to Create a Party Site Use TCA R12	HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE_USE	TCA
10	API to create a Person Type Party and Customer Account	HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT	TCA
11	API to Create a Phone Number (Contacts) TCA R12	HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT	TCA
12	API to create customer Addresses in TCA R12	HZ_LOCATION_V2PUB.CREATE_LOCATION	TCA
13	API to create Group in TCA R12	HZ_PARTY_V2PUB.CREATE_GROUP	TCA
14	API to populate loc_id HZ_LOCATIONS R12	HZ_TAX_ASSIGNMENT_V2PUB. CREATE_LOC_ASSIGNMENT	TCA
15	API to update a Customer Profile TCA R12	HZ_CUSTOMER_PROFILE_V2PUB.UPDATE_CUSTOMER_PROFILE	TCA
16	API to Update Customer Address in Oracle TCA R12	HZ_LOCATION_V2PUB.UPDATE_LOCATION	TCA
17	Assign Receipt method to Customer via API	hz_payment_method_pub.create_payment_method	TCA
18	Create Party of type Organization in Oracle TCA using API	hz_party_v2pub.create_organization	TCA
AP
19	API to apply hold on AP invoice in R12	AP_HOLDS_PKG.INSERT_SINGLE_HOLD	AP
20	API to cancel single AP invoice	AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE	AP
21	API to find AP INVOICE Status	AP_INVOICES_PKG.GET_APPROVAL_STATUS	AP
22	API to release hold on AP invoice in R12	AP_HOLDS_PKG.RELEASE_SINGLE_HOLD	AP
23	Credit Memo Creation via API	ar_credit_memo_api_pub.create_request	AP
24	API to create Supplier in R12	pos_vendor_pub_pkg.Create_Vendor	AP
25	API to create supplier site in R12	pos_vendor_pub_pkg.create_vendor_site	AP
26	API to create or update Supplier Hub UDA attr	pos_vendor_pub_pkg.process_user_attrs_data
PO
27	API for Cancelling the Purchase Order (PO) Document	PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT	PO
28	API to Update Purchase Order Document (PO)	PO_CHANGE_API1_S.UPDATE_PO	PO
AOL
29	API to load value in value set	FND_FLEX_VALUES_PKG.LOAD_ROW	AOL
30	API to submit concurrent program	fnd_request.submit_request	AOL