Supplier and Site bank detail query in R12

/* Formatted on 2/15/2018 2:46:46 PM (QP5 v5.114.809.3010) */
SELECT   /*Supplier Information*/
         aps.segment1 oracle_supplier_number,
         aps.vendor_id,
         aps.vendor_name supplier_name,
         aps.party_id supplier_party_id,
         iepa.remit_advice_fax remit_advice_fax,
         iepa.remit_advice_email remit_advice_email/* Supplier Site Information */
         ,
         assa.vendor_site_id,
         assa.party_site_id supplier_party_site_id,
         assa.vendor_site_code vendor_site_code,
         assa.pay_site_flag pay_site_flag,
         assa.purchasing_site_flag purchasing_site_flag,
         assa.rfq_only_site_flag rfq_only_site_flag/* Bank Information*/
         ,
         ieba.ext_bank_account_id,
         hp.party_name Bank_party_name,
         ieba.bank_account_num bank_account_num,
         ieba.bank_account_name bank_account_name,
         ieba.country_code bank_acct_country_code,
         ieba.currency_code bank_acct_currency_code/* Bank Address */
         ,
         hp.address1 bank_address_line1,
         hp.address2 bank_address_line2,
         hp.address3 bank_address_line3,
         hp.city bank_address_city,
         hp.state bank_address_state,
         hp.postal_code bank_address_zip,
         hp.country bank_address_country/* Bank Branch Address */
         ,
         hp1.address1 branch_address_line1,
         hp1.address2 branch_address_line2,
         hp1.address3 branch_address_line3,
         hp1.city branch_address_city,
         hp1.state branch_address_state,
         hp1.postal_code branch_address_zip,
         hp1.country branch_address_country
  FROM   ap_supplier_sites_all assa,
         hz_parties hp,
         iby_ext_bank_accounts ieba,
         iby_external_payees_all iepa,
         iby_pmt_instr_uses_all ipiua,
         ap_suppliers aps,
         hz_parties hp1
 WHERE       assa.vendor_site_id = iepa.supplier_site_id
         AND hp.party_id = ieba.bank_id
         AND ipiua.instrument_id = ieba.ext_bank_account_id
         AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
         AND assa.vendor_id = aps.vendor_id
         AND ieba.branch_id = hp1.party_id
         AND ipiua.instrument_type = 'BANKACCOUNT'
         AND aps.vendor_name LIKE '3M%'
         AND assa.vendor_site_id = 6916
         AND ipiua.payment_flow = 'DISBURSEMENTS'
         AND ipiua.order_of_preference = 1;

Supplier / Supplier Site Details:-

SELECT   assa.vendor_site_id
                  FROM   ap_supplier_sites_all assa,
                         hz_parties hp,
                         iby_ext_bank_accounts ieba,
                         iby_external_payees_all iepa,
                         iby_pmt_instr_uses_all ipiua,
                         ap_suppliers aps,
                         hz_parties hp1
                 WHERE       assa.vendor_site_id = nvl(iepa.supplier_site_id,assa.vendor_site_id)
                         AND hp.party_id = ieba.bank_id
                         AND ipiua.instrument_id = ieba.ext_bank_account_id
                         AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
                         AND assa.vendor_id = aps.vendor_id
                         AND ieba.branch_id = hp1.party_id
                         AND ipiua.instrument_type = ''BANKACCOUNT''
                         --and assa.vendor_site_id = 6916
                         AND ieba.ext_bank_account_id = : Bank Id
                         AND assa.org_id = fnd_profile.VALUE (''org_id'')


        ********************  ##################  ********************

SELECT  aps.vendor_name "VERDOR NAME",

        apss.vendor_site_code "VENDOR SITE CODE",

        ieb.bank_name "BANK NAME",

        iebb.bank_branch_name "BANK BRANCH NAME",

        iebb.branch_number "BRANCH NUMBER",

        ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER",

        ieba.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME"

FROM    ap.ap_suppliers aps,

        ap.ap_supplier_sites_all apss,

        apps.iby_ext_bank_accounts ieba,

        apps.iby_account_owners iao,

        apps.iby_ext_banks_v ieb,

        apps.iby_ext_bank_branches_v iebb

WHERE   aps.vendor_id = apss.vendor_id

        and iao.account_owner_party_id = aps.party_id

        and ieba.ext_bank_account_id = iao.ext_bank_account_id

        and ieb.bank_party_id = iebb.bank_party_id

        and ieba.branch_id = iebb.branch_party_id

        and ieba.bank_id = ieb.bank_party_id;

How to Set R12 Supplier Page in Read Only Mode

How to Set R12 Supplier Page in Read Only Mode?

Open System Administrator resp. Navigate to Security->Responsibility->Define

  --> Query responsibility name(Ex: Payables, HG Operations) you want to use that will have the Supplier page as Read Only.
--> Under Menu Exclusions select Type as 'Function' and Name as 'Supplier Full Access : Buyer View'
  --> Save changes.

Navigation Path: System Administrator->Security->Responsibility->Define 

 

  --> Now Now open Suppliers page and check changes open Suppliers page and check changes.
Navigation Path: Payables Resp(Payables, HGOperations)->Suppliers->EntrySupplier page looks like this in read only mode

Oracle Payment Processing Request (PPR) in AP – R12

