PAYABLES(ABOUT INVOICES USEFUL INFORMATION)

ABOUT INVOICES
--------------
 /*ACCOUNTING METHOD , ACCRUAL OR CASH : 
     So do you set the accounting method only at the Payables,Receivables levels,
  not at the GL Level. I believe so,because of those settings,payables and 
  receivables will generate the journal entries accordingly.
 
 When we create an invoice we initially create a invoice batch and then create 
 invoices in that batch. While creating invoice batch, we provide the control amount 
 and control count (the intial batch amount and intial batch count),payment terms, 
 type of invoice(like PO default, standard,CM,DM etc), pay group. These invoices go into
 the "ap_batches_all" table. However some of the above fields are optional at the batch 
 level and can be specific at the invoice level.
 
 Having entered an invoice batch, we enter the invoices in that batch and enter 
 the supplier information currency. Here the values mentioned at the batch level 
 will default at the invoice level which can be changed at the invoice level. All 
 the invoices go into the "ap_invoices_all" table.
 
 Each invoice will have distributions and each distribution (which could be either 
 item, tax, frieght charge) will correspond to a particular gl_account and will 
 be stored in "ap_invoice_distributions_all" with the corresponding code_combination_id 
 in the table.
 
 Having created an invoice the invoice needs to be validated,payables accounting 
 process and approved(may not be required). For validating an invoice it is 
 important that the control amount at the batch level sums up to the sum of the 
 individual amounts of the invoices. The control count at the batch level should 
 be equal to the number of invoices in that batch.
  The invoice can be validated by pressing the validate button.
  Make sure there are no holds on that invoice.i.e holds are 0.
 And only the validated invoices will be paid or payment applied.
 
 As mentioned before, the invoice is distributed on item,tax,freight etc), where 
 we mentioned for each distribution which account it should go to i.e the item should 
 go to account1 and tax should go to another tax related account. 
    Or we can create a distribution set where we mention the percentages of each item, 
 say the 70% of the invoice item should go to the account1 and 30% to account2.etc
 and assign that distribtuon set to that supplier. 
 once this is done, for every invoice corresponding to that supplier ,if we select 
 that distribution set, the above %'s are automatically applied.
 
 One feature of the Oracle Payables is that, the AP can also treat the employees 
 in a particular company as suppliers (i.e typically the suppliers send the invoices 
 which are paid for) similarly the employees should also be paid for the services 
 they render every pay period and what should be the employees payment terms.
 
 Accounting in Payables Transactions.
 
 There are two document classes in Accounts Payables which can give rise to accounting 
 transactions. 
   Invoice  (creating Invoices)
   Payment  (Issuing Payments).
 
 Entering an Invoice :
  An invoice increases the suppliers account balance by the invoice amount. 
  Once the invoice is completed, 
 
 The corresponding journal entries will be posted to the General Ledger in the 
 next GL transfer. As an ex, the following are the entries created for an invoice 
 entered for amount $200. Hence the three main accounts that are updated in an 
 invoice entry are
  
 Account    Derivation                      DR      CR
 --------   --------------------------------------------------------
 Liability    : Defaulted from the Supplier site ,otherwise can be entered  :     $235
            at the invoice batch level    
 Expense/ Asset  : Defaulted from the Purchase Order,otherwise entered at the  : $200 
       invoice level.
 Tax    : Defaulted from tax name,can be overwritten during invoice.  :  $35
 
 */
 
 select batch_id,batch_name,invoice_currency_code,payment_currency_code
     terms_id,gl_date,invoice_type_lookup_code,org_id
 from ap_batches_all 
 where  batch_name ='mybatch9'
 
 select * from ap_terms_lines  -- ap_terms_tl
 where  terms_id = 1205
 
 /* Actually there is a payment term specified at the batch level and as well as the invoice
    level. However invoice level take precedence. When you dont mention anything at the 
    invoice level/batch level, the term from supplier will default. Since invoices come from
    suppliers, there is a chance that two invoices from two different suppliers might have
    the same invoice number, and hence internally we assign a voucher number. For the same 
    supplier u cannot have two invoices with the same invoice number.*/ 
 SELECT * FROM ap_invoices_all 
 WHERE 1 =1 
 and   batch_id = 10065
 --and  invoice_num = 'myinv1'  
 --where invoice_id = 52989
 
 -- Just like AR, in AP as well, the payment schedules table will have the due dates etc.
 --Even without any payments,once an invoice is created, it will figure in this table.
 select invoice_id, gross_amount, amount_remaining, due_date,payment_method_lookup_code, 
     batch_id,org_id,inv_curr_gross_amount
 from ap_payment_schedules_all 
 where invoice_id = 10544
 
 SELECT * --dist_code_combination_id 
 FROM ap_invoice_distributions_all  
 WHERE invoice_id = 10663
 
 /* While creating an invoice batch we can provide the liability account and
  while creating the invoice we provide the expense accounts for the main item 
  and frieght or tax purposes (called distributions) */
 
 select * from gl_code_combinations -- 1012 
 where segment1 =01
 and segment2 = 0000
 and segment3 = 0000
 and segment4 in (73100,67170,21330)
 and segment5 = 0000
 and segment6 = 0000
 and segment7 = 0000
 and segment8 = 0000
 
 /*Default Expense or Charge Account for AP Invoice , Distribution Sets:
 We know that the default liability account will come from the Financials Options and which is
 shown at the invoice header level(liability account field can be sometimes hidden and hence 
 should be unhidden.  
     There is also an expense account which is at the invoice distribution level. In general 
    this has to be entered manually at the dist level. However this can be defaulted by defining 
 a distribution set and associating that distribution set at the supplier site level. Hence it is
 important that DISTRIBUTION SETS ARE ONLY FOR CHARGE ACCOUNTS NOT LIABILITY ACCOUNT.
 
 */
   select * 
   from   ap_invoice_distributions_all
 
 /*To approve or generate accounting entries online , ensure that the following setting is done,
    setup => options => payables => invoice tab => Allow Online Validation.  */

 
 /*Hence after this is done, we want to transfer all this information into the GL, 
 because it is a ledger system. AP,AR,Assets are the feeder systems(or subledgers) 
 in financials and they will be transferring the txn's to GL to reflect the accounts 
 correctly. From payables to GL, this can be done by "Payables Transfer to GL" program.
 
 So the various steps the payables invoices will go thru before they are transferred 
 to GL are given below
 */
   Invoice Validation, 
   Payables Accounting Process, 
   Invoice Approval workflow and then 
   Payables Transfer to gl(which involves payables transfer,journal import and posting).

 /*So once the payables txns are transferred (gl_je_headers and gl_je_lines) tables
  are populated and when the txns are posted, the gl_balances tables is updated. 
  The gl_balances tables is a huge table which consists of an entry for each account 
  or code_combination_id, when the txns are posted, the corresponding accounts in the 
  invoice distributions are updated accordingly.*/

  /* ACCOUNT GENERATOR Role in Payables & Projects :  
  Just want to talk about ,what is the role of account generator, say while 
  creating an invoice, For ex;liability and expense account are already specified 
  as part of the system options/distribution sets and hence it has to copy it 
  straight from that.

  What I mean to say is that unlike AutoAccounting in AR(or FA),which has to build each 
  segment and the combination, the Account Generator in AP does not have to build 
  each segment,correct? The only place where I saw account generator work, is when 
  you specify a project information at the invoice distribution level, then it will 
  rebuild the distribution. Other than that, the Account generator does not really 
  have to build segments of the code combination, other than copy from the options. 
  
  So for ex, if you specify project information in the distribution level, there is
  a standard workflow functionality to build the combination. However even this 
  can be customized by developing our own workflow functionality and specifying at
  this location, (from the General Ledger responsibility)
     
    Setup => Financials => Flexfields => Key => Accounts 
  
  This will open the form "Account Generator Processes",query for the application
   "General Ledger" and the flexfield title "Accounting Flexfield" and for your 
  corresponding structure. Here you can see for the item type "Project Supplier 
  Invoice Account Generation", what is the process name. THe default value is
  "Generate Default Account",which is the standard value. Any customized workflow
  process can be specified here.  
  
  
  
  INVOICE VALIDATION :
  --------------------
  /*The Invoice validation program does a bunch of things when it starts validation,like ,
    Matching
    Tax
    Distribution
    Period Status
    Exchange Rate
  
  so while it is matching the invoice, what kind of matching it uses 2-way,3-way 
  or 4-way. Typically when we create an invoice we provide the supplier and supplier 
  site information from where we are receiving that invoice. So the system will 
  decide how to do the Matching based on the setting at the supplier site level. 
  If at the supplier site, the Invoice Match Option is set to Purhcase Order,then at the
  time of validation, the invoice is matched against the purchase order.
  Another interesting point is that, when we create a purchase order, at the 
  distributions the invoice match option is also specified which basically defaults 
  from the supplier site,however we can change that accordingly.  
  Hence as we see, there are two important parameters
    Invoice Match Option : PO (or Receipt)
    Match Approval Level : 2-Way (3-Way or 4-Way)
  
   2-Way :  Invoice Quantity <= PO Quantity ; Invoice Price <= PO Price; 
   3-Way :  2-Way   +   Invoice Quantity <= Receipt Quantity (by the tolerance level)
   4-Way :  3-Way   +  Invoice Quantity <= Accepted Quantity (by the tolerance level) 
  
  (Interestingly there is also a Invoice Match Option that you can set at the Financials Options 
     Setup => Options => Financials Options )
  
  The different kinds of scenarios that are available are 
   We can have 1 PO , 1 Receipt  => 1 Invoice 
   We can have 1 PO , 5 Receipts => 5 Invoice
  */
    -- The validation flag gets updated in the ap_invoices_all table
    
    select * from ap_invoices_all -- approval_status?

 -- Invoice Matching with Purchase Order and 3-way Matching.

 /* 
 Generally, when an invoice is raised and there is a corresponding matching 
 PO, then the po_distribution_id column in the ap_invoice_distribution will 
 be not null, and that is the best way to identify invoices that have been 
 matched with PO, even though there is a column by name match_status_flag.
 Once an invoice is raised against this PO, the quantity billed and the amount 
 billed columns will get updated with the invoice prices).
 */
 
 select a.quantity_invoiced,a.unit_price ,c.quantity ,c.unit_price,
     c.unit_price - a.unit_price price_difference
 from   ap_invoice_distributions_all a,  
     po_distributions_all b, 
     po_lines_all c
 where  a.po_distribution_id = b.po_distribution_id
 and    b.po_line_id   = c.po_line_id
 and    a.accounting_date  between trunc(sysdate -1) and trunc(sysdate-1) + 0.99999 --- 60354
 and    a.unit_price   < c.unit_price
 and    a.po_distribution_id is not null
 
 /* So basically the following query should be able to give us what is the 
 corresponding PO from the invoice.  This is very much similar to the relation 
 that we have between requisition and the PO i.e by distribution id.
 */
     ap_invoice_distributions_all.po_distribution_id = po_distributions_all.po_distribution_id
  
 So if the Matching fails, then it will put the Invoice on Hold. We can run the 
 Invoices on Hold report to see what invoices are hold   
 
