Requisition Details Query

 select 

-----------------Requisition Header Information

hou.name req_bu,

porh.REQUISITION_NUMBER,

to_char(PORH.CREATION_DATE,'DD/MM/YYYY','NLS_DATE_LANGUAGE =AMERICAN') PR_DT,

PPNF.DISPLAY_NAME ENTER_BY,

porh.DESCRIPTION req_desc,

--PORH.DOCUMENT_STATUS req_status,

req_appr_status.MEANING req_status,

PORH.JUSTIFICATION,

to_char(PORH.APPROVED_DATE,'DD/MM/YYYY','NLS_DATE_LANGUAGE =AMERICAN') APPROVED_DATE,

(CASE WHEN PORD.DISTRIBUTION_QUANTITY IS NULL THEN 

(NVL(PORD.NONRECOVERABLE_TAX,0)*NVL(PORL.RATE,0)+NVL(PORD.DISTRIBUTION_CURRENCY_AMOUNT,0))

ELSE

(PORD.DISTRIBUTION_QUANTITY* NVL(PORL.CURRENCY_UNIT_PRICE,0)+NVL(PORD.NONRECOVERABLE_TAX,0)*NVL(PORL.RATE,0))

end

) req_amount,

(CASE WHEN PORD.DISTRIBUTION_QUANTITY IS NULL THEN NVL(PORD.DISTRIBUTION_CURRENCY_AMOUNT,0)

ELSE (PORD.DISTRIBUTION_QUANTITY* NVL(PORL.CURRENCY_UNIT_PRICE,0))

end

) approval_amount,

null has_attachment,

--------------------------Requisition Lines Information

porl.LINE_NUMBER, PORL.CURRENCY_CODE,

PR_LINE_TYPE.LINE_TYPE_CODE LINE_TYPE,

ITEM.ITEM_NUMBER,

PORL.ITEM_REVISION REVISION,

PORL.ITEM_DESCRIPTION LINE_DESC,

ITEM_CAT.CATEGORY_NAME,

NULL COST_CENTER,

PORL.UNSPSC_CODE,

PORL.QUANTITY LINE_QTY,

uoml.unit_of_measure PR_UOM,

PORL.SECONDARY_UOM_CODE,

PORL.SECONDARY_QUANTITY,

PORL.UNIT_PRICE,

PORL.CURRENCY_UNIT_PRICE, --Foreign currency unit price

PORL.AMOUNT, --Amount on the req line

PORL.CURRENCY_AMOUNT, --Foreign currency amount on the req line

PORL.LINE_STATUS,

PORL.RATE_TYPE, -- Conversion rate type

PORL.RATE_DATE, -- conversion rate date

PORL.RATE, -- conversion rate

porl.NEGOTIATION_REQUIRED_FLAG, --Indicates whether an RFQ is required prior to placement on a purchase order

porl.NEGOTIATED_BY_PREPARER_FLAG, --Indicates whether price has been negotiated by buyer

---------------------Line Details

rqstr.DISPLAY_NAME Requester,

to_char(PORL.NEED_BY_DATE,'DD/MM/YYYY','NLS_DATE_LANGUAGE =AMERICAN') "Requested Delivery Date" ,

--porl.DESTINATION_TYPE_CODE,

(case when porl.ONE_TIME_LOCATION_FLAG = 'Y' and not porl.ONE_TIME_LOCATION_FLAG is null then porl.ONE_TIME_LOCATION_FLAG

else case when porl.DESTINATION_TYPE_CODE='DROP SHIP' then 'External' 

else 'Internal' 

end 

end 

) DELIVER_TO_LOC_TYPE,

porl.DESTINATION_SUBINVENTORY SUBINVENTORY,

LOC.LOCATION_NAME DELIVER_TO_LOCATION, 

DEST_TYE.MEANING DESTINATION_TYPE,

--porl.SOURCE_SUBINVENTORY,

buyer.DISPLAY_NAME BUYER, -- Assigned Buyer or Buyer

porl.SUGGESTED_VENDOR_NAME,

porl.SUGGESTED_VENDOR_SITE,

porl.SUGGESTED_VENDOR_CONTACT,

porl.SUGGESTED_SUPPLIER_ITEM_NUMBER SUPPLIER_ITEM,

PORL.ATTRIBUTE1 PROJECT_TYPE,

PORL.ATTRIBUTE2 PROJECT,

PORL.ATTRIBUTE3 COST_CENTER_DFF,

PORL.ATTRIBUTE4 REMARKS,

PORL.ATTRIBUTE5 CAPEX,

------------------- Distribution Details

pord.DISTRIBUTION_NUMBER dist_num,

gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7 charge_account,

gl_flexfields_pkg.get_description_sql

(gcc.chart_of_accounts_id,

  1,

  gcc.segment1 

)||'.'||