PAYMENT PROCESSING REQUEST FUNCTIONALITY-
In 11i we used Payment batches to pay for multiple invoices same time. In R12, PPR is the replacement of Payment batches. R12 PPR process enables payment Administrator to select multiple invoices for payment by selection criteria and he can pause the invoice selection and payment build process. During the invoice selection review, payment manager can review the invoice selected; if the invoices were validated or approved and hence did not get included in the payment process request. He can add or remove the invoices in the Payment process and also can check the cash requirements for the full payment. Payment manager can also dismiss the individual documents or payments if necessary, and restart the payment build process.
Steps in Pay run Process-
Managing a Pay run involves 3 main processes

• Selection of the invoices for payment
• Grouping the invoices into payments
• Building the payment instruction files to either print checks or send instructions to bank.

There are four steps in the processing of PPR:-

• Document selection – Handled by Payables(AP)
• Build Payments – Handled by Payments(IBY)
• Format Payments – Handled by Payments(IBY)
• Confirm Payments – Handled by Payables(AP)

Submitting a Single Payment Process Request
Mandatory fields – Payment Process Request name, pay through date, Payment date, and Exchange rate type.
Under Processing tab, options are available to stop the process after document selection/payment and also how to create the payment instructions:

1. Maximize Credits.
2. Stop Process for review after scheduled payment selection.
3. Calculate payment withholding and interest during scheduled payment selection.
4. Stop process for review after creation of proposed payments.

Click on submit to submit the Payment process request.
Document Selection – Payables
This process calls AP_AUTOSELECT_PKG.
When a payment process request is submitted, a record is inserted in AP_INV_SELECTION_CRITERIA_ALL with a checkrun_name i.e payment process request name. Invoices are then selected based on the due date, discount date, paygroup, and other criteria provided by the user while submitting the PPR.
The AP_SELECTED_INVOICES_ALL table is populated with the selected invoices and AP_UNSELECTED_INVOICES_ALL table by the unselected invoices.
Note: After selecting the documents, the invoices are locked to prevent other check runs from selecting the same invoices.
If the PPR has been setup to ‘Stop Process for Review after Scheduled Payment Selection’, the process stops for user review.
Then the status of the PPR is set to Invoices Pending Review.
If the ‘Stop Process for Review after Scheduled Payment Selection’ was not enabled, at the end of invoice selection, build program is submitted automatically.
If no invoices met the selection criteria and no payment schedules selected for payment, the PPR is cancelled automatically and the status of the PPR is set to “Cancelled – No Invoices Selected”. Then void all invoices
For others, the actions available are
a) Terminate the PPR
b) Modify / proceed to submit the PPR and start the build process.
Build Payments – Payments
Call IBY_DISBURSE_SUBMIT_PUB_PKG
Build payment creates records in IBY_PAY_SERVICE_REQUESTS with call_app_pay_service_req_code = checkrun_name.
A payment process request is a group of documents payable that a source product submits to Oracle Payments for payment service processing. This table contains the parameters like Calling application identifier, Internal bank account, Allow zero payments flag, etc. selected in the Payment Process Request.

PAYMENT_SERVICE_REQUEST_ID	NUMBER	System generated primary key
CALLING_APP_ID	NUMBER	Source product Identifier
CALL_APP_PAY_SERVICE_REQ_CODE	VARCHAR2	Source product’s payment process request Identifier. Since the source product’s Identifiers may be alphanumeric, even numeric document Identifiers are stored as VARCHAR2.
PAYMENT_SERVICE_REQUEST_STATUS	VARCHAR2	Payment process request status. Values from the lookup IBY_REQUEST_STATUSES include PAYMENTS_CREATED.
PROCESS_TYPE	VARCHAR2	Specifies the process by which documents payable are built into payments and payments into payment instructions. Values from the lookup IBY_PROCESS_TYPES include STANDARD, IMMEDIATE, and MANUAL.
ALLOW_ZERO_PAYMENTS_FLAG	VARCHAR2	Y or N flag that indicates whether zero payments are allowed for this payment request. If set to N, any zero value payments created for this payment request is failed.
INTERNAL_BANK_ACCOUNT_ID	NUMBER	Internal bank account identifier
MAXIMUM_PAYMENT_AMOUNT	NUMBER	Maximum payment amount used to override default maximum payment amount
MINIMUM_PAYMENT_AMOUNT	NUMBER	Minimum payment amount used to override default minimum payment amount

Note: The displayed status of the PPR is generated by ibyvutlb.pls
Following are the possible values of PAYMENT_SERVICE_REQUEST_STATUS column-

• DOCUMENTS_VALIDATED
• INFORMATION_REQUIRED
• INSERTED
• PAYMENTS_CREATED
• PENDING_REVIEW
• TERMINATED
• VALIDATION_FAILED
• COMPLETED

 In 11i AP_SELECTED_INVOICE_CHECKS_ALL table is populated by the Build Payment process.
The Build Program also populates IBY_DOCS_PAYABLE_ALL table
IBY_DOCS_PAYABLE_ALL– This table contains the documents payable which are updated by system while processing “Build Payments” program. A document payable is a supplier invoice or similar document that needs to be paid.  In addition, this table contains whatever document information is necessary for payment processing.
This table contains transaction details, document details, payer, payee, etc.”