/* PAYABLES ACCOUNTING PROCESS ;
 --------------------------------
 ACCRUALS :
   We know that the two main accounts that get updated in Accounts payables when an invoice is
      created are LIABILITY(and EXPENSE).That is when you create a stand alone AP Invoice,
   with out any PO matched, where you will manually enter the expense account. The dists
   can be seen from the query.
 */  
   select * from ap_invoice_distributions_all

 /*However let us take the case of a PO received and an invoice is created by matching the PO.
   When the PO is received, the following accounting entries gets generated
   at the time of receiving 

  Receiving Inventory (Dr)
  AP accrual (Cr)
  
 Later when AP invoice is created and matched.
  
  AP Accrual Acount Dr
  Trade Payables Cr
  
 Here one thing we need to notice is that, once you match the PO, the account is auto
 matically defaulted with the accrual account from PO.
  
 Concept of Accounting Event : 
 An accounting event is a payables transaction that results in an accounting impact. 
 So for the two document classes of Invoices and Payments, the accounting events would be
 
 Invoices => Invoice, Invoice Adjustment, Cancellation, Prepayment application, Unapplication.
 Payments => Payment,future dated payment, adjustment, cancellation, clearing,unclearing.
  
 So having created the accounting entries we can expect the data in these two 
 tables with the balances being updated for the liability and the expense 
 accounts. We track this by using the code combination id.*/
  */ 

 /* In the headers table we will not have invoice references.Interestingly we 
 would expect the gl transfer flag in the lines,but it is in the headers table here */
 select * from ap_ae_headers_all  
 where ae_header_id = (
    select distinct ae_header_id
    from   ap_ae_lines_all 
    where  reference2 = 10407)
 
 /*Here in the lines table, the invoice references are there from reference2 (invoice_id)
  or from source _id */
 select ae_header_id, ae_line_id, code_combination_id,entered_dr, accounted_dr,entered_cr, 
     accounted_cr,creation_date, last_update_date
 from   ap_ae_lines_all 
 where  reference2 = 10407
 -- and source _id = 10467 
 -- and source_table in ('AP_INVOICES','AP_INVOICE_DISTRIBUTIONS')
 
    select ae_header_id, ae_line_id, code_combination_id,entered_dr, accounted_dr,entered_cr, accounted_cr
       ,creation_date, last_update_date
    from   ap_ae_lines_all 
    where  creation_date >= trunc(sysdate)
    -- and   code_combination_id in (1012,4583,5933)
 

 /*INVOICE APPROVAL WORKFLOW :
  ----------------------------
 To setup the invoice approval workflow in AP, you need to use the Oracle 
 Approvals Management(AME) to define the required rules. And if you want the 
 HR hierarchy to be used for the AME,then you need to define the logic 
 accordingly in OAM.

 manual holds after matching can be released.
 interestingly at the payables invoice level, no inv items are specified=> and hence no lines only 
 distributions probably
  */
  select * from ap_invoices_all

 /*Payables Transfer to GL :
 ****************************
 It is very important to understand that Payables Transfer to GL will result in failure if
 the accounting combination given is invalid. What this means is the if say for the expense
 account combination if you give an asset account, the payable transfer program will fail.
 
 Another thing I noticed is that some times, the parameters set of books id, are not displayed
 and hence they dont have any value,so they are failing, in which case enable and display them
 and then run again.
 */
 select * from gl_interface 
 
 
   /*Supplier Balances : Just as in AR (where the customer balances are given by the 
   ar_payment_schedules_all),the supplier balances are given by ap_payment_schedules_all 
   table. However there is no vendor id and hence the query to get the supplier 
   balances are given by. */
   
   select * from po_vendors 
   where vendor_name = 'mysupplier' -- 57175 

   select invoice_id, gross_amount,amount_remaining
    from ap_payment_schedules_all where invoice_id in ( 
   select invoice_id from ap_invoices_all where vendor_id = 57175)
 
  
  /*ABOUT PAYMENTS
  ---------------
   Having created the invoice batch and invoices,we now create a payment batch 
   and possible give the invoice batch name as the source for this payment. The 
   different phases a payment will go thru are
  
        New
   Selecting
     Built
     Rebuilding

     Formatted
     Confirmed (payment batch) 
     Create Accounting
  
  All these statuses we can progress thru from the Action button.     
      A caveat is while creating a payment batch, we have to provide the 
    documents which we may have to create using the bank ,branch, account form. */
  
  /* Initially when we create a payment batch, we create a batch which corresponds 
  to a group of invoices from a particular supplier or from a particular pay group. 
  After this, from the actions  button, we select "select invoices and build 
  payments options" and choose this action to be performed. what this means is that, 
  we want a payment to be made for the amount equal to  the sum of all the invoices 
  corresponding to that particular supplier chosen or corresponding to all the 
  suppliers of a particular pay group. 
           Conveniently we can also give a invoice batch created earlier and it 
  would pick only the invoice corresponding to that particular batch.The requery 
  batch will show the different statuses like Selected,Built etc. 
  
  Also while creating a payment batch, we provide the document of payment. this is 
  +done as follows. We can go to the (setup,payments,banks ) and then create a 
  bank, bank branch and then bank account. While creating a bank account, we provide 
  the GL account corresponding to the cash. That is when this document is encashed,
  this particular GL account is updated. So having created successfully a bank 
  account, we can create the payable documents, where we create a payable document 
  (we also provide what kind of format it is) and provide all the relevant 
  information.(like the starting and ending check numbers in case of check payment method)
  
  So when this particular action is completed we do get a payment total which is equal to this sum.
  and the status of the payment batch is "Built".And then we can "confirm" this batch as well ,upon 
  which the status changed to "Confirmed".
  
  Now when look at the "payments" button for this particular payment batch, then 
  we would see the diversification of this amount into different supplier amounts. 
  What this means is that for each supplier how much amount is owed is provided. 
  In this screen we can also look at the division of this grand amount into 
  individual invoice amounts, by pressing the invoices button.
  */
  
  --Actually the payment batches are called checkrun_names and they are stored in this table.
    select checkrun_name,bank_account_name,status, payment_method_lookup_code,org_id,
        start_print_document,end_print_document,first_available_document,last_update_date
  from ap_inv_selection_criteria_all
  order by last_update_date desc 
  
  /* Hence when we make payments, the liability account(5933) in Payables will get 
     debited while the cash clearing   account will get credited (4583) */
  

   /*Automatic Payment Programs :
   *****************************
    In Oracle Payables, we can use the "Automatic Payment Programs" window to define 
 the payment programs that you use to build,format payments or for creating a 
 remittance advice. 
   
   Oracle Payables defines 3 payment program types
 1 standard program for building the payments => Build Payments  ,
 13 standard programs for formatting the payments => Format Payments (Standard Oracle), 
 1 standard program for creating a separate advice for payments => Remittance Advice.
   
   When we define a payment format, we associate the above defined programs (for 
   build,format and remittance advice) and some other options.
   
   And when we create a payables document ,we associate the above created payment 
   format and specify some other parameters like the min and max document numbers. 
   So the relation is 
      (Payment Formats,methods) => Payment Programs => Payables Documents 
   
   Some information about the document numbers : the document numbers can be 
   associated with any kind of payment method (i.e not necessarily for checks). 
   However let us take the simplest case of checks.  
   We know that in AP, we define banks, branches accounts and for any particular 
   bank we can define the payables documents. In the case of checks, 

   LAST_USED => If you are defining a new payment method,then you usually enter 
 the value of 1 as minimum. Let us say you already defined this document 
 number. After this some payment batch has used this particular payment 
 method. Since it has used that, the document for that payment batch will 
 be 1. Also during that entire process of building,formatting the payments,
 no other payment batch can use this particular document. Also once any 
 payment batch starts using this document,it will update the last_used 
 column  to the appropriate value(i.e to value 1) and the value is not 
 updatable until that batch is done processing. After that we can update 
 that value again.   
   LAST_AVAILABLE => is the document number that is the last available number,
 beyond which we cannot use.
   
   
   /* When you actually start creating a payment batch, the bank account information is
   automatically defaulted. And it comes from the payables options level. Now  to get a bank
   and branch from the bank account name, run this query.*/
   
    select * from ap_bank_accounts 
 WHERE bank_account_name like 'BofA_CN_INTERNAL' --'110 US 2788 BOFAUS3N USD'
    
 --
    select *  
 from ap_bank_branches 
 where bank_branch_id =48000
   
     /*A bank account is striped by org_id i.e by the operating unit id,but not the bank branch.
     That is if you have banks
     in different countries, then just having a bank account in US will not do. 
      You should create a bank account corresponding to that particular operating 
 unit and then transact.
     An exception is Wire method : 
        Wire method of payment is a type of payment which you do outside of payable system and once 
     payment is done then based on document sent by bank you record it in payable system and hence
     payment batch is not possible with wire transfer when you enter in the system,by which time
     the payment is already done and hence no need to tell the system which invoices to be selected.
     */
  
 PAYMENT BATCHES :
 ---------------- 

     begin
     fnd_client_info.set_org_context(fnd_profile.value('ORG_ID'));
     END;
     
 --BUILDING THE PAYMENTS : 
 ------------------------
 /*Building is the process where the selection of the invoices & building 
 for that batch happens. 

 Only invoices which match the criteria of the payment batch will be selected.
  Basically we must ensure on the payment batch for the following things ,:
  
     PAYTHRU DUE DATE, PAY GROUP, PAYMENT PRIORITY, PAYMENT METHOD*/
  
  --Actually the payment batches are called checkrun_names and they are stored in this table.
     select checkrun_name,bank_account_name,status, payment_method_lookup_code,org_id,
         start_print_document,end_print_document,first_available_document,last_update_date
   from ap_inv_selection_criteria_all
   order by last_update_date desc 
  
   /*If you change the payment method code on a supplier , then all the existing
    invoices/batches will not change, you will have to manually change the payment method
 code. However all subsequent changes will have the new changed payment method code.*/ 
   
   different payment terms on batch level and invoice level.
 
 see and explore what exactly the pay thru date means. 
 
  -- All the invoices that are selected for the payment batch are from this table.
     select * from ap_selected_invoices_all
  where  checkrun_name = 'mypbatch7'
   order by last_update_date desc 
     
 -- We can also check what is the check number that is used for this payment here.
     select * 
  from  ap_selected_invoice_checks_all
    -- where  checkrun_name ='mypbatch47'  
  --and    status_lookup_code = 'UNCONFIRMED SET UP' order by selected_check_id
     order by last_update_date desc 
  
