Submit PO Apprl Workflow BG Process

DECLARE
  ln_request_id   NUMBER;
  lv_user_name     VARCHAR2(100) := 'RANREDDY';
  ln_user_id       NUMBER;
  lv_resp_name     VARCHAR2(100) := 'SYSADMIN';
  lv_cp_short_name VARCHAR2(100) := 'FNDWFBG';
  lv_appl          VARCHAR2(100) := 'FND';
  ln_resp_id NUMBER;
  ln_appl_id NUMBER; 
BEGIN
  BEGIN 
    SELECT user_id
    INTO   ln_user_id
    FROM   FND_USER
    WHERE  user_name = lv_user_name;
  EXCEPTION
  WHEN OTHERS THEN
    NULL;
  END;
 
  BEGIN 
    SELECT responsibility_id,application_id
    INTO   ln_resp_id,ln_appl_id
    FROM   fnd_responsibility_tl
    WHERE  responsibility_name = 'System Administrator';
  EXCEPTION
  WHEN OTHERS THEN
    NULL;
  END; 

  fnd_global.apps_initialize(ln_user_id,ln_resp_id,ln_appl_id);

  ln_request_id := fnd_request.submit_request (application => lv_appl
                                               ,program     => lv_cp_short_name
                                               ,description => NULL
                                               ,start_time  => SYSDATE
                                               ,sub_request => FALSE
                                               ,argument1   => 'POAPPRV'
                                               ,argument2   => NULL
                                               ,argument3   => NULL
                                               ,argument4   => 'Y'
                                               ,argument5   => 'Y'
                                               ,argument6   => NULL
                                               ,argument7   => NULL);
           
   COMMIT;
 
   dbms_output.put_line('Request Id'|| ln_request_id); 
 
END;

VO Extension

VO Extension

1. Identify the Page and get the path of the Page in server

2. Download the entire folder from specified path

3. Now Create a structure of the Folder in JDeveloper

4. Create View Object by Extension

5. Go to Project Properties and Substitue

6. Compile the project to generate the JPX and Class Files

7. Migrate the Files to Server and Bounce the Server



VO Extension Migration

1. Copy the JPX file from My Projects and move the file to $JAVA_TOP

2. Copy the custom folder from MY Classes and move the folder to $JAVA_TOP

3. Run the JPX Import Command

4. Run the ADCGNJAR command at the $JAVA_TOP

5. Now Bounce the OA_CORE Server from weblogic server.

To check the Migration is Successful
Execute the below

begin                                       
 jdr_utils.listCustomizations('/oracle/apps/pos/isp/posummaryVO');     
end;

Common OAF Errors

1. “Each row in the Query Result Columns must be mapped to a unique Query Attribute in Mapped Entity columns”

Solution: Check out the Mapping Column and DB Column which should not be same. (case sensitive) Note. 1524622.1

Migrating Extensions

Command to Upload JPX

java oracle.jrad.tools.xml.importer.JPXImporter $JAVA_TOP/xyz/jpxFiles/Order.jpx -username apps -password apps-dbconnection "(description=(address_list=(address=(community=tcp.world)(protocol=tcp) (host=oracle.apps.com) (port=1521)))(connect_data=(service_name=DEV)))"

Payables Queries

--//--++++++++++++++Supplier Site Email Address++++++++++++++

SELECT  HCP.EMAIL_ADDRESS

FROM    HZ_PARTY_SITES HPS,

        HZ_CONTACT_POINTS HCP,

        AP_SUPPLIERS ASS

WHERE   1=1

AND     hps.party_site_id = p_party_site_id

AND     HCP.OWNER_TABLE_NAME='HZ_PARTY_SITES'

AND     HPS.PARTY_ID=ASS.PARTY_ID

AND     HCP.OWNER_TABLE_ID=HPS.PARTY_SITE_ID

AND     HCP.CONTACT_POINT_TYPE='EMAIL';

---//+++++++++++Supplier Details+++++++++++++++++++

select asa.vendor_name,

asa.vendor_id,

asa.segment1 vendor_number,

asaa.vendor_site_code,

hps.party_site_name,