Name	Datatype	Comments
PAY_PROC_TRXN_TYPE_CODE	VARCHAR2	Type of payment processing transaction or document
CALLING_APP_ID	NUMBER	Calling product Identifier
CALLING_APP_DOC_REF_NUMBER	VARCHAR2	Reference number entered by user of the source product. Need not be unique
DOCUMENT_PAYABLE_ID	NUMBER	Oracle Payments’ unique internal document payable Identifier
PAYMENT_FUNCTION	VARCHAR2	Function or purpose of the payment. Values from the lookup IBY_PAYMENT_FUNCTIONS include SUPPLIER_PAYMENT, CUSTOMER_REFUNDS, and others.
PAYMENT_DATE	DATE	Payment date
DOCUMENT_DATE	DATE	Date of document
DOCUMENT_TYPE	VARCHAR2	Type of document payable. Values from the IBY_DOCUMENT_TYPES lookup include INVOICE.
DOCUMENT_STATUS	VARCHAR2	Document status. Values from the lookup IBY_DOCS_PAYABLE_STATUSES include PAYMENT CREATED.
DOCUMENT_CURRENCY_CODE	VARCHAR2	Document currency code
DOCUMENT_AMOUNT	NUMBER	Total amount in document currency
PAYMENT_CURRENCY_CODE	VARCHAR2	Payment currency code
PAYMENT_AMOUNT	NUMBER	Amount to be paid in payment currency
PAYMENT_SERVICE_REQUEST_ID	NUMBER	Identifier of the payment process request in which this document was submitted
PAYMENT_METHOD_CODE	VARCHAR2	Payment method Identifier
EXCLUSIVE_PAYMENT_FLAG	VARCHAR2	Y or N flag indicating whether this document payable should not be grouped with any other documents payable.
CALLING_APP_DOC_UNIQUE_REF1	VARCHAR2	Source product’s first unique document payable Identifier
CALLING_APP_DOC_UNIQUE_REF2	VARCHAR2	Source product’s second unique document payable Identifier (Invoice_id)
CALLING_APP_DOC_UNIQUE_REF3	VARCHAR2	Source product’s third unique document payable Identifier(Payment_number)
CALLING_APP_DOC_UNIQUE_REF4	VARCHAR2	Source product’s fourth unique document payable Identifier
CALLING_APP_DOC_UNIQUE_REF5	VARCHAR2	Source product’s fifth unique document payable Identifier

A.  Internal Bank Account/Payment Process Profile Assignment:
Call IBY_ASSIGN_PUB
If the payment process request has the internal bank account and payment profile assigned to it, the same is assigned to all the documents in the PPR.If a default internal bank account and PPP were not provided when submitting the PPR, Oracle Payments attempts to default the values. If it cannot find a default value for all the documents, the PPR is set to INFORMATION REQUIRED status. The display status of the PPR is “Information Required – Pending Action”
User should complete the missing information and Run Payment Process to continue.
B.    Document Validation
Call IBY_VALIDATIONSETS_PUB

During this step, Oracle Payments validates all the documents using Payment Method based validations and then payment format based validations.b.1 – If all the documents pass validation, all the documents are set to a status of VALIDATED and the request status is set to ‘Documents Validated’.b.2 – If there are any validation failures, Oracle Payments uses the system option used while submitting the PPR to determine the next action.The DOCUMENT_REJECTION_LEVEL_CODE of the PPR can have the following values which determine how the document processing will continue when there is a validation failureb.2.1 – REQUEST
The status of the payment process request is updated to ‘Failed Document Validation’. Oracle Payments calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request.
b.2.2 – DOCUMENT
Oracle Payments rejects all documents that failed validation. Oracle Payments then calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request. The rest of the documents are set to VALIDATED status and the ppr is set to ‘Documents Validated’ status.
b.2.3 – PAYEE
Oracle Payments rejects all documents for the supplier that had one or more documents that failed validation. Oracle Payments calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request. The rest of the documents are set to VALIDATED status and the ppr is set to ‘Documents Validated’ status.
c.     Create Payments
Call IBY_PAYGROUP_PUBThe validated documents are then grouped into proposed payments based on the grouping rules, both users defined and hard coded.
Example: If exclusive_payment_flag = Y on a document, it is paid on a separate payment.
It then numbers the payments (internal identifier not the check numbering) and validates the created payments.Records are inserted into IBY_PAYMENTS_ALL that holds the payment information for the selected documents.The build program then updates the IBY_DOCS_PAYABLE_ALL table with the payment_id and formatting_payment_id values that corresponding to the payment that pays the document.

IBY_PAYMENTS_ALL
This table contains all the payments created by system while processing “Build Payments”. A Payment can be single check or an electronic fund transfer between first party payer and third party payee. A row in this table corresponds to one or more documents payable. Payments are built by grouping documents payable according to Oracle Payments’ grouping rules.
This table also stores information of payments at grouping level. The groups can be Single, Mixed and grouped as defined in Payment Process Profile for the purpose of SEPA.
The payment details are displayed on the Payments tab of the Funds Disbursement Process Home page.

