PO Matching Report

SELECT
      PHA.TYPE_LOOKUP_CODE,
      PHA.LAST_UPDATE_DATE,
      PHA.SEGMENT1,
      PHA.SUMMARY_FLAG,
      PHA.ENABLED_FLAG,
      PHA.APPROVED_FLAG,
      PLA.UNIT_MEAS_LOOKUP_CODE,    
      PLA.QUANTITY,
      PLA.UNIT_PRICE,
      PLA.LIST_PRICE_PER_UNIT,
      PLA.PRICE_TYPE_LOOKUP_CODE,
      PLL.RECEIPT_REQUIRED_FLAG,
      PLL.INSPECTION_REQUIRED_FLAG,decode
         (INSPECTION_REQUIRED_FLAG||RECEIPT_REQUIRED_FLAG,'NN','2-Way','NY','3-Way','YY','4-Way','Not Specified')  
                              Matching,      
      PLL.ENCUMBERED_FLAG,
      PDA.QUANTITY_ORDERED,    
      PDA.ACCRUAL_ACCOUNT_ID,
      PDA.VARIANCE_ACCOUNT_ID
 FROM
       PO_HEADERS_ALL PHA,
       PO_LINES_ALL PLA,
       PO_LINE_LOCATIONS_ALL PLL,
       PO_DISTRIBUTIONS_ALL PDA
 WHERE
        PDA.PO_HEADER_ID=PLL.PO_HEADER_ID
AND          
       PLL.PO_HEADER_ID=PLA.PO_HEADER_ID
AND
       PLA.PO_HEADER_ID=PDA.PO_HEADER_ID
AND         
       PLA.UNIT_PRICE>1000000000
AND
      decode
(INSPECTION_REQUIRED_FLAG||RECEIPT_REQUIRED_FLAG,'NN','2-Way','NY','3-Way','YY','4-Way','Not Specified')=:MATCH LEVEL


Ur's
AmarAlam

Pending Purchase Orders Report Code

select sysdate REP_DATE,
pv.SEGMENT1 VENDOR_NUM,
ph.segment1 PO_NUM,
PH.REVISION_NUM,
ph.type_lookup_code PO_TYPE,
pol.ITEM_DESCRIPTION,
pol.UNIT_MEAS_LOOKUP_CODE,
pol.UNIT_PRICE,hr.name,
pds.QUANTITY_ORDERED,
PDS.QUANTITY_CANCELLED,
pds.QUANTITY_DELIVERED,
pds.QUANTITY_BILLED,
pds.AMOUNT_BILLED,
pol.CANCEL_DATE,
pds.QUANTITY_ORDERED-pds.QUANTITY_DELIVERED REMANING_QTY,
pv.vendor_name Supplier,
PH.AUTHORIZATION_STATUS STATUS,
PH.CREATION_DATE po_date
from po_headers_all ph,
po_lines_all pol,
po_distributions_all pds,
HR_OPERATING_UNITS hr,
po_vendors pv
where ph.PO_HEADER_ID=pol.PO_HEADER_ID
and ph.PO_HEADER_ID=pds.PO_HEADER_ID
and ph.VENDOR_ID=pv.VENDOR_ID
and hr.ORGANIZATION_ID=ph.ORG_ID
AND PdS.QUANTITY_ORDERED<>PdS.QUANTITY_DELIVERED
AND ph.TYPE_LOOKUP_CODE<>'RFQ'
AND ph.TYPE_LOOKUP_CODE<>'QUOTATION'
AND PH.AUTHORIZATION_STATUS='APPROVED'
and hr.NAME=:org
and trunc(ph.creation_date)=trunc(:dt)
and to_char(ph.creation_date,initcap('mon')||'-'||'yy')=:mon
and trunc(ph.creation_date)  between trunc(:inp) - 7   and trunc(:inp)


Ur's
AmarAlam

R12 Fetures and Tables

R12 Feaututes :
--------------
1.MOAC(Multiorg Access Control) :
  ------------------------------
  By Using Single Responsibility we can be able to access
  multiple operating units at a time.
2.Mutiorg views were replaced with MOAC Based synonyms.
3.MO:Security Profile is a new profile added in R12.
4.VPD(Virtual Private Database) System will take care of
  Data security in R12

