UnCoasted, Unprocessed , Pending Quantity

---penting Transactions--Material

/* Formatted on 2016/07/19 12:13 (Formatter Plus v4.8.8) */
SELECT   transaction_source_type_name, transaction_header_id,
         transaction_interface_id, transaction_mode_desc, transaction_mode,
         request_id, process_flag_desc, lock_flag_desc, lock_flag, ERROR_CODE,
         error_explanation, organization_code, organization_name,
         transfer_subinventory,
         transfer_organization_code, transfer_organization_name,
         transaction_uom, 
         transaction_date, transfer_cost_group_id,
         transaction_action_name, transaction_type_name, transaction_cost,
         transaction_reference, reason_name, source_code, source_line_id,
         source_header_id, shipment_number,
         employee_code,
         operation_seq_num, department_code, trx_source_line_id,
         trx_source_delivery_id,
         validation_required, negative_req_flag,
         primary_uom_code, transaction_type_id,
         transaction_source_type_id, transaction_source_id,
         transaction_source_name, required_flag, organization_id,
         inventory_item_id,primary_quantity,
         transaction_quantity,
         transaction_action_id, reason_id,
         wip_entity_type,department_id,
         process_flag
    FROM mtl_transactions_interface_v
   WHERE organization_id IN (
            SELECT oav.organization_id
              FROM org_access_view oav
             WHERE oav.responsibility_id = 50369
               AND oav.resp_application_id = 7000)
     AND (process_flag <> 9)
     AND (transaction_date <=
                      TO_DATE ('2016/06/30 23:59:59', 'YYYY/MM/DD HH24:MI:SS')
         )
--     AND (organization_id = 104)
ORDER BY transaction_header_id, transaction_interface_id

---pending shop floor
/* Formatted on 2016/07/19 12:13 (Formatter Plus v4.8.8) */
SELECT   transaction_id, GROUP_ID, process_phase_meaning,
         process_phase, process_status_meaning, process_status, source_code,
         source_line_id, created_by_name, request_id, organization_code,
         organization_id, wip_entity_name, wip_entity_id, entity_type,
         line_code, line_id, primary_item_id,
         fm_operation_seq_num, fm_operation_code, fm_department_code,
         fm_department_id, fm_intraoperation_step_meaning,
         fm_intraoperation_step_type, to_operation_seq_num, to_operation_code,
         to_department_code, to_department_id, to_intraoperation_step_meaning,
         to_intraoperation_step_type, transaction_uom, primary_uom,
         transaction_quantity, primary_quantity,
         overcompletion_transaction_qty, transaction_date, acct_period_id,
         transaction_type, transaction_type_meaning, reason_name, reason_id,
         REFERENCE
    FROM wip_move_txn_interface_v
   WHERE organization_id IN (
            SELECT oav.organization_id
              FROM org_access_view oav
             WHERE oav.responsibility_id = 50369
               AND oav.resp_application_id = 7000)
--     AND (organization_code = '12A')
     AND (transaction_date <=
                      TO_DATE ('2016/06/30 23:59:59', 'YYYY/MM/DD HH24:MI:SS')
         )
ORDER BY process_status DESC, process_phase ASC, transaction_id ASC

--uncoasted Material

/* Formatted on 2016/07/19 12:13 (Formatter Plus v4.8.8) */
SELECT   subinventory_code, revision, transfer_subinventory,
         ship_to_location_id,
         transfer_organization_id,
         owning_organization_id,
         transaction_date, transaction_id,
         transaction_quantity, transaction_uom, primary_quantity,
         secondary_transaction_quantity, secondary_uom_code, shipment_number,
         transaction_reference, costed_flag,
         transaction_group_id,
         source_code, source_line_id,
         transfer_transaction_id,rcv_transaction_id, move_transaction_id,
         completion_transaction_id, operation_seq_num,ERROR_CODE,
         error_explanation, transaction_source_name,
         inventory_item_id,
         organization_id,reason_id,
         transaction_type_id, transaction_action_id,
         transaction_source_type_id, transaction_source_id, employee_code,
         original_transaction_temp_id
    FROM mtl_material_transactions
   WHERE costed_flag IS NOT NULL
--     AND (organization_id = 104)
     AND (transaction_date <=
                      TO_DATE ('30-06-2016 23:59:59', 'DD-MM-YYYY HH24:MI:SS')
         )
     AND (parent_transaction_id IS NULL)