Name	Datatype	Comments
PAYMENT_ID	NUMBER	Unique internal Identifier for this record. Generated using a database sequence.
PAYMENT_METHOD_CODE	VARCHAR2	Payment method used for making the payments.
PAYMENT_SERVICE_REQUEST_ID	NUMBER	Payment service request Id and it is the foreign key to the table iby_pay_service_requests.
PROCESS_TYPE	VARCHAR2	Specifies the process by which the payment is built into a payment instruction. Values, from the lookup IBY_PROCESS_TYPES, include STANDARD, IMMEDIATE, and MANUAL.
PAYMENT_STATUS	VARCHAR2	The status of the Payment. Values are derived from the lookup IBY_PAYMENT_STATUSES. The possible values are CREATED, FORMATTED, TRANSMITTED, VOID_BY_OVERFLOW, REJECTED, FORMATTED, VOID, etc.
PAYMENTS_COMPLETE_FLAG	VARCHAR2	Y or N flag that indicates if the payment is complete
PAYMENT_FUNCTION	VARCHAR2	Function or purpose of the payment. Values from the lookup IBY_PAYMENT_FUNCTIONS include SUPPLIER_PAYMENT, CUSTOMER_REFUNDS, and others.
PAYMENT_AMOUNT	NUMBER	Amount of the payment
PAYMENT_CURRENCY_CODE	VARCHAR2	Currency of the payment
BILL_PAYABLE_FLAG	VARCHAR2	Y or N flag indicating whether a payment is a bill payable, that is, a future dated payment
EXCLUSIVE_PAYMENT_FLAG	VARCHAR2	Y or N flag indicating whether this payment is made up of a single document payable that was meant to be paid alone
SEPARATE_REMIT_ADVICE_REQ_FLAG	VARCHAR2	Y or N flag indicating whether a separate remittance advice needs to be generated for a payment.
INTERNAL_BANK_ACCOUNT_ID	NUMBER	Internal bank account id used for making the payment.
ORG_ID	NUMBER	Unique internal identifier of the Operating Unit. Validated against HR_OPERATING_UNITS.ORGANIZATION_ID.
ORG_TYPE	VARCHAR2	Organization type. Values, from the lookup IBY_ORGANIZATION_TYPES Include Operating Unit, Business Group, and Legal Entity
LEGAL_ENTITY_ID	NUMBER	Legal entity identifier

The PAYMENT_REJECTION_LEVEL_CODE can have the following values which determine how the payment processing will continue when there is a validation failure
Request – Entire PPR is rejected. Oracle Payments raises a business event that calls AP to release the documents. The status of the payment process request and proposed payments is updated to ‘REJECTED’.
Payment – Payments that failed validation are rejected and AP releases the documents that belong to the payment that failed validation. The other payments are accepted. The accepted payments get a status of ‘CREATED’.
None – Payments that failed Validation are set to ‘Failed Validation’ and allows for user intervention. Status of the PPR is set to ‘PENDING REVIEW’
If in the PPR setup, ‘Stop Process for Review After Creation of Proposed Payments’ is enabled, the PPR status is set to ‘Pending Proposed Payment Review’. This status prevents further processing until user takes action. If this option to stop for review is not enabled, the status of the PPR is set to ‘Payments Created’. In this status, payment instruction can be created for the PPR.

Format Payments – Payments

Call IBY_PAYINTSR_PUB, IBY_CHECKNUMBER_PUB

When a PPR is submitted, there are two options
The CREATE_PMT_INSTRUCTIONS_FLAG can be a Y or N
Y – Payment Instruction will be automatically created after payments are created.
N – Application waits for standard request submission for Payment Instruction.
The table IBY_PAYMENT_INSTRUCTIONS_ALL stores the payment instruction information.
If the PPR is setup to automatically submit instruction, the payment_service_request_id will be populated in iby_payment_instructions_all because the instruction will be specific to the PPR In this case, the instruction can be linked to the PPR using PAYMENT_SERVICE_REQUEST_ID
If the PPR processing is setup for the user to submit the instruction as a standard request, then when the instruction is submitted, then the instruction is linked to the PPR through the payments selected by the instruction.
The link in this case will be through iby_payments_all.payment_instruction_id
Key Columns of IBY_PAYMENT_INSTRUCTIONS_ALL table
Payment_instruction_id
Payment_profile_id
Payment_instruction_status
Payments_complete_code
Payment_count
Print_instruction_immed_flag
Transmit_instr_immed_flag
Internal_bank_account_id
Payment_document_id
Payment_date
Payment_reason_code
Payment_currency_code
Format:
The following processing occurs during the format step.
a) Number the payments – Check Numbering
b) Create XML Extract message
c) Pass the extract to XML publisher
d) Oracle XML Publisher (BI publisher) applies the format template
e) BI publisher formats and stores the output
f) Oracle Payments then updates the status of the Payment Instruction and the Payments. If successful, the status of Payments and Instruction is ‘Formatted’.
Print Checks:
a) Users can load stationery into the printer and print checks at this stage.
b) Determine if the checks printed ok. If not reprint
Confirm Payments – Payables
Call AP_PMT_CALLOUT_PKG

Record Print Status of the checks to confirm the payments. Oracle Payments calls ap_pmt_callout_pkg.payment_completed to confirm the payments.
This does the following:
a) Assigns sequence/values – Document sequencing.
b) Creates data in AP_CHECKS_ALL with appropriate data from IBY tables.
Checkrun_name = ppr name and checkrun_id = checkrun_id from IBY table.
c) Data inserted into AP_INVOICE_PAYMENTS_ALL for the corresponding checks.
d) AP_PAYMENT_SCHEDULES_ALL for the invoices are updated to indicate the payment details and status.
e) The documents paid in this PPR are released by setting the checkrun_id on the payment schedules to null.
f) AP_INVOICES_ALL is updated to show payment status
g) Data is deleted from the AP_SELECTED_INVOICES_ALL
h) Data is deleted from AP_UNSELECTED_INVOICES_ALL

Supplier's Payment Methods query in oracle apps

                                                                                             Payment_method_lookup_code column of AP_SUPPLIERS and AP_SUPPLIER_SITES_ALL is obsolete in Rel12. These column values reflect values prior to upgrade. Any updates and new addition go to IBY_EXT_PARTY_PMT_MTHDS table.

