Monday, November 23, 2015

Purchasing Useful Scripts

Purchasing Useful Scripts

2013-4-9阅读6008 评论0
Relation between Requisition and PO tables,Here is link:
PO_DISTRIBUTIONS_ALL =>PO_HEADER_ID, REQ_DISTRIBUTION_ID
PO_HEADERS_ALL=>PO_HEADER_ID, SEGMENT1
PO_REQ_DISTRIBUTIONS_ALL =>DISTRIBUTION_ID, REQUISITION_LINE_ID
PO_REQUISITION_LINES_ALL =>REQUISITION_LINE_ID
PO_REQUISITION_HEADERS_ALL =>REQUISITION_HEADER_ID, REQUISITION_LINE_ID, SEGMENT1
What you have to make a join on PO_DISTRIBUTIONS_ALL (REQ_DISTRIBUTION_ID) and PO_REQ_DISTRIBUTIONS_ALL (DISTRIBUTION_ID) to see if there is a PO for the req.

1. You need to list out all Internal Requisitions that do not have an associated Internal Sales order.
---used to list all Internal Requisitions that do not have an  associated Internal Sales order
Select RQH.SEGMENT1 REQ_NUM,
RQL.LINE_NUM,
RQL.REQUISITION_HEADER_ID ,
RQL.REQUISITION_LINE_ID,
RQL.ITEM_ID ,
RQL.UNIT_MEAS_LOOKUP_CODE ,
RQL.UNIT_PRICE ,
RQL.QUANTITY ,
RQL.QUANTITY_CANCELLED,
RQL.QUANTITY_DELIVERED ,
RQL.CANCEL_FLAG ,
RQL.SOURCE_TYPE_CODE ,
RQL.SOURCE_ORGANIZATION_ID ,
RQL.DESTINATION_ORGANIZATION_ID,
RQH.TRANSFERRED_TO_OE_FLAG
from
PO_REQUISITION_LINES_ALL RQL, PO_REQUISITION_HEADERS_ALL RQH
where
RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID
and RQL.SOURCE_TYPE_CODE = 'INVENTORY'
and RQL.SOURCE_ORGANIZATION_ID is not null
and not exists (select 'existing internal order'
from OE_ORDER_LINES_ALL LIN
where LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_ID
and LIN.SOURCE_DOCUMENT_TYPE_ID = 10)
ORDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM;

2. You want to display what requisition and PO are linked(Relation with Requisition and PO )
-----Relation with Requistion and PO
select r.segment1 "Req Num",
       p.segment1 "PO Num"
from po_headers_all p, 
po_distributions_all d,
po_req_distributions_all rd, 
po_requisition_lines_all rl,
po_requisition_headers_all r 
where p.po_header_id = d.po_header_id 
and d.req_distribution_id = rd.distribution_id 
and rd.requisition_line_id = rl.requisition_line_id 
and rl.requisition_header_id = r.requisition_header_id 

3. You need to list out all cancel Requisitions
-----list My cancel Requistion
select prh.REQUISITION_HEADER_ID,
      prh.PREPARER_ID ,
      prh.SEGMENT1 "REQ NUM",
      trunc(prh.CREATION_DATE),
      prh.DESCRIPTION,
      prh.NOTE_TO_AUTHORIZER
from apps.Po_Requisition_headers_all prh,
     apps.po_action_history pah 
where Action_code='CANCEL' 
and pah.object_type_code='REQUISITION' 
and pah.object_id=prh.REQUISITION_HEADER_ID 

4. You need to list those PR which havn't auto created to PO.(Purchase Requisition without a Purchase Order)
-----list all Purchase Requisition without a Purchase Order that means  a PR has not been autocreated to PO.
  select 
  prh.segment1 "PR NUM", 
  trunc(prh.creation_date) "CREATED ON", 
  trunc(prl.creation_date) "Line Creation Date" ,
  prl.line_num "Seq #", 
  msi.segment1 "Item Num", 
  prl.item_description "Description", 
  prl.quantity "Qty", 
  trunc(prl.need_by_date) "Required By", 
  ppf1.full_name "REQUESTOR", 
  ppf2.agent_name "BUYER" 
  from 
  po.po_requisition_headers_all prh, 
  po.po_requisition_lines_all prl, 
  apps.per_people_f ppf1, 
  (select distinct agent_id,agent_name from apps.po_agents_v ) ppf2, 
  po.po_req_distributions_all prd, 
  inv.mtl_system_items_b msi, 
  po.po_line_locations_all pll, 
  po.po_lines_all pl, 
  po.po_headers_all ph 
  WHERE 
  prh.requisition_header_id = prl.requisition_header_id 
  and prl.requisition_line_id = prd.requisition_line_id 
  and ppf1.person_id = prh.preparer_id 
  and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date 
  and ppf2.agent_id(+) = msi.buyer_id 
  and msi.inventory_item_id = prl.item_id 
  and msi.organization_id = prl.destination_organization_id 
  and pll.line_location_id(+) = prl.line_location_id 
  and pll.po_header_id = ph.po_header_id(+) 
  AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+) 
  AND PRH.AUTHORIZATION_STATUS = 'APPROVED' 
  AND PLL.LINE_LOCATION_ID IS NULL 
  AND PRL.CLOSED_CODE IS NULL 
  AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'
  ORDER BY 1,2
