GMF Batch Cost Details

   SELECT          lk.meaning as line_type_dsp, 
                                        item.item_number, 
                                      --  fnd_date.displaydt_to_date(pnd.transaction_date) transaction_date, -- Bug 16814297 Uskumari
                                        to_char(pnd.transaction_date, 'dd-mm-yyyy hh24:mi:ss') transaction_date, -- Bug 16814297 Uskumari
                                        pnd.primary_quantity as transaction_quantity,
                                        item.primary_uom_code as transaction_uom, 
                                        mp.organization_code, 
                                        pnd.transaction_id, 
                                        to_number(decode(pnd.source_line_id, -99, NULL, pnd.source_line_id)) reverse_id, 
					ol.layer_id,
					CASE to_number(lk.lookup_code)
					  WHEN -1 THEN 0
					  WHEN  0 THEN 1
					  WHEN  2 THEN 2
					  WHEN  1 THEN 3
					END line_type
                        FROM            mtl_material_transactions pnd, 
                                        mtl_item_flexfields item, 
                                        gem_lookups lk,
                                        gmf_outgoing_material_layers ol,
                                        mtl_parameters mp
                        WHERE           pnd.transaction_source_type_id  = 5
                        AND             pnd.transaction_action_id       in (1, 27)
                        AND             item.inventory_item_id          = pnd.inventory_item_id
                        AND             item.organization_id            = pnd.organization_id                        
                        AND             pnd.transaction_source_id       = :P_BATCH_ID
                        AND             lk.lookup_type                  = 'GMD_FORMULA_ITEM_TYPE'
                        AND             lk.lookup_code                  = -1
                        AND             ol.mmt_transaction_id           = pnd.transaction_id
                        AND             ol.mmt_organization_id          = pnd.organization_id
                        AND             mp.organization_id              = pnd.organization_id
                        UNION ALL
                        SELECT          lk.meaning as line_type_dsp, 
                                        item.item_number, 
                                        -- fnd_date.displaydt_to_date(pnd.transaction_date) transaction_date, -- Bug 16814297 Uskumari
                                        to_char(pnd.transaction_date, 'dd-mm-yyyy hh24:mi:ss') transaction_date, -- Bug 16814297 Uskumari
                                        pnd.primary_quantity as transaction_quantity,
                                        item.primary_uom_code as transaction_uom, 
                                        mp.organization_code, 
                                        pnd.transaction_id, 
                                        to_number(decode(pnd.source_line_id, -99, NULL, pnd.source_line_id)) reverse_id,
                                        il.layer_id,
					CASE to_number(lk.lookup_code)
					  WHEN -1 THEN 0
					  WHEN  0 THEN 1
					  WHEN  2 THEN 2
					  WHEN  1 THEN 3
					END line_type
                        FROM            mtl_material_transactions pnd, 
                                        mtl_item_flexfields item, 
                                        gem_lookups lk,
                                        gmf_incoming_material_layers il,
                                        mtl_parameters mp
                        WHERE           pnd.transaction_source_type_id  = 5
                        AND             pnd.transaction_action_id       in (31, 32)
                        AND             item.inventory_item_id          = pnd.inventory_item_id
                        AND             item.organization_id            = pnd.organization_id                        
                        AND             pnd.transaction_source_id       = :P_BATCH_ID
                        AND             lk.lookup_type                  = 'GMD_FORMULA_ITEM_TYPE'
                        AND             lk.lookup_code                  = 1
                        AND             il.mmt_transaction_id           = pnd.transaction_id
                        AND             il.mmt_organization_id          = pnd.organization_id
                        AND             mp.organization_id              = pnd.organization_id
                        --begin Bug 25364925
                        UNION ALL
                        SELECT          lk.meaning as line_type_dsp, 
                                        item.item_number, 
                                      --  fnd_date.displaydt_to_date(pnd.transaction_date) transaction_date, -- Bug 16814297 Uskumari
                                        to_char(pnd.transaction_date, 'dd-mm-yyyy hh24:mi:ss') transaction_date, -- Bug 16814297 Uskumari
                                        pnd.primary_quantity as transaction_quantity,
                                        item.primary_uom_code as transaction_uom, 
                                        mp.organization_code, 
                                        pnd.transaction_id, 
                                        to_number(decode(pnd.source_line_id, -99, NULL, pnd.source_line_id)) reverse_id, 
					ol.layer_id,
					CASE to_number(lk.lookup_code)
					  WHEN -1 THEN 0
					  WHEN  0 THEN 1
					  WHEN  2 THEN 2
					  WHEN  1 THEN 3
					END line_type
                        FROM            mtl_material_transactions pnd, 
                                        mtl_item_flexfields item, 
                                        gem_lookups lk,
                                        gmf_outgoing_material_layers ol,
                                        mtl_parameters mp
                        WHERE           pnd.transaction_source_type_id  = 5
                        AND             pnd.transaction_action_id       in (31, 32)
                        AND             item.inventory_item_id          = pnd.inventory_item_id
                        AND             item.organization_id            = pnd.organization_id                        
                        AND             pnd.transaction_source_id       = :P_BATCH_ID
                        AND             lk.lookup_type                  = 'GMD_FORMULA_ITEM_TYPE'
                        AND             lk.lookup_code                  = 2
                        AND             ol.mmt_transaction_id           = pnd.transaction_id
                        AND             ol.mmt_organization_id          = pnd.organization_id
                        AND             mp.organization_id              = pnd.organization_id
                        --end Bug 25364925
                        UNION ALL
                        SELECT          'Resource' as line_type_dsp, 
                                        rt.resources,
                                     -- fnd_date.displaydt_to_date(rt.trans_date) as transaction_date, -- Bug 16814297 Uskumari
                                        to_char(rt.trans_date, 'dd-mm-yyyy hh24:mi:ss') transaction_date, -- Bug 16814297 Uskumari
                                        rt.resource_usage as trans_qty,
                                        rt.trans_qty_um, 
                                        mp.organization_code, 
                                        rt.poc_trans_id as transaction_id, 
                                        rt.reverse_id, 
                                        rl.layer_id,
					CASE rt.line_type
					  WHEN -1 THEN 0
					  WHEN  0 THEN 1
					  WHEN  2 THEN 2
					  WHEN  1 THEN 3
					END line_type
                        FROM            gme_resource_txns rt, 
                                        gmf_resource_layers rl,
                                        mtl_parameters mp
                        WHERE           rt.doc_type                     = 'PROD'
                        and             rt.completed_ind                = 1
                        AND             rt.doc_id                       = :P_BATCH_ID
                        AND             rl.poc_trans_id                 = rt.poc_trans_id
                        AND             mp.organization_id              = rt.organization_id                        
			ORDER BY        transaction_date, line_type, transaction_id