/* Formatted on 9/1/2015 11:11:36 AM (QP5 v5.240.12305.39446) */
SELECT /*+  ORDERED
            USE_NL (spu ss epa ppm)
       */
       sup.creation_date,
       sup.vendor_name,
       sup.segment1 vendor_number,
       sup.party_id,
       epa.payee_party_id,
       epa.supplier_site_id,
       ss.vendor_site_code,
       ppm.payment_method_code,
       ppm.primary_flag
  FROM ap_suppliers sup,
       ap_supplier_sites_all ss,
       iby.iby_external_payees_all epa,
       iby_ext_party_pmt_mthds ppm
 WHERE     sup.vendor_id = ss.vendor_id
       AND ss.vendor_site_id = epa.supplier_site_id       --epa.payee_party_id
       AND epa.ext_payee_id = ppm.ext_pmt_party_id
       --    Supplier created by last 100 days
       AND sup.creation_date BETWEEN SYSDATE - 100 AND SYSDATE - 1
       -- Modify above condition or add new conditions to get desired supplier's info
       AND ss.inactive_date IS NULL
       AND sup.end_date_active IS NULL
       AND ppm.inactive_date IS NULL
       AND epa.inactive_date IS NULL

Supplier With Bank Details and Payment Details in oracle apps

SQL  Query
-----------------------------------
select asp.party_id,
       asp.vendor_id,
       ass.pay_site_flag,
       ass.vendor_site_id,
       hrl_ship.location_code,
       hp_supp.party_id,
       asp.VENDOR_NAME Supplier_name,
       asp.VENDOR_TYPE_LOOKUP_CODE type,
       asp.ONE_TIME_FLAG,
       asp.individual_1099,
       DECODE (
            UPPER (asp.vendor_type_lookup_code),
            'EMPLOYEE',
            papf.national_identifier,
            DECODE (asp.organization_type_lookup_code,
                    'INDIVIDUAL', asp.individual_1099,
                    'FOREIGN INDIVIDUAL', asp.individual_1099,
                    hp_supp.jgzz_fiscal_code)
         ) Taxpayer_ID,
         asp.VAT_REGISTRATION_NUM ,
         hp_supp.DUNS_Number_c  ,
         asp.start_date_active,
         asp.end_date_active,
         asp.hold_flag,
         asp.last_update_date,
         ass.address_line1,
         ass.address_line2,
         ass.address_line3 ,
         ass.city,
         ass.state,
         ass.zip
         , ass.country,
         ass.purchasing_site_flag ,
         ass.RFQ_ONLY_SITE_FLAG ,
         ass.PAY_SITE_FLAG
         , ass.Vendor_Site_Code
         , ass.ATTRIBUTE1 Supplier_Qualification_Status
         , ass.ATTRIBUTE15  Invoice_Approver --, ass.SHIP_TO_LOCATION_CODE
         , hrl_ship.location_code ship_to_location_code
         , hrl_bill.location_code bill_to_location_code
         , ass.inactive_date
         , ass.last_update_date site_last_update_date
         , ass.terms_id
         , att.name terms_name
         , ass.invoice_currency_code
         , ass.payment_currency_code
         , ass.hold_all_payments_flag
         , ass.hold_future_payments_flag
         , ass.vat_registration_num
         , hou.name OPERATING_UNIT_NAME
         , ass.email_address
         , ass.primary_pay_site_flag
         , ass.DUNS_NUMBER
         , ass.PAY_GROUP_LOOKUP_CODE
         , person.person_first_name first_name
         , person.person_last_name  last_name
         , pty_rel.email_address
         , pty_rel.primary_phone_number
         , apsc.creation_date contact_creation_date
         , apsc.last_update_date contact_last_update_date
          --hrl_ship.*
-- person.person_first_name ,
-- person.person_last_name ,pty_rel.primary_phone_number ,
-- pty_rel.email_address
-- , apsc.email_address , ass.*
,ass.duns_number site_duns_number
from ap_suppliers asp
   , ap_supplier_sites_all ass
   , ap_supplier_contacts apsc
   , hz_parties person
   , hz_parties pty_rel
   , hz_parties hp_supp
   , per_all_people_f papf
   , hr_locations hrl_ship
   , hr_locations hrl_bill  
   , ap_terms_tl att
   , hr_operating_units hou
   --, AND pv.party_id = hp.party_id
where 1 = 1
--and asp.vendor_id = 46006 -- 5001 -- 
and ass.vendor_id = asp.vendor_id
and asp.party_id = hp_supp.party_id
AND apsc.org_party_site_id(+) = ass.party_site_id
--and ass.org_id = 222 -- 123
AND apsc.per_party_id = person.party_id(+)
AND apsc.rel_party_id = pty_rel.party_id(+)
AND asp.employee_id = papf.person_id(+)
and hrl_ship.location_id(+) = ass.ship_to_location_id
and hrl_bill.location_id(+) = ass.bill_to_location_id
and att.term_id(+) = ass.terms_id
and ass.org_id = hou.organization_id
and TRUNC(asp.creation_date) between :P_FROM_CREATION_DATE AND :P_TO_CREATION_DATE
and TRUNC(asp.last_update_date) between NVL(:P_FROM_UPDATE_DATE,TRUNC(asp.last_update_date)) AND NVL(:P_TO_UPDATE_DATE,TRUNC(asp.last_update_date))
and asp.vendor_name = nvl(:p_supplier_name,asp.vendor_name)

Formula Column1(CF_BANK_DATA)
-----------------------------
function CF_BANK_DATAFormula return Number is
begin
 
 
     SELECT   eb.bank_name                 ,
              eb.bank_number ,
                  ebb.bank_branch_name         ,
              ebb.branch_number            ,
              eba.bank_account_num         ,
              eba.bank_account_name
       INTO :CP_BANK_NAME,
            :CP_BANK_NUMBER,
            :CP_BANK_BRANCH_NAME,
            :CP_BRANCH_NUMBER,
            :CP_BANK_ACCOUNT_NUM  ,
            :CP_bank_account_name
   FROM ap.ap_suppliers av         ,
        ap.ap_supplier_sites_all assa     ,
        apps.iby_ext_bank_accounts eba  ,
        apps.iby_account_owners ao      ,
        apps.iby_ext_banks_v eb         ,
        apps.iby_ext_bank_branches_v ebb,
            hr_operating_units hou
  WHERE av.vendor_id           = assa.vendor_id
