QUERY TO EXTRACT TO PURCHASE ORDERS DATA

-
select
  pha.SEGMENT1
, pha.ATTRIBUTE9 LEGACY_PO
, pha.COMMENTS Description
, pha.CREATION_DATE
, aps.SEGMENT1 VENDOR_NUM
, aps.VENDOR_NAME
, hlv.LOCATION_CODE SHIP_TO
, hlv.LOCATION_CODE BILL_TO
, pha.CURRENCY_CODE
, pha.RATE_TYPE
, pha.RATE_DATE
, pha.RATE
, papf.full_name
, pla.LINE_NUM
, msik.CONCATENATED_SEGMENTS ITEM
, msik.ATTRIBUTE1 ban_code
, msik.ITEM_TYPE
, pla.UNIT_MEAS_LOOKUP_CODE
, pla.QUANTITY
, pla.UNIT_PRICE
, plla.NEED_BY_DATE
, ppa.SEGMENT1 PROJECT
, ptv.TASK_NUMBER
, pda.EXPENDITURE_TYPE
, pda.EXPENDITURE_ITEM_DATE
, haou.NAME EXPENDITURE_ORG
, gcck.CONCATENATED_SEGMENTS CHARGE_ACCOUNT
, papf2.FULL_NAME PR_UNIT_HEAD
, haou2.NAME DEPARTMENT
, pha.ATTRIBUTE14 DEPARTMENT_HEADER
from
PO_HEADERS_All pha
, AP_SUPPLIERS aps
, HR_LOCATIONS_V hlv
, HR_LOCATIONS_V hlv2
, per_all_people_f papf
, po_lines_all pla
, mtl_system_items_kfv msik
, PA_PROJECTS_ALL ppa
, pa_tasks_v ptv
, po_distributions_all pda
, hr_all_organization_units haou
, gl_code_combinations_kfv gcck
, per_all_people_f papf2
, hr_all_organization_units haou2
,  po_line_locations_all plla
where
pha.VENDOR_ID =aps.VENDOR_ID
and pha.SHIP_TO_LOCATION_ID = hlv.LOCATION_ID
and pha.BILL_TO_LOCATION_ID = hlv2.LOCATION_ID
and pha.AGENT_ID = papf.PERSON_ID
and pha.PO_HEADER_ID = pla.PO_HEADER_ID
and pla.ITEM_ID = msik.INVENTORY_ITEM_ID
and pla.PROJECT_ID = ppa.PROJECT_ID (+)
and pla.TASK_ID = ptv.TASK_ID (+)
and pla.PO_LINE_ID = pda.PO_LINE_ID
and pda.EXPENDITURE_ORGANIZATION_ID = haou.ORGANIZATION_ID (+)
and pda.PO_DISTRIBUTION_ID = gcck.CODE_COMBINATION_ID
and pha.ATTRIBUTE1 = papf2.PERSON_ID (+)
and pha.ATTRIBUTE3 = haou2.ORGANIZATION_ID (+)
and pla.PO_LINE_ID = plla.PO_LINE_ID

Comments

Post a Comment

Popular posts from this blog

Invoice Price Variance Process Accounting

-
Image
Invoice Price Variance Process Accounting: Note: Below Working and results are only applicable where Purchase Orders and Invoice are not routed through LCM Module. For LCM Enabled Items IPVs are routed and transferred to Inventory directly from LCM. Invoice Price Variance is variance between the invoice price and the purchase price for all inventory and work in process related invoice distributions. Payables records invoice price variances when the invoices are matched, approved, and posted. This Article covers the detail Accounting for Oracle Invoice Price Variance Process and also its effect on item Unit Cost in Inventory. Let's Put up a scenario here. We will be receiving an item in Inventory which has currently no On Hand Balance or Unit Cost in Inventory. We Booked a Purchase Order for Quantity 100 at Unit Price of BDT 10 . We did the Receipt of the booked Quantity 100 at 01-August-2014 and receipt delivery was made on same date with all quantity acce...
Read more

Query to Find Out Move Order Number for Work Order with Transacted Quantity

-
Query to Find Out Move Order Number for Work Order with Transacted Quantity: There could be scenarios where for some reason you need to know Move Order number for Work Order Material Line which has be transacted. Once quantity is transacted, no record exists for that move order number to be found. The below query can be used to find out All Move Order Numbers generated for that relevant Work Order. SELECT a.REQUEST_NUMBER, mmt.TRANSACTION_SOURCE_ID, mmt.TRANSACTION_SOURCE_NAME, mmt.* FROM mtl_material_transactions mmt, mtl_txn_request_lines b, mtl_txn_request_headers a, wip_entities w WHERE --mmt.transaction_id = 55356941 --AND mmt.move_order_line_id = b.line_id AND b.header_id = a.header_id and mmt.TRANSACTION_SOURCE_ID = w.WIP_ENTITY_ID and w.WIP_ENTITY_NAME = ' WO30683'       "Change Work Order Number to Relevant Work Order"  Special Thanks to Waqas Munir (PwC Pakistan)  
Read more

DISALLOW USER TO CANCEL PO LINE IF QUANTITY RECEIVED

-
In Oracle EBS R12, currently system allow user to Cancel PO Line even if quantity is received against the PO Line (Partial/Full). Oracle explains this functionality in following note: WHY DOES APPLICATION ALLOW You TO CANCEL A PO LINE WHEN FULLY RECEIVED? (Doc ID 93690.1) For few businesses scenario is not same and admin needs to restrict user to cancel PO Lines against which there is received quantity. Following personalizations on Purchase Order Entry Form helps to serve the business need. PERSONALIZATION 01 Seq Description 10 SMA-LOCK CANCEL PURCHASE LINE BUTTON IF RECEIPT EXISTS Condition Triger Event WHEN-NEW-ITEM-INSTANCE Triger Object PO_DOCON_CONTROL.ACTION Condition :PO_LINES.TOTAL_QUANTITY_RECEIVED is not Null and :PO_LINES.TOTAL_QUANTITY_RECEIVED >0 Actions Seq 10 Type Property Object Type Item Target Obj...
Read more