asaa.vendor_site_id,

hou.name ou_name,

asaa.org_id,

asa.party_id,

asaa.party_site_id

from ap_supplier_sites_all asaa

,ap_suppliers asa

,hr_operating_units hou

,hz_party_sites hps

where asa.vendor_id = asaa.vendor_id

and asa.segment1 = NVL(:p_segment1,asa.segment1)

and asa.vendor_name = NVL(:p_vendor_name,asa.vendor_name)

and asaa.vendor_site_code = NVL(:p_ven_site_code,asaa.vendor_site_code)

and asaa.org_id = hou.organization_id

and hps.party_site_id = asaa.party_site_id;

--//--++++++++++++++SUpplier Contacts+++++++++++++++++++++++++

SELECT DISTINCT asu.party_id

, asu.segment1 Supp_Num

,asu.vendor_name

,hpc.party_name Contact_Name

,hpr.primary_phone_country_code cnt_cntry

,hpr.primary_phone_area_code cnt_area

,hpr.primary_phone_number cnt_phone

,assa.vendor_site_code

,assa.vendor_site_id

,asco.vendor_contact_id

FROM

 hz_relationships hr

,ap_suppliers asu

,ap_supplier_sites_all assa

,ap_supplier_contacts asco

,hz_org_contacts hoc

,hz_parties hpc

,hz_parties hpr

,hz_contact_points hpcp

WHERE hoc.party_relationship_id = hr.relationship_id

AND hr.subject_id = asu.party_id

AND hr.relationship_code = 'CONTACT'

AND hr.object_table_name = 'HZ_PARTIES'

AND asu.vendor_id = assa.vendor_id

AND hr.object_id = hpc.party_id

AND hr.party_id = hpr.party_id

AND asco.relationship_id  = hoc.party_relationship_id

AND assa.party_site_id = asco.org_party_site_id

AND hpr.party_type='PARTY_RELATIONSHIP'

AND hpr.party_id = hpcp.owner_table_id

AND hpcp.owner_table_name = 'HZ_PARTIES'

--//--------Supplier Payment Method at Supplier Level----------

SELECT

    supp.segment1 supplier_num,

    paym.payment_method_code

FROM

    ap_suppliers supp,

    iby_external_payees_all payee,

    iby_ext_party_pmt_mthds paym

WHERE

           supp.party_id = payee.payee_party_id

    AND    payee.ext_payee_id = paym.ext_pmt_party_id

    AND    supp.segment1 =:supplier_num

    AND    supplier_site_id IS NULL

    AND    paym.primary_flag = 'Y';

--//--------Supplier Payment Method at Site Level--------  

     

SELECT

    supp.segment1 supplier_num,

    sites.vendor_site_code,

    paym.payment_method_code

FROM

    ap_suppliers supp,

    ap_supplier_sites_all sites,

    iby_external_payees_all payee,

    iby_ext_party_pmt_mthds paym

WHERE

           supp.party_id = payee.payee_party_id

    AND    payee.ext_payee_id = paym.ext_pmt_party_id

    AND    supp.segment1 =:supplier_num

    AND    supp.vendor_id = sites.vendor_id

    AND    sites.vendor_site_id = payee.supplier_site_id

    AND    paym.primary_flag = 'Y';

     

--//-----------Supplier Remit Site-------------

SELECT asa.vendor_site_code, asa1.vendor_site_code

  FROM iby_ext_payee_relationships iep,

       ap_supplier_sites_all asa,

       ap_supplier_sites_all asa1

 WHERE     iep.supplier_site_id = asa.vendor_site_id

       AND asa1.vendor_site_id = iep.REMIT_SUPPLIER_SITE_ID

       AND asa.vendor_site_code IN ('10230908E');      

        

--//--+++++++++++++++++++Invoice Query +++++++++++++++