AND ao.account_owner_party_id = av.party_id
AND eba.ext_bank_account_id   = ao.ext_bank_account_id
AND eb.bank_party_id          = ebb.bank_party_id
AND eba.branch_id             = ebb.branch_party_id
AND eba.bank_id               = eb.bank_party_id
AND assa.org_id                 =hou.organization_id
AND av.vendor_id               = :vendor_id
AND assa.vendor_site_id    = :vendor_site_id;

    RETURN(NULL);
 
EXCEPTION
    WHEN OTHERS THEN
    SRW.MESSAGE(100,'No bank records for vendor site id:- '||:vendor_site_id);
    RETURN(NULL);
 
end;

Formula Column2(CF_PAYMENT_METHOD_CODE)
--------------------------------
function CF_PAYMENT_METHOD_CODEFormula return Char is
lv_payment_method_code   VARCHAR2(30);
begin
 
 
  SELECT ieppm.payment_method_code
   INTO lv_payment_method_code
   FROM IBY_EXT_PARTY_PMT_MTHDS IEPPM,
        IBY_EXTERNAL_PAYEES_ALL IEPA
  WHERE ieppm.ext_pmt_party_id = iepa.ext_payee_id
    AND iepa.payee_party_id = :party_id
    AND iepa.supplier_site_id = :vendor_site_id;
 

    RETURN(lv_payment_method_code);
 
EXCEPTION
    WHEN OTHERS THEN
    SRW.MESSAGE(101,'No payment_method_code for vendor site id:- '||:vendor_site_id);
    RETURN(NULL);
 
end;

formula Column3(CF_FORMAT_CODE)
-----------------------
function CF_FORMAT_CODEFormula return Char is
lv_format_code   VARCHAR2(30);
begin
 
 
  SELECT ifv.format_code
   INTO lv_format_code
   FROM IBY_EXT_PARTY_PMT_MTHDS IEPPM,
        IBY_EXTERNAL_PAYEES_ALL IEPA,
        IBY_FORMATS_VL IFV
  WHERE ieppm.ext_pmt_party_id = iepa.ext_payee_id
    AND iepa.payee_party_id = :party_id
    AND iepa.supplier_site_id = :vendor_site_id
    AND ifv.format_code(+) = iepa.payment_format_code;
 

    RETURN(lv_format_code);
 
EXCEPTION
    WHEN OTHERS THEN
    SRW.MESSAGE(102,'No format_code for vendor site id:- '||:vendor_site_id);
    RETURN(NULL);
 
end;

Supplier Prepayment Balance Report in Oracle apps

SELECT AIA.INVOICE_ID                                          ,
       aia.description,
       aia.org_id                                              ,
       aia.INVOICE_NUM                                         ,
       aia.INVOICE_DATE                                        ,
       aia.INVOICE_AMOUNT                                      ,
       aia.EXCHANGE_RATE                                       ,
       aia.INVOICE_AMOUNT*NVL(aia.EXCHANGE_RATE,1) Invoice_Inr ,
aia.attribute10 requested_by,
       aps.VENDOR_NAME                                         ,
 apC.CHECK_id,    
 apC.CHECK_NUMBER                                         ,
       apc.CHECK_DATE                      payment_date                             ,
       (apc.amount )                         paid_amount                              ,
       --apa.amount*NVL(apa.EXCHANGE_RATE,1) Paid_Inr                                 ,
       avp.invoice_num                     app_std_inv_num                          ,
       aia.invoice_amount                                                           ,
       avp.PREPAY_AMOUNT_APPLIED                                                    ,
       avp.INVOICE_ID APP_INVOICE_ID                                                ,
       avp.ACCOUNTING_DATE                                                          ,
       aia.invoice_currency_code                                                    ,
       hou.name                                                                     ,
       apss.VENDOR_SITE_CODE                                                        ,
       DECODE(EARLIEST_SETTLEMENT_DATE,'','PERMENENT','TEMPORARY') STATUS
FROM   AP_INVOICES_ALL aia            ,
       AP_INVOICE_PAYMENTS_ALL APA    ,
       AP_SUPPLIERS aps               ,
       ap_checks_all apc              ,
       AP_VIEW_PREPAYS_FR_PREPAY_V avp,
       hr_operating_units hou         ,
       ap_supplier_sites_All apss
WHERE  INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT'
AND    aps.vendor_id           =aia.vendor_id
AND    AIA.INVOICE_ID          =APA.INVOICE_ID
AND    apa.check_id            =apc.check_id
AND    aia.invoice_id          =avp.prepay_invoice_id
AND    APA.INVOICE_ID          =avp.prepay_invoice_id
AND    nvl(apa.reversal_flag,'a')      !='Y'
AND    aia.org_id              =hou.organization_id
AND    aia.vendor_id           =apss.vendor_id
AND    aia.vendor_site_id      =apss.vendor_site_id
AND    aps.VENDOR_id BETWEEN NVL(:P_VENDOR_NAME_FROM,aps.VENDOR_id) AND    NVL(:P_VENDOR_NAME_TO,aps.VENDOR_id)
AND    aps.VENDOR_id BETWEEN NVL(:P_VENDOR_NUM_FROM,aps.VENDOR_id) AND    NVL(:P_VENDOR_NUM_TO,aps.VENDOR_id)
AND    HOU.NAME=NVL(:P_OPERATING_UNIT,HOU.NAME)
AND    APA.ACCOUNTING_DATE<=nvl(:P_AS_ON_DATE,sysdate)
AND    AVP.ACCOUNTING_DATE<=nvl(:P_AS_ON_DATE,sysdate)