5. You need to list all information form PR to PO ...as a requisition moved from different stages till converting into PR. This query capture all details related to that PR to PO.
----- List and all data entry from PR till PO

select distinct u.description "Requestor", 
porh.segment1 as "Req Number", 
trunc(porh.Creation_Date) "Created On", 
pord.LAST_UPDATED_BY, 
porh.Authorization_Status "Status", 
porh.Description "Description", 
poh.segment1 "PO Number", 
trunc(poh.Creation_date) "PO Creation Date", 
poh.AUTHORIZATION_STATUS "PO Status", 
trunc(poh.Approved_Date) "Approved Date"
from apps.po_headers_all poh, 
apps.po_distributions_all pod, 
apps.po_req_distributions_all pord, 
apps.po_requisition_lines_all porl, 
apps.po_requisition_headers_all porh, 
apps.fnd_user u 
where porh.requisition_header_id = porl.requisition_header_id 
and porl.requisition_line_id = pord.requisition_line_id 
and pord.distribution_id = pod.req_distribution_id(+) 
and pod.po_header_id = poh.po_header_id(+) 
and porh.created_by = u.user_id
order by 2 

6.Identifying all PO's which does not have any PR's
-----list all Purchase Requisition without a Purchase Order that means  a PR has not been autocreated to PO.
  select 
  prh.segment1 "PR NUM", 
  trunc(prh.creation_date) "CREATED ON", 
  trunc(prl.creation_date) "Line Creation Date" ,
  prl.line_num "Seq #", 
  msi.segment1 "Item Num", 
  prl.item_description "Description", 
  prl.quantity "Qty", 
  trunc(prl.need_by_date) "Required By", 
  ppf1.full_name "REQUESTOR", 
  ppf2.agent_name "BUYER" 
  from 
  po.po_requisition_headers_all prh, 
  po.po_requisition_lines_all prl, 
  apps.per_people_f ppf1, 
  (select distinct agent_id,agent_name from apps.po_agents_v ) ppf2, 
  po.po_req_distributions_all prd, 
  inv.mtl_system_items_b msi, 
  po.po_line_locations_all pll, 
  po.po_lines_all pl, 
  po.po_headers_all ph 
  WHERE 
  prh.requisition_header_id = prl.requisition_header_id 
  and prl.requisition_line_id = prd.requisition_line_id 
  and ppf1.person_id = prh.preparer_id 
  and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date 
  and ppf2.agent_id(+) = msi.buyer_id 
  and msi.inventory_item_id = prl.item_id 
  and msi.organization_id = prl.destination_organization_id 
  and pll.line_location_id(+) = prl.line_location_id 
  and pll.po_header_id = ph.po_header_id(+) 
  AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+) 
  AND PRH.AUTHORIZATION_STATUS = 'APPROVED' 
  AND PLL.LINE_LOCATION_ID IS NULL 
  AND PRL.CLOSED_CODE IS NULL 
  AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'
  ORDER BY 1,2
7. List all the PO's with there approval ,invoice and Payment Details
----- List and PO With there approval , invoice and payment details
select 
a.org_id "ORG ID", 
E.SEGMENT1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER(e.vendor_type_lookup_code) "VENDOR TYPE", 
f.vendor_site_code "VENDOR SITE CODE",
f.ADDRESS_LINE1 "ADDRESS",
f.city "CITY",
f.country "COUNTRY", 
to_char(trunc(d.CREATION_DATE)) "PO Date", 
d.segment1 "PO NUM",
d.type_lookup_code "PO Type", 
c.quantity_ordered "QTY ORDERED", 
c.quantity_cancelled "QTY CANCELLED", 
g.item_id "ITEM ID" , 
g.item_description "ITEM DESCRIPTION",
g.unit_price "UNIT PRICE", 
(NVL(c.quantity_ordered,0)-NVL(c.quantity_cancelled,0))*NVL(g.unit_price,0) "PO Line Amount", 
(select 
decode(ph.approved_FLAG, 'Y', 'Approved') 
from po.po_headers_all ph 
where ph.po_header_ID = d.po_header_id)"PO Approved?", 
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT", 
to_char(trunc(a.INVOICE_DATE)) "INVOICE DATE", 
a.invoice_num "INVOICE NUMBER", 
(select 
decode(x.MATCH_STATUS_FLAG, 'A', 'Approved') 
from ap.ap_invoice_distributions_all x 
where x.INVOICE_DISTRIBUTION_ID = b.invoice_distribution_id)"Invoice Approved?", 
a.amount_paid,
h.amount, 
h.check_id, 
h.invoice_payment_id "Payment Id", 
i.check_number "Cheque Number", 
to_char(trunc(i.check_DATE)) "PAYMENT DATE" 
 
FROM AP.AP_INVOICES_ALL A, 
AP.AP_INVOICE_DISTRIBUTIONS_ALL B, 
PO.PO_DISTRIBUTIONS_ALL C, 
PO.PO_HEADERS_ALL D, 
PO.PO_VENDORS E, 
PO.PO_VENDOR_SITES_ALL F, 
PO.PO_LINES_ALL G, 
AP.AP_INVOICE_PAYMENTS_ALL H, 
AP.AP_CHECKS_ALL I 
where a.invoice_id = b.invoice_id 
and b.po_distribution_id = c. po_distribution_id (+) 
and c.po_header_id = d.po_header_id (+) 
and e.vendor_id (+) = d.VENDOR_ID 
and f.vendor_site_id (+) = d.vendor_site_id 
and d.po_header_id = g.po_header_id 
and c.po_line_id = g.po_line_id 
and a.invoice_id = h.invoice_id 
and h.check_id = i.check_id 
and f.vendor_site_id = i.vendor_site_id 
and c.PO_HEADER_ID is not null 
and a.payment_status_flag = 'Y' 
and d.type_lookup_code != 'BLANKET' 