gl_flexfields_pkg.get_description_sql

(gcc.chart_of_accounts_id,

  2,

  gcc.segment2

)||'.'||

gl_flexfields_pkg.get_description_sql

(gcc.chart_of_accounts_id,

  3,

  gcc.segment3

)||'.'||

gl_flexfields_pkg.get_description_sql

(gcc.chart_of_accounts_id,

  4,

  gcc.segment4

)||'.'||

gl_flexfields_pkg.get_description_sql

(gcc.chart_of_accounts_id,

  5,

  gcc.segment5 

)||'.'||

gl_flexfields_pkg.get_description_sql

(gcc.chart_of_accounts_id,

  6,

  gcc.segment6 

)||'.'||

gl_flexfields_pkg.get_description_sql

(gcc.chart_of_accounts_id,

  7,

  gcc.segment7

) code_comb_desc,

pord.PERCENT dist_percnt,

pord.DISTRIBUTION_QUANTITY dist_qty,

pord.DISTRIBUTION_AMOUNT dist_amount, --Amount on the req line

pord.DISTRIBUTION_CURRENCY_AMOUNT dist_curr_amount --Foreign Currency Amount on the requisition distribution

--porl.SUGGESTED_BUYER_ID,

--porl.ASSIGNED_BUYER_ID 

from 

hr_operating_units hou,

POR_REQUISITION_HEADERS_ALL PORH,

POR_REQUISITION_LINES_ALL PORL,

POR_REQ_DISTRIBUTIONS_ALL PORD,

EGP_SYSTEM_ITEMS_VL ITEM,

EGP_CATEGORIES_TL ITEM_CAT,

INV_UNITS_OF_MEASURE_TL UOML,

INV_UNITS_OF_MEASURE_B UOM,

PO_LINE_TYPES_B PR_LINE_TYPE,

PER_PERSON_NAMES_F PPNF,

PER_PERSON_NAMES_F rqstr,

PER_PERSON_NAMES_F buyer,

fnd_lookups req_appr_status,

fnd_lookups DEST_TYE,

HR_LOCATIONS LOC,

gl_code_combinations gcc

where 1=1

AND PORH.REQUISITION_HEADER_ID = PORL.REQUISITION_HEADER_ID

AND PORL.REQUISITION_LINE_ID = PORD.REQUISITION_LINE_ID 

AND PORL.ITEM_ID = ITEM.INVENTORY_ITEM_ID(+) 

    AND PORL.DESTINATION_ORGANIZATION_ID = ITEM.ORGANIZATION_ID(+)

AND ITEM_CAT.CATEGORY_ID(+) = PORL.CATEGORY_ID

AND ITEM_CAT.LANGUAGE(+) = 'US'

AND PR_LINE_TYPE.LINE_TYPE_ID = PORL.LINE_TYPE_ID

and uom.UNIT_OF_MEASURE_ID = uoml.UNIT_OF_MEASURE_ID(+)

and uoml.LANGUAGE(+) = 'US'

AND UOM.uom_code(+) = PORL.UOM_CODE

AND PORL.LINE_STATUS NOT IN ('CANCELED','SPLIT','REPLACED','REINSTATE')

and PORH.DOCUMENT_STATUS = req_appr_status.LOOKUP_CODE

and req_appr_status.lookup_type = 'POR_DOCUMENT_STATUS'

and PORL.DESTINATION_TYPE_CODE = DEST_TYE.LOOKUP_CODE

AND DEST_TYE.LOOKUP_TYPE = 'POR_DESTINATION_TYPE'

and hou.organization_id = porh.REQ_BU_ID

and porh.REQUISITION_NUMBER in( 'ABS-LLC-PR-2022-00402','ABS-LLC-PR-2022-00663')

and ppnf.person_id(+) = PORH.PREPARER_ID

and TRUNC(SYSDATE) BETWEEN trunc(ppnf.effective_start_date(+)) AND trunc(ppnf.effective_end_date(+))

AND ppnf.NAME_TYPE(+) = 'GLOBAL'

and rqstr.person_id(+) = porl.REQUESTER_ID

and TRUNC(SYSDATE) BETWEEN trunc(rqstr.effective_start_date(+)) AND trunc(rqstr.effective_end_date(+))

AND rqstr.NAME_TYPE(+) = 'GLOBAL'

and buyer.person_id(+) = porl.ASSIGNED_BUYER_ID

and TRUNC(SYSDATE) BETWEEN trunc(buyer.effective_start_date(+)) AND trunc(buyer.effective_end_date(+))

AND buyer.NAME_TYPE(+) = 'GLOBAL'

AND LOC.LOCATION_ID = PORL.DELIVER_TO_LOCATION_ID

and pord.code_combination_id = gcc.code_combination_id