Material Transaction Type Wise (/*Po receipt standard and blanket*/) transaction details

/*Po receipt (standard)*/
SELECT distinct REGEXP_REPLACE (msib.description,
                                 '([^[:alnum:]])',
                                 ' '
                                ) AS item_description,
                 rrsl.transaction_date AS trans_date, rt.uom_code AS uom,
                 gcc.segment3, gcc.segment2, msib.segment1 AS item_code,
                 SUBSTR (msib.segment1, 1, 1) AS item_code_series,
                 rt.primary_quantity AS quantity,
                 rt.po_unit_price AS unit_rate,
                 rt.primary_quantity * rt.po_unit_price AS VALUE,
                 gcc.segment4, 'PO Receipt' AS transaction_type_name,
                 ppa.segment1 AS project, pt.task_number AS task,
                 rsh.receipt_num AS reference_number,
                 msib.organization_id AS org_id
--                 tnpl_repair_pomaxdate(rsh.receipt_num) as last_update_date
            FROM apps.po_headers_all pha,
                 apps.po_lines_all pla,
                 po_distributions_all pda,
                 apps.po_line_types_tl pltt,
                 apps.mtl_system_items_b msib,
                 apps.gl_code_combinations gcc,
                 apps.pa_projects_all ppa,
                 apps.pa_tasks pt,
                 apps.rcv_transactions rt,
                 apps.rcv_shipment_headers rsh,
                 apps.rcv_shipment_lines rsl,
                 rcv_receiving_sub_ledger rrsl
           WHERE 1 = 1
--             and rsh.receipt_num=201200001249
             AND pha.authorization_status = 'APPROVED'
             AND pha.approved_flag = 'Y'
             AND pha.type_lookup_code = 'STANDARD'
             AND pha.po_header_id = pla.po_header_id
             AND pda.po_line_id = pla.po_line_id
             AND pda.po_header_id = pha.po_header_id
--             AND pltt.line_type = 'Services'
             AND pda.destination_type_code = 'EXPENSE'
             AND rt.transaction_type = 'DELIVER'
             AND pltt.line_type_id = pla.line_type_id
             AND pla.item_id = msib.inventory_item_id
             AND msib.organization_id = pda.destination_organization_id
             AND gcc.code_combination_id = pda.code_combination_id
             AND gcc.segment4 IN
                    ('3021000', '3021100', '3021200', '3021300', '3021400',
                     '3021500', '3021600', '3021700', '3021610')
             AND pda.project_id = ppa.project_id(+)
             AND pda.task_id = pt.task_id(+)
             AND rsh.shipment_header_id = rsl.shipment_header_id
             AND rsh.shipment_header_id = rt.shipment_header_id
             AND rt.po_header_id = pha.po_header_id
             AND rt.po_line_id = pla.po_line_id
             AND msib.organization_id = rt.organization_id
             AND rsl.item_id = pla.item_id
             AND accounting_line_type = 'Charge'
             AND rt.transaction_id = rrsl.rcv_transaction_id
             AND gcc.code_combination_id = rrsl.code_combination_id
             AND msib.organization_id = '102'
--             and tnpl_repair_pomaxdate(rsh.receipt_num)= rt.last_update_date
             AND gcc.segment4 = NVL (:p_account_code, gcc.segment4)
             AND gcc.segment2 = NVL (:p_cost_center, gcc.segment2)
             AND gcc.segment3 BETWEEN NVL (:p_from_department, gcc.segment3)
                                  AND NVL (:p_to_department, gcc.segment3)
             AND SUBSTR (msib.segment1, 1, 1) =
                            NVL (:p_item_series, SUBSTR (msib.segment1, 1, 1))
             AND trunc(rrsl.transaction_date) BETWEEN :p_f_date AND :p_t_date
        ORDER BY msib.segment1

/*Po receipt (blanket)*/

SELECT   REGEXP_REPLACE (msib.description,
                                 '([^[:alnum:]])',
                                 ' '
                                ) AS item_description,
                 rrsl.transaction_date AS trans_date, rt.uom_code AS uom,
                 gcc.segment3, gcc.segment2, msib.segment1 AS item_code,
                 SUBSTR (msib.segment1, 1, 1) AS item_code_series,
                 rt.primary_quantity AS quantity,
                 rt.po_unit_price AS unit_rate,
                 rt.primary_quantity * rt.po_unit_price AS VALUE,
                 gcc.segment4, ppa.segment1 AS project,
                 pt.task_number AS task,
                 'PO Receipt' AS transaction_type_name,
                 rsh.receipt_num AS reference_number,
                 msib.organization_id AS org_id
--                 tnpl_repair_pomaxdate1(rsh.receipt_num) as last_update_date
            FROM po_headers_all pha,
                 po_lines_all pla,
                 po_distributions_all pda,
                 po_releases_all pra,
                 po_line_locations_all plla,
                 po_line_types_tl pltt,
                 pa_projects_all ppa,
                 apps.pa_tasks pt,
                 mtl_system_items_b msib,
                 apps.rcv_transactions rt,
                 apps.rcv_shipment_headers rsh,
                 apps.rcv_shipment_lines rsl,
                 apps.rcv_receiving_sub_ledger rrsl,
                 apps.gl_code_combinations gcc
           WHERE 1 = 1
             AND pha.type_lookup_code = 'BLANKET'
             AND pha.po_header_id = pla.po_header_id
             AND pla.po_line_id = pda.po_line_id
             AND pha.po_header_id = pra.po_header_id
             AND pra.po_release_id = plla.po_release_id
             AND plla.line_location_id = pda.line_location_id
             AND pltt.line_type_id = pla.line_type_id
             AND ppa.project_id(+) = pda.project_id
             AND pt.task_id(+) = pda.task_id
             AND msib.inventory_item_id = pla.item_id
             AND msib.organization_id = pda.destination_organization_id
             AND rt.po_line_location_id = plla.line_location_id
             AND rt.po_distribution_id = pda.po_distribution_id
             AND rt.organization_id = pda.destination_organization_id
             AND rt.shipment_header_id = rsh.shipment_header_id
             AND rt.organization_id = rsh.ship_to_org_id
             AND rt.transaction_type = 'DELIVER'
             AND rsh.shipment_header_id = rsl.shipment_header_id
             AND rsl.destination_type_code = 'EXPENSE'
             AND rt.shipment_line_id = rsl.shipment_line_id
             AND rt.po_release_id = pra.po_release_id
             AND rt.transaction_id = rrsl.rcv_transaction_id
             AND rrsl.code_combination_id = gcc.code_combination_id
             AND rrsl.accounting_line_type = 'Charge'
             AND gcc.code_combination_id = pda.code_combination_id
             AND gcc.segment4 IN
                    ('3021000', '3021100', '3021200', '3021300', '3021400',
                     '3021500', '3021600', '3021700', '3021610')
             AND msib.organization_id = '102'
--             and tnpl_repair_pomaxdate1(rsh.receipt_num)= rt.last_update_date
             AND gcc.segment4 = NVL (:p_account_code, gcc.segment4)
             AND gcc.segment2 = NVL (:p_cost_center, gcc.segment2)
             AND gcc.segment3 BETWEEN NVL (:p_from_department, gcc.segment3)
                                  AND NVL (:p_to_department, gcc.segment3)
             AND SUBSTR (msib.segment1, 1, 1) =
                            NVL (:p_item_series, SUBSTR (msib.segment1, 1, 1))
             AND trunc(rrsl.transaction_date) BETWEEN :p_f_date AND :p_t_date
        ORDER BY msib.segment1