Submitting the concurrent program from Tools menu using form personalization

Wednesday, August 8, 2012 by Ajay Atre


Submitting the concurrent program from tools menu using form personalization

Requirement
You have an invoice form opened with a particular invoice number and wanted to print the particular invoice from the same from instead of submitting the report request separately. This can be achieved using form personalization and user can able to submit the print program directly from the tools menu.
The solution can be applied at various places like printing a sales order, purchase order etc.
 
In this example I am putting up the form personalization on invoice transaction form.
Step#1
Switch to Receivables super user responsibility
N> transactions->transactions
Then Click on Help from toolbar Help-> diagnostics-> custom code-> personalize
I am rendering Menu first, let’s say Print selected invoice

Then click on actions

 
 
Once done save the changes. Come back to personalization form and add menu actions



And click on the action




Here I am setting up the global variable with value and the value is the string construct by which we pass the parameters to a concurrent program so make sure you pass values correctly.
So this is my actual string constuct
=select 'Transaction Number:::'||${item.TGW_HEADER.TRX_NUMBER_MIR.value}||':'||${item.TGW_HEADER.TRX_NUMBER_MIR.value}||'::::::No:No:' from dual
When you submit the form the sting will pass on the value to the concurrent report as 


 
Once done add new action as built-in and select the type as Launch SRS Form and pass the invoice printing program name there.

Step#2
Add personalization on FND_FNDRSRUN form (request submission form)

 
 
Click on action and pass on the same global variable we defined in earlier stages

 

Save the personalization.

And come back to invoice form query an invoice number and click on tools and click on the new menu you have added (print selected invoice). Once you clicked on the menu the concurrent request submission form will get opened and you can check the parameters are being passed correctly if not please check the SQL which we have built in earlier stage.






Filed under having 11 comments

PO SQL Queries

Tuesday, August 7, 2012 by Ajay Atre

 SQL Queries
 -- +------------------------------------------------------------+
 -- + customer TCA & site details based on order number
 -- +------------------------------------------------------------+
SELECT ooh.order_number
      ,hp_bill.party_name
      ,    hl_ship.address1
        || DECODE (hl_ship.address2, NULL, '', CHR (10))
        || hl_ship.address2
        || DECODE (hl_ship.address3, NULL, '', CHR (10))
        || hl_ship.address3
        || DECODE (hl_ship.address4, NULL, '', CHR (10))
        || hl_ship.address4
        || DECODE (hl_ship.city, NULL, '', CHR (10))
        || hl_ship.city
        || DECODE (hl_ship.state, NULL, '', ',')
        || hl_ship.state
        || DECODE (hl_ship.postal_code, '', ',')
        || hl_ship.postal_code ship_to_address
      ,    hl_bill.address1
        || DECODE (hl_bill.address2, NULL, '', CHR (10))
        || hl_bill.address2
        || DECODE (hl_bill.address3, NULL, '', CHR (10))
        || hl_bill.address3
        || DECODE (hl_bill.address4, NULL, '', CHR (10))
        || hl_bill.address4
        || DECODE (hl_bill.city, NULL, '', CHR (10))
        || hl_bill.city
        || DECODE (hl_bill.state, NULL, '', ',')
        || hl_bill.state
        || DECODE (hl_bill.postal_code, '', ',')
        || hl_bill.postal_code bill_to_address
      ,ooh.transactional_curr_code currency_code
      ,mp.organization_code
      ,ooh.fob_point_code
      ,ooh.freight_terms_code
      ,ooh.cust_po_number
      ,hca_ship.cust_account_id
      ,hca_bill.cust_account_id
      ,hca_ship.ece_tp_location_code
  FROM oe_order_headers_all ooh
      ,hz_cust_site_uses_all hcs_ship
      ,hz_cust_acct_sites_all hca_ship
      ,hz_party_sites hps_ship
      ,hz_parties hp_ship
      ,hz_locations hl_ship
      ,hz_cust_site_uses_all hcs_bill
      ,hz_cust_acct_sites_all hca_bill
      ,hz_party_sites hps_bill
      ,hz_parties hp_bill
      ,hz_locations hl_bill
      ,mtl_parameters mp
 WHERE 1 = 1
   AND ooh.order_number IN (SELECT order_number
                              FROM oe_order_headers_all
                             WHERE order_number = :p_order_number)
   AND ooh.ship_to_org_id = hcs_ship.site_use_id
   AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
   AND hca_ship.party_site_id = hps_ship.party_site_id
   AND hps_ship.party_id = hp_ship.party_id
   AND hps_ship.location_id = hl_ship.location_id
   AND ooh.invoice_to_org_id = hcs_bill.site_use_id
   AND hcs_bill.cust_acct_site_id = hca_bill.cust_acct_site_id
   AND hca_bill.party_site_id = hps_bill.party_site_id
   AND hps_bill.party_id = hp_bill.party_id
   AND hps_bill.location_id = hl_bill.location_id
   AND mp.organization_id(+) = ooh.ship_from_org_id


 -- +------------------------------------------------------------+
 -- + PO details based on PO number
 -- +------------------------------------------------------------+