5.Org Initialisation process in R12 :
  ----------------------------------
   begin
     mo_global.set_policy_context('S','204');
   end;

  MOAC Synonym Initialisation :
  ----------------------------
   begin
     FND_GLOBAL.APPS_INTILISE('APPLICATION_ID','RESPONSIBILTY_ID,'USER_ID');
     MO_GLOBAL.INIT('SQLAP'); -- Application Short Name
   end;

  Modulewise Changes in R12 :
  --------------------------
  General Ledger :
  --------------
  1. Subledger Accounting module was added in  R12.
  2. gl_sets_of_books table was replaced with gl_ledgers
     and gl_ledger_Sets.
  3. In Addition to Currency,Calendar,Chart of accounts,
     Convention was added in R12.

   Accounts Payable :
   ----------------
   1.ap_invoice_lines_all table added in R12.
   2.ap_invoice_distributions_all table populates the data
     when ever invoice gets accounted.
   3.Supplier form was converted from form based solution to
     webbased solution.
   4.po_vendors tables replaced with ap_suppliers tables.
   5.Supplier and customer information was defined together
     Under TCA(Trading Community Architecture).
   6.Accounting Tables were modified.
   7.Both supplier and customer bank information was defined
     under payments(New application in R12) Application.
   
      11i Table               R12 Table
      -----------             ------------
      po_vendors              ap_suppliers
      po_vendor_sites_all     ap_supplier_sites_all
      po_vendor_contacts      ap_supplier_contacts
      ap_banks                ce_banks
      ap_bank_branches        ce_bank_branches
      ap_ae_headers_all       xla_ae_headers
      ap_ae_lines_all         xla_ae_lines
      ap_ae_accounting_events xla_events

Order Management  :
-----------------
  Move Order Fautures were added in R12.
  ra_customers ,ra_site_uses_all ,ra_adderesses_all
  views were removed in R12.
  in place of ra_customers,ra_site_uses_all,ra_addersses_all
  views need to use the below mentioned base tables.
 
    hz_cust_accounts
    hz_parties
    hz_cust_site_uses_all
    hz_cust_acct_sites_all
    hz_party_sites
    hz_locations

Ur's

AmarAlam

Important TCA API's

Ur's
AmarAlam

Order to Cash Cycle - Tables get Affected @ Each Step

1) Order Entry
------------------------------------
oe_order_headers_all.flow_status_code = 'ENTERED'
oe_order_headers_all.booked_flag = 'N'

oe_order_lines_all.flow_status_code = 'ENTERED'
oe_order_lines_all.booked_flag = 'N'
oe_order_lines_all.open_flag = 'Y'

2) Order Booking
------------------------------------
oe_order_headers_all.flow_status_code = 'BOOKED'
oe_order_headers_all.booked_flag = 'Y'

oe_order_lines_all.flow_status_code = 'AWAITING SHIPPING'
oe_order_lines_all.booked_flag = 'Y'

wsh_delivery_details.released_status = 'R' (ready to release)

wsh_delivery_assignments.delivery_id = BLANK

3) Reservation
------------------------------------
mtl_demand
mtl_reservations

4) Pick Release
------------------------------------
wsh_new_deliveries.status_code = 'OP'
wsh_delivery_assignments.delivery_id gets assigned
wsh_delivery_details.released_status = 'S' (submitted for release)
mtl_txn_request_headers
mtl_txn_request_lines
mtl_material_transactions_temp

5) Pick Confirm
------------------------------------
oe_order_lines_all.flow_status_code = 'PICKED'
mtl_material_transactions
wsh_delivery_details.released_status = 'Y' (Released)
mtl_onhand_quantities

6) Ship Confirm
------------------------------------
Data will be removed from wsh_new_deliveries
oe_order_lines_all.flow_status_code = 'SHIPPED'
wsh_delivery_details.released_status = 'C' (Shipped)
wsh_serial_numbers
data will be deleted from mtl_demand and mtl_reservations
item qty gets deducted from mtl_onhand_quantities

7) Enter Invoice
------------------------------------
After shipping the order, workflow backgroud engine picks up records and post it to
ra_interface_lines_all
Auto invoice program picks up records from interface table and insert them into
ra_customer_trx_all (trx_number is invoice number)
ra_customer_trx_lines_all (line_attribute_1 = Order number and line_attribute_6 = Order line id)

8) Complete Line
------------------------------------
oe_order_lines_all.flow_status_code = 'SHIPPED'
oe_order_lines_all.open_flag = 'N'

9) Close Order
------------------------------------
oe_order_headers_all.flow_status_code = 'CLOSED'
oe_order_lines_all.flow_status_code = 'CLOSED'
oe_order_lines_all.open_flag = 'N'

10) Create Receipt
------------------------------------
AR_CASH_RECEIPTS_ALL

