SQL Queries

SQL queries are used to retrieve, analyze, filter, combine, and manipulate data stored in relational databases. A few examples are listed below.

Human Capital Management (HCM)

SELECT xep.name LEGAL_ENTITY_NAME, xep.LEGAL_ENTITY_ID, ffbuv.PRIMARY_LEDGER_ID, ffbuv.BU_NAME, ffbuv.SHORT_CODE, ffbuv.BU_ID, ffbuv.LOCATION_ID, ffbuv.DATE_FROM, ffbuv.DATE_TO
FROM fun_all_business_units_v ffbuv,xle_entity_profiles xep
WHERE ffbuv.legal_entity_id = xep.legal_entity_id
AND TRUNC (SYSDATE) BETWEEN ffbuv.DATE_FROM AND ffbuv.DATE_TO

SELECT hauft.ORGANIZATION_ID, hauft.NAME, hauft.TITLE, houcf.SET_ID, SET_NAME, SET_CODE
FROM HR_ORGANIZATION_UNITS_F_TL hauft, HR_ORG_UNIT_CLASSIFICATIONS_F houcf, fnd_setid_sets fss
WHERE hauft.ORGANIZATION_ID = houcf.ORGANIZATION_ID
AND houcf.set_id = fss.set_id
AND hauft.LANGUAGE = fss.LANGUAGE
AND hauft.LANGUAGE = 'US'
AND SYSDATE BETWEEN hauft.EFFECTIVE_START_DATE AND hauft.EFFECTIVE_END_DATE
AND houcf.CLASSIFICATION_CODE = 'DEPARTMENT'

SELECT hapf.POSITION_CODE, hapft.name, hapf.ATTRIBUTE1 arabic_name_dff, hapf.POSITION_ID, hapf.BUSINESS_UNIT_ID, ffbuv.BU_NAME, hapf.ORGANIZATION_ID, hapf.LOCATION_ID, hapf.JOB_ID, hapf.ACTIVE_STATUS, hapf.HIRING_STATUS, hapf.POSITION_TYPE, hapf.FULL_PART_TIME, hapf.FTE, hapf.MAX_PERSONS
FROM hr_all_positions_f_tl hapft, hr_all_positions_f hapf, fun_all_business_units_v ffbuv
where hapft.position_id = hapf.position_id
and hapf.BUSINESS_UNIT_ID = ffbuv.BU_ID
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(hapft.effective_start_date, sysdate) ) AND  TRUNC(NVL(hapft.effective_end_date,sysdate) )
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(hapf.effective_start_date, sysdate) ) AND  TRUNC(NVL(hapf.effective_end_date,sysdate) )
AND LANGUAGE = 'US'

SELECT 
	pba.bank_name bank_name,
	pba.bank_branch_name ,
	pba.bank_account_num clear_bank_account_number,
	pba.iban_number clear_iban,
	cbv.bank_number,
	papf.person_id,
	papf.person_number,
	popf.BASE_ORG_PAY_METHOD_NAME
FROM 
	pay_bank_accounts pba,
	pay_person_pay_methods_f pppmf,
	pay_org_pay_methods_f popf,
	(select * 
	   from pay_pay_relationships_dn a 
	  where a.payroll_relationship_id = 
			(select max (payroll_relationship_id) 
			   from pay_pay_relationships_dn b 
			  where a.person_id = b.person_id)) ppr,
	per_all_people_f papf,
	ce_banks_v cbv
 WHERE     pppmf.bank_account_id = pba.bank_account_id(+)
	   AND pppmf.org_payment_method_id = popf.org_payment_method_id
	   AND ppr.payroll_relationship_id = pppmf.payroll_relationship_id
	   AND papf.person_id = ppr.person_id
	   AND (papf.person_number IN (:p_person_number) OR LEAST (:p_person_number) IS NULL)
	   AND cbv.bank_name(+) = pba.bank_name
	   AND TRUNC (sysdate) BETWEEN TRUNC (NVL (pppmf.effective_start_date,sysdate)) AND TRUNC (NVL (pppmf.effective_end_date,sysdate))
	   AND TRUNC (sysdate) BETWEEN TRUNC (NVL (papf.effective_start_date,sysdate)) AND TRUNC (NVL (papf.effective_end_date,sysdate))
	   AND TRUNC (sysdate) BETWEEN TRUNC (NVL (popf.effective_start_date,sysdate)) AND TRUNC (NVL (popf.effective_end_date,sysdate))
	   AND TRUNC (sysdate) BETWEEN TRUNC (NVL (pba.start_date,sysdate)) AND TRUNC (NVL (pba.end_date,sysdate))
	   AND TRUNC (sysdate) BETWEEN TRUNC (NVL (ppr.start_date,sysdate)) AND TRUNC (NVL (ppr.end_date,sysdate))

SELECT  papf.person_number,
		papf.person_id per_id,
		paam.assignment_id,
		ppnf.display_name,
		ppg.SEGMENT1 FIELD1,
		ppg.SEGMENT2 FIELD2,
		ppg.SEGMENT3 FIELD3,
		ppg.SEGMENT4 FIELD4,
		ppg.SEGMENT5 FIELD5,
		ppg.segment6 FIELD6,
		ppg.segment7 FIELD7,
		ppg.segment8 FIELD8,
		ppg.SEGMENT9 FIELD9,
		ppg.SEGMENT10 FIELD10,
		ppg.SEGMENT11 FIELD11,
		ppg.SEGMENT12 FIELD12,
		paam.assignment_status_type
FROM     per_person_secured_list_v papf
		,per_all_assignments_f paam
		,per_persons pp
		,per_people_groups ppg
		,per_person_names_f ppnf
WHERE   papf.person_id = paam.person_id
and    	papf.person_id = ppnf.person_id(+) --name may be missing
AND 	papf.person_id = pp.person_id(+) --name may be missing
AND     ppg.people_group_id(+) = paam.people_group_id --- people group segments may be missing--
AND     paam.assignment_type = 'E'         -- Default Conditions
AND     paam.effective_latest_change = 'Y' -- Default Conditions
AND     paam.primary_flag = 'Y'           ---primary assignment
AND     UPPER (paam.assignment_status_type) = 'ACTIVE'
AND     ppnf.name_type = 'GLOBAL'
AND     TRUNC (paam.effective_start_date) BETWEEN TRUNC ( ppnf.effective_start_date) AND      TRUNC ( ppnf.effective_end_date)
AND     TRUNC (paam.effective_start_date) BETWEEN TRUNC ( papf.effective_start_date) AND      TRUNC ( papf.effective_end_date)
AND     NVL(:p_start_date,TRUNC(sysdate)) BETWEEN TRUNC ( paam.effective_start_date) AND      TRUNC (paam.effective_end_date)
AND 	(papf.person_number IN (:p_person_number) OR LEAST (:p_person_number) IS NULL)

SELECT ppnf.FIRST_NAME||' '||ppnf.NAM_INFORMATION15||' '||ppnf.NAM_INFORMATION16||' '||ppnf.LAST_NAME manager_name
FROM per_person_names_f            ppnf,
   per_assignment_supervisors_f  sup,
   per_person_secured_list_v     papf1,
   per_person_secured_list_v     mgr_papf
WHERE     ppnf.person_id = sup.manager_id
   AND papf1.person_id = sup.person_id
   AND TRUNC(sysdate) BETWEEN TRUNC (sup.effective_start_date)
						  AND TRUNC (sup.effective_end_date)
   AND TRUNC(sysdate) BETWEEN TRUNC (papf1.effective_start_date)
						  AND TRUNC (papf1.effective_end_date)
   AND TRUNC(sysdate) BETWEEN TRUNC (ppnf.effective_start_date)
						  AND TRUNC (ppnf.effective_end_date)
   AND UPPER (ppnf.name_type) = 'GLOBAL' --should fetch these details only
   AND sup.manager_type = 'LINE_MANAGER' --- to fetch technical manager detail
   AND mgr_papf.person_id = sup.manager_id
   AND papf1.person_id = pasf.manager_id
   AND TRUNC(sysdate) BETWEEN TRUNC (mgr_papf.effective_start_date)
						  AND TRUNC (mgr_papf.effective_end_date)

SELECT Max(fl.meaning)
  FROM fnd_lookup_VALUEs_tl fl
 WHERE UPPER(TRIM(fl.lookup_type)) = 'NATIONALITY' --to get nationality
   AND fl.lookup_code (+) = pcz.legislation_code    --to get all records though legislation  details not included for all
   AND fl.language = userenv('LANG')

SELECT * 
  FROM per_people_legislative_f 
 WHERE LEGISLATION_CODE = 'KW'

select person_id, PASSPORT_NUMBER, PASSPORT_TYPE, ISSUING_AUTHORITY, ISSUING_COUNTRY, ISSUE_DATE, ISSUING_LOCATION, EXPIRATION_DATE
from per_passports where LEGISLATION_CODE = 'KW'

SELECT
    pbt.balance_name,
    pet.element_name,
    piv.base_name AS input_value,
    pbf.scale AS feed_scale,
    CASE
        WHEN pbf.scale = 1 THEN 'ADD'
        WHEN pbf.scale = -1 THEN 'SUBTRACT'
        ELSE 'OTHER'
    END AS feed_type
FROM
    pay_balance_types_vl pbt
    JOIN pay_balance_feeds_f pbf
        ON pbf.balance_type_id = pbt.balance_type_id
    JOIN pay_input_values_f piv
        ON piv.input_value_id = pbf.input_value_id
    JOIN pay_element_types_vl pet
        ON pet.element_type_id = piv.element_type_id
WHERE
    UPPER(pbt.balance_name) = '<<BALANCE_NAME>>'
    AND TRUNC(SYSDATE) BETWEEN pbf.effective_start_date AND pbf.effective_end_date
    AND TRUNC(SYSDATE) BETWEEN piv.effective_start_date AND piv.effective_end_date
ORDER BY
    pet.element_name

-- 1) Element entries
SELECT 'ENTRY'                  AS source,
       pet.element_name,
       piv.base_name            AS input_value,
       peev.screen_entry_value  AS value,
       pee.effective_start_date,
       pee.effective_end_date
FROM   pay_element_entries_f      pee
JOIN   pay_element_entry_values_f peev ON peev.element_entry_id = pee.element_entry_id
                                      AND pee.effective_start_date BETWEEN peev.effective_start_date AND peev.effective_end_date
JOIN   pay_input_values_f         piv  ON piv.input_value_id = peev.input_value_id
                                      AND pee.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
JOIN   pay_element_types_vl       pet  ON pet.element_type_id = pee.element_type_id
                                      AND pee.effective_start_date BETWEEN pet.effective_start_date AND pet.effective_end_date
JOIN   per_all_people_f           papf ON papf.person_id = pee.person_id
                                      AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
WHERE  papf.person_number = :p_person_no
AND    UPPER(pet.element_name) LIKE '<<Element Name>>'
ORDER  BY pet.element_name, pee.effective_start_date, piv.base_name

-- 2) Payroll run results (what payroll calculated), previous + current month
SELECT 'RUN RESULT'             AS source,
       pet.element_name,
       piv.base_name            AS input_value,
       prv.result_value         AS value,
       ptp.end_date             AS period_end,
       ptp.status               AS period_status
FROM   pay_run_results         prr
JOIN   pay_run_result_values   prv  ON prv.run_result_id = prr.run_result_id
JOIN   pay_input_values_f      piv  ON piv.input_value_id = prv.input_value_id
JOIN   pay_element_types_vl    pet  ON pet.element_type_id = prr.element_type_id
JOIN   pay_payroll_assignments ppa  ON ppa.payroll_assignment_id = prr.payroll_assignment_id
JOIN   pay_payroll_rel_actions ppra ON ppra.payroll_rel_action_id = prr.payroll_rel_action_id
JOIN   pay_payroll_actions     ppac ON ppac.payroll_action_id = ppra.payroll_action_id
JOIN   pay_time_periods        ptp  ON ptp.time_period_id = ppac.earn_time_period_id
                                   AND ptp.payroll_id     = ppac.payroll_id
JOIN   per_all_people_f        papf ON papf.person_id = ppa.person_id
                                   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
WHERE  papf.person_number = :p_person_no
AND    UPPER(pet.element_name) LIKE '<<Element Name>>'
AND    ptp.end_date IN (LAST_DAY(TRUNC(:cperiod)), TRUNC(:cperiod, 'MM') - 1)
AND    ppra.retro_component_id IS NULL
AND    TRUNC(ptp.end_date) BETWEEN piv.effective_start_date AND piv.effective_end_date
AND    TRUNC(ptp.end_date) BETWEEN pet.effective_start_date AND pet.effective_end_date
ORDER  BY ptp.end_date, pet.element_name, piv.base_name

SELECT
    pbt.balance_name,
    pet.element_name,
    piv.base_name AS input_value,
    pbf.scale AS feed_scale,
    CASE
        WHEN pbf.scale = 1 THEN 'ADD'
        WHEN pbf.scale = -1 THEN 'SUBTRACT'
        ELSE 'OTHER'
    END AS feed_type
FROM
    pay_balance_types_vl pbt
    JOIN pay_balance_feeds_f pbf
        ON pbf.balance_type_id = pbt.balance_type_id
    JOIN pay_input_values_f piv
        ON piv.input_value_id = pbf.input_value_id
    JOIN pay_element_types_vl pet
        ON pet.element_type_id = piv.element_type_id
WHERE
    UPPER(pbt.balance_name) = '<<BALANCE_NAME>>'
    AND TRUNC(SYSDATE) BETWEEN pbf.effective_start_date AND pbf.effective_end_date
    AND TRUNC(SYSDATE) BETWEEN piv.effective_start_date AND piv.effective_end_date
ORDER BY
    pet.element_name

SELECT
       papf.person_number,
       ppnf.display_name          employee_name,
       paam.assignment_number,
       pap.payroll_name,
       prd.payroll_id,
       prd.start_date             payroll_start_date,
       prd.end_date               payroll_end_date
FROM   per_all_people_f papf,
       per_person_names_f ppnf,
       per_all_assignments_m paam,
       pay_payroll_assignments ppa,
       pay_assigned_payrolls_dn prd,
       pay_all_payrolls_f pap

WHERE  papf.person_id = ppnf.person_id
AND    papf.person_id = paam.person_id
AND    paam.assignment_id = ppa.hr_assignment_id
AND    ppa.payroll_term_id = prd.payroll_term_id
AND    prd.payroll_id = pap.payroll_id
AND    ppnf.name_type = 'GLOBAL'
AND    paam.assignment_type = 'E'
AND    paam.primary_flag = 'Y'
AND    paam.effective_latest_change = 'Y'
AND    TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND    TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
AND    TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND    TRUNC(SYSDATE) BETWEEN prd.start_date AND prd.end_date
AND    TRUNC(SYSDATE) BETWEEN pap.effective_start_date AND pap.effective_end_date
AND    papf.person_number = '<<PERSON NUMBER>>'
ORDER BY papf.person_number

Supply Chain Management (SCM)

SELECT DISTINCT
ITEMS.ITEM_NUMBER,
ITEMCATS.category_id,
CATSETS.CATALOG_CODE,
ECT.CATEGORY_NAME,
ITEMS.CREATION_DATE,
ITEMS.Organization_ID,
ORG.ORGANIZATION_CODE,
items.DESCRIPTION  item_DESC ,
items.PRIMARY_UOM_CODE,
ITEMS.attribute1 LEGACY_ITEM

FROM egp_item_cat_assignments ITEMCATS,
egp_category_sets_b 	CATSETS,
egp_system_items 		ITEMS,
inv_org_parameters 		ORG,
EGP_CATEGORIES_TL 		ECT,
EGP_SYSTEM_ITEMS_TL    	ITM_TL


WHERE 1=1
AND CATSETS.category_set_id 	= ITEMCATS.category_set_id
AND ITEMS.inventory_item_id 	= ITEMCATS.inventory_item_id
AND ITM_TL.LANGUAGE 			= USERENV ('LANG')
AND Items.INVENTORY_ITEM_ID 	= ITM_TL.INVENTORY_ITEM_ID
AND ORG.organization_id 		= ITEMS.organization_id
AND ITEMCATS.category_id		= ECT.category_id
AND CATSETS.CATEGORY_SET_ID = 300000006022015
AND BUSINESS_UNIT_ID = NVL (:P_BU, BUSINESS_UNIT_ID)
AND ( ( CATSETS.control_level 	= 1
AND ITEMCATS.organization_id 	= ORG.master_organization_id )
OR ( CATSETS.control_level = 2
	AND ITEMCATS.organization_id 	= ORG.organization_id ) )

ORDER BY ITEMS.Organization_ID, ITEM_NUMBER

select CATEGORY_CODE,CATEGORY_NAME  from EGP_CATEGORIES_VL WHERE ENABLED_FLAG = 'Y'

SELECT
IOD.ORGANIZATION_CODE ,
IOD.ORGANIZATION_Name,
loc.ORGANIZATION_ID,
LOC.SUBINVENTORY_ID,
LOC.SUBINVENTORY_CODE,
LOC.SEGMENT1,LOC.SEGMENT2,
LOC.SEGMENT1||'.'||LOC.SEGMENT2 LOCATOR_Combination
FROM inv_item_locations loc,INV_ORGANIZATION_DEFINITIONS_V IOD
WHERE LOC.ORGANIZATION_ID = IOD.ORGANIZATION_ID
AND LOC.DISABLE_DATE IS NULL
AND
(loc.ORGANIZATION_ID in (:P_ORG)
or least(:P_ORG) is null
)
ORDER BY ORGANIZATION_CODE,SUBINVENTORY_CODE