SELECT DECODE (por.release_num, NULL, poh.segment1, poh.segment1 || '-' || por.release_num) po_number_release
      ,pol.line_num line
      ,pov.vendor_name vendor
      ,pol.item_revision rev
      ,pol.item_description description
      ,pll.shipment_num
      ,pod.distribution_num distribution
      ,DECODE (plt.order_type_lookup_code, 'AMOUNT', NULL, pll.price_override) unit_price
      ,pll.promised_date promised_date
      ,pol.unit_meas_lookup_code unit
      ,DECODE (pol.order_type_lookup_code, 'RATE', pod.amount_ordered, 'FIXED PRICE', pod.amount_ordered, pod.quantity_ordered)
                                                                                                                    quantity_amount_ordered
      ,DECODE (pol.order_type_lookup_code, 'RATE', pod.amount_billed, 'FIXED PRICE', pod.amount_billed, pod.quantity_billed)
                                                                                                                     quantity_amount_billed
      ,DECODE (pol.order_type_lookup_code, 'RATE', pod.amount_delivered, 'FIXED PRICE', pod.amount_delivered, pod.quantity_delivered)
                                                                                                                       qty_amount_delivered
      ,   DECODE (pol.order_type_lookup_code
                 ,'RATE', (NVL (pod.amount_ordered, 0) - NVL (pod.amount_billed, 0))
                   / DECODE (NVL (pod.amount_ordered, 0), 0, 1, pod.amount_ordered)
                 ,'FIXED PRICE', (NVL (pod.amount_ordered, 0) - NVL (pod.amount_billed, 0))
                   / DECODE (NVL (pod.amount_ordered, 0), 0, 1, pod.amount_ordered)
                 ,   (NVL (pod.quantity_ordered, 0) - NVL (pod.quantity_billed, 0))
                   / DECODE (NVL (pod.quantity_ordered, 0), 0, 1, pod.quantity_ordered)
                 )
        * 100 percent_unbilled
      ,DECODE (pol.order_type_lookup_code
              ,'RATE', pod.amount_ordered - NVL (pod.amount_cancelled, 0) - NVL (pod.amount_billed, 0)
              ,'FIXED PRICE', pod.amount_ordered - NVL (pod.amount_cancelled, 0) - NVL (pod.amount_billed, 0)
              , (pod.quantity_ordered - NVL (pod.quantity_cancelled, 0) - NVL (pod.quantity_billed, 0)) * pll.price_override
              ) c_amount_open_inv
      ,poh.po_header_id
      ,pol.po_line_id
      ,por.release_num
      ,poh.currency_code currency
      ,NVL (por.po_release_id, -1) release_id
      ,poh.cancel_flag poh_cancel_flag
      ,por.cancel_flag por_cancel_flag
      ,pol.cancel_flag pol_cancel_flag
      ,pll.cancel_flag pll_cancel_flag
      ,poh.closed_code poh_closed_code
      ,por.closed_code por_closed_code
      ,pol.closed_code pol_closed_code
      ,pll.closed_code pll_closed_code
  FROM po_distributions pod
      ,mtl_system_items msi
      ,po_line_locations pll
      ,po_lines pol
      ,po_releases por
      ,po_headers poh
      ,po_vendors pov
      ,financials_system_parameters fsp
      ,po_line_types plt
 WHERE poh.segment1 = :p_po_number
   AND poh.po_header_id = pol.po_header_id
   AND pol.po_line_id = pll.po_line_id
   AND pll.line_location_id = pod.line_location_id
   AND pol.item_id = msi.inventory_item_id(+)
   AND msi.organization_id = fsp.inventory_organization_id
   AND poh.vendor_id = pov.vendor_id(+)
   AND pll.po_release_id = por.po_release_id(+)
   AND pol.line_type_id = plt.line_type_id

 -- +------------------------------------------------------------+
 -- + Find Receipts based on PO number
 -- +------------------------------------------------------------+