11) Transfer to General Ledger
------------------------------------
GL_INTERFACE

12) Journal Import
------------------------------------
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES

13) Posting
------------------------------------
GL_BALANCES


Ur's
AmarAlam

Finding Responsibility of a Form

Below is the Query, which helps you to find all the responsibilities of a Form, to which it has been attached.

**********************************************************************************************
SELECT   FORMS.form_name,
         FORMSTL.user_form_name,
         RESTL.responsibility_name,
         FORMSTL.language
  FROM   fnd_form FORMS,
         fnd_form_tl FORMSTL,
         fnd_form_functions FUNC,
         fnd_menu_entries MENU,
         fnd_responsibility RES,
         fnd_responsibility_tl RESTL
 WHERE       FORMSTL.user_form_name LIKE 'CUM Workbench%'
         AND FORMS.form_id = FORMSTL.form_id
         AND FUNC.form_id = FORMS.form_id
         AND MENU.function_id = FUNC.function_id
         AND RES.menu_id(+) = MENU.menu_id
         AND RES.responsibility_id = RESTL.responsibility_id(+)


Ur's
AmarAlam

On-Hand quantity details as per Oracle Form

Below is the PL/SQL, which gives you the On-hand Quantity details as per oracle form.

Oracle form show details like On-Hand Quantity, Available to reserve, Quantity Reserved,Quantity Suggested, Available to Transact and Available to Reserve.

All These details can be fetched using API => inv_quantity_tree_pub.query_quantities

****************************************************************************
DECLARE
   x_return_status         VARCHAR2 (50);
   x_msg_count             VARCHAR2 (50);
   x_msg_data              VARCHAR2 (50);
   v_item_id               NUMBER;
   v_organization_id       NUMBER;
   v_qoh                   NUMBER;
   v_rqoh                  NUMBER;
   v_atr                   NUMBER;
   v_att                   NUMBER;
   v_qr                    NUMBER;
   v_qs                    NUMBER;
   v_lot_control_code      BOOLEAN;
   v_serial_control_code   BOOLEAN;
BEGIN
   SELECT   inventory_item_id, mp.organization_id
     INTO   v_item_id, v_organization_id
     FROM   mtl_system_items_b msib, mtl_parameters mp
    WHERE       segment1 = :item_number
            AND msib.organization_id = mp.organization_id
            AND mp.organization_code = :organization_code;


   v_qoh := NULL;
   v_rqoh := NULL;
   v_atr := NULL;
   v_lot_control_code := FALSE;
   v_serial_control_code := FALSE;


   fnd_client_info.set_org_context (1);


   inv_quantity_tree_pub.query_quantities (
      p_api_version_number           => 1.0,
      p_init_msg_lst                        => 'F',
      x_return_status                      => x_return_status,
      x_msg_count                         => x_msg_count,
      x_msg_data                           => x_msg_data,
      p_organization_id                  => v_organization_id,
      p_inventory_item_id              => v_item_id,
      p_tree_mode                         => apps.inv_quantity_tree_pub.g_transaction_mode,
      p_is_revision_control             => FALSE,
      p_is_lot_control                     => v_lot_control_code,
      p_is_serial_control                 => v_serial_control_code,
      p_revision                              => NULL,                          -- p_revision,
      p_lot_number                        => NULL,                        -- p_lot_number,
      p_lot_expiration_date            => SYSDATE,
      p_subinventory_code            => NULL,                 -- p_subinventory_code,
      p_locator_id                         => NULL,                        -- p_locator_id,
      p_onhand_source                 => 3,
      x_qoh                                   => v_qoh,                    -- Quantity on-hand
      x_rqoh                                  => v_rqoh,         --reservable quantity on-hand
      x_qr                                     => v_qr,
      x_qs                                     => v_qs,
      x_att                                     => v_att,               -- available to transact
      x_atr                                    => v_atr                 -- available to reserve
   );


   DBMS_OUTPUT.put_line ('On-Hand Quantity: ' || v_qoh);
   DBMS_OUTPUT.put_line ('Available to reserve: ' || v_atr);
   DBMS_OUTPUT.put_line ('Quantity Reserved: ' || v_qr);
   DBMS_OUTPUT.put_line ('Quantity Suggested: ' || v_qs);
   DBMS_OUTPUT.put_line ('Available to Transact: ' || v_att);
   DBMS_OUTPUT.put_line ('Available to Reserve: ' || v_atr);


EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);
END;


****************************************************************************


Ur's
AmarAlam