SELECT
       PHA.SEGMENT1 PO_NUMBER,
       PHA.REVISION_NUM,
       PLA.LINE_NUM,
       PLA.UNIT_PRICE PO_UNIT_PRICE,
       PLA.ITEM_DESCRIPTION ,
       PHA.PO_HEADER_ID ,
       PLA.PO_LINE_ID ,
	   RCT.TRANSACTION_ID ,
	   RCT.Shipment_HEADER_ID ,
	   RSH.SHIPMENT_NUM  ,
       RSH.COMMENTS ,
       RSH.PACKING_SLIP,
       RSH.ATTRIBUTE1||' '||RSH.COMMENTS SUP_INV_NO,
	   TO_CHAR(RSH.SHIPPED_DATE,'DD-MM-YYYY') as SHIPPED_DATE,
	   PHA.CURRENCY_CODE,
       RSH.RECEIPT_NUM ,
	      TO_CHAR (RCT.TRANSACTION_DATE,
                'DD-Mon-YYYY',
                'nls_date_language=English')
          EXPECTED_RECEIPT_DATE,
       RCT.Transaction_type,
	   (SELECT PPNF.DISPLAY_NAME
          FROM PER_PERSON_NAMES_F PPNF
         WHERE     TRUNC (SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE
                                       AND PPNF.EFFECTIVE_END_DATE
               AND PPNF.NAME_TYPE = 'GLOBAL'
               AND PPNF.PERSON_ID = RCT.EMPLOYEE_ID)
          RECIEVER_NAME ,
		  (SELECT UNIT_OF_MEASURE
          FROM INV_UNITS_OF_MEASURE_B UOMB, INV_UNITS_OF_MEASURE_TL UOMT
         WHERE     UOMB.UOM_CODE = RSL.UOM_CODE
               AND UOMB.UNIT_OF_MEASURE_ID = UOMT.UNIT_OF_MEASURE_ID
               AND UOMT.LANGUAGE = 'US')
          UOM_CODE ,
		  RSL.QUANTITY_RECEIVED,
		 (PLA.unit_price * RSL.QUANTITY_RECEIVED)Amount ,
          RSL.LINE_NUM  Release_Revision ,
		  RSL.PO_DISTRIBUTION_ID ,
		  (
select
SHIPMENT_NUMBER
fROM CMR_PURCHASE_ORDER_DTLS POD
where
POD.PO_LINE_LOCATION_ID =PLLA.LINE_LOCATION_ID
AND RowNum=1
)Schedule ,
HP.PARTY_NAME SUPPLIER_NAME,
 HL_LOC.LOCATION_CODE ,
PSSA.VENDOR_SITE_CODE ,
ESB.ITEM_NUMBER ,
ESB.INVENTORY_ITEM_ID ,
(
SELECT AIA.INVOICE_NUM
FROM  AP_INVOICES_ALL AIA,
      AP_INVOICE_LINES_ALL ALLO
where
 AIA.INVOICE_ID =ALLO.INVOICE_ID
AND ALLO.PO_HEADER_ID =PHA.PO_HEADER_ID
AND ESB.INVENTORY_ITEM_ID =ALLO.INVENTORY_ITEM_ID
AND ROWNUM = 1
)invoice_number,
RCT.ORGANIZATION_ID,
(SELECT DISTINCT bu.bu_name
FROM fusion.FUN_ALL_BUSINESS_UNITS_V BU, fusion.inv_org_parameters OP, fusion.gl_ledgers gl, fusion.HR_ORGANIZATION_UNITS HOU, fusion.HR_ORG_UNIT_CLASSIFICATIONS_X HOUC
WHERE bu.bu_id = op.business_unit_id
AND bu.primary_ledger_id = gl.ledger_ID
AND hou.organization_id = HOUC.organization_id
and houc.CLASSIFICATION_CODE = 'INV'
AND hou.organization_id = OP.organization_id
AND hou.organization_id = RCT.ORGANIZATION_ID) BU_NAME

  FROM
       PO_LINES_ALL PLA,
	   PO_HEADERS_ALL PHA ,
       RCV_TRANSACTIONS RCT ,
       RCV_SHIPMENT_HEADERS RSH ,
       RCV_SHIPMENT_LINES RSL ,
       PO_LINE_LOCATIONS_ALL PLLA ,
	   POZ_SUPPLIERS PS,
       HZ_PARTIES HP ,
	   HR_LOCATIONS HL_LOC,
	   POZ_SUPPLIER_SITES_ALL_M PSSA ,
	   EGP_SYSTEM_ITEMS_B ESB ,
	   EGP_SYSTEM_ITEMS_TL EST


 WHERE     1 = 1
       AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID

	   aND RCT.po_HEADER_ID= PHA.PO_HEADER_ID
	   AND RCT.PO_LINE_ID = PLA.PO_LINE_ID
	   aND RCT.Shipment_HEADER_ID = RSH.Shipment_HEADER_ID
       AND RCT.Transaction_type ='RECEIVE'
	   AND RSH.Shipment_HEADER_ID =RSL.Shipment_HEADER_ID
	  AND RSL.SHIPMENT_LINE_ID = RCT.SHIPMENT_LINE_ID
	  aND PLLA.PO_HEADER_ID = PLA.PO_HEADER_ID
	  aND PLLA.PO_LINE_ID = PLLA.PO_LINE_ID
	  AND RCT.PO_LINE_LOCATION_ID = PLLA.LINE_LOCATION_ID
	  AND PS.VENDOR_ID = RCT.VENDOR_ID
      AND PS.PARTY_ID = HP.PARTY_ID
      AND HP.STATUS = 'A'
	  AND  HL_LOC.LOCATION_ID(+) = RCT.LOCATION_ID
	  AND PSSA.VENDOR_SITE_ID = RCT.VENDOR_SITE_ID

	   AND PLA.ITEM_ID = ESB.INVENTORY_ITEM_ID
       AND PLLA.SHIP_TO_ORGANIZATION_ID = ESB.ORGANIZATION_ID
	   AND EST.INVENTORY_ITEM_ID = ESB.INVENTORY_ITEM_ID
       AND EST.ORGANIZATION_ID = ESB.ORGANIZATION_ID
       AND EST.LANGUAGE = 'US'
	   AND (PHA.BILLTO_BU_ID = :P_BU OR :P_BU IS NULL)
 AND HP.PARTY_ID = NVL(:Vendor_id,HP.PARTY_ID)
  AND PSSA.VENDOR_SITE_CODE = nvl(:Site ,PSSA.VENDOR_SITE_CODE)
   AND PHA.SEGMENT1 = nvl(:PO_number,PHA.SEGMENT1)
  AND RSH.RECEIPT_NUM = NVL(:RECEIPT_num,RSH.RECEIPT_NUM)
AND RSL.LINE_NUM  =NVL(:ReleaseRevision,RSL.LINE_NUM)
	  order by RECEIPT_NUM, LINE_NUM ASc

/******************************************************************************************************************
DATE              DEVELOPER            VERSION          DESCRIPTION
10-04-2022        MOHAMMED AHMAD       1.0              STOCK ONHAND REPORT
*******************************************************************************************************************/
select
    inv_org_name
  , inv_org_code
  , organization_id
  , inventory_item_id
  , item_number
  , REPLACE (item_description, CHR(10), ' ') item_description
  , MIN_QTY
  , MAX_QTY
  , item_Traceable
  , Manufacturer
  , ITEM_BRAND_NAME
  , CATEGORY_NAME item_category
  , primary_uom
  , nvl(secondary_uom, '-')                                                                                     secondary_uom
  , quantity                                                                                                    primary_uom_quantity
  , decode(nvl(secondary_uom, primary_uom), '-', 0, nvl(round((quantity * NVL (conversion_rate, 1)), 10), 0))                      secondary_uom_quantity
  , primary_uom_cost                                                                                            primary_uom_cost
  , decode(nvl(secondary_uom, '-'), '-', 0, nvl(round(( primary_uom_cost ) * NVL (conversion_rate, 1), 10), 0))           secondary_uom_cost
  , quantity * primary_uom_cost                                                                                 primary_uom_value
  , decode(nvl(secondary_uom, '-'), '-', 0, nvl(round((quantity * primary_uom_cost  * NVL (conversion_rate, 1)), 10), 0)) secondary_uom_value
  , NVL (conversion_rate, 1) uom_conversion
  , subinventory_code
  , lot_number
  , locator
  , to_char (expiration_date, 'DD-MM-YYYY') expiration_date
  , to_char (lot_origination_date, 'DD-MM-YYYY') lot_origination_date
  , lot_expiry_days lot_expiry_days
  , CASE WHEN lot_expiry_days <= 0
		THEN
			'ASON-Today'
		ELSE
			CASE WHEN lot_expiry_days > 0 AND lot_expiry_days <= 30 
				THEN
					'ONE-Month'
				WHEN lot_expiry_days > 30 AND lot_expiry_days <= 180 
				THEN
					'SIX-Months'
				WHEN lot_expiry_days > 180 AND lot_expiry_days <= 360 
				THEN
					'During the Year'
				ELSE
					'More then Year'
				END
		END EXPIRY
  , Supplier
  , LAST_PO_PRICE, LEGACY_ITEM
from
	(
		select
		    org.organization_code             inv_org_code
		  , org.organization_name             inv_org_name
		  , imt.organization_id
		  , EC.CATEGORY_NAME				  CATEGORY_NAME
		  , imt.inventory_item_id
		  , esib.item_number 				  item_number
		  , esib.description 				  item_description
		  , esib.MIN_MINMAX_QUANTITY		  MIN_QTY
		  , esib.MAX_MINMAX_QUANTITY		  MAX_QTY
		  , NVL(esib.ATTRIBUTE1,'No')		  item_Traceable
		  , esib.attribute4 				  Manufacturer
		  , esib.ATTRIBUTE5					  ITEM_BRAND_NAME
		  /*, (
				select
					ect.category_code
				from
					egp_category_sets_b catsets
				  , egp_item_categories items
				  , egp_categories_vl   ect
				where
					catsets.category_set_id         = items.category_set_id
					and items.inventory_item_id     = imt.inventory_item_id
					and items.organization_id       = imt.organization_id
					and items.category_id           = ect.category_id
					and upper(catsets.catalog_code) = 'DHAMAN_CATALOG'
			)
			item_category*/
		  , (
				select
					unit_of_measure
				from
					inv_units_of_measure_vl ium
				where
					ium.uom_code = esib.primary_uom_code
			)
			primary_uom
		  , (
				select
					unit_of_measure
				from
					inv_units_of_measure_vl ium
				where
					ium.uom_code = esib.secondary_uom_code
			)
			secondary_uom
		  , (
				select
					iuc.conversion_rate
				from
					inv_uom_conversions iuc
				where
					iuc.inventory_item_id = imt.inventory_item_id
					and iuc.uom_code      = esib.primary_uom_code
					and trunc (sysdate)  <= trunc (nvl (DISABLE_DATE, sysdate))
			)
			                                                 conversion_rate
		  , SUM (nvl(itln.primary_quantity, imt.primary_quantity)) quantity
		  , nvl(
				 (
					 select
						 round(sum(ccv.unit_cost), 10) unit_cost
					 from
						 CST_B_COSTED_VU_COSTS_V ccv
					   , cst_cost_inv_orgs     csio
					   , cst_val_units_vl      cvu
					   , cst_val_structures_vl cvs
					   , inv_org_parameters    iop
					   , cst_cost_orgs_v       cco
					 where
						 trunc (:p_date) between snapshot_date and
						 (
							 eff_to_date - 1
						 )
						 and ccv.cost_org_id       = csio.cost_org_id
						 and ccv.cost_org_id       = cco.cost_org_id
						 and ccv.val_unit_id       = cvu.val_unit_id
						 and cvu.val_structure_id  = cvs.val_structure_id
						 and csio.inv_org_id       = iop.organization_id
						 and csio.inv_org_id       = imt.organization_id
						 and ccv.inventory_item_id = imt.inventory_item_id
						 and cvu.val_unit_code     =
							 (
								 case
									 when cvs.val_structure_code IN ('DH Material Valuation Asset','DH Pharma Valuation Asset')
										 then cco.cost_org_code
											 || '-'
											 || iop.organization_code
											 || '-'
											 || REPLACE (imt.subinventory_code,'MNSTR-1','MNSTR\-1')
										 else cco.cost_org_code
											 || '-'
											 || iop.organization_code
											 || '-'
											 || imt.subinventory_code
								 end
							 )
				)
				,
				(SELECT   ROUND (SUM (NVL (CPCC.UNIT_COST_AVERAGE, 0)),10) AVG_COST
				   FROM   CST_PERPAVG_COST CPCC
						 ,CST_COST_ELEMENTS_B cceb
						 ,CST_TRANSACTIONS CT
						 ,CST_COST_BOOKS_B ccb
				  WHERE  CPCC.INVENTORY_ITEM_ID = imt.inventory_item_id
					  and cceb.cost_element_id = CPCC.cost_element_id
					  AND CPCC.TRANSACTION_ID = CT.TRANSACTION_ID
					  and CPCC.cost_book_id = ccb.cost_book_id
					  AND CT.INVENTORY_ORG_ID = imt.organization_id
					  AND CPCC.TRANSACTION_ID IN (SELECT MAX (CPCC1.TRANSACTION_ID)
									FROM CST_PERPAVG_COST CPCC1, CST_TRANSACTIONS CT1
								   WHERE CPCC1.INVENTORY_ITEM_ID = CPCC.INVENTORY_ITEM_ID--300000041240358
								     AND CT1.INVENTORY_ORG_ID = CT.INVENTORY_ORG_ID--300000033854146
									 AND CPCC1.TRANSACTION_ID = CT1.TRANSACTION_ID
									 AND TRUNC(:p_date) BETWEEN TRUNC (CPCC1.cost_date) AND TRUNC (CPCC1.COST_END_DATE)-1)
					  AND TRUNC(:p_date) BETWEEN TRUNC (CPCC.cost_date) AND TRUNC (CPCC.COST_END_DATE)-1)
				  )                                                 primary_uom_cost
		  , imt.subinventory_code
		  , (
				select
					fnd_flex_ext.get_segs('INV', 'MTLL', iil.structure_instance_number, iil.inventory_location_id, iil.subinventory_id)
				from
					inv_item_locations iil
				where
					iil.subinventory_code         = imt.subinventory_code
					and iil.organization_id       = imt.organization_id
					and iil.inventory_location_id = imt.locator_id
			)
			locator
		  , itln.lot_number
		  , (
				select
					iln.expiration_date
				from
					inv_lot_numbers iln
				where
					iln.inventory_item_id   = imt.inventory_item_id
					and iln.organization_id = imt.organization_id
					and iln.lot_number      = itln.lot_number
				group by
					iln.expiration_date
			)
			expiration_date
		  ,	(
				select 
					origination_date 
				from 
					inv_lot_numbers
				where 
					inventory_item_id 		= imt.inventory_item_id
				and organization_id 		= imt.organization_id
				and lot_number 				= itln.lot_number
			)
			lot_origination_date
		  , ROUND 
			(
				(
					select
						iln.expiration_date
					from
						inv_lot_numbers iln
					where
						iln.inventory_item_id   = imt.inventory_item_id
						and iln.organization_id = imt.organization_id
						and iln.lot_number      = itln.lot_number
					group by
						iln.expiration_date
				)
				-
				TRUNC (SYSDATE), 3
			)
			lot_expiry_days
		  /*, (
			 SELECT psv.vendor_name
			   FROM RCV_TRANSACTIONS 	RCV_T
				  , poz_suppliers_v		psv
			  WHERE RCV_T.TRANSACTION_ID 	 = RCV_TRANSACTION_ID
				AND RCV_T.VENDOR_ID 		 = psv.vendor_id
			) Supplier*/
		  , (
			 SELECT 
				listagg( vendor_name, ', ') within group (order by vendor_name)
			   FROM
				(SELECT DISTINCT psv.vendor_name
				   FROM RCV_TRANSACTIONS 				RCV_T
					  , poz_suppliers_v					psv
					  , inv_transaction_lot_numbers		itln1
					  , inv_material_txns				imt1
				  WHERE RCV_T.TRANSACTION_ID 	 = imt1.RCV_TRANSACTION_ID
					AND RCV_T.VENDOR_ID 		 = psv.vendor_id
					and imt1.transaction_id      = itln1.transaction_id (+)
					and itln1.lot_number		 = itln.lot_number
				GROUP BY psv.vendor_name)
			) Supplier
		  , (
			SELECT 
				MAX (UNIT_PRICE)
			  FROM PO_LINES_ALL
			 WHERE ITEM_ID = imt.inventory_item_id
			 and PO_HEADER_ID IN (
				SELECT MAX (PO_HEADER_ID)
				FROM PO_LINES_ALL
				WHERE ITEM_ID = imt.inventory_item_id)
			) LAST_PO_PRICE, esib.attribute1 LEGACY_ITEM
		from
			egp_system_items_v             esib
		  , EGP_ITEM_CATEGORIES 		   eic
		  , EGP_CATEGORIES_VL			   EC
		  , inv_material_txns              imt
		  , inv_transaction_lot_numbers    itln
		  , inv_organization_definitions_v org
		  , inv_txn_source_types_vl        itst
		  , inv_transaction_types_vl       itt
		where
			imt.organization_id                 = esib.organization_id
			and imt.inventory_item_id           = esib.inventory_item_id
			and imt.organization_id             = eic.organization_id
			and imt.inventory_item_id           = eic.inventory_item_id
			and eic.category_id					= ec.category_id(+)
			and imt.transaction_id              = itln.transaction_id (+)
			and imt.inventory_item_id           = itln.inventory_item_id (+)
			and imt.organization_id             = itln.organization_id (+)
			and imt.organization_id             = org.organization_id
			and imt.transaction_type_id         = itt.transaction_type_id
			and itst.transaction_source_type_id = imt.transaction_source_type_id
			--and EC.CATEGORY_NAME = 'Tamdeen Item Catalog'
			and eic.CATEGORY_SET_ID = 300000006022015
			and TRUNC (imt.transaction_date)	<= :p_date
			and 
			(
				imt.locator_id in (:p_locator)
				or least(:p_locator) is null
			)
			and 
			(
				eic.category_id in (:p_item_cat)
				or least(:p_item_cat) is null
			)
			and 
			(
				imt.subinventory_code in (:p_subinven)
				or least(:p_subinven) is null
			)
			and
			(
				esib.item_number like (:p_item)
				or least(:p_item) is null
			)
			and
			(
				org.organization_name in (:p_inv_org)
				or least(:p_inv_org) is null
			)
			and :p_report = 'OISL'
	  having SUM (nvl(itln.primary_quantity, imt.primary_quantity)) > 0
	group by 
			org.organization_code
		  , org.organization_name
		  , ec.CATEGORY_NAME
		  , imt.inventory_item_id
		  , esib.item_number
		  , esib.description
		  , esib.MIN_MINMAX_QUANTITY
		  , esib.MAX_MINMAX_QUANTITY
		  , esib.attribute1
		  , esib.attribute4
		  , esib.ATTRIBUTE5
		  --, imt.inventory_item_id
		  , imt.organization_id
		  , itln.lot_number
		  , imt.locator_id
		  , esib.primary_uom_code
		  , esib.secondary_uom_code
		  , imt.subinventory_code, esib.attribute1
		  --, RCV_TRANSACTION_ID
	)
where
	-- decode(nvl(to_char(lot_number), 'xx'), 'xx', sysdate, nvl(expiration_date, sysdate)) between nvl(:p_lot_exp_from, decode(nvl(to_char(lot_number), 'xx'), 'xx', sysdate, nvl(expiration_date, sysdate))) and nvl(:p_lot_exp_to, decode(nvl(to_char(lot_number), 'xx'), 'xx', sysdate, nvl(expiration_date, sysdate)))
	--nvl(expiration_date, sysdate)     > nvl(:p_lot_exp_from, to_date('01-01-1000', 'dd-mm-yyyy'))
	--and nvl(expiration_date, sysdate) < nvl(:p_lot_exp_to, to_date('31-12-4712', 'dd-mm-yyyy'))
	/*(
	CASE WHEN lot_expiry_days <= 0
		THEN
			'ASON-Today'
		ELSE
			CASE WHEN lot_expiry_days > 0 AND lot_expiry_days <= 30
				THEN
					'ONE-Month' 
				 WHEN lot_expiry_days > 30 AND lot_expiry_days <= 180
				THEN
					'SIX-Months'
				END
		END IN (:p_expiry)
		or least(:p_expiry) is null
	)
	and*/
	(
		supplier LIKE '%'||:p_supplier||'%'
		or least(:p_supplier) is null
	)
order by 
	inv_org_name, 
	item_number asc

SELECT inv.ORGANIZATION_CODE ORGANIZATION_CODE, inv.ORGANIZATION_NAME
FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.inv_organization_definitions_v inv,
fusion.per_users pu,
fusion.per_roles_dn_vl pr
WHERE inv.ORGANIZATION_ID = role.INV_ORGANIZATION_ID
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME
and pu.username = fnd_global.user_name
order by ORGANIZATION_NAME

SELECT DISTINCT fnd_flex_ext.get_segs('INV', 'MTLL', iil.structure_instance_number, iil.inventory_location_id, iil.subinventory_id) LOCATOR, iil.inventory_location_id
FROM inv_item_locations iil, inv_organization_definitions_v org, inv_secondary_inventories a
WHERE 1 = 1
and iil.organization_id             = org.organization_id
and org.organization_id = a.organization_id
and (org.organization_name in (:p_inv_org) or least(:p_inv_org) is null)
and (a.SECONDARY_INVENTORY_NAME in (:p_subinven) or least(:p_subinven) is null)
AND iil.DISABLE_DATE IS NULL

WITH qty AS (
    SELECT
        cost_org_id,
        cost_book_id,
        val_unit_id,
        period_name,
        inventory_org_id,
        subinventory_code,
        inventory_item_id,
--        CASE cost_method_code
--            WHEN 'STANDARD'           THEN standard_cost
--            WHEN 'PERPETUAL_AVERAGE'  THEN decode(sign(quantity_onhand),1,perp_average_cost,-1,
--vu_unit_cost,perp_average_cost)
--            WHEN 'ACTUAL'             THEN vu_unit_cost
--            WHEN 'PERIODIC_AVERAGE'   THEN PERIODIC_AVG_COST
--            ELSE NULL
--        END                           total_unit_cost,
        MAX(uom_code)                 uom_code,
        SUM(quantity_onhand)          quantity_onhand,
        SUM(starting_quantity)        starting_quantity,
        SUM(new_receipt_quantity)     new_receipt_quantity,
        SUM(new_issue_quantity)       new_issue_quantity
    FROM
        cst_attr_onhand_valuations
    WHERE
            cost_org_id = :p_cost_org_id
        AND cost_book_id = :p_cost_book_id
        AND period_name = :p_period_name
        AND :p_organization_id IS NULL
        AND :p_subinventory_code IS NULL
    GROUP BY
        cost_org_id,
        cost_book_id,
        val_unit_id,
        period_name,
        inventory_org_id,
        subinventory_code,
        inventory_item_id
--        ,CASE cost_method_code
--            WHEN 'STANDARD'           THEN standard_cost
--            WHEN 'PERPETUAL_AVERAGE'  THEN decode(sign(quantity_onhand),1,perp_average_cost,-1,
--vu_unit_cost,perp_average_cost)
--            WHEN 'ACTUAL'             THEN vu_unit_cost
--            WHEN 'PERIODIC_AVERAGE'   THEN PERIODIC_AVG_COST
--            ELSE NULL
--        END
	UNION
	SELECT
        cost_org_id,
        cost_book_id,
        val_unit_id,
        period_name,
        inventory_org_id,
        subinventory_code,
        inventory_item_id,
--        CASE cost_method_code
--            WHEN 'STANDARD'           THEN standard_cost
--            WHEN 'PERPETUAL_AVERAGE'  THEN decode(sign(quantity_onhand),1,perp_average_cost,-1,
--vu_unit_cost,perp_average_cost)
--            WHEN 'ACTUAL'             THEN vu_unit_cost
--            WHEN 'PERIODIC_AVERAGE'   THEN PERIODIC_AVG_COST
--            ELSE NULL
--        END                           total_unit_cost,
        MAX(uom_code)                 uom_code,
        SUM(quantity_onhand)          quantity_onhand,
        SUM(starting_quantity)        starting_quantity,
        SUM(new_receipt_quantity)     new_receipt_quantity,
        SUM(new_issue_quantity)       new_issue_quantity
    FROM
        cst_attr_onhand_valuations
    WHERE
            cost_org_id = :p_cost_org_id
        AND cost_book_id = :p_cost_book_id
        AND period_name = :p_period_name
	AND :p_organization_id IS NOT NULL
        AND inventory_org_id = :p_organization_id		
        AND :p_subinventory_code IS NULL
    GROUP BY
        cost_org_id,
        cost_book_id,
        val_unit_id,
        period_name,
        inventory_org_id,
        subinventory_code,
        inventory_item_id
--        ,CASE cost_method_code
--            WHEN 'STANDARD'           THEN standard_cost
--            WHEN 'PERPETUAL_AVERAGE'  THEN decode(sign(quantity_onhand),1,perp_average_cost,-1,
--vu_unit_cost,perp_average_cost)
--            WHEN 'ACTUAL'             THEN vu_unit_cost
--            WHEN 'PERIODIC_AVERAGE'   THEN PERIODIC_AVG_COST
--            ELSE NULL
--        END
	UNION 
	SELECT
        cost_org_id,
        cost_book_id,
        val_unit_id,
        period_name,
        inventory_org_id,
        subinventory_code,
        inventory_item_id,
--        CASE cost_method_code
--            WHEN 'STANDARD'           THEN standard_cost
--            WHEN 'PERPETUAL_AVERAGE'  THEN decode(sign(quantity_onhand),1,perp_average_cost,-1,
--vu_unit_cost,perp_average_cost)
--            WHEN 'ACTUAL'             THEN vu_unit_cost
--            WHEN 'PERIODIC_AVERAGE'   THEN PERIODIC_AVG_COST
--            ELSE NULL
--        END                           total_unit_cost,
        MAX(uom_code)                 uom_code,
        SUM(quantity_onhand)          quantity_onhand,
        SUM(starting_quantity)        starting_quantity,
        SUM(new_receipt_quantity)     new_receipt_quantity,
        SUM(new_issue_quantity)       new_issue_quantity
    FROM
        cst_attr_onhand_valuations
    WHERE
            cost_org_id = :p_cost_org_id
        AND cost_book_id = :p_cost_book_id
        AND period_name = :p_period_name
        AND :p_organization_id IS NULL
	AND :p_subinventory_code IS NOT NULL
        AND subinventory_code = :p_subinventory_code		
    GROUP BY
        cost_org_id,
        cost_book_id,
        val_unit_id,
        period_name,
        inventory_org_id,
        subinventory_code,
        inventory_item_id
--        ,CASE cost_method_code
--            WHEN 'STANDARD'           THEN standard_cost
--            WHEN 'PERPETUAL_AVERAGE'  THEN decode(sign(quantity_onhand),1,perp_average_cost,-1,
--vu_unit_cost,perp_average_cost)
--            WHEN 'ACTUAL'             THEN vu_unit_cost
--            WHEN 'PERIODIC_AVERAGE'   THEN PERIODIC_AVG_COST
--            ELSE NULL
--        END
	UNION 
	SELECT
        cost_org_id,
        cost_book_id,
        val_unit_id,
        period_name,
        inventory_org_id,
        subinventory_code,
        inventory_item_id,
--        CASE cost_method_code
--            WHEN 'STANDARD'           THEN standard_cost
--            WHEN 'PERPETUAL_AVERAGE'  THEN decode(sign(quantity_onhand),1,perp_average_cost,-1,
--vu_unit_cost,perp_average_cost)
--            WHEN 'ACTUAL'             THEN vu_unit_cost
--            WHEN 'PERIODIC_AVERAGE'   THEN PERIODIC_AVG_COST
--            ELSE NULL
--        END                           total_unit_cost,
        MAX(uom_code)                 uom_code,
        SUM(quantity_onhand)          quantity_onhand,
        SUM(starting_quantity)        starting_quantity,
        SUM(new_receipt_quantity)     new_receipt_quantity,
        SUM(new_issue_quantity)       new_issue_quantity
    FROM
        cst_attr_onhand_valuations
    WHERE
            cost_org_id = :p_cost_org_id
        AND cost_book_id = :p_cost_book_id
        AND period_name = :p_period_name
	AND :p_organization_id IS NOT NULL
        AND inventory_org_id = :p_organization_id
	AND :p_subinventory_code IS NOT NULL
        AND subinventory_code = :p_subinventory_code
    GROUP BY
        cost_org_id,
        cost_book_id,
        val_unit_id,
        period_name,
        inventory_org_id,
        subinventory_code,
        inventory_item_id
--        ,CASE cost_method_code
--            WHEN 'STANDARD'           THEN standard_cost
--            WHEN 'PERPETUAL_AVERAGE'  THEN decode(sign(quantity_onhand),1,perp_average_cost,-1,
--vu_unit_cost,perp_average_cost)
--            WHEN 'ACTUAL'             THEN vu_unit_cost
--            WHEN 'PERIODIC_AVERAGE'   THEN PERIODIC_AVG_COST
--            ELSE NULL
--        END
), amt AS (
    SELECT
        caod.cost_org_id,
        caod.cost_book_id,
        caod.val_unit_id,
        caod.period_name,
        caod.inventory_org_id,
        caod.subinventory_code,
        caod.inventory_item_id,
        ce.cost_element_code                                                         cost_element_code,
        MAX(caod.currency_code)                                                      currency_code,
        MAX(caod.cost_method_code)                                                   cost_method_code,
--        CASE caod.cost_method_code
--                  WHEN 'STANDARD'           THEN caod.standard_cost
--                  WHEN 'PERPETUAL_AVERAGE'  THEN
--decode(sign(caod.quantity_onhand),1,caod.perp_average_cost,-1,
--caod.vu_unit_cost,caod.perp_average_cost)
--                  WHEN 'ACTUAL'             THEN caod.vu_unit_cost
--                  WHEN 'PERIODIC_AVERAGE'   THEN caod.PERIODIC_AVG_COST
--                  ELSE NULL
--        END                                                                          element_unit_cost,
        SUM(caod.amount+ caod.writeoff_amount)  curr_total_costed_amount,
        SUM(NVL(caod1.amount + caod1.writeoff_amount,0)) prior_total_costed_amount,
        SUM((caod.amount + caod.writeoff_amount )-(NVL(caod1.amount + caod1.writeoff_amount,0))) costed_amount,
        SUM((caod.acctd_amount+caod.acctd_writeoff_amount)-NVL((caod1.acctd_amount+caod1.acctd_writeoff_amount),0))                          acctd_amount,
        SUM(((( caod.amount+ caod.writeoff_amount ) - NVL((caod1.amount + caod1.writeoff_amount), 0)) - (( caod.acctd_amount + caod.acctd_writeoff_amount + caod.excl_from_acctg_amount + caod.excl_from_acctg_wo_amount) - NVL((caod1.acctd_amount + caod1.acctd_writeoff_amount + caod1.excl_from_acctg_amount + caod1.excl_from_acctg_wo_amount), 0))))        AS  unacctd_amount
    FROM
        cst_attr_onhand_details  caod,cst_attr_onhand_details  caod1,
        cst_cost_elements_b      ce
    WHERE
            caod.cost_element_id = ce.cost_element_id
        AND caod.cost_org_id = :p_cost_org_id
        AND caod.cost_book_id = :p_cost_book_id
        AND caod.period_name = :p_period_name
        AND :p_organization_id IS NULL
        AND :p_subinventory_code IS NULL
        AND caod.prior_val_detail_id = caod1.val_detail_id (+)
        AND caod.cost_org_id          = caod1.cost_org_id (+)
        AND caod.cost_book_id         = caod1.cost_book_id (+)
    GROUP BY
        caod.cost_org_id,
        caod.cost_book_id,
        caod.val_unit_id,
        caod.period_name,
        caod.inventory_org_id,
        caod.subinventory_code,
        caod.inventory_item_id,
        ce.cost_element_code
--        ,CASE caod.cost_method_code
--            WHEN 'STANDARD'           THEN caod.standard_cost
--            WHEN 'PERPETUAL_AVERAGE'  THEN decode(sign(caod.quantity_onhand),1,caod.perp_average_cost,-1,
--caod.vu_unit_cost,caod.perp_average_cost)
--            WHEN 'ACTUAL'             THEN caod.vu_unit_cost
--            WHEN 'PERIODIC_AVERAGE'   THEN caod.PERIODIC_AVG_COST
--            ELSE NULL
--        END
	UNION 
	SELECT
        caod.cost_org_id,
        caod.cost_book_id,
        caod.val_unit_id,
        caod.period_name,
        caod.inventory_org_id,
        caod.subinventory_code,
        caod.inventory_item_id,
        ce.cost_element_code                                                         cost_element_code,
        MAX(caod.currency_code)                                                      currency_code,
        MAX(caod.cost_method_code)                                                   cost_method_code,
--        CASE caod.cost_method_code
--                  WHEN 'STANDARD'           THEN caod.standard_cost
--                  WHEN 'PERPETUAL_AVERAGE'  THEN
--decode(sign(caod.quantity_onhand),1,caod.perp_average_cost,-1,
--caod.vu_unit_cost,caod.perp_average_cost)
--                  WHEN 'ACTUAL'             THEN caod.vu_unit_cost
--                  WHEN 'PERIODIC_AVERAGE'   THEN caod.PERIODIC_AVG_COST
--                  ELSE NULL
--        END                                                                         element_unit_cost,
        SUM(caod.amount+ caod.writeoff_amount)  curr_total_costed_amount,
        SUM(NVL(caod1.amount + caod1.writeoff_amount,0)) prior_total_costed_amount,
        SUM((caod.amount + caod.writeoff_amount )-(NVL(caod1.amount + caod1.writeoff_amount,0))) costed_amount,
        SUM((caod.acctd_amount+caod.acctd_writeoff_amount)-NVL((caod1.acctd_amount+caod1.acctd_writeoff_amount),0))                          acctd_amount,
        SUM(((( caod.amount + caod.writeoff_amount ) - NVL((caod1.amount + caod1.writeoff_amount), 0)) - (( caod.acctd_amount + caod.acctd_writeoff_amount + caod.excl_from_acctg_amount + caod.excl_from_acctg_wo_amount) - NVL((caod1.acctd_amount + caod1.acctd_writeoff_amount + caod1.excl_from_acctg_amount + caod1.excl_from_acctg_wo_amount), 0))))        AS  unacctd_amount
    FROM
        cst_attr_onhand_details  caod,cst_attr_onhand_details  caod1,
        cst_cost_elements_b      ce
    WHERE
            caod.cost_element_id = ce.cost_element_id
        AND caod.cost_org_id = :p_cost_org_id
        AND caod.cost_book_id = :p_cost_book_id
        AND caod.period_name = :p_period_name
	AND :p_organization_id IS NOT NULL
        AND caod.inventory_org_id = :p_organization_id 
        AND :p_subinventory_code IS NULL
        AND caod.prior_val_detail_id = caod1.val_detail_id (+)
        AND caod.cost_org_id          = caod1.cost_org_id (+)
        AND caod.cost_book_id         = caod1.cost_book_id (+)
    GROUP BY
        caod.cost_org_id,
        caod.cost_book_id,
        caod.val_unit_id,
        caod.period_name,
        caod.inventory_org_id,
        caod.subinventory_code,
        caod.inventory_item_id,
        ce.cost_element_code
--        ,CASE caod.cost_method_code
--            WHEN 'STANDARD'           THEN caod.standard_cost
--            WHEN 'PERPETUAL_AVERAGE'  THEN decode(sign(caod.quantity_onhand),1,caod.perp_average_cost,-1,
--caod.vu_unit_cost,caod.perp_average_cost)
--            WHEN 'ACTUAL'             THEN caod.vu_unit_cost
--            WHEN 'PERIODIC_AVERAGE'   THEN caod.PERIODIC_AVG_COST
--            ELSE NULL
--        END
	UNION
	SELECT
        caod.cost_org_id,
        caod.cost_book_id,
        caod.val_unit_id,
        caod.period_name,
        caod.inventory_org_id,
        caod.subinventory_code,
        caod.inventory_item_id,
        ce.cost_element_code                                                         cost_element_code,
        MAX(caod.currency_code)                                                      currency_code,
        MAX(caod.cost_method_code)                                                   cost_method_code,
--        CASE caod.cost_method_code
--                  WHEN 'STANDARD'           THEN caod.standard_cost
--                  WHEN 'PERPETUAL_AVERAGE'  THEN
--decode(sign(caod.quantity_onhand),1,caod.perp_average_cost,-1,
--caod.vu_unit_cost,caod.perp_average_cost)
--                  WHEN 'ACTUAL'             THEN caod.vu_unit_cost
--                  WHEN 'PERIODIC_AVERAGE'   THEN caod.PERIODIC_AVG_COST
--                  ELSE NULL
--        END                                                                          element_unit_cost,
        SUM(caod.amount+ caod.writeoff_amount)  curr_total_costed_amount,
        SUM(NVL(caod1.amount + caod1.writeoff_amount,0)) prior_total_costed_amount,
        SUM((caod.amount + caod.writeoff_amount )-(NVL(caod1.amount + caod1.writeoff_amount,0))) costed_amount,
        SUM((caod.acctd_amount+caod.acctd_writeoff_amount)-NVL((caod1.acctd_amount+caod1.acctd_writeoff_amount),0))                          acctd_amount,
        SUM(((( caod.amount       + caod.writeoff_amount ) - NVL((caod1.amount + caod1.writeoff_amount), 0)) - (( caod.acctd_amount + caod.acctd_writeoff_amount + caod.excl_from_acctg_amount + caod.excl_from_acctg_wo_amount) - NVL((caod1.acctd_amount + caod1.acctd_writeoff_amount + caod1.excl_from_acctg_amount + caod1.excl_from_acctg_wo_amount), 0))))        AS  unacctd_amount
    FROM
        cst_attr_onhand_details  caod,cst_attr_onhand_details  caod1,
        cst_cost_elements_b      ce
    WHERE
            caod.cost_element_id = ce.cost_element_id
        AND caod.cost_org_id = :p_cost_org_id
        AND caod.cost_book_id = :p_cost_book_id
        AND caod.period_name = :p_period_name
        AND :p_organization_id IS NULL
	AND :p_subinventory_code IS NOT NULL
        AND caod.subinventory_code = :p_subinventory_code
        AND caod.prior_val_detail_id = caod1.val_detail_id (+)
        AND caod.cost_org_id          = caod1.cost_org_id (+)
        AND caod.cost_book_id         = caod1.cost_book_id (+)
    GROUP BY
        caod.cost_org_id,
        caod.cost_book_id,
        caod.val_unit_id,
        caod.period_name,
        caod.inventory_org_id,
        caod.subinventory_code,
        caod.inventory_item_id,
        ce.cost_element_code
--        ,CASE caod.cost_method_code
--            WHEN 'STANDARD'           THEN caod.standard_cost
--            WHEN 'PERPETUAL_AVERAGE'  THEN decode(sign(caod.quantity_onhand),1,caod.perp_average_cost,-1,
--caod.vu_unit_cost,caod.perp_average_cost)
--            WHEN 'ACTUAL'             THEN caod.vu_unit_cost
--            WHEN 'PERIODIC_AVERAGE'   THEN caod.PERIODIC_AVG_COST
--            ELSE NULL
--        END
	UNION
	SELECT
        caod.cost_org_id,
        caod.cost_book_id,
        caod.val_unit_id,
        caod.period_name,
        caod.inventory_org_id,
        caod.subinventory_code,
        caod.inventory_item_id,
        ce.cost_element_code                                                         cost_element_code,
        MAX(caod.currency_code)                                                      currency_code,
        MAX(caod.cost_method_code)                                                   cost_method_code,
--        CASE caod.cost_method_code
--                  WHEN 'STANDARD'           THEN caod.standard_cost
--                  WHEN 'PERPETUAL_AVERAGE'  THEN
--decode(sign(caod.quantity_onhand),1,caod.perp_average_cost,-1,
--caod.vu_unit_cost,caod.perp_average_cost)
--                  WHEN 'ACTUAL'             THEN caod.vu_unit_cost
--                  WHEN 'PERIODIC_AVERAGE'   THEN caod.PERIODIC_AVG_COST
--                  ELSE NULL
--        END                                                                          element_unit_cost,
        SUM(caod.amount+ caod.writeoff_amount)  curr_total_costed_amount,
        SUM(NVL(caod1.amount + caod1.writeoff_amount,0)) prior_total_costed_amount,
        SUM((caod.amount + caod.writeoff_amount )-(NVL(caod1.amount + caod1.writeoff_amount,0))) costed_amount,
        SUM((caod.acctd_amount+caod.acctd_writeoff_amount)-NVL((caod1.acctd_amount+caod1.acctd_writeoff_amount),0))                          acctd_amount,
        SUM(((( caod.amount       + caod.writeoff_amount ) - NVL((caod1.amount + caod1.writeoff_amount), 0)) - (( caod.acctd_amount + caod.acctd_writeoff_amount + caod.excl_from_acctg_amount + caod.excl_from_acctg_wo_amount) - NVL((caod1.acctd_amount + caod1.acctd_writeoff_amount + caod1.excl_from_acctg_amount + caod1.excl_from_acctg_wo_amount), 0))))        AS  unacctd_amount
    FROM
        cst_attr_onhand_details  caod,cst_attr_onhand_details  caod1,
        cst_cost_elements_b      ce
    WHERE
            caod.cost_element_id = ce.cost_element_id
        AND caod.cost_org_id = :p_cost_org_id
        AND caod.cost_book_id = :p_cost_book_id
        AND caod.period_name = :p_period_name
	AND :p_organization_id IS NOT NULL
        AND caod.inventory_org_id = :p_organization_id
	AND :p_subinventory_code IS NOT NULL
        AND caod.subinventory_code = :p_subinventory_code
        AND caod.prior_val_detail_id = caod1.val_detail_id (+)
        AND caod.cost_org_id          = caod1.cost_org_id (+)
        AND caod.cost_book_id         = caod1.cost_book_id (+)
    GROUP BY
        caod.cost_org_id,
        caod.cost_book_id,
        caod.val_unit_id,
        caod.period_name,
        caod.inventory_org_id,
        caod.subinventory_code,
        caod.inventory_item_id,
        ce.cost_element_code
--        ,CASE caod.cost_method_code
--            WHEN 'STANDARD'           THEN caod.standard_cost
--            WHEN 'PERPETUAL_AVERAGE'  THEN decode(sign(caod.quantity_onhand),1,caod.perp_average_cost,-1,
--caod.vu_unit_cost,caod.perp_average_cost)
--            WHEN 'ACTUAL'             THEN caod.vu_unit_cost
--            WHEN 'PERIODIC_AVERAGE'   THEN caod.PERIODIC_AVG_COST
--            ELSE NULL
--        END
	
), org AS (
    SELECT /*+ materialize */
        hou.organization_id,
        hou.name                    organization_name
    FROM
        hr_organization_units_f_tl  hou
    WHERE
            trunc(sysdate) BETWEEN hou.effective_start_date AND hou.effective_end_date
        AND language = userenv('LANG')
), ec AS (
    SELECT
        eic.inventory_item_id,
        eic.category_id,
        eic.organization_id,
        ecv.category_name,
        ecv.category_code
    FROM
        egp_item_categories        eic,
        egp_default_category_sets  edc,
        egp_categories_vl          ecv
    WHERE
            eic.category_set_id = edc.category_set_id
        AND edc.functional_area_id = 2
        AND eic.category_id = ecv.category_id
)
SELECT /*+ leading(qty) USE_HASH(amt) */ 
    gl.name                     ledger_name,
    co.organization_name        cost_org_name,
    cb.cost_book_code,
    vs.val_structure_code,
    vu.val_unit_code,
    qty.period_name,
    io.organization_name       inv_org_name,
    qty.subinventory_code,
    esi.item_number,
    esi.description, /*Added this as part of Bug# 35381965*/
    qty.inventory_item_id,  /*Added this as part of Bug# 35381965*/
    CASE
        WHEN esi.item_type IS NOT NULL THEN
          (SELECT meaning from fnd_lookups where lookup_type = 'EGP_ITEM_TYPE' and lookup_code = esi.item_type)
    ELSE
       NULL
    END                         AS item_type,
    qty.uom_code,
    amt.currency_code,
    cm.meaning                  cost_method,
    ec.category_name,
    qty.quantity_onhand,
    qty.starting_quantity,
    qty.new_receipt_quantity,
    qty.new_issue_quantity,
    amt.cost_element_code cost_element_code,
--    DECODE(NVL(:p_cost_summarization, 'Y'), 'Y', qty.total_unit_cost, 'N', amt.element_unit_cost) unit_cost,
--    qty.starting_quantity * amt.element_unit_cost starting_value,
--    qty.quantity_onhand * DECODE(NVL(:p_cost_summarization, 'Y'), 'Y', qty.total_unit_cost, 'N', amt.element_unit_cost) onhand_value,
--    SUM(amt.curr_total_costed_amount)/nvl(qty.quantity_onhand,1) as derived_unit_cost,
    SUM(amt.curr_total_costed_amount)      curr_total_costed_amount,
    SUM(amt.prior_total_costed_amount)     prior_total_costed_amount,
    SUM(amt.costed_amount)     costed_amount,
    SUM(amt.acctd_amount)       acctd_amount,
    SUM(amt.unacctd_amount)     unacctd_amount
FROM
    qty,
    amt,
--    cst_cost_org_parameters ccop,
    org                   co,
    cst_cost_org_books    cob,
    cst_cost_books_b      cb,
    egp_system_items_vl  esi, /*Replaced egp_system_items_b_v with egp_system_items_vl as part of Bug# 35381965*/
    org                   io,
    cst_val_structures_b  vs,
    cst_val_units_b       vu,
    fnd_lookups           cm,
    ec,
    gl_ledgers            gl
WHERE
        qty.cost_org_id = amt.cost_org_id
--    AND qty.cost_org_id = ccop.cost_org_id
    AND qty.cost_book_id = amt.cost_book_id
    AND qty.period_name = amt.period_name
    AND qty.inventory_item_id = amt.inventory_item_id
    AND qty.inventory_org_id = amt.inventory_org_id
    AND qty.subinventory_code = amt.subinventory_code
    AND qty.val_unit_id = amt.val_unit_id
    AND co.organization_id = qty.cost_org_id
    AND co.organization_id = cob.cost_org_id
    AND cb.cost_book_id = qty.cost_book_id
    AND cob.cost_book_id = cb.cost_book_id
    AND cob.ledger_id = gl.ledger_id (+)
    AND qty.inventory_org_id = esi.organization_id
    -- AND ccop.validation_organization_id = esi.organization_id (+) /*Replaced inner join with outer join as part of Bug# 35381965*/
    AND qty.inventory_item_id = esi.inventory_item_id (+) /*Replaced inner join with outer join as part of Bug# 35381965*/
    AND qty.inventory_org_id = io.organization_id
    AND cm.lookup_type = 'CST_COST_METHODS'
    AND cm.lookup_code = amt.cost_method_code
    AND (:p_item_type IS NULL OR esi.item_type = :p_item_type)
    AND vs.val_structure_id = vu.val_structure_id
    AND vs.val_structure_type_code = DECODE(:p_valuation_structure_type, 'CONSIGNED', 'CONSIGNED', 'EXPENSE', 'EXPENSE', 'ASSET')
    AND qty.val_unit_id = vu.val_unit_id
    AND qty.inventory_item_id = ec.inventory_item_id (+)
    AND qty.inventory_org_id = ec.organization_id (+)
    -- AND ccop.validation_organization_id = ec.organization_id (+)
    AND ( ( NVL(:p_include_negative_qty, 'Y') = 'Y' )
          OR ( :p_include_negative_qty = 'N' AND qty.quantity_onhand > 0 ) 
          OR ( :p_include_negative_qty = 'NEGATIVE_ONLY' AND qty.quantity_onhand < 0 ) )
    AND ( ( NVL(:p_include_zero_cost, 'Y') = 'Y' )
          OR ( :p_include_zero_cost = 'N' 
          --AND amt.element_unit_cost > 0 
          ) )
    AND ( ( NVL(:p_unaccounted_only, 'N') = 'N' )
          OR ( :p_unaccounted_only = 'Y' AND amt.unacctd_amount <> 0 ) )
    AND ( (:p_from_item IS NULL)
          OR esi.item_number BETWEEN NVL(:p_from_item, esi.item_number) AND NVL(:p_to_item, esi.item_number) )
    AND ( (:p_from_val_unit_code IS NULL)
          OR vu.val_unit_code BETWEEN NVL(:p_from_val_unit_code, vu.val_unit_code) AND NVL(:p_to_val_unit_code, vu.val_unit_code) )
    AND ( (:p_from_category IS NULL)
          OR ec.category_name BETWEEN NVL(:p_from_category, ec.category_name) AND NVL(:p_to_category, ec.category_name) )
    AND NVL(:p_cost_summarization, 'Y')='N'
GROUP BY
    gl.name,
    co.organization_name,
    cb.cost_book_code,
    vs.val_structure_code,
    vu.val_unit_code,
    qty.period_name,
    io.organization_name,
    qty.subinventory_code,
    esi.item_number,
    esi.description, /*Added this as part of Bug# 35381965*/
    qty.inventory_item_id, /*Added this as part of Bug# 35381965*/
    esi.item_type,
    qty.uom_code,
    amt.currency_code,
    cm.meaning,
    ec.category_name,
    qty.quantity_onhand,
    qty.starting_quantity,
    qty.new_receipt_quantity,
    qty.new_issue_quantity,
    amt.cost_element_code
   -- ,amt.element_unit_cost
ORDER BY
    ec.category_name,
    io.organization_name,
    qty.subinventory_code,
    esi.item_number,
    vs.val_structure_code,
    vu.val_unit_code,
    cm.meaning

SELECT   ROUND (SUM (NVL (CPCC.UNIT_COST_AVERAGE, 0)),10) AVG_COST
				   FROM   CST_PERPAVG_COST CPCC
						 ,CST_COST_ELEMENTS_B cceb
						 ,CST_TRANSACTIONS CT
						 ,CST_COST_BOOKS_B ccb
				  WHERE  CPCC.INVENTORY_ITEM_ID = imt.inventory_item_id
					  and cceb.cost_element_id = CPCC.cost_element_id
					  AND CPCC.TRANSACTION_ID = CT.TRANSACTION_ID
					  and CPCC.cost_book_id = ccb.cost_book_id
					  AND CT.INVENTORY_ORG_ID = imt.organization_id
					  AND CPCC.TRANSACTION_ID IN (SELECT MAX (CPCC1.TRANSACTION_ID)
									FROM CST_PERPAVG_COST CPCC1, CST_TRANSACTIONS CT1
								   WHERE CPCC1.INVENTORY_ITEM_ID = CPCC.INVENTORY_ITEM_ID--300000041240358
								     AND CT1.INVENTORY_ORG_ID = CT.INVENTORY_ORG_ID--300000033854146
									 AND CPCC1.TRANSACTION_ID = CT1.TRANSACTION_ID
									 AND TRUNC(:p_date) BETWEEN TRUNC (CPCC1.cost_date) AND TRUNC (CPCC1.COST_END_DATE)-1)
					  AND TRUNC(:p_date) BETWEEN TRUNC (CPCC.cost_date) AND TRUNC (CPCC.COST_END_DATE)-1

SELECT 
	MAIN_QRY.PO_NUMBER,
	MAIN_QRY.organization_id, MAIN_QRY.business_unit_name,
    MAIN_QRY.inv_org_name , 
    MAIN_QRY.inv_org , 
    MAIN_QRY.PO_DATE, MAIN_QRY.VENDOR_ID, MAIN_QRY.SUPPLIER_NUM, MAIN_QRY.VENDOR_SITE_CODE,
    MAIN_QRY.SUPPLIER_NAME_NUM,
    MAIN_QRY.payment_terms,
    MAIN_QRY.currency_code, MAIN_QRY.rate,
    MAIN_QRY.DOCUMENT_STATUS,
    MAIN_QRY.approver,
    MAIN_QRY.approved_date,
	MAIN_QRY.destination_type_code,
	MAIN_QRY.item_id,
	MAIN_QRY.item_number,
	REPLACE (MAIN_QRY.Item_Description, CHR(10), ' ') Item_Description,
	MAIN_QRY.PO_QTY,
	MAIN_QRY.unit_price,
	MAIN_QRY.po_value_fc,
	MAIN_QRY.po_value_lc,
    MAIN_QRY.CODE_COMBINATION_ID,
	MAIN_QRY.full_comb_code,
	MAIN_QRY.LEGACY_PO_REF,
	REPLACE (MAIN_QRY.COMMENTS, CHR(10), ' ') COMMENTS
FROM 
(

SELECT PHA.SEGMENT1 PO_NUMBER,
  hw.organization_id, hw.name business_unit_name,
   (SELECT inve.organization_name
  FROM INV_ORGANIZATION_DEFINITIONS_V inve
  WHERE inve.ORGANIZATION_ID = plla.SHIP_TO_ORGANIZATION_ID
  ) inv_org_name , 
  (SELECT inve.organization_code
  FROM INV_ORGANIZATION_DEFINITIONS_V inve
  WHERE inve.ORGANIZATION_ID = plla.SHIP_TO_ORGANIZATION_ID
  ) inv_org , 
  PHA.CREATION_DATE PO_DATE,
  --pha.po_header_id,
  POZ.VENDOR_ID,
  POZ.SEGMENT1 SUPPLIER_NUM,
  PVSA.VENDOR_SITE_CODE,
  HZP.PARTY_NAME
  ||'-'
  ||POZ.SEGMENT1 SUPPLIER_NAME_NUM,
  atl.name payment_terms,
  PHA.currency_code, pha.rate,
  PHA.DOCUMENT_STATUS,
  case when ( CASE 
                    WHEN PHA.document_status NOT IN ( 
                         'REJECTED', 'PENDING APPROVAL', 
                         'INCOMPLETE' 
                                                    ) THEN 'APPROVED' 
                    ELSE PHA.document_status 
                  END ) = 'APPROVED' then ppf2.appr_name  else ppf2.appr_name END  approver,
   case when ( CASE 
                    WHEN PHA.document_status NOT IN ( 
                         'REJECTED', 'PENDING APPROVAL', 
                         'INCOMPLETE' 
                                                    ) THEN 'APPROVED' 
                    ELSE PHA.document_status 
                  END ) = 'APPROVED' then ppf2.ACTION_DATE else NULL END approved_date,
	pha.attribute11 dff_project, pha.attribute10 dff_cost_center, 
	pha.attribute1 LEGACY_PO_REF,
	pha.COMMENTS,
	pda.destination_type_code,
	pla.item_id,
	pla.po_line_id,
	(select distinct MAX (item_number) from EGP_SYSTEM_ITEMS_B
	where inventory_item_id = pla.item_id) item_number,
	PLA.Item_Description,
	NVL (PDA.quantity_ORDERED, 1) PO_QTY,
	NVL (pla.unit_price, 0) unit_price,
	NVL (pla.unit_price, 0) * NVL (PDA.quantity_ORDERED, 1) po_value_fc,
	(NVL (pla.unit_price, 0) * NVL (PDA.quantity_ORDERED, 1))*NVL (pha.rate, 1) po_value_lc,
	--RSH_RECEIPT_NUM,
	--RSH_CREATION_DATE,
	--SUM (RT_QUANTITY) RT_QUANTITY,
	--SUM (RCV.Amount) received_value_fc,
	--SUM (RCV.Amount*NVL (pha.rate, 1)) received_value_lc,
	--INVOICE_NUM,
	--LINE_AMOUNT_DR_fc Invoice_amount_fc, 
	--LINE_AMOUNT_DR_lc Invoice_amount_lc, 
    PDA.CODE_COMBINATION_ID,
	gcc.segment1 ||'-'|| gcc.segment2 ||'-'|| gcc.segment3 ||'-'|| gcc.segment4 ||'-'|| gcc.segment5 ||'-'|| gcc.segment6 ||'-'|| gcc.segment7 ||'-'|| gcc.segment8 full_comb_code
FROM POZ_SUPPLIERS_V POZ,
  PO_HEADERS_ALL PHA,
  HZ_PARTIES HZP ,
  POZ_SUPPLIER_SITES_ALL_M PVSA,
  AP_TERMS_vL atl,
  PO_LINES_ALL PLA ,
  PO_DISTRIBUTIONS_ALL PDA,
  GL_CODE_COMBINATIONS GCC,
  --po_versions pv,
  (SELECT DISTINCT iuomt.unit_of_measure unit_of_measure,
    iuomt.description uom_desc,
    iuomb.uom_code
  FROM inv_units_of_measure_tl iuomt,
    inv_units_of_measure_b iuomb
  WHERE iuomb.unit_of_measure_id = iuomt.unit_of_measure_id
  AND iuomt.language             = userenv('LANG')
  ) uom,
  HR_LOCATIONS_ALL HLAS,
  (SELECT DISTINCT pu1.username u_name,
    ppnf.first_name
    ||' '
    ||ppnf.last_name create_by
  FROM per_users pu1,
    per_all_people_f papf,
    per_person_names_f ppnf
  WHERE papf.person_id = ppnf.person_id(+)
  AND ppnf.person_id   = pu1.person_id
  AND TRUNC(sysdate) BETWEEN TRUNC(NVL(ppnf.effective_start_date,TRUNC(sysdate))) AND TRUNC(NVL(ppnf.effective_end_date,TRUNC(sysdate)))
  AND TRUNC(sysdate) BETWEEN TRUNC(NVL(papf.effective_start_date,TRUNC(sysdate))) AND TRUNC(NVL(papf.effective_end_date,TRUNC(sysdate)))
  AND NVL(ppnf.name_type,'GLOBAL') = 'GLOBAL'
  ) pu ,
  HR_OPERATING_UNITS hw ,
  (SELECT ppnf.first_name
    ||' '
    ||ppnf.last_name appr_name,
    NVL(pah.object_id,1) obj_id ,
    pah.action_date ,
    RANK() OVER (PARTITION BY pah.object_id ORDER BY pah.action_date DESC)  AS "RANK",
    RANK() OVER (PARTITION BY pah.object_id ORDER BY pah.sequence_num DESC) AS "SEQRANK"
  FROM per_all_people_f papf ,
    per_person_names_f ppnf ,
    PO_ACTION_HISTORY pah ,
    po_headers_all pha
  WHERE papf.person_id = ppnf.person_id (+)
  AND TRUNC(sysdate) BETWEEN TRUNC(NVL(ppnf.effective_start_date,TRUNC(sysdate))) AND TRUNC(NVL(ppnf.effective_end_date,TRUNC(sysdate)))
  AND TRUNC(sysdate) BETWEEN TRUNC(NVL(papf.effective_start_date,TRUNC(sysdate))) AND TRUNC(NVL(papf.effective_end_date,TRUNC(sysdate)))
  AND NVL(ppnf.name_type,'GLOBAL') = 'GLOBAL'
  AND pah.performer_id             = papf.person_id(+)
  AND pah.object_id                = pha.po_header_id
  AND pah.role_code                = 'APPROVER'
  AND pah.object_type_code         = 'PO'
  AND pah.SEQUENCE_NUM             =
    (SELECT MAX(SEQUENCE_NUM)
    FROM PO_ACTION_HISTORY pah1
    WHERE pah1.object_id     = pah.object_id
    AND pah.object_type_code = 'PO'
    AND pah1.role_code       = 'APPROVER'
    )
  ) ppf2,
  PO_LINE_LOCATIONS_ALL PLLA
WHERE pha.BILLTO_BU_ID      = hw.organization_id
AND PLLA.PO_HEADER_ID (+)   = PLA.PO_HEADER_ID
AND PLLA.PO_LINE_ID   (+)   = PLA.PO_LINE_ID
AND POZ.VENDOR_ID(+)        = PHA.VENDOR_ID
AND HZP.party_id(+)         = POZ.party_id
AND PHA.VENDOR_SITE_ID      = PVSA.VENDOR_SITE_ID(+)
AND pha.terms_id            = atl.TERM_ID(+)
AND PHA.PO_HEADER_ID        = PLA.PO_HEADER_ID(+)
AND PLA.uom_code            = uom.uom_code(+)
AND PDA.PO_HEADER_ID 		= PHA.PO_HEADER_ID
AND pla.po_line_id          = pda.po_line_id(+)
AND plla.line_location_id 	= pda.line_location_id
AND Pha.ship_to_location_id = HLAS.location_id(+)
AND lower(PHA.Created_by)   = lower(pu.u_name)
AND GCC.CODE_COMBINATION_ID = PDA.CODE_COMBINATION_ID
AND ppf2.obj_id(+)          = pha.po_header_id
--
--AND POH_PO_HEADER_ID(+)		= PHA.PO_HEADER_ID
--AND POL_PO_LINE_ID(+)		= PLA.PO_LINE_ID
--AND POD_PO_DISTRIBUTION_ID(+) = PDA.PO_DISTRIBUTION_ID
--
--AND INV.AIA_PO_HEADER_ID(+)	= PHA.PO_HEADER_ID
--AND INV.AIDA_PO_DISTRIBUTION_ID(+) = PDA.PO_DISTRIBUTION_ID
--
AND NVL(ppf2.RANK,1)        = 1
AND NVL(ppf2.SEQRANK,1)     = 1
--and pha.po_header_id        = pv.po_header_id 

and nvl(pla.LINE_STATUS, -99) not in nvl(('CANCELED'),-99)

--and PHA.SEGMENT1 = '1702000181'
--and item_id = 300000003254609
--AND PHA.PRC_BU_ID                   = :P_ORG_ID
--AND PHA.CREATION_DATE BETWEEN NVL(:P_DATE_FROM,PHA.CREATION_DATE) AND NVL(:P_DATE_TO,PHA.CREATION_DATE)
--AND PHA.VENDOR_ID = NVL(:P_VENDOR,PHA.VENDOR_ID)
/*
  GROUP BY 
  PHA.SEGMENT1 ,
  hw.organization_id, hw.name ,
   plla.SHIP_TO_ORGANIZATION_ID, 
  PHA.CREATION_DATE , POZ.VENDOR_ID,
  pha.attribute11, pha.attribute10,
  --pha.po_header_id,
  HZP.PARTY_NAME
  ||'-'
  ||POZ.SEGMENT1, POZ.SEGMENT1,
  atl.name ,
  PHA.currency_code, 
  pha.rate,
    PHA.DOCUMENT_STATUS,
    ppf2.appr_name,
    ppf2.ACTION_DATE,
	pda.destination_type_code,
	pla.item_id, PLA.Item_Description,
	pla.po_line_id,
	NVL (pda.quantity_ORDERED, 1) ,
	NVL (pla.unit_price, 0) * NVL (pda.quantity_ORDERED, 1) ,
	--RSH_RECEIPT_NUM ,
	--RSH_CREATION_DATE ,
	--INVOICE_NUM,
	--LINE_AMOUNT_DR_fc , 
	--LINE_AMOUNT_DR_lc , 
    PDA.CODE_COMBINATION_ID,
	gcc.segment1,
	gcc.segment2,
	gcc.segment3,
	gcc.segment4,
	gcc.segment5,
	gcc.segment6,
	gcc.segment7,
	gcc.segment8*/) MAIN_QRY
WHERE MAIN_QRY.ORGANIZATION_ID                   = NVL (:P_ORG_ID, MAIN_QRY.ORGANIZATION_ID)
AND MAIN_QRY.PO_DATE 						   BETWEEN NVL (:P_DATE_FROM,MAIN_QRY.PO_DATE) AND NVL (:P_DATE_TO,MAIN_QRY.PO_DATE)
AND MAIN_QRY.VENDOR_ID 						   = NVL (:P_VENDOR, MAIN_QRY.VENDOR_ID)
AND (DOCUMENT_STATUS IN (:P_STATUS) OR 'All' IN (:P_STATUS || 'All'))
	order by 2,3,1

SELECT DISTINCT  
IRTH.REQUEST_NUMBER,
SUBSTR (IODV.ORGANIZATION_NAME,1,28) ORGANIZATION_NAME,
TIODV.ORGANIZATION_NAME TO_ORGANIZATION_NAME,
IODV.BUSINESS_UNIT_NAME,
IRTH.CREATED_BY,
IRTH.CREATION_DATE,
IRTH.HEADER_ID,
IRTH.DESCRIPTION,
IRTH.HEADER_STATUS_NAME,
IRTH.DATE_REQUIRED ,
IRTL.TRANSACTION_TYPE_NAME TRANSACTION_TYPE_NAME,
MAX (IRTL.FROM_SUBINVENTORY_CODE) FROM_SUBINVENTORY_CODE,
IRTL.TO_SUBINVENTORY_CODE,
(
 SELECT ppn.FULL_NAME
   FROM PER_PERSON_NAMES_F ppn, PER_USERS pea
  WHERE pea.USERNAME = IRTH.CREATED_BY
	and ppn.PERSON_ID = pea.PERSON_ID
	AND ppn.CHAR_SET_CONTEXT = 'US'
	AND ROWNUM = 1
) CREATED_BY_NAME
--GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||GCC.SEGMENT6||'.'||GCC.SEGMENT7||'.'||GCC.SEGMENT8||'.'||GCC.SEGMENT9 ACCOUNT_CODE
FROM INV_TXN_REQUEST_HEADERS_V IRTH,
     INV_TXN_REQUEST_LINES_V IRTL,
(SELECT   DISTINCT IUOMT.UNIT_OF_MEASURE UNIT_OF_MEASURE, IUOMT.DESCRIPTION UOM_DESC,IUOMB.UOM_CODE
FROM   INV_UNITS_OF_MEASURE_TL IUOMT,
INV_UNITS_OF_MEASURE_B IUOMB
WHERE  IUOMB.UNIT_OF_MEASURE_ID = IUOMT.UNIT_OF_MEASURE_ID
AND  IUOMT.LANGUAGE = USERENV('LANG')) UOM,
EGP_SYSTEM_ITEMS_TL ESIT,
EGP_SYSTEM_ITEMS_B ESIB,
GL_CODE_COMBINATIONS GCC,
INV_ORGANIZATION_DEFINITIONS_V IODV,
INV_ORGANIZATION_DEFINITIONS_V TIODV,
hr_operating_units hou
WHERE IRTH.HEADER_ID=IRTL.HEADER_ID
AND ESIB.INVENTORY_ITEM_ID = ESIT.INVENTORY_ITEM_ID
AND ESIB.ORGANIZATION_ID = ESIT.ORGANIZATION_ID
AND ESIB.INVENTORY_ORGANIZATION_ID = IRTL.ORGANIZATION_ID
AND ESIB.INVENTORY_ITEM_ID = IRTL.INVENTORY_ITEM_ID
AND ESIT.LANGUAGE='US'
AND IRTL.UOM_CODE = UOM.UOM_CODE(+)
AND GCC.CODE_COMBINATION_ID(+) = IRTL.TO_ACCOUNT_ID
AND IRTH.ORGANIZATION_ID           = IODV.ORGANIZATION_ID   
AND IRTL.TO_ORGANIZATION_ID   = TIODV.ORGANIZATION_ID(+)
AND IODV.BUSINESS_UNIT_ID  = HOU.ORGANIZATION_ID
--AND IODV.LEGAL_ENTITY	= :P_LE
AND IODV.ORGANIZATION_ID    = NVL (:P_ORG_ID, IODV.ORGANIZATION_ID)
AND	(HOU.organization_id in (:P_BU_ID) or least(:P_BU_ID) is NULL)
AND IRTL.TRANSACTION_TYPE_NAME = NVL(:P_TYPE_NAME,IRTL.TRANSACTION_TYPE_NAME)
--AND IODV.ORGANIZATION_ID   = NVL (:P_INV_ORG_ID, IODV.ORGANIZATION_ID)    
AND IRTH.REQUEST_NUMBER =NVL(:P_MOVEMENT_NO, IRTH.REQUEST_NUMBER)
--AND IRTH.REQUEST_NUMBER = '13842161'
--AND   IRTH.DATE_REQUIRED BETWEEN NVL(:P_FROM_DATE, IRTH.DATE_REQUIRED) AND NVL(:P_TO_DATE, IRTH.DATE_REQUIRED)
GROUP BY 
IRTH.REQUEST_NUMBER,
SUBSTR (IODV.ORGANIZATION_NAME,1,28) ,
TIODV.ORGANIZATION_NAME ,
IODV.BUSINESS_UNIT_NAME,
IRTH.CREATED_BY,
IRTH.CREATION_DATE,
IRTH.HEADER_ID,
IRTH.DESCRIPTION,
IRTH.HEADER_STATUS_NAME,
IRTH.DATE_REQUIRED ,
IRTL.TRANSACTION_TYPE_NAME ,
IRTL.TO_SUBINVENTORY_CODE
order by 1

SELECT IRTH.HEADER_ID,
  IRTL.LINE_ID,
  UOM.UNIT_OF_MEASURE,
  IRTL.QUANTITY,
  NVL (IRTL.QUANTITY_DELIVERED,
  (SELECT NVL(SUM(RESERVATION_QUANTITY),0)
     FROM INV_RESERVATIONS
    WHERE IRTH.HEADER_ID = SUPPLY_SOURCE_HEADER_ID
	  AND IRTL.LINE_ID = SUPPLY_SOURCE_LINE_ID)) QUANTITY_PICKED,
  IRTL.QUANTITY_DELIVERED,
  IRTL.LINE_NUMBER,
  ESIB.ITEM_NUMBER,
  ESIT.DESCRIPTION ITEM_DESCRIPTION,
--  IRTL.FROM_SUBINVENTORY_CODE,
--  IRTL.TO_SUBINVENTORY_CODE,
  IRTL.LINE_STATUS LINE_STATUS_CODE,
  CASE WHEN IRTL.LINE_STATUS = 5 AND NVL(IRTL.QUANTITY_DELIVERED,0) = 0 THEN 'Cancelled' ELSE 
  (SELECT MEANING FROM FND_LOOKUP_VALUES
	WHERE LANGUAGE = 'US'
	AND LOOKUP_CODE = IRTL.LINE_STATUS
	AND LOOKUP_TYPE = 'INV_TXN_REQUEST_STATUS') END LINE_STATUS,
  IRTL.STATUS_DATE,
  (SELECT NVL(SUM(TRANSACTION_QUANTITY),0) A
  FROM INV_ONHAND_QUANTITIES_DETAIL
  WHERE INVENTORY_ITEM_ID =IRTL.INVENTORY_ITEM_ID
  AND ORGANIZATION_ID     =IRTL.ORGANIZATION_ID
  ) - (
  (SELECT NVL(SUM(RESERVATION_QUANTITY),0) B
  FROM INV_RESERVATIONS
  WHERE INVENTORY_ITEM_ID =IRTL.INVENTORY_ITEM_ID
  AND ORGANIZATION_ID     =IRTL.ORGANIZATION_ID
  ) +
  (SELECT NVL(SUM(TRANSACTION_QUANTITY),0) C
  FROM INV_ONHAND_QUANTITIES_DETAIL
  WHERE INVENTORY_ITEM_ID =IRTL.INVENTORY_ITEM_ID
  AND ORGANIZATION_ID     =IRTL.ORGANIZATION_ID
  AND SUBINVENTORY_CODE  IN
    (SELECT SECONDARY_INVENTORY_NAME
    FROM INV_SECONDARY_INVENTORIES
    WHERE ORGANIZATION_ID=IRTL.ORGANIZATION_ID
    AND RESERVABLE_TYPE  = '2'
    )
  )) STOCK_AVAILABILITY,
  (SELECT listagg(distinct LOT_NUMBER, ', ') within group (order by LOT_NUMBER) FROM
(SELECT distinct LOT_NUMBER
	 FROM INV_MATERIAL_TXNS IMT, INV_TRANSACTION_LOT_NUMBERS ITLN
	WHERE IMT.TRANSACTION_ID = ITLN.TRANSACTION_ID
	  AND TRX_SOURCE_LINE_ID = IRTL.LINE_ID)) LOT_NUMBER,
   (SELECT MAX(SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4)
	  FROM INV_ITEM_LOCATIONS
	 WHERE INVENTORY_LOCATION_ID = FROM_LOCATOR_ID
	   AND ORGANIZATION_ID = IRTL.ORGANIZATION_ID) LOCATOR_NAME,
   NVL 
   (
   (SELECT MAX(SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4)
	  FROM INV_ITEM_LOCATIONS
	 WHERE INVENTORY_LOCATION_ID = TO_LOCATOR_ID
	   AND ORGANIZATION_ID = IRTL.ORGANIZATION_ID),
	GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||GCC.SEGMENT6||'.'||GCC.SEGMENT7||'.'||GCC.SEGMENT8) ACCOUNT_CODE,
	(SELECT MAX (DESCRIPTION)
	   FROM FND_FLEX_VALUES_VL
	  WHERE ENABLED_FLAG = 'Y'
		AND FLEX_VALUE = gcc.segment3
		AND VALUE_CATEGORY IN ('Location TEC','Location KNCC')) LOCATION
	/*gl_flexfields_pkg.get_description_sql (gcc.CHART_OF_ACCOUNTS_ID,
                                                3,
                                                gcc.segment3) LOCATION*/
FROM INV_TXN_REQUEST_HEADERS_V IRTH,
  INV_TXN_REQUEST_LINES_V IRTL,
  --INV_MATERIAL_TXNS IMT,
  (SELECT DISTINCT IUOMT.UNIT_OF_MEASURE UNIT_OF_MEASURE,
    IUOMT.DESCRIPTION UOM_DESC,
    IUOMB.UOM_CODE
  FROM INV_UNITS_OF_MEASURE_TL IUOMT,
    INV_UNITS_OF_MEASURE_B IUOMB
  WHERE IUOMB.UNIT_OF_MEASURE_ID = IUOMT.UNIT_OF_MEASURE_ID
  AND IUOMT.LANGUAGE             = USERENV('LANG')
  ) UOM,
  EGP_SYSTEM_ITEMS_TL ESIT,
  EGP_SYSTEM_ITEMS_B ESIB,
  GL_CODE_COMBINATIONS GCC
WHERE IRTH.HEADER_ID               = IRTL.HEADER_ID
--AND IRTH.HEADER_ID 			   = IMT.TRANSACTION_SOURCE_ID (+)
--AND IRTL.LINE_ID				   = IMT.TRX_SOURCE_LINE_ID (+)
--AND IMT.TRANSACTION_ID           = CTD.TRANSACTION_ID (+)
AND ESIB.INVENTORY_ITEM_ID         = ESIT.INVENTORY_ITEM_ID
AND ESIB.ORGANIZATION_ID           = ESIT.ORGANIZATION_ID
AND ESIB.INVENTORY_ORGANIZATION_ID = IRTL.ORGANIZATION_ID
AND ESIB.INVENTORY_ITEM_ID         = IRTL.INVENTORY_ITEM_ID
AND ESIT.LANGUAGE                  ='US'
AND IRTL.UOM_CODE                  = UOM.UOM_CODE(+)
AND GCC.CODE_COMBINATION_ID(+)     = IRTL.TO_ACCOUNT_ID
AND IRTL.TRANSACTION_TYPE_NAME = NVL(:P_TYPE_NAME,IRTL.TRANSACTION_TYPE_NAME)
AND 1 = 1
order by IRTL.LINE_NUMBER

SELECT HP.PARTY_ID, SUP.VENDOR_ID, PARTY_NUMBER, NVL (HPA.VENDOR_SITE_CODE_ALT, SUP.VENDOR_NAME_ALT) VENDOR_NAME_ALT, SUP.SEGMENT1, SUP.VENDOR_TYPE_LOOKUP_CODE, HP.PARTY_UNIQUE_NAME, 
HP.PARTY_NAME, HP.PARTY_TYPE, HPS.PARTY_SITE_ID, HPS.LOCATION_ID, CURRENCY_CODE, HPS.PARTY_SITE_NUMBER, HPS.PARTY_SITE_NAME, PSS.PRC_BU_ID, HOU.NAME, 
PSS.VENDOR_SITE_ID,pss.vendor_site_code, PSS.PURCHASING_SITE_FLAG, PSS.PAY_SITE_FLAG, PSS.RFQ_ONLY_SITE_FLAG, PSS.TERMS_ID, 
(SELECT DISTINCT NAME FROM AP_TERMS_vL atl WHERE atl.TERM_ID = PSS.TERMS_ID) TERM_NAME,
PSS.INVOICE_CURRENCY_CODE, PSS.PAYMENT_CURRENCY_CODE, PSS.CREATION_DATE SITE_CREATION_DATE, PSS.CREATED_BY SITE_CREATED_BY, HPS.ADDRESSEE, HPA.ADDRESS_LINE1||', '||HPA.ADDRESS_LINE2||', '||HPA.ADDRESS_LINE3||', '||HPA.ADDRESS_LINE4 ADDRESS_LINE1, HPA.CITY, HPA.COUNTRY,
	gcc.segment1 COMPANY,
	--gl_flexfields_pkg.get_description_sql (GCC.CHART_OF_ACCOUNTS_ID,1,GCC.segment1) company_name,
	gcc.segment2 DEPARTMENT,
	--gl_flexfields_pkg.get_description_sql (GCC.CHART_OF_ACCOUNTS_ID,2,GCC.segment2) department_name,
	gcc.segment3 LOCATION, 
	--gl_flexfields_pkg.get_description_sql (GCC.CHART_OF_ACCOUNTS_ID,3,GCC.segment3) location_name,
	gcc.segment4 PROFIT_CENTER, 
	--gl_flexfields_pkg.get_description_sql (GCC.CHART_OF_ACCOUNTS_ID,4,GCC.segment4) profit_center_name,
	gcc.segment5 ACCOUNT, 
	--gl_flexfields_pkg.get_description_sql (GCC.CHART_OF_ACCOUNTS_ID,5,GCC.segment5) account_name,
	gcc.segment6 AUX_ACCOUNT, 
	--gl_flexfields_pkg.get_description_sql (GCC.CHART_OF_ACCOUNTS_ID,6,GCC.segment6) aux_acc_name,
	gcc.segment7 future1_use, 
	--gl_flexfields_pkg.get_description_sql (GCC.CHART_OF_ACCOUNTS_ID,7,GCC.segment7) future1_name,
	gcc.segment8 future2_use, 
	--gl_flexfields_pkg.get_description_sql (GCC.CHART_OF_ACCOUNTS_ID,8,GCC.segment8) future2_name,
	gcc.segment1 ||'-'|| gcc.segment2 ||'-'|| gcc.segment3 ||'-'|| gcc.segment4 ||'-'|| gcc.segment5 ||'-'|| gcc.segment6 ||'-'|| gcc.segment7 ||'-'|| gcc.segment8 full_comb_code
FROM HZ_PARTIES HP, POZ_SUPPLIERS_V SUP, HZ_PARTY_SITES HPS, POZ_SUPPLIER_SITES_V HPA, POZ_SUPPLIER_SITES_ALL_M PSS, POZ_SITE_ASSIGNMENTS_ALL_M PSA, HR_ORGANIZATION_UNITS HOU,
GL_CODE_COMBINATIONS GCC,HR_OPERATING_UNITS HU
WHERE HP.PARTY_ID = HPS.PARTY_ID
AND SUP.PARTY_ID = HP.PARTY_ID
--AND SUP.SEGMENT1 = '10077'
AND PSS.PRC_BU_ID = HU.ORGANIZATION_ID
AND HPS.PARTY_SITE_ID = PSS.PARTY_SITE_ID
AND PSA.VENDOR_SITE_ID = PSS.VENDOR_SITE_ID
AND HPS.PARTY_SITE_ID = HPA.PARTY_SITE_ID (+)
AND PSA.VENDOR_SITE_ID = HPA.VENDOR_SITE_ID (+)
AND SUP.VENDOR_ID = HPA.VENDOR_ID (+)
AND HOU.ORGANIZATION_ID = HU.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = nvl(:P_BU_ID,HOU.ORGANIZATION_ID)
and hu.DEFAULT_LEGAL_CONTEXT_ID = nvl(:p_legal, hu.DEFAULT_LEGAL_CONTEXT_ID)
AND PSA.ACCTS_PAY_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID (+)
AND (SUP.END_DATE_ACTIVE IS NULL OR SUP.END_DATE_ACTIVE >= TRUNC (SYSDATE))

Finance

SELECT NAME, LEGAL_ENTITY_ID
FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.per_users pu,
fusion.per_roles_dn_vl pr,
hr_legal_entities hle
WHERE 1 = 1
and classification_code = 'HCM_PSU'
and role.DATASEC_CONTEXT_VALUE1 = LEGAL_ENTITY_ID
and pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME
and pu.username = fnd_global.user_name

SELECT DISTINCT hou.name, hou.organization_id
FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.per_users pu,
fusion.per_roles_dn_vl pr,
hr_operating_units  hou
WHERE 1 = 1
and hou.ORGANIZATION_ID = role.org_id
and pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME
and pu.username = fnd_global.user_name
and default_legal_context_id = :p_legal

SELECT * 
FROM
(SELECT  DECODE (ac.PAYMENT_TYPE_FLAG,'R','Refund','Payment') type, 
		ac.DOC_SEQUENCE_VALUE, 
		DECODE (ac.PAYMENT_TYPE_FLAG,'R','RFU','PAY') INV_TYP_CODE, 
		DECODE (ac.PAYMENT_TYPE_FLAG,'R','RFU','PAY') TRX_TYPE,
		STATUS_LOOKUP_CODE INVOICE_STATUS, 
		'Y' PAYMENT_STATUS_FLAG, ac.vendor_id, ac.vendor_site_id, 
		ac.ORG_ID, REPLACE (NVL (AC.BANK_ACCOUNT_NAME, '-'),CHR(10),NULL) Header_desc, hou.name bg_name,
		--
		ac.check_id, 
		null invoice_payment_id, 
		aps.segment1 vendor_code, aps.vendor_name vendor_name, aps.VENDOR_NAME_ALT,
		ac.check_id invoice_id, 
		TO_CHAR (ac.check_number) invoice_number,
		ac.check_date, aipa.ACCOUNTING_DATE GL_DATE, 
		ac.currency_code currency_code,
		ac.amount inv_amt_frc,
		ac.exchange_rate inv_exc_rate,
		ac.exchange_date inv_exc_date,
		NULL Line_desc,
		ac.doc_sequence_value ap_vou_num, 
		TO_CHAR (ac.check_number) check_number, 
		DECODE (ac.currency_code, 'KWD', 0, -aipa.amount) line_amount, 
		CASE WHEN STATUS_LOOKUP_CODE = 'VOIDED' THEN 0 ELSE -aipa.amount * NVL (ac.exchange_rate, 1) END line_amount_lc, 
		aia.invoice_num invoice_num_ref,
		ac.created_by, ac.creation_date,
		(SELECT DISPLAY_NAME 
		   FROM PER_USERS PU, PER_PERSON_NAMES_F_V PAP 
		  WHERE PAP.PERSON_ID = PU.PERSON_ID 
			AND USERNAME = ac.created_by
			AND TRUNC (SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE) employee_name, 
		NULL OVERLAY_DIST_CODE_CONCAT,
		--NULL ACCTS_PAY_CODE_COMBINATION_ID,
		--NULL SUPPLIER_ACCR_ACCOUNT,
		aia.ACCTS_PAY_CODE_COMBINATION_ID,
		(SELECT SEGMENT1||'-'||SEGMENT2||'-'||SEGMENT3||'-'||SEGMENT4||'-'||SEGMENT5||'-'||SEGMENT6||'-'||SEGMENT7||'-'||SEGMENT8
			FROM GL_CODE_COMBINATIONS 
			WHERE CODE_COMBINATION_ID = aia.ACCTS_PAY_CODE_COMBINATION_ID) SUPPLIER_ACCR_ACCOUNT,
		NULL PO_DISTRIBUTION_ID,
		NULL RCV_TRANSACTION_ID,
		NULL DIST_CODE_COMBINATION_ID,
		NULL PO_NUM,
		NULL RECEIPT_NUM

	FROM ap_checks_all ac,
		ap_invoice_payments_all aipa,
		ap_invoices_all aia,
		POZ_SUPPLIERS_V aps,
		POZ_SUPPLIER_SITES_ALL_M PSS,
		--ap_invoice_lines_all aila,
		--ap_invoice_distributions_all aida,
		HR_OPERATING_UNITS HOU
		--ap_payment_hist_dists aphd
	  WHERE 1 = 1
		AND ac.vendor_id = aps.vendor_id
		AND ac.VENDOR_SITE_ID = PSS.VENDOR_SITE_ID
		AND aps.VENDOR_ID = PSS.VENDOR_ID
		AND PSS.PRC_BU_ID = HOU.ORGANIZATION_ID
		AND STATUS_LOOKUP_CODE <> 'VOIDED'
		AND aipa.CHECK_ID = AC.CHECK_ID
        AND aia.invoice_id = aipa.invoice_id
		--AND (DECODE (ac.PAYMENT_TYPE_FLAG,'R','RFU','PAY') IN (:P_STATUS) OR 'All' IN (:P_STATUS || 'All'))
		--AND aila.invoice_id = aida.invoice_id
		--AND aila.line_number = aida.invoice_line_number
		--AND aila.line_number = 1
	  --AND glcc.segment3 = '123456789'
	  --AND aida.distribution_line_number = 1
		--AND aipa.invoice_payment_id = aphd.invoice_payment_id
		--and aps.SEGMENT1 = '10117'
		--AND ac.check_number = '2309'
		--AND aia.invoice_num = '00015'
		AND ac.ORG_ID 						= NVL (:P_ORG_ID, ac.ORG_ID)
		AND hou.DEFAULT_LEGAL_CONTEXT_ID 	= nvl (:p_legal, hou.DEFAULT_LEGAL_CONTEXT_ID)
		AND TRUNC (aipa.ACCOUNTING_DATE) BETWEEN :P_FDATE AND :P_TDATE
		--AND TRUNC (AC.CHECK_DATE) BETWEEN :P_FDATE AND :P_TDATE
		AND aps.VENDOR_ID = NVL (:P_VENDOR, aps.VENDOR_ID)
		--AND ac.check_date  		BETWEEN NVL (:P_INVOICE_DATE_FROM, ac.check_date) AND NVL (:P_INVOICE_DATE_TO, ac.check_date)

UNION ALL

SELECT   'Exchange Gain/Loss' type
		,ACA.DOC_SEQUENCE_VALUE
		,DECODE (PAYMENT_TYPE_FLAG,'R','RFU','PAY') INV_TYP_CODE
		,'EXH' TRX_TYPE
		,STATUS_LOOKUP_CODE INVOICE_STATUS
		,'Y' PAYMENT_STATUS_FLAG, ACA.VENDOR_ID,ACA.VENDOR_SITE_ID
		,ACA.ORG_ID,'EXCHANGE GAIN/LOSS' Header_desc, HOU.NAME BG_NAME
		--
		,ACA.CHECK_ID
		,null invoice_payment_id
		,aps.segment1 vendor_code, aps.vendor_name vendor_name, aps.VENDOR_NAME_ALT
		,aca.check_id invoice_id
		,TO_CHAR (ACA.CHECK_NUMBER) invoice_number
		,ACA.CHECK_DATE TRX_DATE
		,AIP.ACCOUNTING_DATE GL_DATE
		,'KWD' CURRENCY_CODE
		,-SUM (AIP.INVOICE_BASE_AMOUNT - AIP.PAYMENT_BASE_AMOUNT) inv_amt_frc
		/*,DECODE (SIGN ( (SUM (AIP.INVOICE_BASE_AMOUNT - AIP.PAYMENT_BASE_AMOUNT)))
				,1
				,SUM (AIP.INVOICE_BASE_AMOUNT - AIP.PAYMENT_BASE_AMOUNT))
			FR_DR
		,DECODE (SIGN ( (SUM (AIP.INVOICE_BASE_AMOUNT - AIP.PAYMENT_BASE_AMOUNT)))
				,-1
				,SUM ( (AIP.INVOICE_BASE_AMOUNT - AIP.PAYMENT_BASE_AMOUNT) * -1))
			FR_CR
		,DECODE (SIGN ( (SUM (AIP.INVOICE_BASE_AMOUNT - AIP.PAYMENT_BASE_AMOUNT)))
				,1
				,SUM (AIP.INVOICE_BASE_AMOUNT - AIP.PAYMENT_BASE_AMOUNT))
			DEBIT
		,DECODE (SIGN ( (SUM (AIP.INVOICE_BASE_AMOUNT - AIP.PAYMENT_BASE_AMOUNT)))
				,-1
				,SUM ( (AIP.INVOICE_BASE_AMOUNT - AIP.PAYMENT_BASE_AMOUNT) * -1))
			CREDIT*/
		,aca.exchange_rate inv_exc_rate
		,aca.exchange_date inv_exc_date
		,NULL Line_desc
		,aca.doc_sequence_value ap_vou_num 
		,TO_CHAR (aca.check_number) check_number
		,CASE WHEN STATUS_LOOKUP_CODE = 'VOIDED' AND aca.currency_code = 'KWD' THEN 
			NULL ELSE -SUM (AIP.INVOICE_BASE_AMOUNT - AIP.PAYMENT_BASE_AMOUNT) 
		 END line_amount
		,CASE WHEN STATUS_LOOKUP_CODE = 'VOIDED' THEN
			NULL --CASE WHEN aca.currency_code = 'KWD' THEN -SUM (AIP.INVOICE_BASE_AMOUNT - AIP.PAYMENT_BASE_AMOUNT) ELSE NULL END
		 ELSE -SUM (AIP.INVOICE_BASE_AMOUNT - AIP.PAYMENT_BASE_AMOUNT)
		 END line_amount_lc, 
		NULL invoice_num_ref, ACA.created_by, ACA.creation_date,
		(SELECT DISPLAY_NAME 
		   FROM PER_USERS PU, PER_PERSON_NAMES_F_V PAP 
		  WHERE PAP.PERSON_ID = PU.PERSON_ID 
			AND USERNAME = ACA.created_by
			AND TRUNC (SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE) employee_name, 
		NULL OVERLAY_DIST_CODE_CONCAT,
		NULL ACCTS_PAY_CODE_COMBINATION_ID,
		NULL SUPPLIER_ACCR_ACCOUNT,
		NULL PO_DISTRIBUTION_ID,
		NULL RCV_TRANSACTION_ID,
		NULL DIST_CODE_COMBINATION_ID,
		NULL PO_NUM,
		NULL RECEIPT_NUM
  FROM   AP_CHECKS_ALL ACA
		,AP_INVOICE_PAYMENTS_ALL AIP
		,POZ_SUPPLIERS_V aps
		,POZ_SUPPLIER_SITES_ALL_M PSS
		,HR_OPERATING_UNITS HOU
 WHERE   ACA.CHECK_ID 		= AIP.CHECK_ID
	 AND ACA.ORG_ID 		= AIP.ORG_ID
	 AND aca.vendor_id 		= aps.vendor_id
	 AND aca.VENDOR_SITE_ID = PSS.VENDOR_SITE_ID
	 AND aps.VENDOR_ID 		= PSS.VENDOR_ID
	 AND PSS.PRC_BU_ID 		= HOU.ORGANIZATION_ID
	 --AND ACA.CHECK_NUMBER = '1000015'
     AND STATUS_LOOKUP_CODE <> 'VOIDED'
	 AND ACA.ORG_ID 		= NVL (:P_ORG_ID, ACA.ORG_ID)
	 AND hou.DEFAULT_LEGAL_CONTEXT_ID 	= nvl (:p_legal, hou.DEFAULT_LEGAL_CONTEXT_ID)
	 --AND TRUNC (ACA.CHECK_DATE) BETWEEN :P_FDATE AND :P_TDATE
	 AND TRUNC (AIP.ACCOUNTING_DATE) BETWEEN :P_FDATE AND :P_TDATE
	 AND aps.VENDOR_ID = NVL (:P_VENDOR, aps.VENDOR_ID)
	 AND 1 = 2
	 --AND (AIP.INVOICE_BASE_AMOUNT - AIP.PAYMENT_BASE_AMOUNT) <> 0
--AND ACA.VENDOR_ID in (SELECT VENDOR_ID FROM poz_suppliers_v pos WHERE pos.segment1 = '10227')
GROUP BY   ACA.CHECK_DATE
		,AIP.ACCOUNTING_DATE
		,TO_CHAR (ACA.CHECK_NUMBER)
		,ACA.VENDOR_ID
		,ACA.VENDOR_SITE_ID
		,aps.segment1
		,aps.vendor_name
		,aps.VENDOR_NAME_ALT
		,ACA.CHECK_ID
		,ACA.DOC_SEQUENCE_VALUE
		,ACA.ORG_ID
		,DECODE (PAYMENT_TYPE_FLAG,'R','RFU','PAY')
		,STATUS_LOOKUP_CODE
		,aca.currency_code
		,aca.exchange_rate
		,aca.exchange_date
		,HOU.NAME, ACA.created_by, ACA.creation_date
--HAVING   SUM (AIP.INVOICE_BASE_AMOUNT - AIP.PAYMENT_BASE_AMOUNT) <> 0

UNION ALL

SELECT  'Invoice' type, 
		AIA.DOC_SEQUENCE_VALUE DOCUMENT_NUM, 
		aia.invoice_type_lookup_code INVOICE_TYPE_CODE, 
		DECODE (AIA.INVOICE_TYPE_LOOKUP_CODE
								,'STANDARD'
								,'INV'
								,'CREDIT'
								,'CM'
								,'DEBIT'
								,'DM'
								,'MIXED'
								,'INV'
								,'PREPAYMENT'
								,'PRE-P'
								,'EXPENSE REPORT'
								,'EXP'
								,'AWT'
								,'AWT') INVOICE_TYPE, 
		AP_INVOICES_PKG.GET_APPROVAL_STATUS (AIA.INVOICE_ID
											 ,AIA.INVOICE_AMOUNT
											 ,AIA.PAYMENT_STATUS_FLAG
											 ,AIA.INVOICE_TYPE_LOOKUP_CODE) INVOICE_STATUS,
		AIA.PAYMENT_STATUS_FLAG, aia.vendor_id, aia.vendor_site_id, 
		aia.ORG_ID, REPLACE (aia.description,CHR(10),' ') Header_desc, HOU.NAME bg_name,
		--
		NULL check_id, 
		NULL invoice_payment_id, 
		aps.segment1 vendor_code, aps.vendor_name vendor_name, aps.VENDOR_NAME_ALT,
		aia.invoice_id, aia.invoice_num,
		aia.invoice_date, ail.ACCOUNTING_DATE GL_DATE,
		aia.invoice_currency_code currency_code,
		aia.invoice_amount inv_amt_frc,
		aia.exchange_rate inv_exc_rate,
		aia.exchange_date inv_exc_date,
		ail.description Line_desc,
		aia.doc_sequence_value ap_vou_num, 
		NULL check_number, 
		DECODE (aia.invoice_currency_code, 'KWD', 0, SUM (aida.amount)) line_amount, 
		--SUM (ail.amount) * NVL (aia.exchange_rate, 1) line_amount_lc, 
		SUM (NVL (AIDA.BASE_AMOUNT,AIDA.AMOUNT)) line_amount_lc,
		NULL invoice_num_ref,
		aia.created_by, aia.creation_date,
		(SELECT DISPLAY_NAME 
		   FROM PER_USERS PU, PER_PERSON_NAMES_F_V PAP 
		  WHERE PAP.PERSON_ID = PU.PERSON_ID 
			AND USERNAME = aia.created_by
			AND TRUNC (SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE) employee_name,
		NVL (NVL (OVERLAY_DIST_CODE_CONCAT,
			(SELECT SEGMENT1||'-'||SEGMENT2||'-'||SEGMENT3||'-'||SEGMENT4||'-'||SEGMENT5||'-'||SEGMENT6||'-'||SEGMENT7||'-'||SEGMENT8
			FROM GL_CODE_COMBINATIONS 
			WHERE CODE_COMBINATION_ID = ail.DEFAULT_DIST_CCID)),
			(SELECT SEGMENT1||'-'||SEGMENT2||'-'||SEGMENT3||'-'||SEGMENT4||'-'||SEGMENT5||'-'||SEGMENT6||'-'||SEGMENT7||'-'||SEGMENT8
			FROM GL_CODE_COMBINATIONS 
			WHERE CODE_COMBINATION_ID = AIDA.DIST_CODE_COMBINATION_ID)) OVERLAY_DIST_CODE_CONCAT,
		aia.ACCTS_PAY_CODE_COMBINATION_ID,
		(SELECT SEGMENT1||'-'||SEGMENT2||'-'||SEGMENT3||'-'||SEGMENT4||'-'||SEGMENT5||'-'||SEGMENT6||'-'||SEGMENT7||'-'||SEGMENT8
			FROM GL_CODE_COMBINATIONS 
			WHERE CODE_COMBINATION_ID = aia.ACCTS_PAY_CODE_COMBINATION_ID) SUPPLIER_ACCR_ACCOUNT,
		AIDA.PO_DISTRIBUTION_ID,
		AIDA.RCV_TRANSACTION_ID,
		AIDA.DIST_CODE_COMBINATION_ID,
		(
        SELECT
            poh.SEGMENT1
        FROM
            PO_DISTRIBUTIONS_ALL pod,
            PO_HEADERS_ALL poh
        WHERE
            AIDA.PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID
			AND pod.PO_HEADER_ID = poh.PO_HEADER_ID
            AND ROWNUM = 1
		) PO_NUM,
		(
		SELECT 
			RSH.RECEIPT_NUM 
		FROM 
			RCV_TRANSACTIONS RCVT,
			RCV_SHIPMENT_LINES RSL,
			RCV_SHIPMENT_HEADERS RSH
		WHERE RCVT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
		  AND RCVT.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
		  AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
		  AND TRANSACTION_ID = AIDA.RCV_TRANSACTION_ID
		  AND ROWNUM = 1
		) RECEIPT_NUM

	  FROM ap_invoices_all aia,
		ap_invoice_lines_all ail,
		AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
		POZ_SUPPLIERS_V aps,
		POZ_SUPPLIER_SITES_ALL_M PSS,
		HR_OPERATING_UNITS HOU
	  WHERE --xdl.applied_to_source_id_num_1 = aia.invoice_id(+)
	   aps.vendor_id 				   = aia.vendor_id
	  AND aia.VENDOR_SITE_ID 		   = PSS.VENDOR_SITE_ID
	  AND aps.VENDOR_ID 			   = PSS.VENDOR_ID
	  AND aida.invoice_id 			   = aia.invoice_id
	  AND aia.invoice_id 			   = ail.invoice_id
	  AND aida.invoice_line_number 	   = ail.line_number
	  AND PSS.PRC_BU_ID 			   = HOU.ORGANIZATION_ID
      --AND xdl.applied_to_dist_id_num_1 = aida.invoice_distribution_id
	  --AND SOURCE_DISTRIBUTION_TYPE 	   = 'AP_INV_DIST'
	  --AND SOURCE_DISTRIBUTION_ID_NUM_1 = aida.invoice_distribution_id
	  --AND xdl.application_id           = 200
	  AND hou.DEFAULT_LEGAL_CONTEXT_ID 	= nvl (:p_legal, hou.DEFAULT_LEGAL_CONTEXT_ID)
	  AND aia.ORG_ID 				= NVL (:P_ORG_ID, aia.ORG_ID)
	  --AND TRUNC (aia.invoice_date) BETWEEN :P_FDATE AND :P_TDATE
	  AND TRUNC (ail.ACCOUNTING_DATE) BETWEEN :P_FDATE AND :P_TDATE
	  AND aps.VENDOR_ID = NVL (:P_VENDOR, aps.VENDOR_ID)
	  AND ail.line_type_lookup_code <> 'PREPAY'
	  AND AIDA.LINE_TYPE_LOOKUP_CODE IN ('ACCRUAL','ITEM','MISCELLANEOUS')
	  /*AND (DECODE (AIA.INVOICE_TYPE_LOOKUP_CODE
								,'STANDARD'
								,'INV'
								,'CREDIT'
								,'CM'
								,'DEBIT'
								,'DM'
								,'MIXED'
								,'INV'
								,'PREPAYMENT'
								,'PRE-P'
								,'EXPENSE REPORT'
								,'EXP'
								,'AWT'
								,'AWT') IN (:P_STATUS) OR 'All' IN (:P_STATUS || 'All'))*/

GROUP BY AIA.DOC_SEQUENCE_VALUE, aia.invoice_type_lookup_code, 
		AP_INVOICES_PKG.GET_APPROVAL_STATUS (AIA.INVOICE_ID
											 ,AIA.INVOICE_AMOUNT
											 ,AIA.PAYMENT_STATUS_FLAG
											 ,AIA.INVOICE_TYPE_LOOKUP_CODE),
		AIA.PAYMENT_STATUS_FLAG, aia.vendor_id, aia.vendor_site_id, 
		aia.ORG_ID, aia.description, HOU.NAME,
		--
		aps.segment1 , aps.vendor_name , aps.VENDOR_NAME_ALT,
		aia.invoice_id, aia.invoice_num,
		aia.invoice_date, ail.ACCOUNTING_DATE, aia.invoice_currency_code ,
		aia.invoice_amount ,
		aia.exchange_rate ,
		aia.exchange_date ,
		aia.invoice_amount, aia.created_by, aia.creation_date, OVERLAY_DIST_CODE_CONCAT, ail.DEFAULT_DIST_CCID,
		aia.ACCTS_PAY_CODE_COMBINATION_ID, 
		AIDA.PO_DISTRIBUTION_ID,
		AIDA.RCV_TRANSACTION_ID,
		AIDA.DIST_CODE_COMBINATION_ID,
		ail.description
)
WHERE (type IN (:P_TYPE) OR 'All' IN (:P_TYPE || 'All'))
AND (PAYMENT_STATUS_FLAG IN (:P_STATUS) OR 'All' IN (:P_STATUS || 'All'))

SELECT FCB.BOOK_TYPE_CODE
	   ,fa.asset_id asset_id
	   ,FA.asset_number asset_number
	   ,FCB.CATEGORY_ID
	   ,FC.SEGMENT1 MAJOR_CAT
	   ,(SELECT DESCRIPTION FROM FND_FLEX_VALUES_VL
		  WHERE FLEX_VALUE = FC.SEGMENT1
			AND VALUE_CATEGORY = 'XXXX FA Category Major Value Set') MAJOR_CAT_DESC
	   ,FC.SEGMENT2 MINOR_CAT
	   ,(SELECT DESCRIPTION FROM FND_FLEX_VALUES_VL
		  WHERE FLEX_VALUE = FC.SEGMENT2
			AND PARENT_FLEX_VALUE_LOW = FC.SEGMENT1
			AND VALUE_CATEGORY = 'XXXX FA Category Minor Value Set') MINOR_CAT_DESC
	   ,ASSET_COST_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = ASSET_COST_ACCOUNT_CCID) ASSET_COST_ACCOUNT_CCID_DESC
	   ,ASSET_CLEARING_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = ASSET_CLEARING_ACCOUNT_CCID) ASSET_CLEARING_ACCOUNT_CCID_DESC
	   ,WIP_COST_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = WIP_COST_ACCOUNT_CCID) WIP_COST_ACCOUNT_CCID_DESC
	   ,WIP_CLEARING_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = WIP_CLEARING_ACCOUNT_CCID) WIP_CLEARING_ACCOUNT_CCID_DESC
	   ,DEPRN_EXPENSE_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = DEPRN_EXPENSE_ACCOUNT_CCID) DEPRN_EXPENSE_ACCOUNT_CCID_DESC
	   ,RESERVE_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = RESERVE_ACCOUNT_CCID) RESERVE_ACCOUNT_CCID_DESC
	   ,BONUS_EXPENSE_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = BONUS_EXPENSE_ACCOUNT_CCID) BONUS_EXPENSE_ACCOUNT_CCID_DESC
	   ,BONUS_RESERVE_ACCT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = BONUS_RESERVE_ACCT_CCID) BONUS_RESERVE_ACCT_CCID_DESC
	   ,REVAL_AMORT_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = REVAL_AMORT_ACCOUNT_CCID) REVAL_AMORT_ACCOUNT_CCID_DESC
	   ,REVAL_RESERVE_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = REVAL_RESERVE_ACCOUNT_CCID) REVAL_RESERVE_ACCOUNT_CCID_DESC
	   ,UNPLAN_EXPENSE_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = UNPLAN_EXPENSE_ACCOUNT_CCID) UNPLAN_EXPENSE_ACCOUNT_CCID_DESC
	   ,ALT_COST_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = ALT_COST_ACCOUNT_CCID) ALT_COST_ACCOUNT_CCID_DESC
	   ,WRITE_OFF_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = WRITE_OFF_ACCOUNT_CCID) WRITE_OFF_ACCOUNT_CCID_DESC
	   ,IMPAIR_EXPENSE_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = IMPAIR_EXPENSE_ACCOUNT_CCID) IMPAIR_EXPENSE_ACCOUNT_CCID_DESC
	   ,IMPAIR_RESERVE_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = IMPAIR_RESERVE_ACCOUNT_CCID) IMPAIR_RESERVE_ACCOUNT_CCID_DESC
	   ,CAPITAL_ADJ_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = CAPITAL_ADJ_ACCOUNT_CCID) CAPITAL_ADJ_ACCOUNT_CCID_DESC
	   ,GENERAL_FUND_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = GENERAL_FUND_ACCOUNT_CCID) GENERAL_FUND_ACCOUNT_CCID_DESC
	   ,REVAL_LOSS_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = REVAL_LOSS_ACCOUNT_CCID) REVAL_LOSS_ACCOUNT_CCID_DESC
	   ,COST_WRITE_OFF_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = COST_WRITE_OFF_ACCOUNT_CCID) COST_WRITE_OFF_ACCOUNT_CCID_DESC
	   ,LEASE_LIABILITY_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = LEASE_LIABILITY_ACCOUNT_CCID) LEASE_LIABILITY_ACCOUNT_CCID_DESC
	   ,LEASE_INT_EXPENSE_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = LEASE_INT_EXPENSE_ACCOUNT_CCID) LEASE_INT_EXPENSE_ACCOUNT_CCID_DESC
	   ,LEASE_EXPENSE_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = LEASE_EXPENSE_ACCOUNT_CCID) LEASE_EXPENSE_ACCOUNT_CCID_DESC
	   ,LEASE_GAIN_LOSS_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = LEASE_GAIN_LOSS_ACCOUNT_CCID) LEASE_GAIN_LOSS_ACCOUNT_CCID_DESC
	   ,LEASE_CLEARING_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = LEASE_CLEARING_ACCOUNT_CCID) LEASE_CLEARING_ACCOUNT_CCID_DESC
	   ,REVALUED_COST_ACCOUNT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = REVALUED_COST_ACCOUNT_CCID) REVALUED_COST_ACCOUNT_CCID_DESC
	   ,BACKLOG_DEPRN_RSV_ACCT_CCID
	   ,(SELECT dhcc.segment1||'-'||dhcc.segment2||'-'||dhcc.segment3||'-'||dhcc.segment4||'-'||dhcc.segment5||'-'||dhcc.segment6||'-'||dhcc.segment7||'-'||dhcc.segment8 dhcc
		   FROM GL_CODE_COMBINATIONS dhcc
		  WHERE CODE_COMBINATION_ID = BACKLOG_DEPRN_RSV_ACCT_CCID) BACKLOG_DEPRN_RSV_ACCT_CCID_DESC
	   ,V_GL_CODE.comb
  FROM FA_CATEGORY_BOOKS FCB
	 , FA_CATEGORIES_B FC
	 , FA_ADDITIONS_B fa
	 , fa_books fb
	 , (SELECT  
		DISTINCT book_type_code,asset_id, gcc.segment1||'-'||gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||gcc.segment5||'-'||gcc.segment6||'-'||gcc.segment7||'-'||gcc.segment8 comb
		 FROM FA_DEPRN_summary FAD, xla_events xle, xla_ae_headers aeh, xla_ae_lines ael, gl_code_combinations gcc, gl_import_references gir, gl_je_lines gjl
		 WHERE fad.event_id = xle.event_id
		 AND aeh.event_id = xle.event_id
		 AND xle.application_id = aeh.application_id
		 AND ael.application_id = aeh.application_id
		 AND ael.AE_HEADER_ID = aeh.AE_HEADER_ID
		 AND gir.gl_sl_link_id = ael.gl_sl_link_id
		 AND gir.gl_sl_link_table = ael.gl_sl_link_table
		 AND gjl.je_header_id = gir.je_header_id
		 AND gjl.je_line_num = gir.je_line_num
		 AND ael.ACCOUNTED_DR IS NOT NULL
		 --AND FAD.asset_id = 18381
		 AND gcc.CODE_COMBINATION_ID = gjl.CODE_COMBINATION_ID
		 AND PERIOD_COUNTER=(select max(PERIOD_COUNTER) FROM fa_deprn_periods
										WHERE TRUNC(CALENDAR_PERIOD_OPEN_DATE,'MM')= TRUNC (:p_from_date,'MM')	-- CHANGE LESS THEN SIGN TO EQUAL 21 APR 2024
										and PERIOD_COUNTER in (select period_counter   from fa_deprn_summary 
																where asset_id = FAD.asset_id
																and   book_type_code = FAD.book_type_code))
																
					GROUP BY   book_type_code,asset_id, gcc.segment1||'-'||gcc.segment2||'-'||gcc.segment3||'-'||gcc
		.segment4||'-'||gcc.segment5||'-'||gcc.segment6||'-'||gcc.segment7||'-'||gcc.segment8) V_GL_CODE
  WHERE FCB.CATEGORY_ID = FC.CATEGORY_ID
    AND FCB.BOOK_TYPE_CODE = :P_ASSET_BOOK
	AND fa.asset_category_id = FC.category_id
	AND fb.asset_id = fa.asset_id
	AND FCB.book_type_code = fb.book_type_code
	AND V_GL_CODE.book_type_code (+) =FB.book_type_code
	and V_GL_CODE.asset_id (+) =fa.asset_id