FORMATTING THE PAYMENTS :(Very Important Step)
-------------------------
 /*The next step after building the payments is formatting. As mentioned before, there are
 13 different standard format programs which oracle provides. And we can create as many
 as we want as well. When you run the format program, the program that is specified
 in the payment method(like check etc),which you specify in the payment batch will be run. 
    */
     From the bank account => 
    Payables document => 
       document name =>
          (payment format,method) => 
     BUILD and FORMAT program name.
   
     /*Usually in the case of the check payment , the remittance advice program is part of the 
 Format program and hence we dont need to run any separate program. However for the payment
 methods like EFT, then there will be a separete remittance advice program which will be
 running. 
        And once the payments are built,formatted and confirmed, the checks last_used value will
 get updated as mentioned earlier,*/
   
  /*Basically formatting means (nothing much happens on the back end side) it 
  searches for certain options and shows how the output or check will look like
  and based on the parameters like stub first or last, print check stub or not
  it will show us how the check looks like in the output file once the program 
  completes. The following query gives pretty much the formatting options, 
   */
  
  SELECT aisc.checkrun_name,
         acf.separate_remittance_advice, acf.stub_first_flag,
         acf.print_check_stub, aisc.check_date
    FROM ap_invoice_selection_criteria aisc,
         ap_check_formats acf,
         ap_check_stocks acs
   WHERE aisc.checkrun_name = 'mypbatch7'
     AND acs.check_stock_id = aisc.check_stock_id
     AND acs.check_format_id = acf.check_format_id
  
   -- Even though the table name says check formats all the payment formats are 
   --stored in this table.
   select stub_first_flag,print_check_stub, separate_remittance_advice,invoices_per_stub
   from ap_check_formats
   where name like 'Standard Check Format'
   order by last_update_date desc
   
   /* Basically check stocks is not transaction dependent. It tells you what is the 
   starting number and ending number */
   select * from ap_check_stocks
   order by last_update_date desc
    
   /* "ap_selected_invoice_checks_all" is a temporary table that stores payment 
    information during a payment batch. Payables inserts into this table when 
 you build payments in a payment batch. There will be one row for each payment 
 issued during the current payment batch. When you confirm a payment batch, 
 your Oracle Payables application inserts these payments into"ap_checks_all" 
 and creates a payment file. And then it deletes from the 
 ap_selected_invoice_checks_all*/
    
   select *    -- 
   FROM ap_selected_invoice_checks_all
   order by last_update_date desc
  
   /*I changed the option in the payment format from before document to after document, 
   and then the invoice information is printed below the check now.So the formatting
   gives us with a lot of options like how the check document looks like.*/
   
   --
    select * from ap_checks_all
    order by last_update_date desc
    
    select * from ap_checkrun_conc_processes_all
    
    select * from ap_checkrun_confirmations_all
    
       
 /* Just like AR, in AP as well, the payment schedules table will have the due dates etc.
   now we can see the amount remaining is zero,once the payments are made for the invoices.*/
 select invoice_id, gross_amount, amount_remaining, due_date,payment_method_lookup_code, 
     batch_id,org_id,inv_curr_gross_amount
 from ap_payment_schedules_all 
 where invoice_id = 10544
 
 --And from the form, we can check the invoice payments from 
  Invoices => View Payments 
  
  