UNION

SELECT AIA.INVOICE_ID                                          ,
       aia.description,
       aia.org_id                                              ,
       aia.INVOICE_NUM                                         ,
       aia.INVOICE_DATE                                        ,
       aia.INVOICE_AMOUNT                                      ,
       aia.EXCHANGE_RATE                                       ,
       aia.INVOICE_AMOUNT*NVL(aia.EXCHANGE_RATE,1) Invoice_Inr ,
aia.attribute10 requested_by,
       aps.VENDOR_NAME                                         ,
 apC.CHECK_id,         
   apC.CHECK_NUMBER                                         ,
       apc.CHECK_DATE                      payment_date                             ,
      (apc.amount)                          paid_amount                              ,
     --  apa.amount*NVL(apa.EXCHANGE_RATE,1) Paid_Inr                                 ,
       NULL                                                                         ,
       aia.invoice_amount                                                           ,
       NULL                                                                         ,
       NULL                                                                         ,
       NULL                                                                         ,
       aia.invoice_currency_code                                                    ,
       hou.name                                                                     ,
       apss.VENDOR_SITE_CODE                                                        ,
       DECODE(EARLIEST_SETTLEMENT_DATE,'','PERMENENT','TEMPORARY') STATUS
FROM   AP_INVOICES_ALL aia         ,
       AP_INVOICE_PAYMENTS_ALL APA ,
       AP_SUPPLIERS aps            ,
       ap_checks_all apc           ,
       hr_operating_units hou      ,
       ap_supplier_sites_All apss
WHERE  INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT'
AND    aps.vendor_id           =aia.vendor_id
AND    AIA.INVOICE_ID          =APA.INVOICE_ID
AND    apa.check_id            =apc.check_id
AND    nvl(apa.reversal_flag,'a')      !='Y'
AND    aia.org_id              =hou.organization_id
AND    aia.vendor_id           =apss.vendor_id
AND    aia.vendor_site_id      =apss.vendor_site_id
AND    aps.VENDOR_id BETWEEN NVL(:P_VENDOR_NAME_FROM,aps.VENDOR_id) AND    NVL(:P_VENDOR_NAME_TO,aps.VENDOR_id)
AND    aps.VENDOR_id BETWEEN NVL(:P_VENDOR_NUM_FROM,aps.VENDOR_id) AND    NVL(:P_VENDOR_NUM_TO,aps.VENDOR_id)
AND    HOU.NAME=NVL(:P_OPERATING_UNIT,HOU.NAME)
AND    APA.ACCOUNTING_DATE<=nvl(:P_AS_ON_DATE,sysdate)

Supplier Balances Report Query in oracle apps

QUERY 1
-----------------
select
hou.name             OU_NAME,
aps.segment1          VENDOR_CODE,
aps.vendor_name      VENDOR_NAME,
apss.vendor_site_code  SITE_CODE,
hou.organization_id org_id,
apss.vendor_site_id,
gcc.segment3||'-'||gcc.segment4 Account
from ap_suppliers aps
, ap_supplier_sites_all apss
, hr_operating_units hou
,gl_code_combinations gcc
where aps.vendor_id=apss.vendor_id
and apss.org_id=hou.organization_id
and apss.vendor_site_code=nvl(:p_vendor_site_code,apss.vendor_site_code)
and aps.vendor_name between nvl(:p_from_vendor_name,aps.vendor_name) and nvl(:p_to_vendor_name,aps.vendor_name)
and aps.segment1 between nvl(:p_from_vendor_number,aps.segment1) and nvl(:p_to_vendor_number,aps.segment1)
and hou.name=nvl(:p_operating_unit,hou.name)
and gcc.code_combination_id=apss.ACCTS_PAY_CODE_COMBINATION_ID
and nvl(aps.VENDOR_TYPE_LOOKUP_CODE,1)=nvl(:p_vendor_type,
NVL(aps.VENDOR_TYPE_LOOKUP_CODE,1))

2)QUERY2(vendor Site Id)
---------------------------------
select  aia.creation_date     "TRX DATE",
        aia.DOC_SEQUENCE_VALUE       "VNo",
        hrou.name             "OU",
        aia.invoice_type_lookup_code "INVOICE_TYPE",
        aia.invoice_num,
        aia.gl_date "INVOICE GL DATE",
        aia.vendor_site_id,
        aia.invoice_id,
        null check_id,
        null invoice_payment_id,
        null check_number,
        null "PAYEMENT GL DATE",
        null bank_account_name,
        aia.invoice_currency_code,
        round(NVL(aia.exchange_rate,1),2) exchange_rate,
decode(aia.invoice_type_lookup_code,'STANDARD',aia.invoice_amount,
'FTE_INVOICES',aia.invoice_amount,
             'EXPENSE REPORT',aia.invoice_amount,'MIXED',decode(sign(aia.invoice_amount),1,aia.invoice_amount),0) "ER CREDIT",