ORDER BY FA.asset_number

SELECT 	acr.cash_receipt_id
      	,acr.TYPE
      	,acr.receipt_number AS cheque_number
-- ,acr.doc_sequence_value  AS receipt_number
      	, acr.receipt_number   AS receipt_number  ---Modified by pankaj on 20/6/2009
      	,acr.misc_payment_source party_name
      	,NVL (acr.amount, 0) amount
      	,FLOOR (NVL (acr.amount, 0)) Famount
		,NVL (acr.amount, 0) - FLOOR (NVL (acr.amount, 0)) Damount
      	,REPLACE (iby_amount_in_words.get_amount_in_words 
		(NVL (acr.amount, 0),acr.currency_code)
               		,'*',''
               	) amount_words
      	,acr.currency_code
      	,acr.pay_from_customer
      	,acr.customer_site_use_id
      	,acr.receipt_date
      	,acr.deposit_date
      	,hou.NAME ou_name
      	,acr.comments inv_type
-- /*rec_trx.NAME AS inv_type_obs,*/
      	,SUBSTR (UPPER (arm.NAME), 1, 4) AS receipt_method
      	,fnd.USERNAME user_name

FROM   	ar_cash_receipts_all acr
      	,ar_receivables_trx_all rec_trx
      	,ar_receipt_methods arm
      	,hr_operating_units hou
      	,per_users fnd