8.You need to know the link to GL_JE_LINES table for purchasing accrual and budgetary control actions..
The budgetary (encumbrance) and accrual actions in the purchasing module generate records that will be imported into GL for the corresponding accrual and budgetary journals.
The following reference fields are used to capture and keep PO information in the GL_JE_LINES table.
These reference fields are populated when the Journal source (JE_SOURCE in GL_JE_HEADERS) is
Purchasing.
Budgetary Records from PO (These include reservations, reversals and cancellations):
  • REFERENCE_1- Source (PO or REQ)
  • REFERENCE_2- PO Header ID or Requisition Header ID (from po_headers_all.po_header_id or
    po_requisition_headers_all.requisition_header_id)
  • REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id or
    po_req_distributions_all.distribution_id)
  • REFERENCE_4- Purchase Order or Requisition number (from po_headers_all.segment1 or
    po_requisition_headers_all.segment1)
  • REFERENCE_5- (Autocreated Purchase Orders only) Backing requisition number (from po_requisition_headers_all.segment1)
Accrual Records from PO:
  • REFERENCE_1- Source (PO)
  • REFERENCE_2- PO Header ID (from po_headers_all.po_header_id)
  • REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id
  • REFERENCE_4- Purchase Order number (from po_headers_all.segment1)
  • REFERENCE_5- (ON LINE ACCRUALS ONLY) Receiving Transaction ID (from rcv_receiving_sub_ledger.rcv_transaction_id)
Take a note for Period end accruals, the REFERENCE_5 column is not used.

9. List me all open PO's
----- List all open PO'S
select 
h.segment1 "PO NUM", 
h.authorization_status "STATUS", 
l.line_num "SEQ NUM", 
ll.line_location_id, 
d.po_distribution_id , 
h.type_lookup_code "TYPE" 
from 
po.po_headers_all h, 
po.po_lines_all l, 
po.po_line_locations_all ll, 
po.po_distributions_all d 
where h.po_header_id = l.po_header_id 
and ll.po_line_id = l.po_Line_id 
and ll.line_location_id = d.line_location_id 
and h.closed_date is null 
and h.type_lookup_code not in ('QUOTATION')

10.There are different authorization_status can a requisition have.
  • Approved
  • Cancelled
  • In Process
  • Incomplete
  • Pre-Approved
  • Rejected
and you should note: When we finally close the requisition from Requisition Summary form the authorization_status of the requisition does not change. Instead it's closed_code becomes 'FINALLY CLOSED'.

--------------------------------------------------------------------------------------------------------------------------------------------------------
2..1 Purchase 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, prh.type_lookup_code, prl.line_num,
       prl.line_type_id, 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 = 'PURCHASE'
   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
   AND TO_CHAR (prh.creation_date, 'YYYY') IN ('2010', '2011')
2.2 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 (+)
   AND TO_CHAR (prh.creation_date, 'YYYY') IN ('2010', '2011')

2.3 Purchase Order details
-- Purchase Orders for non inventory items like service
SELECT
    ph.SEGMENT1 po_num
  , ph.CREATION_DATE
  , hou.name  "Operating Unit"
  , ppx.full_name "Buyer Name"
  , ph.type_lookup_code "PO Type"
  , plc.displayed_field "PO Status"
  , ph.COMMENTS
  , pl.line_num
  , plt.order_type_lookup_code "Line Type"
  , NULL "Item Code"
  , pl.item_description
  , pl.unit_meas_lookup_code "UOM"
  , pl.base_unit_price
  , pl.unit_price
  , pl.quantity
  , ood.organization_code "Shipment Org Code"
  , ood.organization_name "Shipment Org Name"
  , pv.vendor_name supplier
  , pvs.vendor_site_code
  , (pl.unit_price * pl.quantity) "Line Amount"
  , prh.segment1 req_num
  , prh.type_lookup_code req_method
  , ppx1.full_name "Requisition requestor"
FROM  po_headers_all ph
    , po_lines_all pl
    , po_distributions_all pda
    , po_vendors pv
    , po_vendor_sites_all pvs
    , po_distributions_all pd
    , po_req_distributions_all prd
    , po_requisition_lines_all prl
    , po_requisition_headers_all prh
    , hr_operating_units hou
    , per_people_x ppx
    , po_line_types_b plt
    , org_organization_definitions ood
    , per_people_x ppx1
    , po_lookup_codes plc