SELECT hou.name Operating_Unit_Name,

       aps.vendor_name Supplier_Name,

       assa.vendor_site_code Supplier_Site,

       --aia.invoice_id,

       --aia.vendor_id,

       aia.vendor_site_id ,

       aia.invoice_date,

       aia.invoice_num Invoice_Number,

       aia.invoice_amount,

       fct.name invoice_currency,

       aia.gl_date,

       at.name terms,

       aia.payment_status_flag payment,

       aia.invoice_currency_code,

       aia.payment_method_code,

  DECODE(APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS(aia.invoice_id, aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code),

          'NEVER APPROVED', 'Never Validated',

          'NEEDS REAPPROVAL', 'Needs Revalidation',

          'CANCELLED', 'Cancelled',

          'Validated') INVOICE_STATUS,

  decode(AP_INVOICES_PKG.GET_POSTING_STATUS(invoice_id),'S','Selected','P','Partial','N','Not Posted','Y','Posted') Accounted,

  AP_INVOICES_PKG.GET_APPROVAL_STATUS(

        aia.INVOICE_ID

       ,aia.INVOICE_AMOUNT

       ,aia.PAYMENT_STATUS_FLAG

       ,aia.INVOICE_TYPE_LOOKUP_CODE

       ) Approval_Status

  FROM ap_invoices_all aia,

       hr_operating_units hou,

       ap_suppliers aps,

       ap_supplier_sites_all assa,

       fnd_currencies_tl fct,

       ap_terms at

 WHERE     hou.organization_id = aia.org_id

       AND aps.vendor_id = assa.vendor_id

       AND aps.vendor_id = aia.vendor_id

       AND at.term_id = aia.terms_id

       AND assa.vendor_site_id = aia.vendor_site_id

       AND fct.currency_code = aia.invoice_currency_code

       AND fct.language = USERENV ('LANG')

       AND invoice_num = NVL (:p_invoice_num, invoice_num)

       AND payment_method_code = NVL (:p_payment_method, payment_method_code)

       AND hou.name = NVL (:p_ou_name, hou.name)

       AND aia.invoice_date BETWEEN NVL (:p_inv_from_date, invoice_date)

                                AND NVL (:p_inv_to_date, invoice_date)

       AND aia.gl_date BETWEEN NVL (:p_gl_from_date, gl_date)

                           AND NVL (:p_gl_to_date, gl_date)

       AND AP_INVOICES_PKG.GET_APPROVAL_STATUS (aia.INVOICE_ID,

                                                aia.INVOICE_AMOUNT,

                                                aia.PAYMENT_STATUS_FLAG,

                                                aia.INVOICE_TYPE_LOOKUP_CODE) =

              NVL (:p_approval_status,

                   AP_INVOICES_PKG.GET_APPROVAL_STATUS (

                      aia.INVOICE_ID,

                      aia.INVOICE_AMOUNT,

                      aia.PAYMENT_STATUS_FLAG,

                      aia.INVOICE_TYPE_LOOKUP_CODE))

       AND AP_INVOICES_PKG.GET_APPROVAL_STATUS (aia.invoice_id,

                                                aia.invoice_amount,

                                                aia.payment_status_flag,

                                                aia.invoice_type_lookup_code) =

              NVL (

                 DECODE (:p_invoice_status,

                         'Never Validated', 'NEVER APPROVED',

                         'Needs Revalidation', 'NEEDS REAPPROVAL',

                         'Cancelled', 'CANCELLED',

                         'Validated', 'APPROVED'),

                 AP_INVOICES_PKG.GET_APPROVAL_STATUS (

                    aia.invoice_id,

                    aia.invoice_amount,

                    aia.payment_status_flag,

                    aia.invoice_type_lookup_code))

       AND AP_INVOICES_PKG.GET_POSTING_STATUS (invoice_id) =

              NVL (

                 DECODE (:p_accounted,

                         'Selected', 'S',

                         'Partial', 'P',

                         'Not Posted', 'N',

                         'Posted', 'P',

                         'Yes', 'P',

                         'No', 'N'),

                 AP_INVOICES_PKG.GET_POSTING_STATUS (invoice_id))

                         

--//+++++++++++++++++Supplier Banks and Branches+++++++++++++++++++++++