WHERE  	acr.receivables_trx_id = rec_trx.receivables_trx_id
AND    	acr.receipt_method_id = arm.receipt_method_id
-- AND    	acr.TYPE = :rcpt_type
AND    	acr.TYPE = 'MISC'
AND    	acr.created_by = fnd.USERNAME
AND    	hou.organization_id = acr.org_id
AND    	acr.org_id = NVL (:org_id, acr.org_id)
AND    	acr.cash_receipt_id = NVL (:p_receipt, acr.cash_receipt_id)
AND 	acr.currency_code = NVL (:P_Currency, acr.currency_code)
AND 	acr.misc_payment_source = NVL (:P_Party, acr.misc_payment_source)
-- AND    	acr.cash_receipt_id >= NVL (:p_receipt, acr.cash_receipt_id)
-- AND    	acr.cash_receipt_id <= NVL (:p_receipt2, acr.cash_receipt_id)
AND     	acr.receipt_date between nvl(:p_rcp_dt_from, acr.receipt_date) and nvl(:p_rcp_dt_to, acr.receipt_date)
-- AND     	acr.deposit_date between nvl(:p_dep_dt_from, acr.deposit_date) and nvl(:p_dep_dt_to, acr.deposit_date)
-- AND     	TRUNC(acr.creation_date) between nvl(:p_cr_dt_from, TRUNC(acr.creation_date)) and nvl(:p_cr_dt_to, TRUNC(acr.creation_date))
-- AND     	acr.created_by = nvl(:p_crt_by, acr.created_by)