WHERE
  1=1
  AND TO_CHAR(ph.creation_date, 'YYYY') IN (2010, 2011)
  AND ph.vendor_id = pv.vendor_id
  AND ph.po_header_id = pl.po_header_id
  AND ph.vendor_site_id = pvs.vendor_site_id
  AND ph.po_header_id = pd.po_header_id
  and pl.po_line_id = pd.po_line_id
  AND pd.req_distribution_id = prd.distribution_id (+)
  AND prd.requisition_line_id = prl.requisition_line_id (+)
  AND prl.requisition_header_id = prh.requisition_header_id (+)
 and hou.organization_id = ph.org_id
 and ph.agent_id = ppx.person_id
 and pda.po_header_id = ph.po_header_id
 and pda.po_line_id = pl.po_line_id
 and pl.line_type_id = plt.line_type_id
 and ood.organization_id = pda.destination_organization_id
 and ppx1.person_id (+) = prh.preparer_id
 and plc.lookup_type = 'DOCUMENT STATE'
 and plc.LOOKUP_CODE = ph.closed_code
 and pl.item_id is null
UNION
-- Purchase Orders for inventory items
SELECT
    ph.SEGMENT1 po_num
  , ph.CREATION_DATE
  , hou.name  "Operating Unit"
  , ppx.full_name "Buyer Name"
  , ph.type_lookup_code "PO Type"
  , plc.displayed_field "PO Status"
  , ph.COMMENTS
  , pl.line_num
  , plt.order_type_lookup_code "Line Type"
  , msi.segment1 "Item Code"
  , pl.item_description
  , pl.unit_meas_lookup_code "UOM"
  , pl.base_unit_price
  , pl.unit_price
  , pl.quantity
  , ood.organization_code "Shipment Org Code"
  , ood.organization_name "Shipment Org Name"
  , pv.vendor_name supplier
  , pvs.vendor_site_code
  , (pl.unit_price * pl.quantity) "Line Amount"
  , prh.segment1 req_num
  , prh.type_lookup_code req_method
  , ppx1.full_name "Requisition requestor"
FROM  po_headers_all ph
    , po_lines_all pl
    , po_distributions_all pda
    , po_vendors pv
    , po_vendor_sites_all pvs
    , po_distributions_all pd
    , po_req_distributions_all prd
    , po_requisition_lines_all prl
    , po_requisition_headers_all prh
    , hr_operating_units hou
    , per_people_x ppx
    , mtl_system_items_b msi
    , po_line_types_b plt
    , org_organization_definitions ood
    , per_people_x ppx1
    , po_lookup_codes plc
WHERE
  1=1
  AND TO_CHAR(ph.creation_date, 'YYYY') IN (2010, 2011)
  AND ph.vendor_id = pv.vendor_id
  AND ph.po_header_id = pl.po_header_id
  AND ph.vendor_site_id = pvs.vendor_site_id
  AND ph.po_header_id = pd.po_header_id
  and pl.po_line_id = pd.po_line_id
  AND pd.req_distribution_id = prd.distribution_id (+)
  AND prd.requisition_line_id = prl.requisition_line_id (+)
  AND prl.requisition_header_id = prh.requisition_header_id (+)
 and hou.organization_id = ph.org_id
 and ph.agent_id = ppx.person_id
 and pda.po_header_id = ph.po_header_id
 and pda.po_line_id = pl.po_line_id
 and pl.line_type_id = plt.line_type_id
 and ood.organization_id = pda.destination_organization_id
 and ppx1.person_id (+) = prh.preparer_id
 and pda.destination_organization_id = msi.organization_id (+)
 and msi.inventory_item_id = nvl(pl.item_id, msi.inventory_item_id)-- OR pl.item_id is null)
 and plc.lookup_type = 'DOCUMENT STATE'
 and plc.LOOKUP_CODE = ph.closed_code
 and pl.item_id is not null
2.4 Receiving transactions with PO and requisition information
SELECT
ph.segment1 po_num,
ood.organization_name,
pol.po_line_id,
pll.quantity,
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,
nvl(rct.source_doc_quantity,0) transaction_qty
from rcv_transactions rct
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, po_lines_all pol
, po_line_locations_all pll
, po_headers_all ph
, org_organization_definitions ood
where 1=1
and to_char(rct.creation_date, 'YYYY') in ('2010', '2011')
and rct.po_header_id = ph.po_header_id
and rct.po_line_location_id = pll.line_location_id
and rct.po_line_id = pol.po_line_id
and rct.shipment_line_id=rsl.shipment_line_id
and rsl.shipment_header_id=rsh.shipment_header_id
and rsh.ship_to_org_id = ood.organization_id
order by rct.transaction_id

--------------------------------------------------------------------------------------------------------------------------------------------------------
Thats is possible, your PO get stuck somewhere, so what you have to do is to analyze which stage it stucked.Get po_header_id first and run each query and then analyze the data.For better understanding this is splited into 5 major stages.
Stage 1: PO Creation :
double-arrowPO_HEADERS_ALL
select po_header_id from po_headers_all where segment1 =<po_number>;
select * from po_headers_all where po_header_id =<po_header_id>;
double-arrowpo_lines_all
select * from po_lines_all where po_header_id =<po_header_id>;

double-arrowpo_line_locations_all
select * from po_line_locations_all where po_header_id =<po_header_id>;

double-arrowpo_distributions_all
select * from po_distributions_all where po_header_id =<po_header_id>;
double-arrowpo_releases_all
SELECT * FROM po_releases_all WHERE po_header_id =<po_header_id>;