UNION ALL
SELECT   subinventory_code, revision, transfer_subinventory,
         ship_to_location_id,
         transfer_organization_id,
         owning_organization_id, transaction_date, transaction_id,
         transaction_quantity, transaction_uom, primary_quantity,
         secondary_transaction_quantity, secondary_uom_code, shipment_number,
         transaction_reference, costed_flag, transaction_group_id,
         source_code, source_line_id,
         transfer_transaction_id,rcv_transaction_id, move_transaction_id,
         completion_transaction_id, operation_seq_num,ERROR_CODE,
         error_explanation, transaction_source_name,
         inventory_item_id,
         organization_id, reason_id,
         transaction_type_id, transaction_action_id,
         transaction_source_type_id, transaction_source_id, employee_code,
         original_transaction_temp_id
    FROM mtl_material_transactions
   WHERE parent_transaction_id IS NOT NULL
     AND parent_transaction_id IN (
            SELECT parent_transaction_id
              FROM mtl_material_transactions
             WHERE costed_flag IS NOT NULL
--               AND (organization_id = 104)
               AND (transaction_date <=
                       TO_DATE ('30-06-2016 23:59:59',
                                'DD-MM-YYYY HH24:MI:SS')
                   ))
ORDER BY transaction_date DESC, transaction_id DESC, subinventory_code

---pending wip coasting

/* Formatted on 2016/07/19 12:14 (Formatter Plus v4.8.8) */
SELECT    GROUP_ID, process_phase_meaning, process_phase,
         process_status_meaning, process_status, source_code, source_line_id,
         created_by_name, created_by, request_id, organization_code,
         organization_id, wip_entity_name, wip_entity_id, entity_type,
         line_code, line_id, primary_item_id,
         operation_seq_num, department_code, department_id,
         resource_type, usage_rate_or_amount, basis_type, standard_rate_flag,
         employee_num, employee_id, activity_name, activity_id,
         autocharge_type_meaning, autocharge_type, po_number, currency_code,
         actual_resource_rate, functional_currency_flag,
         currency_conversion_date, currency_conversion_type,
         currency_conversion_rate, currency_actual_resource_rate,
         transaction_uom, primary_uom, primary_uom_class,
         transaction_quantity, primary_quantity, transaction_date,
         acct_period_id, transaction_type_meaning, transaction_type,
         reason_name, reason_id, REFERENCE, 
         move_transaction_id, rcv_transaction_id,
         receiving_account_id, po_header_id, po_line_id, 
         program_id,
         transaction_id
    FROM wip_cost_txn_interface_v
   WHERE organization_id IN (
            SELECT oav.organization_id
              FROM org_access_view oav
             WHERE oav.responsibility_id = 50369
               AND oav.resp_application_id = 7000)
--     AND (organization_code = '12A')
     AND (transaction_date <=
                      TO_DATE ('2016/06/30 23:59:59', 'YYYY/MM/DD HH24:MI:SS')
         )
ORDER BY process_status DESC, process_phase ASC, transaction_id ASC

---------------------------------------------------------------------------------------------------------------------------------
---pending receiving

/* Formatted on 2016/07/19 12:14 (Formatter Plus v4.8.8) */
SELECT ROWID, interface_transaction_id, error_message,pe.*
  FROM po_interface_errors pe
 WHERE interface_type NOT IN ('PO_DOCS_OPEN_INTERFACE', 'REQIMPORT')
 and trunc(creation_date)<=trunc(:p_from)
 and table_name='RCV_TRANSACTIONS_INTERFACE'
--   AND interface_line_id = 3467876 

----unprocessed Material

/* Formatted on 2016/07/19 12:14 (Formatter Plus v4.8.8) */
SELECT transaction_temp_id,
       creation_date,request_id,
       program_id,
       transaction_quantity, primary_quantity,ERROR_CODE,
       serial_transaction_temp_id, group_header_id, description,
       origination_date, origination_type, date_code, change_date, status_id,
       retest_date, hold_date,
        reason_id, supplier_lot_number
          FROM mtl_transaction_lots_temp mtlt
 WHERE 1=1 
-- and (transaction_temp_id = 37673967)
 and trunc(creation_date)<=trunc(:p_from)


 -----------------------------------Cost Period

 --Cost Period

/* Formatted on 2016/07/19 12:12 (Formatter Plus v4.8.8) */
SELECT   status, period_name, period_number, period_year, start_date,
         end_date, close_date, rec_type, organization_id, acct_period_id,
         row_id, accounted_period_type, period_set_name, last_update_date,
         creation_date, last_updated_by, created_by, last_update_login
    FROM org_acct_periods_v
   WHERE (   (rec_type = 'ORG_PERIOD' AND organization_id = 104)
          OR (    rec_type = 'GL_PERIOD'
              AND period_set_name = 'Polycab Cal'
              AND accounted_period_type = '21'
              AND (period_year, period_name) NOT IN (
                                               SELECT period_year,
                                                      period_name
                                                 FROM org_acct_periods
                                                WHERE organization_id = 104)
              AND end_date >= TO_DATE ('30-06-2016', 'DD-MM-YYYY')
             )
         )

ORDER BY end_date DESC