SELECT *

    FROM (SELECT PaymentInstrumentUsesEO.INSTRUMENT_PAYMENT_USE_ID,

                 PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID,

                 PaymentInstrumentUsesEO.INSTRUMENT_TYPE,

                 PaymentInstrumentUsesEO.INSTRUMENT_ID,

                 PaymentInstrumentUsesEO.PAYMENT_FUNCTION,

                 PaymentInstrumentUsesEO.ORDER_OF_PREFERENCE,

                 PaymentInstrumentUsesEO.DEBIT_AUTH_FLAG,

                 PaymentInstrumentUsesEO.DEBIT_AUTH_METHOD,

                 PaymentInstrumentUsesEO.DEBIT_AUTH_REFERENCE,

                 PaymentInstrumentUsesEO.DEBIT_AUTH_BEGIN,

                 PaymentInstrumentUsesEO.DEBIT_AUTH_END,

                 PaymentInstrumentUsesEO.START_DATE,

                 PaymentInstrumentUsesEO.END_DATE,

                 ibyextbankaccts.bank_account_id,

                 ibyextbankaccts.bank_account_name,

                 ibyextbankaccts.bank_account_number,

                 ibyextbankaccts.currency_code,

                 ibyextbankaccts.bank_name,

                 ibyextbankaccts.bank_number,

                 ibyextbankaccts.bank_branch_name,

                 ibyextbankaccts.bank_account_type,

                 ibyextbankaccts.branch_number,

                 ibyextbankaccts.iban_number,

                 ibyextbankaccts.eft_swift_code,

                 'N' detailView,

                 PaymentInstrumentUsesEO.PAYMENT_FLOW,

                 supplier.vendor_name supplier_name,

                 supplier.vendor_name_alt alt_supplier_name,

                 supplier.segment1 supplier_number,

                 NULL vendor_site_id, -- CUSTOM

                 NULL site_name,

                 NULL alt_site_name,

                 NULL business_classification, -- CUSTOM

                 address.party_site_name,

                 NULL organization_name,

                 DECODE(Payee.supplier_site_id, NULL, DECODE(Payee.org_id, NULL, DECODE(Payee.party_site_id, NULL, 'Supplier', 'Address'), 'Address-Operating unit'), 'Site') assignment_level,

                 ibyextbankaccts.ext_bank_account_id ext_bank_account_id,

                 ibyextbankaccts.bank_party_id bank_party_id,

                 ibyextbankaccts.branch_party_id branch_party_id,

                 ibyextbankaccts.FOREIGN_PAYMENT_USE_FLAG FOREIGN_PAYMENT_USE_FLAG,

                 DECODE(ibyextbankaccts.PRIMARY_ACCT_OWNER_PARTY_ID, Payee.payee_party_id, 'Yes', 'No') primary_flag,

                 supplier.vendor_id vendor_id,

                 Payee.payee_party_id

            FROM IBY_PMT_INSTR_USES_ALL PaymentInstrumentUsesEO,

                 IBY_EXTERNAL_BANK_ACCOUNTS_V ibyextbankaccts,

                 IBY_EXTERNAL_PAYEES_ALL Payee,

                 ap_suppliers supplier,

                 hz_party_sites address

           WHERE PaymentInstrumentUsesEO.instrument_id = ibyextbankaccts.bank_account_id

             AND PaymentInstrumentUsesEO.instrument_type = 'BANKACCOUNT'

             AND PaymentInstrumentUsesEO.payment_function = 'PAYABLES_DISB'

             AND PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID = Payee.ext_payee_id

             AND Payee.payee_party_id = supplier.party_id

             AND Payee.party_site_id = address.party_site_id(+)

             AND Payee.org_id IS NULL

             AND Payee.org_type IS NULL

          UNION

          SELECT PaymentInstrumentUsesEO.INSTRUMENT_PAYMENT_USE_ID,

                 PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID,

                 PaymentInstrumentUsesEO.INSTRUMENT_TYPE,

                 PaymentInstrumentUsesEO.INSTRUMENT_ID,

                 PaymentInstrumentUsesEO.PAYMENT_FUNCTION,

                 PaymentInstrumentUsesEO.ORDER_OF_PREFERENCE,

                 PaymentInstrumentUsesEO.DEBIT_AUTH_FLAG,

                 PaymentInstrumentUsesEO.DEBIT_AUTH_METHOD,

                 PaymentInstrumentUsesEO.DEBIT_AUTH_REFERENCE,

                 PaymentInstrumentUsesEO.DEBIT_AUTH_BEGIN,

                 PaymentInstrumentUsesEO.DEBIT_AUTH_END,

                 PaymentInstrumentUsesEO.START_DATE,

                 PaymentInstrumentUsesEO.END_DATE,

                 ibyextbankaccts.bank_account_id,

                 ibyextbankaccts.bank_account_name,

                 ibyextbankaccts.bank_account_number,

                 ibyextbankaccts.currency_code,

                 ibyextbankaccts.bank_name,

                 ibyextbankaccts.bank_number,

                 ibyextbankaccts.bank_branch_name,

                 ibyextbankaccts.bank_account_type,

                 ibyextbankaccts.branch_number,

                 ibyextbankaccts.iban_number,

                 ibyextbankaccts.eft_swift_code,

                 'N' detailView,

                 PaymentInstrumentUsesEO.PAYMENT_FLOW,

                 supplier.vendor_name supplier_name,

                 supplier.vendor_name_alt alt_supplier_name,

                 supplier.segment1 supplier_number,

                 site.vendor_site_id, -- CUSTOM

                 site.vendor_site_code site_name,

                 site.vendor_site_code_alt alt_site_name,

                 site.attribute_category business_classification, -- CUSTOM

                 address.party_site_name,

                 ou.NAME organization_name,

                 DECODE(Payee.supplier_site_id, NULL, DECODE(Payee.org_id, NULL, DECODE(Payee.party_site_id, NULL, 'Supplier', 'Address'), 'Address-Operating unit'), 'Site') assignment_level,

                 ibyextbankaccts.ext_bank_account_id ext_bank_account_id,

                 ibyextbankaccts.bank_party_id bank_party_id,

                 ibyextbankaccts.branch_party_id branch_party_id,

                 ibyextbankaccts.FOREIGN_PAYMENT_USE_FLAG FOREIGN_PAYMENT_USE_FLAG,

                 DECODE(ibyextbankaccts.PRIMARY_ACCT_OWNER_PARTY_ID, Payee.payee_party_id, 'Yes', 'No') primary_flag,

                 supplier.vendor_id vendor_id,

                 Payee.payee_party_id

            FROM iby_pmt_instr_uses_all PaymentInstrumentUsesEO,

                 iby_external_bank_accounts_v ibyextbankaccts,

                 iby_external_payees_all Payee,

                 ap_suppliers supplier,

                 ap_supplier_sites_all site,

                 hz_party_sites address,

                 hr_operating_units ou

           WHERE PaymentInstrumentUsesEO.instrument_id = ibyextbankaccts.bank_account_id

             AND PaymentInstrumentUsesEO.instrument_type = 'BANKACCOUNT'

             AND PaymentInstrumentUsesEO.payment_function = 'PAYABLES_DISB'

             AND PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID = Payee.ext_payee_id

             AND Payee.payee_party_id = supplier.party_id

             AND Payee.supplier_site_id = site.vendor_site_id(+)

             AND Payee.party_site_id = address.party_site_id(+)

             AND Payee.org_id = ou.organization_id(+)

             AND Payee.org_id IS NOT NULL

             AND Payee.org_type IS NOT NULL

             /*AND (Payee.org_id,

                  Payee.org_type) IN (SELECT uo.organization_id,

                                             uo.organization_type

                                        FROM ce_security_profiles_v uo)*/

             ) QRSLT

   WHERE (--VENDOR_SITE_ID = 4944131--1

          --EXT_BANK_ACCOUNT_ID = 183110

          SUPPLIER_NUMBER LIKE '661060'--2

          --AND SUPPLIER_NAME LIKE 'Nadia Khaleghi (REFUND ONLY)%'

          --VENDOR_SITE_ID = 1288119

          --AND (END_DATE IS NULL OR END_DATE > SYSDATE)

      )

--ORDER BY BANK_ACCOUNT_NUMBER

-//+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++