Stage 2: Once PO is received data is moved to respective receving tables and inventory tables
double-arrowRCV_SHIPMENT_HEADERS
select * from rcv_shipment_headers where shipment_header_id in
(select shipment_header_id from rcv_shipment_lines
where po_header_id =<po_header_id>);
double-arrowRCV_SHIPMENT_LINES
select * from rcv_shipment_lines where po_header_id =<po_header_id>;
double-arrowRCV_TRANSACTIONS
select * from rcv_transactions where po_header_id =<po_header_id>;
double-arrowRCV_ACCOUNTING_EVENTS
SELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN
(select transaction_id from rcv_transactions
where po_header_id =<po_header_id>);
double-arrowRCV_RECEIVING_SUB_LEDGER
select * from rcv_receiving_sub_ledger where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =<po_header_id>);
double-arrowRCV_SUB_LEDGER_DETAILS
select * from rcv_sub_ledger_details
where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =<po_header_id>);
double-arrowMTL_MATERIAL_TRANSACTIONS
select * from mtl_material_transactions where transaction_source_id =<po_header_id>;
double-arrowMTL_TRANSACTION_ACCOUNTS
select * from mtl_transaction_accounts where transaction_id in ( select transaction_id from mtl_material_transactions where transaction_source_id = =<po_header_id>);

Stage 3: Invoicing details
double-arrowAP_INVOICE_DISTRIBUTIONS_ALL
select * from ap_invoice_distributions_all where po_distribution_id in ( select po_distribution_id from po_distributions_all where po_header_id =<po_header_id>);
double-arrowAP_INVOICES_ALL
select * from ap_invoices_all where invoice_id in
(select invoice_id from ap_invoice_distributions_all where po_distribution_id in
( select po_distribution_id from po_distributions_all where po_header_id =<po_header_id>));

Stage 4 : Many Time there is tie up with Project related PO
double-arrowPA_EXPENDITURE_ITEMS_ALL
select * from pa_expenditure_items_all peia where peia.orig_transaction_reference in
( select to_char(transaction_id) from mtl_material_transactions
where transaction_source_id = <po_header_id> );

Stage 5 : General Ledger
double-arrowPrompt 17. GL_BC_PACKETS ..This is for encumbrances
SELECT * FROM gl_bc_packets WHERE reference2 IN ('<po_header_id>');
double-arrowGL_INTERFACE
SELECT *
FROM GL_INTERFACE GLI
WHERE user_je_source_name ='Purchasing'
AND gl_sl_link_table ='RSL'
AND reference21='PO'
AND EXISTS
( SELECT 1
FROM rcv_receiving_sub_ledger RRSL
WHERE GLI.reference22 =RRSL.reference2
AND GLI.reference23 =RRSL.reference3
AND GLI.reference24 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id <po_header_id>));
double-arrowGL_IMPORT_REFERENCES
SELECT *
FROM gl_import_references GLIR
WHERE reference_1='PO'
AND gl_sl_link_table ='RSL'
AND EXISTS
( SELECT 1
FROM rcv_receiving_sub_ledger RRSL
WHERE GLIR.reference_2 =RRSL.reference2
AND GLIR.reference_3 =RRSL.reference3
AND GLIR.reference_4 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id =<po_header_id>))

INV GL: SQL Query to link inventory material transaction with GL journal entry

INV GL: SQL Query to link inventory material transaction with GL journal entry

Hello,

The below query used to link the material transactions in the Inventory to the journals entries in the General Ledger, ofcourse you can modify it to fit your requierments.


SELECT DISTINCT glh.*
           FROM xla_transaction_entities_upg xte,
                xla_events xe,
                xla_distribution_links xdl,
                mtl_transaction_accounts mta,
                xla_ae_headers xah,
                xla_ae_lines xal,
                gl_import_references gir,
                gl_je_headers glh
          WHERE 1 = 1
            AND xte.source_id_int_1 = &transaction_id
            AND xte.entity_id = xe.entity_id
            AND mta.transaction_id = xte.source_id_int_1
            AND xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
            AND xdl.source_distribution_id_num_1 = mta.inv_sub_ledger_id
            AND xdl.ae_header_id = xah.ae_header_id
            AND xal.ae_header_id = xdl.ae_header_id
            AND xal.ae_header_id = xah.ae_header_id
            AND gir.gl_sl_link_table = 'XLAJEL'
            AND gir.gl_sl_link_id = xal.gl_sl_link_id
            AND gir.je_header_id = glh.je_header_id;

Debugging an Internal Requisition which not turned to an Internal Sales Order

Debugging an Internal Requisition which not turned to an Internal Sales Order

6 AUGUST 2011 2,714 VIEWS NO COMMENT ARTICLE BY SHAILENDER THALLAM
Once I have encountered a situation wherein an Internal Sales Order is not created for an Internal Requisition. Below are the steps I have followed the to resolve this problem
Firstly execute the following query to make sure whether an Internal SO is created for Internal Requisition or not.
--To check whether a SO is created for an Internal Requisition
SELECT   oeh.order_number, oeh.header_id, oel.line_id, oel.line_number
FROM oe_order_lines_all oel,
oe_order_headers_all oeh,
po_requisition_headers_all porh,
po_requisition_lines_all porl
WHERE oeh.header_id = oel.header_id
AND oel.source_document_id = porh.requisition_header_id
AND oel.source_document_line_id = porl.requisition_line_id
AND porh.requisition_header_id = porl.requisition_header_id
AND oel.order_source_id = 10             --order_source_id for 'Internal'
AND oel.orig_sys_document_ref = '&amp;Your_Int_Req_num'
AND oel.org_id = porh.org_id
ORDER BY oeh.header_id, oel.line_id;
If you find that a SO is not created then follow the below steps.