UNION

SELECT 	acr.cash_receipt_id
      	,acr.TYPE
      	,acr.receipt_number AS cheque_number
-- ,nvl(acr.doc_sequence_value,acr.receipt_number) AS receipt_number
       	,acr.receipt_number   AS receipt_number  ---Modified by pankaj on 20/6/2009
      	,party.party_name
      	,NVL (acr.amount, 0) amount
      	,FLOOR (NVL (acr.amount, 0)) Famount
		,NVL (acr.amount, 0) - FLOOR (NVL (acr.amount, 0)) Damount
      	,REPLACE (iby_amount_in_words.get_amount_in_words 
		(NVL (acr.amount, 0) ,acr.currency_code )
               		,'*' ,''
               	) amount_words
      	,acr.currency_code
      	,acr.pay_from_customer
      	,acr.customer_site_use_id
      	,acr.receipt_date
      	,acr.deposit_date
      	,hou.NAME ou_name
      	,acr.comments inv_type
      	,SUBSTR (UPPER (arm.NAME), 1, 4) AS receipt_method
      	,fnd.USERNAME user_name

FROM   	ar_cash_receipts_all acr
      	,ar_receipt_methods arm
      	,hz_cust_accounts cust
      	,hz_parties party
      	,hr_operating_units hou
      	,per_users fnd