/*CONFIRMING THE PAYMENT BATCH 
----------------------------
 Basically when we format the documents at that point of time, we can print the checks.
 This is how it works. 
      Basically we load the check documents in the printer, these check 
 documents will already have the numbers on them. Now when the printing of checks completes, 
 then we know what got printed successfully and what not. Let us say one particular check,
 say 1005, is damaged ,then we can tell the system during the confirm process. That is we 
 choose the value "skip" and provide that number,so that system will ignore that. We also tell 
 which got printed successfully.  After all this is done, the last_available and last_used ones 
 will get updated successfully. 

 The confirm payment batch will print separate payment document for each 
 supplier ,otherwise it will just print one payment document. is that true.
    */
 

    /*ACCOUNTING FOR PAYMENT BATCHES.
    We have seen that we progressed the payment batches thru building, formatting,
   confirming etc (regardless of the formatting method used). Payment batches 
   can be accounted only after the corresponding invoice batches have been accounted
   for. And payment batches can be accounted only after they have been confirmed.
    */
 
    select * 
  from ap_accounting_events_all
  --where event_type_code like 'PAYMENT%'
  order by creation_date desc
  
      select *
  from   ap_ae_headers_all
  order by last_update_date desc
   
 
   /*Pay Alone Invoices :
 -------------------
 Pay Alone Invoices : When the pay alone check box is checked at the invoice 
   level(which actually defaults from the supplier site level and can be changed 
   at the invoice level), then payables will create a separate payment document 
   for that invoice. That is when you build the payments it does not include 
   any other invoices other than a pay-alone invoice. If there is pay alone
   and non pay-alone invoice, then pay alone invoice will be built first.*/ 
 
   /*SINGLE PAYMENTS : 
   -------------------
 The three different types of single payments are 
     Manual Payments
     Quick Payments
     Refunds.  
 
    One of the major difference between the single payments and batch payments is 
   that in the single payments you do not have the process of building the payments.*/
  
    /*MANUAL Payments : One main functionality of the manual payments are when 
 the payment has already been made,then you come to the manual payments 
 screen and record that payment. So it is basically after the fact and 
 you mention the invoice for which it is paid so the invoice balance is 
 reduced. Since there is no invoice selection criteria and you directly 
 mention/enter the invoices they do not go into the table 
 ap_inv_selection_criteria and they go to ap_invoice_payments_all.
    We can identify such payments from the following table using the invoice id,*/
 
   /* Unlike in the case of batches, where the checks are inserted into ap_checks_all
   at the time of confirming. Here at the time of creation itself the checks are 
   inserted into ap_checks_all,all at once. */
   
   select payment_type_flag, payment_method_lookup_code,check_id,check_number
   from ap_checks_all
   where check_number= '1000009'
 
   -- Put the above check id here to get payment/invoice information.
   select invoice_payment_id,invoice_payment_type, accounting_event_id, cash_posted_flag,check_id, 
   invoice_id,amount,period_name ,accounting_date
   from ap_invoice_payments_all
   where check_id = 10480    

   SELECT *
   FROM ap_lookup_codes
   WHERE lookup_type = 'PAYMENT TYPE' 
   AND lookup_code = 'M'    
   
  /*Following this, for the manual payments there are no further actions, i.e there is nothing like
   building ,formatting,confirming etc.It just reduces the supplier balance. 
   The advantage of manual payments, is that you can create a single manual payment for
    multiple pay-alone invoices or you can pay a supplier who has Hold All Payments enabled. */
 
    /*QUICK Payments : Quick Payments is a process where you quickly print a payment document
 like check and send it to supplier. Basically when you create a payment,then only you 
 specify which invoice you are paying. The advantage is that you can select an invoice 
 regardless of the due date or payment terms. 
 However in this case, after you create a payment you will format and print the payment,but
 there is no build stage.*/

   select payment_type_flag, payment_method_lookup_code,check_id,check_number
   from ap_checks_all
   where check_number= '1000010'
 
   -- put the above check id here to get payment/invoice information.
   select invoice_payment_id,invoice_payment_type, accounting_event_id, cash_posted_flag,check_id, 
   invoice_id,amount,period_name ,accounting_date
   from ap_invoice_payments_all
   where check_id = 10500    

   SELECT *
   FROM ap_lookup_codes
   WHERE lookup_type = 'PAYMENT TYPE' 
   AND lookup_code = 'Q'    
   

   -- GL TRANSFER : 
   /*Now we need to transfer these invoices and payments transactions to the General Ledger. 
   Typically this happens in stages. The steps involved are 
             "Payables Transfer to GL", 
      "Journal Import" and 
      "Posting to General Ledger".
   When we run the "Payables Transfer to GL", the data gets moved into the GL_INTERFACE table,and here
   we can optionally mention to do the Journal Import as well. Hence after the Journal Import is done,we 
   can check the data in the following tables.*/
   
   select * from gl_je_headers
   
   select * from gl_je_batches
   where creation_date  = (select max(creation_date) from gl_je_batches)
   
   select max(creation_date) from gl_je_lines
   
   /* Generally while running the Posting Process i.e "Automatic Posting", the program will ask for 
   the autopost set id.  For this we go to the form "Autopost Criteria Sets" and create a criteria 
   set by providing the priority,source,
   category, balance type and period columns.
   Having created a criteria set, we can then run the conc program (we can also run from the same form)
   by providing this autopost set id */
   
   select * 
   from gl_automatic_posting_sets
   
   /* Hence after Posting process is completed we can check the gl_balances table which will contain most 
   importantly ,only the summarized information. */
   
   SELECT * 
   FROM   gl_balances  
   wHERE code_combination_id =4583
   order by last_update_date desc   
   
   /*Use the Payables func to print a check. A payment document should need only
     one document number right? 
   --why is that it needs so many document numbers from last used to last available 
     numbers.*/
   
   /*
   You need create receipt first and invoice later. If you created invoice and match
   to po the invoice will be on hold as no receipts are created. 
   */
   
   /* PREPAYMENTS IN ACCOUNTS PAYABLES : 
   There are two kinds of prepayments => Temporary and Permanent. 
   Temporary prepayments are those which you apply to an invoice. 
   Perf
   The way you deal with the prepayments in AP is that, you first create a prepayment invoice for a
   particular supplier and make a payment for that prepayment invoice. Then whenever you are entering
   an invoice for that supplier ,the system will prompt you saying that there is a prepayment against
   this supplier.

 1)  First create a Prepayment invoice,make sure it is temporary, and provide a settlement date.
 
 2)  Make a payment for this prepayment invoice by doing this
     Actions => Pay in Full
     (why the prepay invoices needs to paid in full,why not partial?)
 
 3) Now create a Standard Invoice for the same above supplier. when you tab out of the supplier window,
     the system prompts you saying that, there is a prepayment for this supplier.
 
 4) Next there are two ways, a prepayment can be applied to the supplier invoice.
     * First you pull up the prepayment invoice and click on the 
      Actions=> Apply/Unapply Payments.
        now this will give all the eligible invoices for that supplier and you can apply to one or
   more than one number of invoices.
     * Second,you pull up the Supplier invoice created in the step 3 and choose 
      Actions => Apply/Unapply Payments.
      In this case also the screen looks very similar with the exception that you have an 
   additional check box "Prepayment On Invoice". If you check this, that what it means is 
   that the supplier invoice has included the prepayment amount as well
      in the invoice amount. The only difference in both the ways of application is in the 
   creation of accounting distributions.
   (Prepayment on Invoice checkbox :ticking the 'Prepayment on Invoice' checkbox when you 
   Apply the prepayment? This will create a new negative distribution line on the invoice 
   and reduce the overall invoice distribution total accordingly)
 */
 select  aia.invoice_id,invoice_date,invoice_type_lookup_code, aida.prepay_distribution_id,
    prepay_amount_remaining
 from ap_invoices_all aia, ap_invoice_distributions_all aida
 where  aia.invoice_id = aida.invoice_id
 and    aia.invoice_num like '08-MAR-2007' 

  select * from ap_payment_bsets_all

    /* In AP, you cannot create payments without associating them with the invoice. That is ,without
  a supplier invoice which has an open balance,you cannot create payments. 
 */

  select * from ap_invoice_prepays_all where last_update_date > sysdate -100
  
   select * from ap_payment_history_all where last_update_date > sysdate -1  
  
 /*Payables & Cash Management => If the cash clearing needs to work, then 
    you need to give the correct cash clearing account information in the 
    bank account as wells as the payables documents level. then it will hit 
    the cash clearing account when a payment is account. further when you use 
    the cash management you can reconcile the bank statements with the cash 
    clearing account.
    */
  
  
  Recurring Invoices :
  --------------------
  
  Generally a supplier sends a invoice and we enter it into our system. However
  sometimes we can also generate a invoice on behalf of supplier. This can be done
  by recurring invoice. A recurring invoice works on the line of recurring journals
  in GL. It works in two steps, 
    Recurring Invoice template Definition
  Recurring invoice creation.
 
 Some of the important things that are needed in the template definition are 
   Special calendar => needs to be defined,with months,quarters etc.
   Distribution source => get it from the distribution set or from PO.
   About the calendar : Let us say if there is calendar consisting of 12 periods
   from January to December.
   And if you want first invoice period to be ,say , May. Then the next period
   will be populated as May. Once the invoice has been generated, the next
   period will be Jun. So at any point of time, we can see what will be the
   next recurring invoice period. (The fields next invoice period and the
    next amount indicate are non-editable  fields). 

     There are two special amounts and periods, that means, on that period,
   the invoice amount will be that amount.
    
     Once a recurring invoice has been generated, you cannot change any fields
   in the recurring template,other than the account distribution.
   
     Do we need to come to the template everytime and run the recurring invoice
   isn't that cumbersome ??
  
  Payables Credit Memo, Debit Memo :
  ----------------------------------
  
  A credit memo is a document sent by the supplier which decreases the supplier balance.
  It is a negative amount and is applied by matching to an supplier invoice.
  
  A debit memo is a document which you create and send it to the supplier for their 
  reference. It is also a negative amount and is applied by matching to an supplier invoice
  and it decreases the supplier balance.
   
  
  Pay group : 
     Pay group is a look up code defined in the purchasing application.
   
  
  
 Q & A
 ------
 Receiving Transactions processor should be running.
 

  /*Payables Invoice Import Process
  _______________________________
  
  The following process goes thru the flow of generating the invoices (along with the underlying table updates)
  until the ultimate GL Posting Process.
  
  Invoices Testing Queries :  
  
  -- Set the Environment */
   
     begin
     fnd_client_info.set_org_context(fnd_profile.value('ORG_ID'));
     end;
  
   -- Run the invoice_test.sql to populate the invoices into the ap_invoices_interface table.
  
     select status,source,count(*)
     from ap_invoices_interface
     where source = 'WW-Real Estate-Payments'
     group by source,status
  
   -- Run the concurrent program "Payables Open Interface Import", with the invoice batch name as any name. 
     The import program will create a record in the ap_batches_all
  
   -- You can keep checking the query #1 whether the invoices are processed or not in the source table.
  
   -- We can also check in the destination table after setting the env, by running the following query. 
  
     select  to_char(creation_date,'DD-MON-YYYY HH24:MI:SS') ,
         count(*)
     from ap_invoices -- (or ap_invoices_all) 
     where trunc(creation_date) = trunc(sysdate)
     group by to_char(creation_date,'DD-MON-YYYY HH24:MI:SS')
  
   -- Get one such invoice imported into ap_invoices 
  
     select invoice_id,approval_status, posting_status ,wfapproval_status,
         to_char(creation_date,'DD-MON-YYYY HH24:MI:SS') creation_date,
         to_char(last_update_date,'DD-MON-YYYY HH24:MI:SS') last_update_date
     from ap_invoices 
     where creation_date = (select max(creation_date) from ap_invoices)
     and rownum < 2  
  
   --  The Following programs are scheduled in SJPRF to run for every 10 minutes. 
      
       /* "Invoice Validation" Concurrent Program.(APPRVL) (Param: Process all matching lines)
    If the approval_status_lookup_code = 'APPROVED', THEN the status is 'VALIDATED'. if the 
     approval_status_lookup_code is 'NEEDS REAPPROVAL', then the status is 'needs revalidation'
    */  
         select  to_char(creation_date,'DD-MON-YYYY HH24:MI:SS'), count(*)
         from ap_invoices_v
         where trunc(creation_date) = trunc(sysdate)
         and approval_status_lookup_code = 'APPROVED'
         group by to_char(creation_date,'DD-MON-YYYY HH24:MI:SS')
 
       select invoice_id,approval_status, posting_status ,wfapproval_status,approval_status_lookup_code 
       from ap_invoices_v 
       where invoice_id = 5354999
   
   --   Run the "Payables Accounting Process" Concurrent program. (Param:  Validate Accounts : NO).
  
         select to_char(creation_date,'DD-MON-YYYY HH24:MI:SS'),count(*)
         from ap_ae_headers_all where trunc(creation_date) =trunc(sysdate)
         group by to_char(creation_date,'DD-MON-YYYY HH24:MI:SS')
         
         select to_char(creation_date,'DD-MON-YYYY HH24:MI:SS'),count(*)
         from ap_ae_lines_all where trunc(creation_date) =trunc(sysdate)
         group by to_char(creation_date,'DD-MON-YYYY HH24:MI:SS')
  
   /* "Workflow Background Process" for invoice approval. This is already enabled for every 30 sec in SJPRF.
        OR "Invoice Approval Workflow" needs to be run.
     
   --Before approval, the below query should give the import count#  */
   
         select to_char(creation_date,'DD-MON-YYYY HH24:MI:SS'),count(*)
         from ap_invoices_v
      where trunc(creation_date) =trunc(sysdate)
      and   wfapproval_status = 'WFAPPROVED'
         group by to_char(creation_date,'DD-MON-YYYY HH24:MI:SS')
   
   -- After approval process,  the above query should give 0 records.    
       
     /*   "Cisco Payables GL Transfer Post Program" ap_invoice_distributions_all (posted_flag),
          (gl_interface, gl_je_batches, gl_balances.)
    After the feeder systems (i.e AP,AR ) transfer the data to GL, they first go to gl_interface table and then
    from there , they are moved to the gl_je tables. Once these entries are posted, they will update the 
    gl_balances table.   
    So this program essentially kicks off
     Payables Transfer to GL
     Journal Import
     Automatic Posting 
    Hence it is important we need to have US GL Super User resposibility assigned to ourself 
    before we kick off this program.
      */
                select to_char(creation_date,'DD-MON-YYYY HH24:MI:SS') ,count(*) 
                from gl_je_headers
                where trunc(creation_date) = trunc(sysdate)                                                   
                group by to_char(creation_date,'DD-MON-YYYY HH24:MI:SS') 
  
            select to_char(creation_date,'DD-MON-YYYY HH24:MI:SS') ,count(*) 
                from gl_je_lines
                where trunc(creation_date) = trunc(sysdate)                                                   
                group by to_char(creation_date,'DD-MON-YYYY HH24:MI:SS') 
       
       select table_name,num_rows from dba_tables where table_name in
       ('AP_AE_LINES_ALL')
  
    /* Once the invoices are imported into the ap_invoices_all , ap_invoice_distributions_all tables from 
   ap_invoices_interface and ap_invoice_lines_interface, we can also look at them from the applications 
   either in the invoice form or look at all the invoices in 
        Invoice=>Invoices=>Open Interface Invoices. */

   /*
   Quick Invoices & Invoices Workbench:
   Quick invoices window will not do much of the validation and do not default the values. 
   Also this information will go into the ap_invoices_interface table,after which we can run the 
   invoice import program to import them in AP.
 
   For manually entered invoices,when we validate them, does it do matching and if it does,where is
    the PO information.
 */
 