SELECT   pol.po_header_id
        ,pol.po_line_id
        ,pll.line_location_id
        ,pll.quantity
        ,rsh.shipment_header_id
        ,rsh.receipt_source_code
        ,rsh.vendor_id
        ,rsh.vendor_site_id
        ,rsh.organization_id
        ,rsh.shipment_num
        ,rsh.receipt_num
        ,rsh.ship_to_location_id
        ,rsh.bill_of_lading
        ,rsl.shipment_line_id
        ,rsl.quantity_shipped
        ,rsl.quantity_received
        ,rct.transaction_type
        ,rct.transaction_id
        ,DECODE (pol.order_type_lookup_code
                ,'RATE', NVL (rct.amount, 0)
                ,'FIXED PRICE', NVL (rct.amount, 0)
                ,NVL (rct.source_doc_quantity, 0)
                ) transaction_qty
    FROM rcv_transactions rct, rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_lines pol, po_line_locations pll, po_headers_all poh
   WHERE rct.po_line_location_id = pll.line_location_id
     AND rct.po_line_id = pol.po_line_id
     AND NVL (pol.order_type_lookup_code, 'QUANTITY') NOT IN ('RATE', 'FIXED PRICE')
     AND rct.shipment_line_id = rsl.shipment_line_id
     AND rsl.shipment_header_id = rsh.shipment_header_id
     AND poh.po_header_id = pol.po_header_id
     AND poh.segment1 = :p_po_number
ORDER BY rct.transaction_id

 -- +------------------------------------------------------------+
 -- + PO and requisition details based on PO number
 -- +------------------------------------------------------------+
SELECT prh.segment1 req_number
      ,prh.authorization_status
      ,prl.line_num req_line_num
      ,prl.item_description req_item_description
      ,prl.unit_price req_unit_price
      ,prl.quantity req_quantity
      ,pd.req_header_reference_num
      ,pd.req_line_reference_num
      ,pl.line_num
      ,pl.item_description
      ,pl.quantity
      ,pl.amount
      ,ph.segment1 po_number
      ,prd.distribution_id
      ,pd.req_distribution_id
  FROM po_requisition_headers_all prh
      ,po_requisition_lines_all prl
      ,po_req_distributions_all prd
      ,po_distributions_all pd
      ,po_line_locations_all pll
      ,po_lines_all pl
      ,po_headers_all ph
 WHERE prh.requisition_header_id = prl.requisition_header_id
   AND prh.org_id = prl.org_id
   AND prl.requisition_line_id = prd.requisition_line_id
   AND prl.org_id = prd.org_id
   AND prd.distribution_id = pd.req_distribution_id(+)
   AND prd.org_id = pd.org_id(+)
   AND pd.line_location_id = pll.line_location_id(+)
   AND pd.org_id = pll.org_id(+)
   AND pll.po_line_id = pl.po_line_id(+)
   AND pll.org_id = pl.org_id(+)
   AND pl.po_header_id = ph.po_header_id(+)
   AND pl.org_id = ph.org_id(+)
   AND ph.segment1 = :p_po_number

 -- +------------------------------------------------------------+
 -- + Internal Requisition details
 -- +------------------------------------------------------------+
SELECT prh.segment1 "Req #"
      ,prh.creation_date
      ,prh.created_by
      ,poh.segment1 "PO #"
      ,ppx.full_name "Requestor Name"
      ,prh.description "Req Description"
      ,prh.authorization_status
      ,prh.note_to_authorizer
      ,prl.line_num
      ,prl.line_type_id
      ,prl.source_type_code
      ,prl.item_description
      ,prl.unit_meas_lookup_code
      ,prl.unit_price
      ,prl.quantity
      ,prl.quantity_delivered
      ,prl.need_by_date
      ,prl.note_to_agent
      ,prl.currency_code
      ,prl.rate_type
      ,prl.rate_date
      ,prl.quantity_cancelled
      ,prl.cancel_date
      ,prl.cancel_reason
  FROM po_requisition_headers_all prh
      ,po_requisition_lines_all prl
      ,po_req_distributions_all prd
      ,per_people_x ppx
      ,po_headers_all poh
      ,po_distributions_all pda
 WHERE prh.requisition_header_id = prl.requisition_header_id
   AND ppx.person_id = prh.preparer_id
   AND prh.type_lookup_code = 'INTERNAL'
   AND prd.requisition_line_id = prl.requisition_line_id
   AND pda.req_distribution_id(+) = prd.distribution_id
   AND pda.po_header_id = poh.po_header_id(+)