Order Interface Tables

If  SO is not created, then it must have got strucked in Interface tables
OE_HEADERS_IFACE_ALL, OE_LINES_IFACE_ALL
-- To check whether Requisition Lines are in Interface table or not
SELECT COUNT (*)
FROM oe_lines_iface_all
WHERE orig_sys_line_ref IN (
SELECT requisition_line_id
FROM po_requisition_lines_all
WHERE requisition_header_id IN (
SELECT requisition_header_id
FROM po_requisition_headers_all
WHERE segment1 = '&amp;Requisition_Number'));
When a Requisition data is inserted into Order Interface tables then transferred_to_oe_flag of both PO_REQUISITION_HEADERS_ALL and PO_REQUISITION_LINES_ALL with be set to ‘Y
If  SO is strucked in Interface tables then there must be some error/problem with Internal Requisition which we can find out in the application.
This is possible using the Order Management Responsibility – and navigating to Orders –> Import Orders –> Corrections
  • Choose an order type of Internal
  • In Order Reference use the List of Values to locate the Requisition Number
  • If the Requisition is not located – it has either been created into a Sales Order or not inserted into the tables by the Create Internal Orders program
If the Requisition is not located – it has either been created into a Sales Order or not inserted into the tables by the Create Internal Orders program. To resolve this kind of problem read this article.
If Requisition is found, check out for errors as why they got strucked.
Below are the possibles reasons for getting a Requisition strucked.

1) Item Pre-Requisites

  • Firstly make sure that your Internal Requisition is Approved
  • Items involved in Requisition are assigned to source and destination organizations or not? and also make sure that the items are assigned to Master Org.
Usually Master Org is the Item Validation Org, which you can find with the help of a profile option ‘OM: Item Validation Org‘.
  • Items should have enough onhand quantity in source organization

2) Item Attributes

Make sure the following item attributes are set for both Source and Destination Organizations, Item validation organization too.
  • In Purchasing Tab – add a price if the item is to be used in iProcurement.
Uncheck the purchasing checkboxes, if the item is to ONLY be ordered from an internal source.
  • In Order Management tab of Item Master the following attributes should be enabled.
  1. Internal Ordered
  2. Internal Orders Enabled
  3. OE Transactable

3) Shipping Network

Ensure you have shipping network established between Source and Destination Organizations.
Navigation: Inventory Responsibility –>  Setup –> Organizations –> Shipping Networks

4) Destination Organization as an Internal Customer

Destination Inventory Organization must be created as a Customer in the Operating Unit of the Source Inventory Organization that is used on the Internal Requisition.
You can check the Operating Unit for the Source Inventory Organization by using the following sql:
SELECT    'The Inventory Organization '
|| oo.organization_code
|| 'has the inventory organization ID of '
|| oo.organization_id
|| ' and is under the Operating Unit'
|| hro.NAME
|| ' which has the Operating Unit ID of '
|| oo.operating_unit
FROM org_organization_definitions oo, hr_all_organization_units hro
WHERE UPPER (oo.organization_code) LIKE UPPER ('%&amp;INV_ORG_CODE%')
AND hro.organization_id = oo.operating_unit;

5) Item Price

Price must be defined for the Items involved in the Internal Requisition.


Once the error is corrected, In the Order Corrections form the ERROR flag is unchecked and the REQUEST_ID field cleared, the order can be resubmitted for Import with ‘Order Import’ Concurrent Program.
PLEASE NOTE -If you want to delete records from Interface then it is necessary to Delete the Lines first and then the header.

Please do comment if you have any more issues with this kind of problem.

Wednesday, August 12, 2015

GL to AR Drill down Query R12 ,R12 GL Drilldown to AR ,R12 GL Drilldown to AR Functional

  • General Ledger to Receivables Drilldown


  •        Transaction / Invoices
  •          Receipts

Let’s start with Transaction / Invoices.
Tables involved for Journal Entry to Invoice drilldown are
  •         GL_JE_HEADERS
  •         GL_IMPORT_REFERENCES
  •          XLA_AE_LINES
  •         XLA_AE_HEADERS
  •         XLA_TRANSACTION_ENTITIES
  •         RA_CUSTOMER_TRX_ALL

Now, when we are given a Journal entry with JE_SOURCE as ‘Receivables’ and JE_CATEGORY as ‘Sales Invoices’, we got to track its JE_HEADER_ID. Here is the step by step process. 

Step 1: For instance, we are going to track the invoice against the maximum JE_HEADER_ID with Receivables source and Purchase Invoices Category. Here is the query for it;

SELECT MAX(JE_HEADER_ID )
INTO V_JE_HEADER_ID
FROM GL_JE_HEADERS
WHERE JE_SOURCE = ‘Receivables’
AND JE_CATEGORY= ‘Sales Invoices’;

Step 2: Now we are going to check the corresponding GL_SL_LINK_ID against this JE_HEADER_ID. We can get these link ids through the query,

SELECT GL_SL_LINK_ID
FROM GL_IMPORT_REFERENCES
WHERE JE_HEADER_ID = V_JE_HEADER_ID;

Step 3: And to get to the relevant lines in sub-ledger modules we need to find lines against the identified GL_SL_LINK_IDs from XLA_AE_LINES table. Here is the query to get to the sub-ledger lines