--
For invoice matching is the PO really required, just by the supplier id, cant the 
system go and find the related PO and match them.  so we found that for the matching 
to succeed we really need a PO for the invoice,otherwise it fails.
--
regardless of the po, for each receipt, you generally have one AP invoice.
--
you can receive any amount lesser than the PO line amount, however the over-receipt 
tolerance amount pertains to the scenaroi,when u r receiving more than the po line amount.
--
The only way you can enter a quantity invoiced amount in the invoice distribution is
to match it to a po line /shipment.
then the quantity invoiced is defaulted to the value of the po line qnty requested
You could edit that value if you want to and change it.
--
so basically in matching the invoice distributons are matched to the PO lines/distributions
 or receiving distributions based on 2 way or 3 way matching. 

 
--- QUESTIONNAIRE :

1) Is there a tax hiearchy in AP as well ??? 

 Yes. Just like in AR,here too we define tax code(from tax types) and tax codes 
 can be specified at different places like Supplier, site, payables options, 
 invoice header, template. And the hierachy of preference can be found in the 
 Financial options. 
 Howeer I did not find the location tax code in the tax code in the payables. This
 is probably because the company is not taxing ;it is supplier who will have to
 apply the taxes. 
 
 
2) What is a voucher and document sequence ??
 Since two suppliers can provide you two invoices which might have the same invoice
 number, in which case, invoice number cannot be unique. Hence you have to create
 a voucher number within your system for these two invoices, so that you can 
 uniquely identify them. 
 
 Also we can assign a document sequence for each document category with in the 
 Oracle applcations system. And so you can for a AP invoice.Simple steps are
     
    There are already predefined document categories like AP invoice, AP credit memo,
       so no need to create anything new
    Create a new sequence with starting number, etc.
    Assign the above sequence to a particular   
        (Application, Category, Set of Books, Start date) 
  Ensure that Sequential Numbering profile option is aptly set.

3) What happens in these matching  scenarios ??

   PO , 2 -way  => No confusion here, 2-way matching is performed,
   Receipt, 3-way => No confusion here, 3-way matching is performed.
   
   PO, 3-way => what will happen here, 2-way matching or 3-way matching with receipts
   Receipt, 2-way => what will happen here , 3-way matching or matching with PO's.
   
   Also between, 2 way vs 3 way matching, which one is used in what circumstances 
   and what are the advantages of one over the other?   when both of them can be 
   used, which is preferred ?  is it like the more receipts for a PO, better 3,4 way matching?  

4)What are credit memos and debit memos in Payables ??
  A credit memo is a document sent by the Supplier which you will apply to 
  an invoice and which reduces the invoice balance.
  
  A debit memo is a document which you will create ,which will also reduce the
  invoice balance,and send it tto the supplier for verification,approval.
  
5) Should every invoice in Payables be a part of an invoice batch?   
   
 If the profile option "AP: Use Invoice Batch Control" is set to yes, then you can
 create an invoice only after you create a invoice batch. 
 If the above profile option is set to No, the  you can create a standalone invoice,
 without being part of a batch.
 
6) Should every payment be a part of a payment batch? 

   No.
   Payments => Entry => Batches ; Payment batches go thru the build,format and confirmation.
   Payments=> Entry => Payments ; If you enter a standalone payments, they are called
        single payments and they are of 3 types (Quick,Manual,Refund).

7)What is the difference between online Matching and offline Matching????
 
 When you are entering online invoice in Invoice workbench, the only way you
 can enter an invoice quantity (Match Quantity) is by matching it to a PO line,upon 
 which the invoice quantity is defaulted from the PO line,which you can change. 
 So you cannot manually type in the invoice quantity in the Invoice distribution 
 with out matching.
 
 However if we manually create an invoice line which is identical to a PO line then
 run matching,then the system just matches and validates it successfully.
    what is that so & why there is no invoice qty field in the distribution form
 
8) What are the steps required to void a check in AP ?     
 
9) Difference between suppliers and vendors ??  
     I believe vendor is a type of supplier. This is bcoz, when you choose supplier type, 
  you have a option of vendor there.The different kinds
         of suppliers are government, federal ,Mfg.
  Also Employee is a type of Supplier. 
     try to see where you can define supplier types.
 
10) For manually entered invoices,when we validate them, does it do matching and if it does,where 
 is the PO information??
         Yes it does matching and its puts on hold if there is no corresponding matching. In such 
   case,you can release the hold manually.
 
11).When I go to the view accounting from the receiving transaction, i am not able to see the accounting 
 entry for the accrual account??
             Firstly ensure that you have accrue on receipt. And then if you do that, then the 
    accounting lines are automatically xferred to GL and can be seen
             in gl_interface.
 
12).In Oracle 11i, there is no centralized payment systems ?? 
 which means that you just cannot have a
  bank defined in one country and send payments from that bank. You need to define banks in each
   operating unit. So it is understandable bank is tied to an operating unit/ SOB id.
 
 When we build the payments, it is important that you can only give one type of payment types
  i.e you cannot build corresponding to CHECK and ACH with in a payment batch.

HRMS (Human Resources useful information)

/* Purchasing and  HR Stuff.
 If HR is not completely installed. We can define the employees and positions in Oracle purchasing,
However if HR is installed then the apps will force you to define the employees ,supervisors and positions in the
 HR  module.

In Oracle HR,People are different from the employees. Not all the people defined are employees. Employee information
goes into the table "hr_employees" and the people information goes into the "per_all_people_f"  table,but an employee will
be in both the hr_employees and per_all_people_f tables. Typically if there are any contacts for an employee ,then they
will be entered as the people.

-- The below query can be tried to see if the HR product is installed or not. Interesting all the HR related code is present in the applicaction user PER and not HR 
select INIT_FUNCTION_NAME from FND_PRODUCT_INITIALIZATION 
where APPLICATION_SHORT_NAME = 'PER'
and exists (select 1 from FND_PRODUCT_INIT_CONDITION C
          where C.APPLICATION_SHORT_NAME = 'PER' 
     and C.RE_INIT_CONDITION in ('USER','RESP','APPL','NLS'))

Normally, whenever a purchase order is raised ,say,by an employee. then it will be routed thru the next in the hierarchy.
And there are two types of hierarchies in the Oracle purchasing module. One is position approval hierarchy and another
employee/supervisor hierarchy. This setting can be defined using the path 
     setup => Organization => Financials Options screen. => Human Resources tab.
     
If the "Use Approval Hierarchies" check box is checked, then it means that the   position approval hierarchy is used.
If the "Use Approval Hierarchies" check box is unchecked, then it means that the employee/supervisor hierarchy is used.

Let  us briefly dwell on what is the difference between these two hierarchies. In the case of position approval hierarchy,
firstly a hierarchy of all the positions in the company are defined. for ex, Manager, Senior Manager, Executive director
,Vice President,etc; all these positions are first defined. So if a manager raises a PO, then that document is routed up
the hierarchy i.e it is routed to the senior manager. 

In the case of employee/supervisor hierarchy, while we are defining an employee ,say ,Smith, then his manager ,say, David, is also
specified. And a document is routed to that manager David.

INcidentally in Oracle purchasing, there are lot of document types defined. That is ,a requisition(Internal or Purchase), planned purchase order,
standard purchase order.etc We can see these  types from the path   setup => Purchasing => Document Types.

what are the products that we would be supporting in this suite.
outerjoin when the where codnitions
purchase order total price.
*/

/**********************************************/

/* HR Stuff. 
   The profile option MO: Operating Unit will always the store the operating unit id and not the inventory_org id
   The Profile option MO:Top Operating Level stores the values of set of books, legal entity or operating unit. 
   If it is SOB, user can run the report with any LE underneath that sob
   if it is LE,  user can run the report with any OU  underneath that LE
   if it is OU,  user can run the report with any OU mentioned in the MO: Operating Unit profile option.
       There are certain predefined oracle app reports which we can run with above mentioned reporting levels.
     Ex : Aging 4 Buckets report.*/

/* Hr_organization_information table stores two classes of information 
  If org_information_context = 'CLASS', then it gives information about the org itself.
     i.e  whether it is a Legal Entity,Operating Unit, Inventory Organization.
  Interestingly ,the below query will give you info about everything other than set of books id,which makes sense as sob is accounting stuff.
*/
   select    hoi.organization_id
         ,hoi.org_information_context
   ,haou.name     Name
   from  hr_organization_information hoi
   ,hr_all_organization_units  haou
   where hoi.organization_id =2398
   and   hoi.organization_id  = haou.organization_id 
   and   org_information_context = 'CLASS'
  
/*  If org_information_context = 'Accounting Information',it provides information on ow it is related to other Orgs.    
   Given a specific organization id,get a list of parents i.e set of books,legal entity and operating unit, run it.
   If the org_information_context is Accounting Information, the org_information1 will store the set of books id
   and org_information3 will give the inventory org id. Hence given the set of books id we can get all the 
   heirarchy in one particular record as below. 
  */     
 select     org_information1  set_of_books_id
   ,org_information2  legal_entity_id
   ,org_information3  operating_unit_id
   ,hoi.organization_id  Inventory_organization_id
   ,org_information4
   ,haou.name
 from   hr_organization_information hoi
   ,hr_all_organization_units  haou
 where  org_information1= 1  -- this isthe sob id
 and    hoi.organization_id  = haou.organization_id 
 and    org_information_context ='Accounting Information'
 
 select * from gl_sets_of_books where set_of_books_id = 34
   
 
 select * from hr_organization_units where organization_id = 61
 
select * from fnd_profile_options
where profile_option_name like 'GL_SET_OF_BKS_NAME'

select * from fnd_profile_options_tl
where user_profile_option_name like 'GL%Set%'

select * from hz_cust_accounts order by creation_date desc

select * from hz_cust_account_roles where cust_account_id = 4193

select partner_id from pv_Partner_profiles
order by creation_date desc

hr_organization_information_v


------------------------
-- Set the environment in the production environment ( fnd_client_info.set_org_context) 
 BEGIN 
  dbms_application_info.set_client_info(485);
END;

SELECT  employee_id FROM fnd_user WHERE user_name LIKE 'PLUI' 

SELECT * FROM per_all_people_f WHERE last_name ='ADAMS' and first_name ='JAMES'

SELECT * FROM per_all_people_f WHERE person_id in (37303)