decode(aia.invoice_type_lookup_code,'CREDIT',abs(aia.invoice_amount),
'DEBIT',abs(aia.invoice_amount),
             'MIXED',decode(sign(aia.invoice_amount),-1,abs(aia.invoice_amount)),0) "ER DEBIT",
        ROUND(DECODE(aia.invoice_type_lookup_code
                    ,'STANDARD',aia.invoice_amount
                    ,'EXPENSE REPORT',aia.invoice_amount
                    ,'MIXED',DECODE(SIGN(aia.invoice_amount),1,abs(aia.invoice_amount),0)
                    ,'FTE_INVOICES',aia.invoice_amount)*nvl(aia.exchange_rate,1)
                    ,2) "FUNC CR",
        ROUND(DECODE(aia.invoice_type_lookup_code
                    ,'CREDIT',abs(aia.invoice_amount)
                    ,'DEBIT',abs(aia.invoice_amount)
                    ,'MIXED',DECODE(SIGN(aia.invoice_amount),-1,abs(aia.invoice_amount),0))*nvl(aia.exchange_rate,1)
                    ,2) "FUNC DR",                            
        0 "Exchange_Rate_Fluxuation",
        glcc.segment4 "GL_ACCOUNT",
        AP_INVOICES_PKG.GET_POSTING_STATUS(aia.invoice_id) "POSTED"
from ap_invoices_all aia,
        hr_operating_units hrou,
        gl_code_combinations glcc
where aia.accts_pay_code_combination_id=glcc.code_combination_id (+)
and aia.org_id=hrou.organization_id
and trunc(aia.gl_date) between :P_FROM_DATE and :P_TO_DATE
and aia.invoice_type_lookup_code in('STANDARD','FTE_INVOICES','MIXED','CREDIT','EXPENSE REPORT','DEBIT')
and nvl(aia.invoice_amount,0)<>0 

union

select  aca.creation_date     "TRX DATE",
        aia.DOC_SEQUENCE_VALUE   "VNo",
        hrou.name             "OU",
        decode (aia.invoice_type_lookup_code,'PREPAYMENT','ADVANCE PAYMENT','PAYMENT')  "INVOICE_TYPE",
        aia.invoice_num,
        aia.invoice_date "INVOICE GL DATE",
        aia.vendor_site_id,
        aia.invoice_id,
        aca.check_id,
        aip.invoice_payment_id,
        aca.check_number,
        aip.accounting_date  "PAYMENT GL DATE",
        aca.bank_account_name,
        aia.invoice_currency_code,
        round(nvl(aip.exchange_rate,1),2) exchange_rate,
        nvl(decode(sign(aip.amount),-1,abs(aip.amount)),0) "ER CREDIT",
        nvl(decode(sign(aip.amount),1,aip.amount),0) "ER DEBIT",
        ROUND(DECODE(SIGN(nvl(aip.amount,0)),-1,ABS(nvl(aip.amount,0)),0)*nvl(AIP.EXCHANGE_RATE,1),2) "FUNC CR",
        ROUND(DECODE(SIGN(nvl(aip.amount,0)),1,nvl(aip.amount,0),0)*nvl
(AIP.EXCHANGE_RATE,1),2) "FUNC DR",
        nvl(round((aia.exchange_rate-aip.exchange_rate)*nvl(aip.amount,0),2),0) "Exchange_Rate_Fluxuation",
        glcc.segment4 "GL_ACCOUNT",
        AP_INVOICES_PKG.GET_POSTING_STATUS(aia.invoice_id) "POSTED"
from ap_invoices_all aia,
     ap_invoice_payments_all aip,
     ap_checks_all aca,
     hr_operating_units hrou,
     gl_code_combinations glcc
where aia.invoice_id=aip.invoice_id(+)
and aip.check_id=aca.check_id(+)
and aia.accts_pay_code_combination_id=glcc.code_combination_id (+)
and aia.org_id=hrou.organization_id
and aia.invoice_type_lookup_code in ('STANDARD','PREPAYMENT','MIXED','EXPENSE REPORT','FTE_INVOICES','CREDIT','DEBIT')
and AP_INVOICES_PKG.GET_POSTING_STATUS(aia.invoice_id)='Y'
and trunc(aip.accounting_date) between :P_FROM_DATE and :P_TO_DATE

Formula Column
-----------------------
function CF_3Formula return Number is
begin
    return(:P_SELECT);
end;

STEPS TO CREATE SUPPLIER, SUPPLIER SITE AND SUPPLIER BANKS : ORACLE EBS R12

1) Choose the responsibility “Purchasing Super User”. Navigate to “Suppliers” window using the path “Supply Base -> Suppliers”.

2) Enter the supplier name in the “Create Supplier” window. Press on the “Apply” button.

3) To create a supplier site, an “Address” should be created first.

 Click on the “Address Book” in the “Suppliers” window to navigate to the address creation window.

 Click on the “Create” button to create the “Address”.

 Enter the address details and click on “Continue” button.

 Associate the “Address Sites” with the necessary operating units. Click on the “Apply” button. Supplier site is now created for the supplier as shown in the below screen shot.

 4) Create contacts for the supplier site.

 Click on the “Contact Directory” in the Suppliers window.

 Click on the “Create” button.

 Enter the contact details as shown in the above screen shot. Associate the supplier site with the contact in the “Addresses For the Contact” section. Click on the “Apply” button.

 Contact is created as shown in the above screen shot.


5) Create or associate bank accounts to the supplier site.

 Click on “Banking Details” in the Suppliers window.

 Select the level at which you want to create or associate the bank for the supplier. We are choosing to create the bank account at the supplier site level. Click on the “Create” button.

 Select the Country, Bank and Bank Branch as shown in the above screen shot.

 Enter the bank account details and press on “Apply” button.

 Bank account is created for the supplier site.