SELECT DISTINCT AE_HEADER_ID
INTO V_AE_HEADER_ID
FROM XLA_AE_LINES
WHERE GL_SL_LINK_ID IN (SELECT GL_SL_LINK_ID
FROM GL_IMPORT_REFERENCES
WHERE JE_HEADER_ID = V_JE_HEADER_ID
);

Step 4: Now we will find ENTITY_ID against the identified header_id. Here is the query,

SELECT ENTITY_ID
INTO V_ENTITY_ID
FROM XLA_AE_HEADERS
WHERE AE_HEADER_ID = V_AE_HEADER_ID;

Step 5: Now, we will get the SOURCE_ID_INT_1 against identified XLA_TRANSACTION to get to the Receivables transaction.

SELECT SOURCE_ID_INT_1
INTO V_SOURCE_ID
FROM XLA_TRANSACTION_ENTITIES
WHERE ENTITY_ID = V_ENTITY_ID;

Step 6: Now is the last step, we will use this source id as invoice id to get to the invoice in Receivables.

SELECT *
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = V_SOURCE_ID;

So, here is the drilldown from General Ledger to Receivables Invoices. We got to change in step 1 for JE_CATEGORY as ‘Receipts’ and the query becomes

SELECT MAX(JE_HEADER_ID )
INTO V_JE_HEADER_ID
FROM GL_JE_HEADERS
WHERE JE_SOURCE = ‘Receivables’
AND JE_CATEGORY= ‘Receipts’;

 And in step 6, we got to find check details against the identified SOURCE_ID, which makes the SQL as

SELECT *
FROM AR_CASH_RECEIPTS_ALL
WHERE CASH_RECEIPT_ID = V_SOURCE_ID;



SELECT DISTINCT GJH.JE_HEADER_ID,
  GJH.PERIOD_NAME,
  XE.EVENT_ID,
  GJH.NAME HEADER_NAME,
  GJH.DESCRIPTION JE_HEADER_DESCRIPTION,
  GJH.ACTUAL_FLAG,
  GJL.DESCRIPTION JE_LINE_DESCRIPTION,
  RCT.TRX_DATE,
  xal.ENTERED_CR ENT_CR,
  XAL.ENTERED_DR ENT_DR,
  XAL.ACCOUNTED_CR ACC_CR,
  XAL.ACCOUNTED_DR ACC_DR,
  GJL.JE_LINE_NUM,
  GJH.JE_CATEGORY,
  GJH.JE_SOURCE,
  PARTY.PARTY_NAME PARTY,
  RCT.TRX_NUMBER TRX_NUM,
  MTL.SEGMENT1 ITEM ,
  CTL.DESCRIPTION INV_LINE_DESC
FROM GL.GL_JE_HEADERS GJH,
  GL.GL_JE_LINES GJL,
  GL.GL_CODE_COMBINATIONS GCC,
  GL.GL_PERIODS GLP,
  GL.GL_IMPORT_REFERENCES IMP,
  XLA.XLA_AE_LINES XAL,
  XLA.XLA_AE_HEADERS XAH,
  XLA.XLA_EVENTS XE,
  XLA.XLA_TRANSACTION_ENTITIES XTE,
  RA_CUSTOMER_TRX_ALL RCT,
  HZ_PARTIES PARTY,
  AR.HZ_CUST_ACCOUNTS CA,
  GL_CODE_COMBINATIONS_KFV CC,
  AR.RA_CUSTOMER_TRX_LINES_ALL CTL,
  AR.RA_CUST_TRX_LINE_GL_DIST_ALL CTLD,
  xla.xla_distribution_links xdl,
  apps. mtl_system_items_b mtl
WHERE 1              = 1
AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
  --AND GJL.STATUS || '' = 'P'
AND GCC.CODE_COMBINATION_ID   = CTLD.CODE_COMBINATION_ID
AND GJH.PERIOD_NAME           = GLP.PERIOD_NAME
AND GJH.PERIOD_NAME           = 'JUL-15'
AND RCT.CUSTOMER_TRX_ID       = CTLD.CUSTOMER_TRX_ID
AND CTLD.CUSTOMER_TRX_LINE_ID = CTL.CUSTOMER_TRX_LINE_ID
AND GJH.JE_SOURCE                    = 'Receivables'
AND GJL.JE_HEADER_ID                 = IMP.JE_HEADER_ID
AND GJL.JE_LINE_NUM                  = IMP.JE_LINE_NUM
AND IMP.GL_SL_LINK_ID                = XAL.GL_SL_LINK_ID
AND IMP.GL_SL_LINK_TABLE             = XAL.GL_SL_LINK_TABLE
AND XAL.APPLICATION_ID               = XAH.APPLICATION_ID
AND XAL.AE_HEADER_ID                 = XAH.AE_HEADER_ID
AND XAH.APPLICATION_ID               = XE.APPLICATION_ID
AND XAH.EVENT_ID                     = XE.EVENT_ID
AND XE.APPLICATION_ID                = XTE.APPLICATION_ID
AND XTE.APPLICATION_ID               = 222
AND XE.ENTITY_ID                     = XTE.ENTITY_ID
AND XTE.ENTITY_CODE                  = 'TRANSACTIONS'
AND XTE.SOURCE_ID_INT_1              = RCT.CUSTOMER_TRX_ID
AND RCT.BILL_TO_CUSTOMER_ID          = CA.CUST_ACCOUNT_ID
AND CA.PARTY_ID                      = PARTY.PARTY_ID
AND rcT.CUSTOMER_TRX_ID              = ctl.CUSTOMER_TRX_Id
AND xal.ae_header_id                 = xdl.ae_header_id
AND xal.ae_line_num                  = xdl.ae_line_num
AND xdl.source_distribution_id_num_1 = ctld.cust_trx_line_gl_dist_id
AND CTL.LINE_TYPE                    = 'LINE'
AND XAL.CODE_COMBINATION_ID          = CC.CODE_COMBINATION_ID
AND ctl.INVENTORY_ITEM_ID            =MTL.INVENTORY_ITEM_ID
AND CC.CODE_COMBINATION_ID           ='7546' ---- c
ORDER BY RCT.TRX_DATE;