WHERE  	acr.receipt_method_id = arm.receipt_method_id
AND    	acr.pay_from_customer = cust.cust_account_id(+)
AND    	cust.party_id = party.party_id(+)
AND    	acr.org_id = hou.organization_id
AND    	acr.org_id = NVL (:org_id, acr.org_id)
AND    	acr.cash_receipt_id = NVL (:p_receipt, acr.cash_receipt_id)
AND 	acr.currency_code = NVL (:P_Currency, acr.currency_code)
AND 	party.party_name = NVL (:P_Party, party.party_name)
-- AND    	acr.org_id = :p_org_id
-- AND    	acr.TYPE = :rcpt_type
AND    	acr.TYPE = 'CASH'
AND    	acr.created_by = fnd.USERNAME
-- AND    	acr.cash_receipt_id >= NVL (:p_receipt, acr.cash_receipt_id)
-- AND    	acr.cash_receipt_id <= NVL (:p_receipt2, acr.cash_receipt_id)
AND     	acr.receipt_date between nvl(:p_rcp_dt_from, acr.receipt_date) and nvl(:p_rcp_dt_to, acr.receipt_date)
-- AND     	acr.deposit_date between nvl(:p_dep_dt_from, acr.deposit_date) and nvl(:p_dep_dt_to, acr.deposit_date)
-- AND     	TRUNC(acr.creation_date) between nvl(:p_cr_dt_from, TRUNC(acr.creation_date)) and nvl(:p_cr_dt_to, TRUNC(acr.creation_date))
-- AND     	acr.created_by = nvl(:p_crt_by, acr.created_by)