Filed under having 2 comments

Period opening in oracle apps R12

by Ajay Atre


Period opening in oracle apps R12
Financial period/purchasing periods are controlled at operating unit level however Inventory period is controlled at inventory organization level.

Open Financial GL/AP Periods
1.       GL Period
First open accounting period in GL
Switch to GL Responsibility
N> General Ledger-->Setup-->Open/Close
once open period in GL, all subsequent requests should be completed
2.       AP Period
Switch to Payables responsibility
N> Payables-->Accounting-->Control Payables Periods
Open the same period
3.       PO period
Open Purchasing period
Switch to purchasing super user responsibility
N>Setup -> Financials-> Accounting- > Control Purchasing Periods
4.       Inventory organization period
Open Inventory Organization period
Switch to purchasing super user responsibility
N>Inventory ->Accounting Close Cycle -> Inventory Accounting Periods
Concurrent program:-
Open period control request is used to open inventory periods for one or multiple inventory organizations. By passing inventory organization as parameter you can open an inventory period for all inventory organizations
Switch to Inventory Super User
N> Reports -> Transactions -> Select Name of the report ‘Open Period Control’ -> Enter parameters -> Click OK -> Click Submit

Closing Inventory Period
Oracle Inventory uses accounting periods to group material transactions and work in process transactions for accounting purposes. So normally these deatils are grouped for a month and the transaction details are posted to GL.
An automatic general ledger transfer is processed when you close an accounting period.
A.      Pending transactions
Before closing the inventory transactions make sure all pending transactions are resolved.
Resolution Required On the pending transactions window it shows number of transactions with resolution required
It displays the number of unprocessed material transactions, uncosted material transactions, and pending WIP costing transactions existing in this period. These must be resolved before the period is closed.
Resolution Recommended Displays the number of pending receiving transactions,
pending material transactions, and pending shop floor move transactions existing in this period. You can close the accounting period; however, after it is closed these transactions cannot be processed.
B.      Close a open/error status period
You can close the accounting period for a month with the status of Open or Error.

Pending transactions in detail
1.       Pending receiving transactions
Unprocessed receiving transactions with table RCV_TRANSACTIONS_ INTERFACE
These transactions include purchase order receipts internal order receipts and returns for inventory
If you close the period, these transactions cannot be processed because they have a transaction date for a closed period 

2.      Unprocessed material transactions
Unprocessed material transactions with the table MTL_MATERIAL_TRANSACTIONS_TEMP If you have unprocessed inventory transactions you will not able to close the period and is so because it has a transaction date for a closed period.You can look in details by inventory super user N> Transactions ->Pending transactions 
3.     Pending transactions
Unprocessed material transactions in the MTL_TRANSACTIONS_INTERFACE table 
If you close the period, these transactions cannot be processed because they have a transaction date for a closed period 
4.     Uncosted material transactions
Material transactions in the MTL_MATERIAL_TRANSACTIONS table with no accounting entries (Standard Costing) and no accounting entries and no costs (Average Costing). You are unable to close the period with this condition. 
5.     Pending move transactions
Unprocessed shop floor move transactions in the WIP_MOVE_TXN_INTERFACE table.
You are unable to close the period with this condition. 
6.     Pending WIP costing transactions
Unprocessed resource and overhead accounting transactions in the WIP_COST_TXN_INTERFACE table. You are unable to close the period with this condition.
Once you are sure that you are ready to close the inventory period you can come back to the main inventory accounting periods form and click change status button in and select Close and be sure that INVENTORY PERIOD CLOSED CANNOT BE REOPENED (This is a irreversible process).

Filed under having 5 comments

Subscribe to: Posts (Atom)

Search This Blog

Labels

About Me

Ajay Atre
View my complete profile

Total Pageviews

Followers