With the Release 12 of Oracle Applications, Subledger Accounting (SLA) has been introduced. This is a Rule-based accounting engine, toolset & repository supporting Oracle E-Business Suite modules (also called as subledgers). The main idea behind this introduction is to have an option of allowing multiple accounting representations for a single business event, resolving conflicts between corporate and local fiscal accounting requirements. Also this introduces a common data model and user interface (UI) across subledgers, replaces various disparate 11i setups, providing single source of truth for financial and management analysis. Due to SLA, there are some changes in the functionalities of General Ledger. There are also certain setups that need to be done in General Ledger for SLA.

For understanding the Impact of SLA on General Ledger, we have divided this document into three parts:
  1. Drill Down – This will deal with the change in the Drilldown data, which appears now. Also the setups, which decide what data, can be viewed and how data can be secured.
  2. Journal Import – This will deal with the transfer of data from Subledgers to General Ledger. This will also explain the various setups that are done for the Ledger which govern the import for various applications.
  3. Technical Reference – This will give the technical references, for journal import and drill down functionalities, which will help in understanding the architecture and will also help in troubleshooting.

Part I : Impact of SLA on Drilldown in General Ledger

Drilldown functionality enables users to navigate from a Journal in General Ledger to the Subledger Journal Entry in SLA and from there to the transaction which has resulted into that journal. 

To enable Journal drilldown from General Ledger, the "Import Journal References" option should be enabled for the Source. 
This can be checked under General Ledger Responsibility at Setup -> Journal -> Sources -> Define. 
Only when this is enabled, the reference data is populated by SLA engine. 

The Drilldown in Release 12 is different from the prior releases of the Application. R12 drilldown is Web based and not in the Forms. The following screenshots show the Drilldown functionality in Release 12: 

Navigation to perform Drilldown is the same as in Release 11i:
  • Journals -> Enter -> Review Journal
  • Select the journal line and click on “Lines Drilldown”.


Drilldown can also be performed from:
  • Tools -> Drilldown

  

As mentioned above, Drilldown opens the Web Page instead of Form. 




The SLA Page for Drilldown shows the following:
  • GL Journal Line information
  • Subledger Journal Line information



There are two Options available on this SLA page:
  • View Transaction
  • View Journal Entry


View Transaction’ will take us to the Subledger Transaction form. E.g for the current journal line, since the Source was Payables, it opens Invoice Workbench.


‘View Journal Entry’ will take to the Subledger Journal Entry details



View Journal Entry has three parts to it.
  1. Transaction Information
  2. Additional Information (this is hidden by default)
  3. Line Details
Apart from this there are some extra details shown at the top of the page, which is the header level information for the Subledger Transaction.

Some of the important fields in header part of the Subledger Transaction Journal are:
  • Ledger Name
  • Journal Entry Status
  • Balance Type
  • GL Date


This is the 'Transaction Information' section of Subledger Journal Entry page.
The fields here will change based on the transaction and the subledger in which the transaction was entered.
Since the example transaction is entered in Payables, it is showing the details of the invoice and the supplier.
  


This is the 'Additional Information' section of Subledger Transaction Entry page.
This section shows the details of
  • Event
  • General Ledger
  • Sequences
  • Application Accounting Definition
Note that Event and Application Accounting Definition are important from SLA side. However, from GL perspective the relevant details are Period, Transfer to GL Date and Transfer to GL status.




This is the last section, 'Lines' of the Subledger Journal Entry page. 
The important details in this section are:
  • Code Combination
  • Currency
  • Entered Debits and Credits
  • Accounted Debits and Credits
Select the link 'Show' for any of the lines to view additional details for the transaction line




This page gives various details for transaction line from the subledgers side.

Thus the Drilldown from General Ledger, takes us to the Subledger Journal Entry which shows the details of the Subledger journal. From here we can further drilldown to the Subledger Transaction.

Profile Options related to Drilldown:
  • SLA: Enable Subledger Transaction Security in GL
    This profile option controls whether the drilldown to SLA will enforce the transaction security of the application owning the Transaction. For example, if one drills down to Payables, he will only be able to see Journals for transactions belonging to operating units to which he has access (based on your MO: Security Profile.) Thus drilldown can be restricted to the Operating Units to which the responsibility has access. In case the GL responsibility does not have access to a particular Operating Unit, the Drilldown will not show any data.

Types of animations

Animation is a diverse and creative field with a variety of styles that artists and filmmakers use to bring their visions to life. Here are ...