SELECT job_id, person_id, position_id FROM per_all_assignments_f WHERE  person_id in (37303)

SELECT name job_name FROM per_jobs WHERE job_id = 10349

--  This query given a job function,will give the  approval group.
select control_group_name from po_control_groups where control_group_id in 
        (select control_group_id  from po_position_controls where end_date is not null and job_id in ( 
                select job_id from  per_jobs_vl where name like 'E7-G AND A' )  )

-- This query ,given a approval group, will give the  approval limits.
select * from po_control_rules where control_group_id  in 
    (select control_group_id from po_control_groups where control_group_name = 'NCE 10/CE 10')

-- This query given a job function name, will give the  approval limits.(Make sure you set the correct org_id before you check the amount limits.
select * from po_control_rules where control_group_id  in 
    (select control_group_id from po_control_groups where control_group_id in 
        (select control_group_id  from po_position_controls where end_date is not null and job_id in ( 
                select job_id from  per_jobs where name like 'E7-G AND A' )  
   )
  )

ORACLE APPLICATIONS FUNCTIONALITY

ORACLE APPLICATIONS FUNCTIONALITY.
/***************************************/

 Oracle applications is a integrated comprehensive suite of 
applications which includes a complete suite of products starting 
from marketing, SALES, service, telesales, order management, 
AP, AR, GL, Contracts etc.

The hierarchy in Oracle application is given by the  

         user 
           |
  responsibility 
  /  \
    menu    Request Groups
     |          |        \
function. Concurrent programs   Request Sets     | \
    Concurrent Request
 programs   Stages       | 
      Concurrent Programs


When a user enters into the oracle applications, the user is prompted to 
choose one of the responsibilities that are available. The typical 
responsibilities that are available are the system administrator, application 
developer and product specific responsibilities. Typically one responsibility 
corresponds to one product application. As an ex, a responsibility called 
"Marketing Intelligence Collection Manager" is created for accessing the 
Marketing Intelligence application product.  Once the user chooses this 
responsibility then all the functionality corresponding to that product 
comes up. That means he can run all the concurrent programs corresponding 
to that. Similarly a general ledger responsibility will have the functionality 
corresponding to that. A user can be given more than one responsibility from 
which he can choose when he logs in. System administrator is the greatest 
responsibility that a user can have. It gives complete access to the system. 

 Each responsibility is also tied to a request group. A request group 
is a set of concurrent programs. If a user has a responsibility which is tied 
to a request group, then that user can access/run all the concurrent programs 
that are in that request group. Optionally a request group can also be a 
collection of concurrent programs and request set. A request set is a convenient 
way of clubbing certain related concurrent programs into one unit, so that 
when the user runs that request set, all the concurrent programs mentioned
in that request set are run at once concurrently. This is very convenient 
for a customer who does not need to run individually each concurrent 
program. There is a request set wizard to create request sets. In a 
request set, we can optionally specify conditionally whether to continue 
with the next concurrent program if the first one succeeds or fails. 
We can also define the request stages in the request sets.
 As mentioned above in the diagram, a responsibility is tied 
to a single menu. A menu can have submenus. And a menu or submenu is 
tied to multiple functions. A function is typically a report 
(any kind of report like a html or oracle reports report). Hence if 
a user can access to a menu which has five submenu items then he 
can see five menus after he logs in and if he double clicks on each 
one of them, he sees the corresponding functions.  A sysadmin can 
typically define a function by specifying a function name, its 
parameter, the way of calling that function etc.

Now let us see what are the typical functions a user can do with 
the system administrator responsibility. Using sysadmin 
responsibility he can define new users, new responsibilities. He 
can also define profile values (site level or application level). 
He can create new concurrent programs which is done as follows. 
To define a concurrent program he first needs to create an 
executable. An executable is tied to a pl/sql program package 
(package.procedure). Once the executable is defined, the concurrent 
program is defined and this executable is tied to that. And when 
the user runs this concurrent program, the corresponding package 
procedure is executed on the server side. The user can also specify 
the parameters for this concurrent program at the time of definition. 
The user can define a value set. A value set is typically a lov 
(list of values). Once a value set is defined, when a parameter is 
created, the parameter can be associated with this value set, so 
that the user is restricted to those set of values.
  Now let us see the functions that a user can do 
using the application developer responsibility. An apps developer 
can define objects and regions. A region typically consists of 
various attributes and the corresponding values. And objects 
are typically queries. Using Apps Developer responsibility you 
can also define messages. Oracle Message dictionary provides 
nls compliant messages. Once defined all the developers can 
use the message by using the fnd_message.get_message and 
fnd_message.set_token api's. Typically when you can getmessage 
the message is fetched from the message dictionary and put on 
the message stack. And after that the tokens (which are defined 
as &) are replaced with the actual parameters.
Once all these are defined, when the product needs to be 
shipped to the customer, the developer needs to download certain 
files and send them. In oracle application, each kind of file 
does certain function. For ex a .pls file is a package or 
procedure. A .ldt file can be concurrent program, request set 
or request group. A .jlt file is  a region and a .slt file is 
a menu and .odf is a file downloaded from the case which 
constructs the schema.

The typical syntax for that is

FNDLOAD apps/apps@seed115 0 Y $FND_TOP/afcconfig.lct  REQUEST_GROUP bimcprg.ldt

FNDLOAD apps/apps@seed115 0 Y $FND_TOP/afcconfig.lct REQUEST_SET bimcprg.ldt

FNDLOAD apps/apps@seed115 0 Y $FND_TOP/afcconfig.lct  PROGRAM bimcprg.ldt

FNDLOAD apps/apps@seed115 0 Y $FND_TOP/afcconfig.lct  PROFILE  bimcprg.ldt

Lookup Values : 
 We can define lookups in Oracle Applications using the 
Application Developer responsibility. On the lookup screen we can 
define a lookup type, lookup code and its meaning. Once defined a 
lookup goes into the fnd_lookups base tables. Then optionally we can 
define a view on top of that table, which will give only our product 
values as,
  Select code,value from fnd_lookups where lookup_type ='AMS%'
Which will give only the AMS related lookup codes.  So while defining 
value sets we can also hit this view ams_lookups in that screen.

Ak region items Data

The Ak regions, ak_region_items is basically the metadata information 
about the UI components. As an ex, let us consider typical report which 
will have the title ,parameters, row data, labels. In this environment all 
these components are defined in the AK database. This will help us , in 
case if the product is installed in any other language then  those table 
data is converted into that particular language and the report is displayed 
in that particular language.

As mentioned earlier a responsibility is typically tied to a particular 
product functionality. Also a function is part of a product functionality 
that is registered under a unique name, for the purposes of assigning to a 
responsibility.

Form function (or form) a form function is a function which invokes a 
form and is called from a navigator window. Subfunction does not 
appear in a navigator window and cannot be navigated to.

As a responsibility will have a menu associated with it which in turn has 
certain functions or menus of functions.  Exclusion rules are those which 
admin will use to associate certain functions or menus of functions to a 
particular responsibility, and hence restrict the access as needed for that 
responsibility. If exclusion rules are not applied then a new menu 
hierarchy needs to be created.

Profile: A user profile is a set of changeable options which affect the 
way your application behaves. There are four types of profile options 
available and they are
 site level profile
 application level  profile
 responsibility level
 user level 
If the "find system profile Values " is set to Application,
The "find system profile Values " block gives all the system profile 
values pertaining to all the users corresponding to all the responsibilities 
of the application. The user level profile has the highest priority. As an 
ex we can have ams_marketing_calendar profile option set at the site 
level and get hte profiel value in the code using  
fnd_profile.get("AMS_MARKETING_CALENDAR");

Grouping concurrent requests: we know that the concurrent request can 
be grouped into a request groups andrequest group can be assigned to 
the request set. while creating request set we can create request stages 
which is again a groups of request.  All the requests in a request stage 
run simultaneously, however the stages as a whole run sequentially. 
Hence according to our requirement we can set certain request into 
stages and run them.
A request stage is successful only if all the requests in it are successful. 
We can also conditionally run the subsequent stage if the previous stage 
is successful.  Hence a request set consists of stages. We can end the 
request set after any stage by not specifying what to do in case of 
success, error or warning ie. if the stage is not linked to any other stage.
Also we can define a particular stage in the request set to be critical in 
which case the status of the request set is the status of the most recent 
critical stage. Also an end user can create request sets which are 
accessible to only him and are called private request sets.
After a set is defined we can assign a user to be owner of that request 
set. When a set is created, we can specify the owner or need not specify. 
if we do specify, then the owner of the set can run that request set (even 
if it is not included in the request group). Hence in summary if a user is 
assigned as owner to a concurrent program, he can either run or edit that 
concurrent program parameters etc.

It is possible to define a concurrent program (using the concurrent  
program form) to be incompatible with a particular concurrent program 
or request set, then that concurrent  program will not simultaneously run 
when the request set is running. You can also define a concurrent 
program to be incompatible with itself meaning two instances of that 
program should not be run simultaneously. If there is such 
incompatibility and if the user has requested a concurrent program to be 
started (and if there is already an incompatible concurrent program is 
running), then the user program is run immediately after the 
incompatible program is completed.

It is possible to define shared parameters define only once for a program 
is a set and it is accessible for all the programs in the set.

Some changes that we make are made immediately available and some 
changes require choosing a different user or responsibility.

Hitherto we have been seeing the standard submission request form of 
the oracle apps. Typically wehen define the execs and concurrent 
programs, they go into the tables which are given below.(AOL tables) 
fnd_concurrent_requests,fnd_run_requests, 
fnd_conc_request_arguments, fnd_conc_stat_summary etc typically if a 
concurrent program writes to a file using the fnd_file.put_line api, then 
the log file is generated whose name is l.req.

We can access the concurrent manager administration screen when v log 
in using the sys admin responsibility. 
We can also start the concurrent manager from the unix command 
prompt as, by setting certain env, 
   say for ex we can start the concurrent manager on saldbg database, 
then
$ relenv code saldbg /afrz/cmrsal 
$ ps -ef  | grep LIBR 
 $ asu -applrt 
 $ startmgr


Some questions: Preserving the custom menu across the upgrades. : 
When we are upgrading the OA menus they might get overwritten. A 
good idea is to reattach a custom menu to an already existent after the 
upgrade.

Stage function parameters window.  ?




Oracle Applications Concurrent program Coding standards.


When we start coding any oracle concurrent program i.e a package spec and body (s.pls and b.pls) , it is necessary we need to follow certain standards. Let us start from the package.procedure from where the user runs the concurrent program. In that concurrent program, we definitely need to have two out variables ERRBUF and ERRCODE. Depending up on the value of the ERRCODE value the concurrent manager will decide this particular concurrent program has errored out or succeeded. A value of 2 for ERRCODE indicates that the concurrent program has failed. And a value of 1 indicates that it is a warning and a value of 0 indicates that it is a success. Hence this is a very important point to consider.  In turn this top level program can call a number of other package.procedures in its script. Each procedure should have certain IN and OUT parameters. The IN parameters are p_api_version_number, p_init_msg_list, p_validation_level, p_commit and the OUT parameters  x_msg_count, x_msg_data and x_return_status.  Now after the api is called, we need to put some code to check whether the api call was successful  which is given below. 

                 IF    x_return_status = FND_API.g_ret_sts_error
                 THEN
                       RAISE FND_API.g_exc_error;
                 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
                       RAISE FND_API.g_exc_unexpected_error;
                 END IF;

And at the end of that particular procedure we need to put the following logic, (so that if there is any problem with the above called procedure the control jumps to one of the following exceptions)

EXCEPTION

   WHEN FND_API.G_EXC_ERROR THEN
     x_return_status := FND_API.G_RET_STS_ERROR;
     -- Standard call to get message count and if count=1, get the message
     FND_msg_PUB.Count_And_Get (
          --  p_encoded => FND_API.G_FALSE,
            p_count   => x_msg_count,
            p_data    => x_msg_data
     );
 errcode = 2;


   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN

     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
     -- Standard call to get message count and if count=1, get the message
     FND_msg_PUB.Count_And_Get (
            --p_encoded => FND_API.G_FALSE,
            p_count => x_msg_count,
            p_data  => x_msg_data
     );
 errcode = 2;

   WHEN OTHERS THEN
     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
     IF FND_msg_PUB.Check_msg_Level (         FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
     THEN
        FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
     END IF;
     -- Standard call to get message count and if count=1, get the message
     FND_msg_PUB.Count_And_Get (
           -- p_encoded => FND_API.G_FALSE,
            p_count => x_msg_count,
            p_data  => x_msg_data
     );
 errcode = sqlcode;

END POPULATE;

Note that in the exceptions we are setting the errcode to 2 if the api raise either expected or unexpected errors. Apart from api calls raising the exceptions , we can also raise these exceptions, when some error happens.

Flexfields 
A flex field is made up of subfields or segments.  There are two types of flex fields which are key flexfields and descriptive flexfields.

A key flex field consists appears on a form just like a normal text field. But when you click on it, it will open into a window consisting of sub fields or segments. Here each segment has a name and a list of values (or value set). An ex of a key flex field is a inventory part which can segments like the color, size, shape, weight etc. Hence if there is a part number in a particular form. That is this particular part is identified by that value(combination of values). 
Also consider if a part has a flexfield structure f1+f2+f3 , another org can have the same part as f2+f3+f1. Or it can have any number of segments and if the form does not have space to store this, it will provide scrollbars for accommodating this flexfields.

A descriptive fields appears as a one character field with no prompt as[], but when you click on it, it will open into a window with appropriate prompts and segments making up of those fields. It just provides some customizable expansion space.[] They display context sensitive information corresponding to the entered item value.
Descriptive flex fields have two different types of segments, global and context sensitive.
A global segment is a segment that always appears in the descriptive flexfield popup window regardless of the context while a context sensitive segment is a segment that may or may not appear depending upon the context field (reference field)

Hence the flex fields help us in customizing our application and they help us in representing the accounting codes, product codes, item codes with additional programming involved.


Flex field structure
 
  S1  S2 S3 S4 S5

Each segment is represented in the database table as a column. Hence just by jumbling the above segments we get a different structure of the flexfield. Hence we can define more than one structure at a time.

Defining flex fields

 Define value sets for each segment in the flexfield
 Defining the segment structures :prompts,order,sizes)
 define the values for the segments
 define the additional features

 
Combination : A combination is basically a complete set of segment 
values or complete code.A combinations table is basically a key flexfield table which 
stores this kind of complete
codes  and unique id for each such code combination. However the 
other tables refer tot his unique id rather 
than the entire the code.
Qualifier : when you define a flexfield structure, each segment 
 in that configuration is given a particular 
qualifier. This is because a flexfield structure can be represented 
 in any structure (ie S1 S4 S3 S2 S5), 
then we know that the second qualifier is in fourth position currently.

Hence for the already existing flexfield we can define a new structure

When you are capturing key info then typically you would be using a
 key flexfield, and if you are capturing some optional information 
they you would use descriptive flexfield.

MULTI-ORG Concept and Business Groups etc 
/***************************************/
The typical organization hierarchy in Oracle Apps is
  Business group => Legal Entity  => Operating Unit => Inventory Org

We can define each of these things using the responsibility Oracle HRMS Manager, 
 in the work Structures =>  Organization =>  Description.

However if you do not have more than one business group, then 
there is no need to create a business group as Oracle Applications 
provides already predefined business group by name "Setup Business Group".

Now once again,go to the Oracle HRMS manager responsibility and create 
the organizations using the menu,  
 work Structures =>  Organization => Description.
and for each organization, mention the type of organization either 
as legal entity, operating unit and inventory organization. Also we 
can define a single organization and classify as all of them
(legal entity, operating unit and inventory organization ) together.

The UI is being felt as tough here. The sequence of steps that is
followed here are
 create an organization and assign a location to that organization 
 (that you have created before)
 Save this organization
 Classify this organization as legal entity,operating unit or inventory organization.
 Enable this organization , by checking the enable check box and then save it.
 Once enabled, if we try to quit this form, 
 if the organization is legal entity, the system will ask 
for the employer information
 if the organization is operating unit, the system will ask for the legal entity and SET OF BOOKS information.

Hence first create an hierarchy of organizations create as many operating 
units and inventory orgs as has been designed and assigned them to 
correct legal entities and set of books.


MULTIORG SUPPORT :
---------------
The ability to divide a complex organization into a hierarchical 
structure of organization is the idea behind the multiorg structure.
Using oracle apps, we can define the following levels
 Define accounting set of books
A set of books is a functional currency, chart of accounts 
(account structure to record the transactions) and a accounting calendar.
 Define business groups.
Business group is the highest level in the hierarchy of the multi org. 
Employee data is maintained in this group or the business group 
secures the HR information.
 Legal entities
  You prepare tax information for this kind of entity.
 Operating units : or a business unit like Accounting, purchasing etc.
 Inventory orgs : These are the inventory units.

The above hierarchical structure needs to be done after we have 
decided upon which org entities in our org are participating in 
multi-org structure.
First create a set of books account. Once that is done create a 
business group. And then associate this particular business group 
with a responsibility. And then enter the system using that 
particular responsibility. After entering thru this responsibility, 
create the legal entity, when you create the legal entity this 
entity is automatically attached to that set of books.  And then 
create operating units (business units). When you set the profile 
value MO:Operating unit to this particular value , then we you 
login again  thru a responsibility, this profile value is read  
and you will see the info corresponding to that operating unit. 
And then run adadmin.
Typically let us see why a business organization decides to go 
with the multi org concept. This is because if there are multiple 
groups in an org, the people in one org should not be able to see 
the data in other groups which means that every table or data 
structure should be striped by the org_id or every table should 
have org_id as a column. How that is implemented is as follows.

Initially let us say we have a table by name ams_abc.
1. Then we create a table with the name having (_all) i.e ams_abc_all which will have the data corresponding to all the operating units or all organizations. 
2. Then we create a view on top of this table using the query given below.
select * from 1;
  
  SELECT distinct aps.customer_trx_id
  FROM   ar_payment_schedules_all aps
  WHERE  aps.class = 'INV'
  AND    aps.status = 'OP'
  AND    aps.amount_due_remaining < 0
  and    aps.customer_trx_id = 2800242
  AND    EXISTS
      (SELECT 1 FROM ar_payment_schedules_all aps2
       WHERE  aps2.customer_trx_id = 2800242
  AND    aps2.amount_due_remaining > 0 );
  
select selected_for_receipt_batch_id from ar_payment_schedules_all
 where trx_number = '10996'

/*Close Other Windows Disabled 
In Sysadmin Nav Path Security->Responsibility->Define
Query up your PA Reponsibilty, and in the "Menu Exclusion" tab, insert the following line
Type = "Function"
Name = "Navigator: Disable Multiform" 
*/


--CHECK LIST : WHAT ARE THE TYPICAL CHECKS THAT YOU MAKE IN AN ORGANIZATION. 

  --What kind of business they do,is this a Merchandizing business, or a 
     Service business.
  
  /*How many set of books they have,do they reporting set of books as well
    (is it a Multiple country business)*/
  
  select  * from gl_sets_of_books
  -- where mrc_sob_type_code ='N'
  
  /*How many operating units and inventory orgs they have(usually companies have 
    just one each) and multiple subinventories.
    (Interesting in Oracle applications, we do not have a way to see ,given the set 
    of books, how many legal entities,OU,IO's from the forms. But given 
    an Operating unit or IO, we can get to know what is the SOB,LE,OU or IO. 
    Hence the only way to know that heirarchy is to simply run the queries below).*/
  
    select   org_information1  set_of_books_id
      ,org_information2  legal_entity_id
      ,org_information3  operating_unit_id
      ,hoi.organization_id  Inventory_organization_id
      ,org_information4
      ,haou.name
    from   hr_organization_information hoi
       ,hr_all_organization_units  haou
    where   1=1  
    and    hoi.organization_id  = haou.organization_id 
    and    org_information_context ='Accounting Information'
    and    hoi.org_information1 = 1001
  
  Infact there can many set of books even with in US, depending up on the 
  accounting calendar, currency.
   
  Do they use the budgetary control,which let you create the encumbrances from different 
     modules and allows you to funds  checks.
  Do they have MRC (multiple reporting currencies) and where is this set.
  
  What kind of Accounting Methods do they use in the feeder systems like AP, AR etc.
     i.e Cash or Accrual. Mostly this is Accrual.

   The profile option MO: Operating Unit will always the store the 
 operating unit id (and not the inventory_org id)
   The Profile option MO:Top Operating Level stores the values 
 of set of books, legal entity or operating unit. 

   If it is SOB, user can run the report with any LE underneath that sob
   if it is LE,  user can run the report with any OU underneath that LE
   if it is OU,  user can run the report with any OU mentioned in the 
 MO: Operating Unit profile option.
      There are certain predefined oracle app reports which we can run 
 with above mentioned reporting levels.
    Ex : Aging 4 Buckets report.*/
  
 begin
 dbms_output.put_line(fnd_profile.value('ORG_ID'));
 end;

 /* First get to know what is your set of books id. Go to the 
 profile option,"GL Set of Books Name". find the set of 
 books id and name */

  select * from gl_sets_of_books 
  where name = fnd_profile.value('GL_SET_OF_BKS_NAME')

 -- Then you can get to know what is the organization details for your operating unit.
    select organization_id ,name from hr_organization_units
 where organization_id = 
        (select  profile_option_value 
        from    fnd_profile_option_values
        where  profile_option_id
        = (select profile_option_id from fnd_profile_options
           where   profile_option_name = 
    (select DISTINCT profile_option_name 
     from fnd_profile_options_tl
     where user_profile_option_name = 'MO: Operating Unit')
      )
        and  application_id = 0 
        and  level_id = 10001
 )
     
   /* Hr_organization_information table stores two classes of information 
     If org_information_context = 'CLASS', then it gives information 
 about the org itself.
         i.e  whether it is a Legal Entity,Operating Unit, Inventory Organization.
     Interestingly ,the below query will give you info about 
 everything other than set of books id,which makes sense 
 as SOB is accounting stuff.
    */
       select    hoi.organization_id
             ,hoi.org_information_context
       ,haou.name Name,hoi.*
       from  hr_organization_information hoi
       ,hr_all_organization_units  haou
       where hoi.organization_id =728
       and   hoi.organization_id  = haou.organization_id 
       and   org_information_context = 'CLASS'
      
   /* 
   If org_information_context = 'Accounting Information',it provides 
   information on how it is related to other Orgs. Given a specific 
   organization id,get a list of parents i.e set of books,legal entity 
   and operating unit, run it.
   If the org_information_context is Accounting Information, the 
   org_information1 will store the set of books id and org_information3 
    will give the inventory org id. Hence given the set of books id we 
   can get all the heirarchy in one particular record as below. 
  */     
  
 select     org_information1  set_of_books_id
   ,org_information2  legal_entity_id
   ,org_information3  operating_unit_id
   ,hoi.organization_id  Inventory_organization_id
   ,org_information4
   ,haou.name
 from   hr_organization_information hoi
   ,hr_all_organization_units  haou
 where 1=1  
--  and  org_information1= 1  -- this isthe sob id
 and    hoi.organization_id  = haou.organization_id 
 and    org_information_context ='Accounting Information'
and    hoi.org_information1 = 1001

--   What products are installed in the instance

 
      SELECT /*+ USE_NL (A B I) */
       DISTINCT a.application_id, a.application_name,
                b.application_short_name, b.basepath, i.product_version
    ,i.patch_level,i.install_group_num
           FROM fnd_application_tl a,
                fnd_application b,
                fnd_product_installations i
          WHERE a.application_id = i.application_id(+)
            AND a.application_id = b.application_id
            AND a.LANGUAGE = USERENV ('LANG')
  ORDER BY 2




  select * from hr_all_organization_units --where organization_id = 44
  where  name like '%Aus%'
  
  select * from hr_all_organization_units_tl where organization_id = 3608
  
-- If we enable trace from the concurrent requests definition form,  
then the trace file will be created
-- with the oracle process id value as part of its file name.
select oracle_process_id, oracle_session_id, os_process_id, request_id
from fnd_concurrent_requests
where request_id = 4330105
 

-- KFF's & DFF's:
/****************
In the case of Descriptive Flexfields(DFFs),they basically give 
the non-mandatory information (and usually descriptive)
and also they go into the non primary key columns in the 
underlying database tables.
Importantly , they consist of mandatory and optional columns. 
Every DFF will have a code called Global data Elements which will 
consist of certain columns. These are mandatory columns and will 
always show up in the DFF. Apart from it, we can also have 
other user-defined codes and the segments corresponding to it.
So for ex, if "Global data Elements" is defined to have say 3 columns.

and we have defined two more codes as Code1 which has  say 5 
attributes and Code2 which has  say, 6 attributes.
           
Now having defined this, when we go to the from and click on this 
DFF, it will open up and shows the 3 mandatory columns and another
 column which say "Context value". Now for this if we choose Code1,
 it will immediately show
5 more columns in that DFF, making it totally 3 + 5 = 8 columns.
           
Or if we choose Code2, it will immediately show 6 more columns in 
that DFF, making it totally 3 + 6 = 9 columns.

Usually at the table level, this data goes into the attribute 
columns ,with the category column determining
the context. For ex, 
*/

  select interface_header_context, interface_header_attribute1,
 interface_header_attribute2 -- etc
  from    ra_customer_trx_all
  where  interface_header_context = 'VPS-PP SETUP'

/*In the case of Key Flexfield(KFFs) also, we can define as many 
codes as we want for a particular flexfield.However here the user
 is not given the option of choosing which code he wants. Usually 
this is done by a profile
option/System option which decides which particular code is used. 
As an ex, consider the KFF "Accounting Flexfield".,here we can 
define as many codes as we want. However when we define the Set 
of books, we choose which accounting flexfield we want. Once this 
is done, we define the accounting values based on that accounting 
flexfield structure. 

Similarly in the case of KFF, "Sales Tax Location Flexfield", many
 codes are defined like

    Province, city
    state, city
    state,county,city
  And a profile option or a high level System option will determine
which of the above code structures we are going to use (and not the user).
So in the case of AR (Receivables Application), if we go to 
the System Options,*/
 Setup => System => System Options 
 => Tax Tab => Location Flexfield structure(State.County.City)


/*
This actually forms the part of the primary key of the underlying 
table and hence once chosen, we cannot change the option. (However 
we change the context value option and different records will have 
different values).

Apart from all these Item Categories is a different kind of KFF 
and will be explained, this can be found from the Inventory queries.


--Difference between Lookups and Profile Values : 
/*********************************************
 Difference between lookups (more than one value) and profile values.

In the case of Profiles, for a particular profile option, we can have 
a list of values to choose from, but however we only set one value to 
that option. Hence at any point of time, that option will be set to 
a particular value.
For ex, consider the profile option,"GL: Set Of Books" can be set to 
a value of 'US', even though we define multiple set of books like 
AUD,JPN etc.
*/ 

 select * from fnd_profile_option_values

/*
In the case of Lookups, for a particular lookup , we can have may values
active at a particular time. For ex,we can define a lookup say 
crm_campaign_status which can have values like 'NEW', 'PLANNED', 'IN PROGRESS',
'COMPLETED' etc.  We can define as many values for a particular lookup 
and enable and disable a few of them as we need.  But at the time of 
using this lookup, the user may choose only one particular value or more.
*/ 
  select * from ar_lookups

  select * from qp_lookups where lookup_code = 'AGR'

--VALUE SETS :
-------------
Value Set Defn : Typically a value set can be defined as an independent 
list, a LOV based on a table etc. However we can also have a valueset 
which is a DFF. 
Step 1 : Typically a value set can be defined from 
       Setup => application => values
Step 2 : To define a valueset as a DFF, we can go to the DFF definition 
of segments and query for the DFF title as "Flexfield%" and in the value 
below, query by the name created above in the step 1. Once you pull up the 
valueset, we can define the segments corresponding to that flexfield.


Business Group Id and Operating Unit Id :
/***************************************/
Per my understanding,both are used for similar purpose but used by
different modules.
Business groups(business_group_id) are used in HRMS and most(rest!) 
of the other modules use operating unit(org_id), to segregate data visibility. 












Oracle Financials : 

Why not all the flex field structures are stored in a code 
combinations table
• where do u define that the current value of the set of books.(i.e 
what is the current account structure)
• when using the payables we enter the invoices and payments, and in 
receivables we have receipts or cash ,but 
 there is not journal id or batch number  but how they are correlated 
in the general ledger. so we can say that 
 the invoice ,payments,receipts are the actual journals.
• how can we see what is imported

Oracle applications will give predefined flex fields in which 
accounting flexfield is most important (Accounting 
Flexfield is the title) with code = GL#

Now you have to create flexfield structure for ur company based on that 
accounting flexfield by giving a code 
,title etc,(setup,financials,flexfields,key,segments)
Do not freeze the flexfield yet
Define the segments.
 Define the 1st segment. before this also define a value set using 
sysadmin resp and   attach that to this
 Define the 2nd segment. before this also define a value set using 
sysadmin resp and attach that to this
 Define the 3rd segment. before this also define a value set using 
sysadmin resp and attach that to this
 Define the 4th segment. before this also define a value set using 
sysadmin resp and attach that to this
 Define the 5th segment. before this also define a value set using 
sysadmin resp and attach that to this.
Freeze the flexfield definition.
Compile the flexfield. This will generate a concurrent program and will 
be completed by itself.

For the Accounting Flexfield, when it was created which was part of the 
seed data the underlying table defined was 
gl_code_combinations. Likewise for other flexfields we have different 
code combination tables. Hence we choose a 
particular flexfield, we are entering the data, in effect, into a 
particular code combination table.

Hence when all the value sets, segments are properly defined, then the 
flexfield is successfully compiled. So we have
 successfully defined an accounting flexfield structure.
 Actually we can also define the values for each of the segment of the 
flexfield.(This is done at setup,financials,
flexfields,key,values)


  
  
 --- Alerts are of two kinds Periodic and Event driven.
 select * from wf_deferred
 
Periodic alerts are those for which you can set a periodicity like 
on demand,everyweek etc. Event driven alerts basically create a 
trigger on the underlying table ,so that when something like insert 
or update happens they trigger and send an email.