SELECT
    hp.party_number,
    hp.party_name,
    hca.account_number,
    hps.party_site_number,
    hcpc.name                 profile_class_name,
    hcpf.cust_account_profile_id,
    hcpf.profile_class_id,
    hca.orig_system_reference account_orig_ref,
    hcpf.send_statements,
    rt.term_id,
    rt.name                   payment_term,
    hcpf.creation_date,
    hcpf.created_by
FROM
    hz_customer_profiles_f  hcpf,
    hz_cust_accounts        hca,
    hz_cust_profile_classes hcpc,
    hz_party_sites          hps,
    hz_parties              hp,
    hz_cust_acct_sites_all  hcas,
    ra_terms_tl             rt,
    hz_cust_site_uses_all   hcsu
WHERE
        1 = 1
    AND hcpf.cust_account_id = hca.cust_account_id
    AND hcpc.profile_class_id = hcpf.profile_class_id
    AND hp.party_id = hps.party_id
    AND hp.party_id = hca.party_id
    AND hcpf.site_use_id = hcsu.site_use_id
    AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
    AND hcas.party_site_id = hps.party_site_id
    AND hps.party_site_id = hcas.party_site_id
    AND hcpf.standard_terms = rt.term_id
    AND rt.language = 'US'

SELECT
    gl.name ledger_name,
	gjh.je_header_id,
	gjb.je_batch_id,
	gjh.period_name,
	gjh.je_source source_name,
    gjh.je_category category,
    gjb.name batch_name,
    gjh.name journal_name,
    (SELECT MEANING FROM FND_LOOKUP_VALUES
	  WHERE LOOKUP_TYPE = 'JL_INQ_STATUS'
	    AND LOOKUP_CODE = gjb.status
		AND LANGUAGE = 'US') batch_status,
    gjh.doc_sequence_value doc_num2,
    gjh.POSTING_ACCT_SEQ_VALUE doc_num,
    gjh.status,
	(SELECT MEANING FROM FND_LOOKUP_VALUES
	  WHERE LOOKUP_TYPE = 'JL_INQ_STATUS'
	    AND LOOKUP_CODE = gjh.status
		AND LANGUAGE = 'US') status_meaning,
    gjh.DEFAULT_EFFECTIVE_DATE doc_date,
	gjh.date_created,
    gjl.DESCRIPTION,
    gjh.currency_conversion_type currency_conversion_type,
	gjh.currency_conversion_rate currency_conversion_rate,
    gjh.currency_code currency,
    gjl.je_line_num je_line_numer,
    gjl.accounted_cr accounted_cr,
    gjl.accounted_dr accounted_dr,
    gjl.entered_dr entered_dr,
    gjl.entered_cr entered_cr,
    fnd_flex_ext.get_segs (
        'GL',
        'GL#',
        gcc.chart_of_accounts_id,
        gcc.code_combination_id
    ) gl_code,
    gl_flexfields_pkg.get_concat_description (
        gcc.chart_of_accounts_id,
        gcc.code_combination_id
    ) gl_seg5_desc,
    gl_flexfields_pkg.get_description_sql (
        gcc.chart_of_accounts_id,
        5,
		gcc.segment5
    ) gl_seg5_desc1,
	gjh.LAST_UPDATED_BY,
    NVL ((
        SELECT
            ppn.FULL_NAME
        FROM
            PER_PERSON_NAMES_F ppn
            JOIN PER_USERS pea ON ppn.PERSON_ID = pea.PERSON_ID
        WHERE
            pea.USERNAME = gjh.LAST_UPDATED_BY
            AND ppn.CHAR_SET_CONTEXT = 'US'
            AND ROWNUM = 1
    ), 
	(SELECT FIRST_NAME || ' ' || LAST_NAME FROM PER_LDAP_USERS
	  WHERE USERNAME = gjh.LAST_UPDATED_BY
	    AND USER_CATEGORY = 'IS_NULL' AND ROWNUM = 1)) LAST_UPDATED_BY_NAME,
	gjh.CREATED_BY,
    NVL ((
        SELECT
            ppn.FULL_NAME
        FROM
            PER_PERSON_NAMES_F ppn
            JOIN PER_USERS pea ON ppn.PERSON_ID = pea.PERSON_ID
        WHERE
            pea.USERNAME = gjh.CREATED_BY
            AND ppn.CHAR_SET_CONTEXT = 'US'
            AND ROWNUM = 1
    ), 
	(SELECT FIRST_NAME || ' ' || LAST_NAME FROM PER_LDAP_USERS
	  WHERE USERNAME = gjh.CREATED_BY
	    AND USER_CATEGORY = 'IS_NULL' AND ROWNUM = 1)) CREATED_BY_NAME,
    (
        SELECT
            ppn.FULL_NAME
        FROM
            PER_PERSON_NAMES_F ppn
        WHERE
            ppn.PERSON_ID = gjb.APPROVER_EMPLOYEE_ID
            AND ppn.CHAR_SET_CONTEXT = 'US'
            AND ROWNUM = 1
    ) APPROVED_BY,
	(
	 SELECT USER_ID
	   FROM GL_JE_ACTION_LOG
	  WHERE JE_BATCH_ID = gjb.je_batch_id
		AND ACTION_CODE = 'POSTED'
	) POSTED_BY,
	NVL ((
	 SELECT ppn.FULL_NAME
	   FROM GL_JE_ACTION_LOG GJAL, PER_PERSON_NAMES_F ppn, PER_USERS pea
	  WHERE JE_BATCH_ID = gjb.je_batch_id
		AND ACTION_CODE = 'POSTED'
		AND pea.USERNAME = GJAL.USER_ID
		and ppn.PERSON_ID = pea.PERSON_ID
		AND ppn.CHAR_SET_CONTEXT = 'US'
        AND ROWNUM = 1
	),
	(SELECT FIRST_NAME || ' ' || LAST_NAME FROM PER_LDAP_USERS, GL_JE_ACTION_LOG GJAL
	  WHERE USERNAME = gjh.CREATED_BY
	    AND USER_CATEGORY = 'IS_NULL' AND GJAL.JE_BATCH_ID = gjb.je_batch_id AND GJAL.ACTION_CODE = 'POSTED' AND ROWNUM = 1)
	)	POSTED_BY_NAME,
	(
	 SELECT ACTION_DATE
	   FROM GL_JE_ACTION_LOG
	  WHERE JE_BATCH_ID = gjb.je_batch_id
		AND ACTION_CODE = 'POSTED'
	) POSTED_DATE,
	(
	 SELECT ACTION_DATE
	   FROM GL_JE_ACTION_LOG
	  WHERE JE_BATCH_ID = gjb.je_batch_id
		AND ACTION_CODE = 'APPROVED'
	) APPROVED_DATE,
	(
	 SELECT ACTION_DATE
	   FROM GL_JE_ACTION_LOG
	  WHERE JE_BATCH_ID = gjb.je_batch_id
		AND ACTION_CODE = 'CREATED'
	) CREATED_DATE
FROM
    gl_je_lines gjl,
    gl_je_headers gjh,
    gl_je_batches gjb,
    gl_code_combinations gcc,
	gl_ledgers gl
WHERE
    gjl.je_header_id = gjh.je_header_id
    AND gjh.je_batch_id = gjb.je_batch_id
    AND gjl.code_combination_id = gcc.code_combination_id(+)
	AND gjh.ledger_id = gl.ledger_id
    AND gjh.ledger_id = NVL (:p_ledger_id, gjh.ledger_id)
    AND NVL (gjh.POSTING_ACCT_SEQ_VALUE, -99) = NVL (:p_doc_no, NVL (gjh.POSTING_ACCT_SEQ_VALUE, -99))
    AND TRIM (gjh.name) = NVL (:p_journal_name, TRIM (gjh.name))
    AND gjh.status = NVL (:p_status, gjh.status)
    AND TRUNC (gjh.date_created) BETWEEN NVL (:p_date_from, TRUNC (gjh.date_created))
    AND NVL (:p_date_to, TRUNC (gjh.date_created))
    AND gjh.je_source = NVL (:p_source, gjh.je_source)
	AND gjb.je_batch_id = NVL (:p_batch, gjb.je_batch_id)

/* Formatted on 8/23/2023 9:04:24 PM (QP5 v5.360) */
SELECT *
  FROM (  SELECT CHART_OF_ACCOUNTS_ID,
                 LEDGER_ID,
                 SEGMENT1,
                 SEGMENT2,
                 SEGMENT3,
                 SEGMENT4,
                 SEGMENT5,
                 SEGMENT6,
                 SEGMENT7,
                 SEGMENT8,
                 DESC1,
                 DESC2,
                 DESC3,
                 DESC4,
                 DESC5,
                 DESC6,
                 DESC7,
                 DESC8,
                    SEGMENT1
                 || '-'
                 || SEGMENT2
                 || '-'
                 || SEGMENT3
                 || '-'
                 || SEGMENT4
                 || '-'
                 || SEGMENT5
                 || '-'
                 || SEGMENT6
                 || '-'
                 || SEGMENT7
                 || '-'
                 || SEGMENT8                                      Full_Accode,
                 TO_NUMBER(SUM (OPEN_BAL))                                   OPBAL,
                 SUM (DEBIT)                                      ACC_DR,
                 SUM (CREDIT)                                     ACC_CR,
                 (SUM (OPEN_BAL) + SUM (DEBIT)) - SUM (CREDIT)    NET,
                 CASE
                     WHEN     SUM (OPEN_BAL) = 0
                          AND SUM (DEBIT) = 0
                          AND SUM (CREDIT) = 0
                     THEN
                         'Y'
                     ELSE
                         'N'
                 END                                              AS DISPLAY_ZERO
            FROM (  SELECT GLCO.CHART_OF_ACCOUNTS_ID,
                           HD.LEDGER_ID,
                           GLCO.SEGMENT1                     AS SEGMENT1,
                           GLCO.SEGMENT2                     AS SEGMENT2,
                           GLCO.SEGMENT3                     AS SEGMENT3,
                           GLCO.segment4                     AS segment4,
                           GLCO.SEGMENT5                     AS SEGMENT5,
                           GLCO.SEGMENT6                     AS SEGMENT6,
                           GLCO.SEGMENT7                     AS SEGMENT7,
                           GLCO.SEGMENT8                     AS SEGMENT8,
                           gl_flexfields_pkg.get_description_sql (
                               GLCO.CHART_OF_ACCOUNTS_ID,
                               1,
                               GLCO.segment1)                AS DESC1,
                           gl_flexfields_pkg.get_description_sql (
                               GLCO.CHART_OF_ACCOUNTS_ID,
                               2,
                               GLCO.segment2)                AS DESC2,
                           gl_flexfields_pkg.get_description_sql (
                               GLCO.CHART_OF_ACCOUNTS_ID,
                               3,
                               GLCO.segment3)                AS DESC3,
                           gl_flexfields_pkg.get_description_sql (
                               GLCO.CHART_OF_ACCOUNTS_ID,
                               4,
                               GLCO.segment4)                AS DESC4,
                           gl_flexfields_pkg.get_description_sql (
                               GLCO.CHART_OF_ACCOUNTS_ID,
                               5,
                               GLCO.segment5)                AS DESC5,
                           gl_flexfields_pkg.get_description_sql (
                               GLCO.CHART_OF_ACCOUNTS_ID,
                               6,
                               GLCO.segment6)                AS DESC6,
                           gl_flexfields_pkg.get_description_sql (
                               GLCO.CHART_OF_ACCOUNTS_ID,
                               7,
                               GLCO.segment7)                AS DESC7,
                           gl_flexfields_pkg.get_description_sql (
                               GLCO.CHART_OF_ACCOUNTS_ID,
                               8,
                               GLCO.segment8)                AS DESC8,
                           0                                 OPEN_BAL,
                           NVL (SUM (TB.ACCOUNTED_DR), 0)    DEBIT,
                           NVL (SUM (TB.ACCOUNTED_CR), 0)    CREDIT
                      FROM GL_JE_LINES TB
                           JOIN GL_CODE_COMBINATIONS GLCO
                               ON (TB.CODE_COMBINATION_ID =
                                   GLCO.CODE_COMBINATION_ID)
                           JOIN GL_JE_HEADERS HD
                               ON (TB.JE_HEADER_ID = HD.JE_HEADER_ID)
                     WHERE     HD.ACTUAL_FLAG = :P_ACTUAL_FLAG --:P_ACTUAL_FLAG
                           AND GLCO.SUMMARY_FLAG = 'N'
                           AND GLCO.TEMPLATE_ID IS NULL
                           AND HD.LEDGER_ID = :P_LEDGER_ID
                           AND (HD.STATUS = :P_STATUS OR :P_STATUS IS NULL)
                           AND TRUNC (HD.DEFAULT_EFFECTIVE_DATE) BETWEEN TRUNC (
                                                                             :CREATION_DATE_FROM)
                                                                     AND TRUNC (
                                                                             :CREATION_DATE_TO)
                           AND (GLCO.SEGMENT1 IN (:P_COMPANY) OR LEAST (:P_COMPANY) IS NULL)
                           -- AND GLCO.CHART_OF_ACCOUNTS_ID = :P_COA
                           AND TB.CURRENCY_CODE <> 'STAT'
                           AND (   ( :P_CLOSING = 1)
                                OR (    :P_CLOSING = 0
                                    AND HD.JE_CATEGORY !=
                                        'Income Statement Close'))
                  GROUP BY GLCO.CHART_OF_ACCOUNTS_ID,
                           HD.LEDGER_ID,
                           GLCO.SEGMENT1,
                           GLCO.SEGMENT2,
                           GLCO.SEGMENT3,
                           GLCO.SEGMENT4,
                           GLCO.SEGMENT5,
                           GLCO.SEGMENT6,
                           GLCO.SEGMENT7,
                           GLCO.SEGMENT8
                  ----------------------------------------------------------------------------------------------------------
                  UNION ALL
                    ------------------------------------------------------------------------------------------------------------
                    SELECT GLCO.CHART_OF_ACCOUNTS_ID,
                           HD.LEDGER_ID,
                           GLCO.SEGMENT1                       AS SEGMENT1,
                           GLCO.SEGMENT2                       AS SEGMENT2,
                           GLCO.SEGMENT3                       AS SEGMENT3,
                           GLCO.segment4                       AS segment4,
                           GLCO.SEGMENT5                       AS SEGMENT5,
                           GLCO.SEGMENT6                       AS SEGMENT6,
                           GLCO.SEGMENT7                       AS SEGMENT7,
                           GLCO.SEGMENT8                       AS SEGMENT8,
                           gl_flexfields_pkg.get_description_sql (
                               GLCO.CHART_OF_ACCOUNTS_ID,
                               1,
                               GLCO.segment1)                  AS DESC1,
                           gl_flexfields_pkg.get_description_sql (
                               GLCO.CHART_OF_ACCOUNTS_ID,
                               2,
                               GLCO.segment2)                  AS DESC2,
                           gl_flexfields_pkg.get_description_sql (
                               GLCO.CHART_OF_ACCOUNTS_ID,
                               3,
                               GLCO.segment3)                  AS DESC3,
                           gl_flexfields_pkg.get_description_sql (
                               GLCO.CHART_OF_ACCOUNTS_ID,
                               4,
                               GLCO.segment4)                  AS DESC4,
                           gl_flexfields_pkg.get_description_sql (
                               GLCO.CHART_OF_ACCOUNTS_ID,
                               5,
                               GLCO.segment5)                  AS DESC5,
                           gl_flexfields_pkg.get_description_sql (
                               GLCO.CHART_OF_ACCOUNTS_ID,
                               6,
                               GLCO.segment6)                  AS DESC6,
                           gl_flexfields_pkg.get_description_sql (
                               GLCO.CHART_OF_ACCOUNTS_ID,
                               7,
                               GLCO.segment7)                  AS DESC7,
                           gl_flexfields_pkg.get_description_sql (
                               GLCO.CHART_OF_ACCOUNTS_ID,
                               8,
                               GLCO.segment8)                  AS DESC8,
                             NVL (SUM (TB.ACCOUNTED_DR), 0)
                           - NVL (SUM (TB.ACCOUNTED_CR), 0)    OPEN_BAL,
                           0                                   DEBIT,
                           0                                   CREDIT
                      FROM GL_JE_LINES TB
                           JOIN GL_CODE_COMBINATIONS GLCO
                               ON (TB.CODE_COMBINATION_ID =
                                   GLCO.CODE_COMBINATION_ID)
                           JOIN GL_JE_HEADERS HD
                               ON (TB.JE_HEADER_ID = HD.JE_HEADER_ID)
                     WHERE     HD.ACTUAL_FLAG = :P_ACTUAL_FLAG --:P_ACTUAL_FLAG
                           AND GLCO.SUMMARY_FLAG = 'N'
                           AND GLCO.TEMPLATE_ID IS NULL
                           AND HD.LEDGER_ID = :P_LEDGER_ID
                           AND (HD.STATUS = :P_STATUS OR :P_STATUS IS NULL)
                           AND TRUNC (HD.DEFAULT_EFFECTIVE_DATE) <
                               TRUNC ( :CREATION_DATE_FROM)
                           --AND (GLCO.SEGMENT1 = :P_COMPANY OR :P_COMPANY IS NULL)
                           AND (GLCO.SEGMENT1 IN (:P_COMPANY) OR LEAST (:P_COMPANY) IS NULL)
                           --AND GLCO.CHART_OF_ACCOUNTS_ID = :P_COA
                           AND TB.CURRENCY_CODE <> 'STAT'
                  GROUP BY GLCO.CHART_OF_ACCOUNTS_ID,
                           HD.LEDGER_ID,
                           GLCO.SEGMENT1,
                           GLCO.SEGMENT2,
                           GLCO.SEGMENT3,
                           GLCO.SEGMENT4,
                           GLCO.SEGMENT5,
                           GLCO.SEGMENT6,
                           GLCO.SEGMENT7,
                           GLCO.SEGMENT8)
           WHERE     SEGMENT1 BETWEEN NVL (:P_PROJECTS_COMPANY_FROM, SEGMENT1)
                                  AND NVL (:P_PROJECTS_COMPANY_TO, SEGMENT1)
                 AND SEGMENT2 BETWEEN NVL (:P_PROJECTS_DEPARTMENT_FROM, SEGMENT2)
                                  AND NVL (:P_PPROJECTS_DEPARTMENT_TO, SEGMENT2)
                 AND SEGMENT3 BETWEEN NVL ( :P_PROJECTS_LOCATION_FROM, SEGMENT3)
                                  AND NVL ( :P_PROJECTS_LOCATION_TO, SEGMENT3)
                 AND SEGMENT4 BETWEEN NVL ( :P_PROJECT_FROM, SEGMENT4)
                                  AND NVL ( :P_PROJECT_TO, SEGMENT4)
                 AND SEGMENT5 BETWEEN NVL ( :P_ACCOUNT_FROM, SEGMENT5)
                                  AND NVL ( :P_ACCOUNT_TO, SEGMENT5)
                 AND SEGMENT6 BETWEEN NVL ( :P_AUXILARY_FROM, SEGMENT6)
                                 AND NVL ( :P_AUXILARY_TO, SEGMENT6)
                 AND SEGMENT7 BETWEEN NVL ( :P_FURTURE_FROM, SEGMENT7)
                                  AND NVL ( :P_FURTURE_TO, SEGMENT7)
                 AND SEGMENT8 BETWEEN NVL ( :P_FUTURE_FROM, SEGMENT8)
                                  AND NVL ( :P_FUTURE_TO, SEGMENT8)
        GROUP BY CHART_OF_ACCOUNTS_ID,
                 LEDGER_ID,
                 SEGMENT1,
                 SEGMENT2,
                 SEGMENT3,
                 SEGMENT4,
                 SEGMENT5,
                 SEGMENT6,
                 SEGMENT7,
                 SEGMENT8,
                 DESC1,
                 DESC2,
                 DESC3,
                 DESC4,
                 DESC5,
                 DESC6,
                 DESC7,
                 DESC8
        ORDER BY SEGMENT5)
 WHERE DISPLAY_ZERO = :P_DISPLAY_ZERO
 OR DECODE(:P_DISPLAY_ZERO,'Y',1) =1

Others

WITH employee_assignment AS
(
    SELECT
        paam.person_id,
        paam.assignment_number,
        paam.organization_id,
        paam.business_unit_id,
        paam.legal_entity_id,
        paam.position_id,

        ROW_NUMBER() OVER
        (
            PARTITION BY paam.person_id
            ORDER BY
                CASE
                    WHEN paam.primary_flag = 'Y'
                    THEN 0 ELSE 1
                END,
                paam.effective_start_date DESC,
                paam.effective_sequence DESC,
                paam.assignment_id DESC
        ) assignment_rn

    FROM per_all_assignments_m paam

    WHERE TRUNC(SYSDATE) BETWEEN
          paam.effective_start_date
          AND paam.effective_end_date

      AND paam.effective_latest_change = 'Y'
      AND paam.assignment_type = 'E'
      AND paam.assignment_status_type = 'ACTIVE'
),

employee_details AS
(
    SELECT
        ppf.person_id,
        ppf.person_number,
        ppnf.display_name employee_name,
        ea.assignment_number,
        ea.business_unit_id,
        dept.name department_name,
        bu.bu_name business_unit_name,
        le.name legal_entity_name,
        pos.name position_name

    FROM per_all_people_f ppf

    LEFT JOIN per_person_names_f ppnf
      ON ppnf.person_id = ppf.person_id
     AND ppnf.name_type = 'GLOBAL'
     AND TRUNC(SYSDATE) BETWEEN
         ppnf.effective_start_date
         AND ppnf.effective_end_date

    LEFT JOIN employee_assignment ea
      ON ea.person_id = ppf.person_id
     AND ea.assignment_rn = 1

    LEFT JOIN hr_all_organization_units_f_vl dept
      ON dept.organization_id = ea.organization_id
     AND TRUNC(SYSDATE) BETWEEN
         dept.effective_start_date
         AND dept.effective_end_date

    LEFT JOIN fun_all_business_units_v bu
      ON bu.bu_id = ea.business_unit_id

    LEFT JOIN per_legal_employers le
      ON le.organization_id = ea.legal_entity_id
     AND TRUNC(SYSDATE) BETWEEN
         le.effective_start_date
         AND le.effective_end_date

    LEFT JOIN hr_all_positions_f_vl pos
      ON pos.position_id = ea.position_id
     AND TRUNC(SYSDATE) BETWEEN
         pos.effective_start_date
         AND pos.effective_end_date

    WHERE TRUNC(SYSDATE) BETWEEN
          ppf.effective_start_date
          AND ppf.effective_end_date
)

SELECT DISTINCT
       pu.username,

       ed.person_number,
       ed.employee_name,
       ed.assignment_number,
       ed.department_name,
       ed.business_unit_id,
       ed.business_unit_name,
       ed.legal_entity_name,
       ed.position_name,

       pr.role_name,
       pr.role_common_name role_code,

       das.name data_access_set_name,
       das.access_set_id,

       CASE das.security_segment_code
           WHEN 'F' THEN 'Full Ledger'
           WHEN 'B' THEN 'Balancing Segment'
           WHEN 'M' THEN 'Management Segment'
           ELSE das.security_segment_code
       END data_access_set_type,

       gl.name ledger_name,
       gl.ledger_id,

       asa.segment_value,

       CASE asa.access_privilege_code
           WHEN 'R' THEN 'Read Only'
           WHEN 'B' THEN 'Read and Write'
           ELSE asa.access_privilege_code
       END access_privilege,

       fda.active_flag data_access_active,
       fda.creation_date assignment_creation_date

FROM fun_user_role_data_asgnmnts fda

JOIN per_users pu
  ON pu.user_guid = fda.user_guid

LEFT JOIN per_roles_dn_vl pr
  ON pr.role_common_name = fda.role_name

JOIN gl_access_sets das
  ON das.access_set_id = fda.access_set_id

LEFT JOIN gl_access_set_assignments asa
  ON asa.access_set_id = das.access_set_id
 AND (
       asa.start_date IS NULL
       OR TRUNC(SYSDATE) >= TRUNC(asa.start_date)
     )
 AND (
       asa.end_date IS NULL
       OR TRUNC(SYSDATE) <= TRUNC(asa.end_date)
     )

LEFT JOIN gl_ledgers gl
  ON gl.ledger_id = asa.ledger_id

LEFT JOIN employee_details ed
  ON ed.person_id = pu.person_id

WHERE pu.active_flag = 'Y'

  AND NVL(pu.suspended, 'N') = 'N'

  AND pu.username NOT IN ('3', '5004')

  AND fda.active_flag = 'Y'

  /*AND (
       :P_PERSON_NUMBER IS NULL
       OR ed.person_number = :P_PERSON_NUMBER
      )

  AND (
       :P_BUSINESS_UNIT_ID IS NULL
       OR ed.business_unit_id = :P_BUSINESS_UNIT_ID
      )*/

  AND (ed.business_unit_id IN (:P_BUSINESS_UNIT_ID) OR LEAST (:P_BUSINESS_UNIT_ID) is null)
  AND :p_query = 2

ORDER BY
    pu.username,
    pr.role_name,
    das.name,
    gl.name,
    asa.segment_value

WITH employee_assignment AS
(
    SELECT
        paam.person_id,
        paam.assignment_number,
        paam.organization_id,
        paam.business_unit_id,
        paam.legal_entity_id,
        paam.position_id,

        ROW_NUMBER() OVER
        (
            PARTITION BY paam.person_id
            ORDER BY
                CASE
                    WHEN paam.primary_flag = 'Y'
                    THEN 0 ELSE 1
                END,
                paam.effective_start_date DESC,
                paam.effective_sequence DESC,
                paam.assignment_id DESC
        ) assignment_rn

    FROM per_all_assignments_m paam

    WHERE TRUNC(SYSDATE) BETWEEN
          paam.effective_start_date
          AND paam.effective_end_date

      AND paam.effective_latest_change = 'Y'
      AND paam.assignment_type = 'E'
      AND paam.assignment_status_type = 'ACTIVE'
),

employee_details AS
(
    SELECT
        ppf.person_id,
        ppf.person_number,
        ppnf.display_name employee_name,
        ea.assignment_number,
        ea.business_unit_id,
        dept.name department_name,
        bu.bu_name business_unit_name,
        le.name legal_entity_name,
        pos.name position_name

    FROM per_all_people_f ppf

    LEFT JOIN per_person_names_f ppnf
      ON ppnf.person_id = ppf.person_id
     AND ppnf.name_type = 'GLOBAL'
     AND TRUNC(SYSDATE) BETWEEN
         ppnf.effective_start_date
         AND ppnf.effective_end_date

    LEFT JOIN employee_assignment ea
      ON ea.person_id = ppf.person_id
     AND ea.assignment_rn = 1

    LEFT JOIN hr_all_organization_units_f_vl dept
      ON dept.organization_id = ea.organization_id
     AND TRUNC(SYSDATE) BETWEEN
         dept.effective_start_date
         AND dept.effective_end_date

    LEFT JOIN fun_all_business_units_v bu
      ON bu.bu_id = ea.business_unit_id

    LEFT JOIN per_legal_employers le
      ON le.organization_id = ea.legal_entity_id
     AND TRUNC(SYSDATE) BETWEEN
         le.effective_start_date
         AND le.effective_end_date

    LEFT JOIN hr_all_positions_f_vl pos
      ON pos.position_id = ea.position_id
     AND TRUNC(SYSDATE) BETWEEN
         pos.effective_start_date
         AND pos.effective_end_date

    WHERE TRUNC(SYSDATE) BETWEEN
          ppf.effective_start_date
          AND ppf.effective_end_date
)

SELECT DISTINCT
       pu.username,

       ed.person_number,
       ed.employee_name,
       ed.assignment_number,
       ed.department_name,
       ed.business_unit_id,
       ed.business_unit_name,
       ed.legal_entity_name,
       ed.position_name,

       pr.role_name,
       pr.role_common_name role_code,

       das.name data_access_set_name,
       das.access_set_id,

       CASE das.security_segment_code
           WHEN 'F' THEN 'Full Ledger'
           WHEN 'B' THEN 'Balancing Segment'
           WHEN 'M' THEN 'Management Segment'
           ELSE das.security_segment_code
       END data_access_set_type,

       gl.name ledger_name,
       gl.ledger_id,

       asa.segment_value,

       CASE asa.access_privilege_code
           WHEN 'R' THEN 'Read Only'
           WHEN 'B' THEN 'Read and Write'
           ELSE asa.access_privilege_code
       END access_privilege,

       fda.active_flag data_access_active,
       fda.creation_date assignment_creation_date

FROM fun_user_role_data_asgnmnts fda

JOIN per_users pu
  ON pu.user_guid = fda.user_guid

LEFT JOIN per_roles_dn_vl pr
  ON pr.role_common_name = fda.role_name

JOIN gl_access_sets das
  ON das.access_set_id = fda.access_set_id

LEFT JOIN gl_access_set_assignments asa
  ON asa.access_set_id = das.access_set_id
 AND (
       asa.start_date IS NULL
       OR TRUNC(SYSDATE) >= TRUNC(asa.start_date)
     )
 AND (
       asa.end_date IS NULL
       OR TRUNC(SYSDATE) <= TRUNC(asa.end_date)
     )

LEFT JOIN gl_ledgers gl
  ON gl.ledger_id = asa.ledger_id

LEFT JOIN employee_details ed
  ON ed.person_id = pu.person_id

WHERE pu.active_flag = 'Y'

  AND NVL(pu.suspended, 'N') = 'N'

  AND pu.username NOT IN ('3', '5004')

  AND fda.active_flag = 'Y'

  /*AND (
       :P_PERSON_NUMBER IS NULL
       OR ed.person_number = :P_PERSON_NUMBER
      )

  AND (
       :P_BUSINESS_UNIT_ID IS NULL
       OR ed.business_unit_id = :P_BUSINESS_UNIT_ID
      )*/

  AND (ed.business_unit_id IN (:P_BUSINESS_UNIT_ID) OR LEAST (:P_BUSINESS_UNIT_ID) is null)
  AND :p_query = 2

ORDER BY
    pu.username,
    pr.role_name,
    das.name,
    gl.name,
    asa.segment_value