{"id":18617,"date":"2019-07-12T13:39:50","date_gmt":"2019-07-12T16:39:50","guid":{"rendered":"https:\/\/orabr.com\/?p=18617"},"modified":"2019-07-12T14:31:56","modified_gmt":"2019-07-12T17:31:56","slug":"r12-query-para-encontrar-detalhesda-po-projeto-pedidos-recebimentos-ordem-de-movimento-consumo","status":"publish","type":"post","link":"https:\/\/orabr.virttus.com\/?p=18617","title":{"rendered":"R12 – QUERY para encontrar detalhes da PO, projeto, pedidos \/ recebimentos \/ ordem de movimento \/ consumo"},"content":{"rendered":"\n
\n\n\n\n\nSELECT (SELECT DISTINCT organization_name\n          FROM org_organization_definitions\n         WHERE organization_id = pa.carrying_out_organization_id) org_name\n      ,(SELECT DISTINCT organization_code\n          FROM org_organization_definitions\n         WHERE organization_id = pa.carrying_out_organization_id) org_code\n      ,pa.project_id\n      ,pa.org_id\n      ,pa.NAME\n      ,pa.long_name\n      ,pa.segment1\n      ,pa.project_status_code\n      ,pab.burdened_cost budget_cost\n      ,pa.created_by\n      ,pa.project_type\n      ,pa.start_date\n      ,pa.completion_date\n      ,pa.closed_date\n      ,pa.carrying_out_organization_id\n      ,NVL((SELECT DISTINCT ppf.first_name || ' ' || ppf.last_name\n             FROM pa_project_players p\n                 ,per_all_people_f   ppf\n            WHERE ppf.person_id = p.person_id\n              AND project_role_type = 'PROJECT MANAGER'\n              AND p.project_id = pa.project_id), 'Not Defined') key_member\n      ,pt.task_id\n      ,pt.task_number\n      ,pt.attribute1 task_status\n      ,pt.task_name\n      ,pt.description\n      ,pab.budget_version_id\n      ,trunc(pab.cur_base_date) approval_date\n      ,(SELECT SUM(burdened_cost)\n          FROM pafv_budget_lines\n         WHERE task_id = pt.task_id\n           AND budget_version_id = pab.budget_version_id\n         GROUP BY task_id) task_budget\n      ,pt.top_task_id\n      ,pt.wbs_level\n      ,pt.start_date\n      ,pt.completion_date\n      ,pod.DESTINATION_TYPE_CODE\n      ,\n       -- pod.PO_DISTRIBUTION_ID,\n       (SELECT item_id\n          FROM po_lines_all\n         WHERE po_line_id = pod.po_line_id) item\n      ,(SELECT description\n          FROM mtl_system_items_kfv k\n         WHERE inventory_item_id =\n               (SELECT item_id\n                  FROM po_lines_all\n                 WHERE po_line_id = pod.po_line_id)\n           AND organization_id = pa.carrying_out_organization_id) item_desc\n      ,(SELECT concatenated_segments\n          FROM mtl_system_items_kfv k\n         WHERE inventory_item_id =\n               (SELECT item_id\n                  FROM po_lines_all\n                 WHERE po_line_id = pod.po_line_id)\n           AND organization_id = pa.carrying_out_organization_id) item_code\n      ,(SELECT pha.segment1\n          FROM po_headers_all pha\n         WHERE pha.po_header_id = pod.po_header_id) po_num\n      ,(SELECT pv.vendor_name\n          FROM po_headers_all pha\n              ,po_vendors     pv\n         WHERE pv.vendor_id = pha.vendor_id\n           AND pha.po_header_id = pod.po_header_id) vendor_name\n      ,(SELECT pha.currency_code\n          FROM po_headers_all pha\n         WHERE pha.po_header_id = pod.po_header_id) Currency_Code\n      ,(SELECT trunc(pha.creation_date)\n          FROM po_headers_all pha\n         WHERE pha.po_header_id = pod.po_header_id) po_date\n      ,(SELECT quantity\n          FROM po_lines_all\n         WHERE po_line_id = pod.po_line_id) PO_QTY\n      ,(SELECT unit_price\n          FROM po_lines_all\n         WHERE po_line_id = pod.po_line_id) PO_unit_price\n      ,(SELECT (pl.unit_price * pl.quantity * nvl(ph.rate, 1))\n          FROM po_lines_all   pl\n              ,po_headers_all ph\n         WHERE ph.po_header_id = pl.po_header_id\n           AND pl.po_line_id = pod.po_line_id) PO_commit_cost\n      ,(SELECT DISTINCT receipt_num\n          FROM rcv_shipment_headers rsh\n              ,rcv_transactions     rcv\n              ,po_lines_all         pla\n         WHERE rsh.shipment_header_id = rcv.shipment_header_id\n           AND rcv.po_line_id = pla.po_line_id\n           AND PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID\n           AND transaction_id = rcv1.transaction_id) rec_num\n      ,(SELECT DISTINCT trunc(transaction_date)\n          FROM rcv_transactions rcv\n         WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID\n           AND transaction_type = 'DELIVER'\n           AND rownum = 1) Rec_Date\n      ,(SELECT SUM((decode(transaction_type, 'DELIVER', quantity,\n                           'RETURN TO VENDOR', (quantity * (-1)))))\n          FROM rcv_transactions rcv\n         WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID\n           AND shipment_line_id = rcv1.shipment_line_id\n           AND transaction_type IN ('DELIVER', 'RETURN TO VENDOR')) rec_qty\n      ,(SELECT DISTINCT po_unit_price\n          FROM rcv_transactions rcv\n         WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID\n           AND transaction_type = 'DELIVER') Rec_Price\n      ,((SELECT SUM((decode(transaction_type, 'DELIVER', quantity,\n                            'RETURN TO VENDOR', (quantity * (-1)))))\n           FROM rcv_transactions rcv\n          WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID\n            AND shipment_line_id = rcv1.shipment_line_id\n            AND transaction_type IN ('DELIVER', 'RETURN TO VENDOR')) *\n       (SELECT DISTINCT po_unit_price\n           FROM rcv_transactions rcv\n          WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID\n            AND transaction_type = 'DELIVER')) Rec_Val\n      ,NULL transfered_project_from_other\n      ,NULL transfered_qty_from_other\n      ,NULL transfered_date_from_other\n      ,NULL transfered_cost_from_other\n      ,NULL transfer_project_to_other\n      ,NULL transfer_qty_to_other\n      ,NULL transfer_cost_to_other\n      ,NULL transfer_date_to_other\n      ,NULL issued_qty\n      ,NULL issued_date\n      ,NULL issued_cost\n      ,NULL issued_ref_no\n      ,DECODE(pod.DESTINATION_TYPE_CODE, 'EXPENSE',\n              ((SELECT SUM((decode(transaction_type, 'DELIVER', quantity,\n                                    'RETURN TO VENDOR', (quantity * (-1)))))\n                   FROM rcv_transactions rcv\n                  WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID\n                    AND transaction_type IN ('DELIVER', 'RETURN TO VENDOR')) *\n               (SELECT DISTINCT po_unit_price\n                   FROM rcv_transactions rcv\n                  WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID\n                    AND transaction_type = 'DELIVER'))) expense_loaded_to_project\n      ,NULL to_project_id\n      ,NULL transaction_quantity\n      ,NULL transaction_type\n      ,NULL subinventory_code\n      ,NULL locator_id\n      ,NULL location\n      ,(SELECT DISTINCT uom_code\n          FROM mtl_units_of_measure_tl uo\n              ,po_lines_all            pl\n         WHERE pl.UNIT_MEAS_LOOKUP_CODE = uo.unit_of_measure\n           AND po_line_id = pod.po_line_id) transaction_uom\n      ,(SELECT pha.creation_date\n          FROM po_headers_all pha\n         WHERE pha.po_header_id = pod.po_header_id) trans_date\n      ,NULL per_unit_cost\n  FROM pa_projects_all            pa\n      ,pa_tasks                   pt\n      ,pa_budget_versions_draft_v pab\n      ,pa_budget_types            ty\n      ,po_distributions_all       pod\n      ,rcv_transactions           rcv1\n WHERE pa.project_id = pt.project_id\n   AND pa.project_id = pab.project_id\n   AND ty.budget_type_code = pab.budget_type_code\n   AND pod.task_id(+) = pt.task_id\n   AND pod.task_id = rcv1.task_id(+)\n   AND pod.po_distribution_id = rcv1.po_distribution_id(+)\n   AND pa.org_id = :p_org_id\n   AND pa.segment1 = :p_from_segment\n\/*  AND pa.project_status_code = NVL (:p_status, pa.project_status_code)\nAND pa.segment1 BETWEEN NVL (:p_from_segment, pa.segment1)\n                    AND NVL (:p_to_segment, pa.segment1)\nAND TRUNC (pa.start_date) BETWEEN NVL\n                                     (TRUNC (TO_DATE (:p_from_start_date,\n                                                      'DD-MON-RRRR'\n                                                     )\n                                            ),\n                                      TRUNC (pa.start_date)\n                                     )\n                              AND NVL (TRUNC (TO_DATE (:p_to_start_date,\n                                                       'DD-MON-RRRR'\n                                                      )\n                                             ),\n                                       TRUNC (pa.start_date)\n                                      )\nAND pab.burdened_cost > = NVL (:p_cost, pab.burdened_cost)     *\/\nUNION\nSELECT (SELECT DISTINCT organization_name\n          FROM org_organization_definitions\n         WHERE organization_id = pa.carrying_out_organization_id) org_name\n      ,(SELECT DISTINCT organization_code\n          FROM org_organization_definitions\n         WHERE organization_id = pa.carrying_out_organization_id) org_code\n      ,pa.project_id\n      ,pa.org_id\n      ,pa.NAME\n      ,pa.long_name\n      ,pa.segment1\n      ,pa.project_status_code\n      ,pab.burdened_cost budget_cost\n      ,pa.created_by\n      ,pa.project_type\n      ,pa.start_date\n      ,pa.completion_date\n      ,pa.closed_date\n      ,pa.carrying_out_organization_id\n      ,NVL((SELECT DISTINCT ppf.first_name || ' ' || ppf.last_name\n             FROM pa_project_players p\n                 ,per_all_people_f   ppf\n            WHERE ppf.person_id = p.person_id\n              AND project_role_type = 'PROJECT MANAGER'\n              AND p.project_id = pa.project_id), 'Not Defined') key_member\n      ,pt.task_id\n      ,pt.task_number\n      ,pt.attribute1 task_status\n      ,pt.task_name\n      ,pt.description\n      ,pab.budget_version_id\n      ,trunc(pab.cur_base_date) approval_date\n      ,(SELECT SUM(burdened_cost)\n          FROM pafv_budget_lines\n         WHERE task_id = pt.task_id\n           AND budget_version_id = pab.budget_version_id\n         GROUP BY task_id) task_budget\n      ,pt.top_task_id\n      ,pt.wbs_level\n      ,pt.start_date\n      ,pt.completion_date\n      ,NULL destination_type_code\n      ,mmt.inventory_item_id item\n      ,(SELECT description\n          FROM mtl_system_items_kfv k\n         WHERE inventory_item_id = mmt.inventory_item_id\n           AND organization_id = pa.carrying_out_organization_id) item_desc\n      ,(SELECT concatenated_segments\n          FROM mtl_system_items_kfv k\n         WHERE inventory_item_id = mmt.inventory_item_id\n           AND organization_id = pa.carrying_out_organization_id) item_code\n      ,NULL po_num\n      ,NULL vendor_name\n      ,NULL Currency_Code\n      ,NULL po_date\n      ,NULL po_qty\n      ,NULL po_unit_price\n      ,NULL po_commit_cost\n      ,NULL rec_num\n      ,NULL rec_date\n      ,NULL rec_qty\n      ,NULL rec_price\n      ,NULL rec_val\n      ,CASE\n         WHEN mmt.transaction_quantity > 0 THEN\n          decode(mmt.transaction_type_id, 66,\n                 (SELECT DISTINCT segment1\n                     FROM pa_projects_all\n                    WHERE project_id = mmt.to_project_id), 67,\n                 (SELECT DISTINCT segment1\n                     FROM pa_projects_all\n                    WHERE project_id = mmt.to_project_id), 68,\n                 (SELECT DISTINCT segment1\n                     FROM pa_projects_all\n                    WHERE project_id = mmt.to_project_id))\n       END AS transfered_project_from_other\n      ,CASE\n         WHEN mmt.transaction_quantity > 0 THEN\n          DECODE(mmt.transaction_type_id, 66, mmt.transaction_quantity, 67,\n                 mmt.transaction_quantity, 68, mmt.transaction_quantity)\n       END AS transfered_QTY_from_other\n      ,CASE\n         WHEN mmt.transaction_quantity > 0 THEN\n          DECODE(mmt.transaction_type_id, 66, TRUNC(mmt.transaction_date), 67,\n                 TRUNC(mmt.transaction_date), 68, TRUNC(mmt.transaction_date))\n       END AS transfered_DATE_from_other\n      ,CASE\n         WHEN mmt.transaction_quantity > 0 THEN\n          DECODE(mmt.transaction_type_id, 66,\n                 (mmt.transaction_quantity * mmt.actual_cost), 67,\n                 (mmt.transaction_quantity * mmt.actual_cost), 68,\n                 (mmt.transaction_quantity * mmt.actual_cost))\n       END AS transfered_cost_from_other\n      ,CASE\n         WHEN mmt.transaction_quantity < 0 THEN\n          decode(mmt.transaction_type_id, 66,\n                 (SELECT DISTINCT segment1\n                     FROM pa_projects_all\n                    WHERE project_id = mmt.to_project_id), 67,\n                 (SELECT DISTINCT segment1\n                     FROM pa_projects_all\n                    WHERE project_id = mmt.to_project_id), 68,\n                 (SELECT DISTINCT segment1\n                     FROM pa_projects_all\n                    WHERE project_id = mmt.to_project_id))\n       END AS transfer_project_to_other\n      ,CASE\n         WHEN mmt.transaction_quantity < 0 THEN\n          DECODE(mmt.transaction_type_id, 66, mmt.transaction_quantity, 67,\n                 mmt.transaction_quantity, 68, mmt.transaction_quantity)\n       END AS transfer_qty_to_other\n      ,CASE\n         WHEN mmt.transaction_quantity < 0 THEN\n          DECODE(mmt.transaction_type_id, 66,\n                 (mmt.transaction_quantity * mmt.actual_cost), 67,\n                 (mmt.transaction_quantity * mmt.actual_cost), 68,\n                 (mmt.transaction_quantity * mmt.actual_cost))\n       END AS transfer_cost_to_other\n      ,CASE\n         WHEN mmt.transaction_quantity < 0 THEN\n          DECODE(mmt.transaction_type_id, 66, TRUNC(mmt.transaction_date), 67,\n                 TRUNC(mmt.transaction_date), 68, TRUNC(mmt.transaction_date))\n       END AS transfer_DATE_to_other\n      ,DECODE(mmt.transaction_type_id, 120, mmt.transaction_quantity, 35,\n              mmt.transaction_quantity, 63, mmt.transaction_quantity) issued_qty\n      ,DECODE(mmt.transaction_type_id, 120, TRUNC(mmt.transaction_date), 35,\n              TRUNC(mmt.transaction_date), 63, TRUNC(mmt.transaction_date)) issued_date\n      ,DECODE(mmt.transaction_type_id, 120,\n              (mmt.transaction_quantity * mmt.actual_cost * (-1)), 35,\n              (mmt.transaction_quantity * mmt.actual_cost * (-1)), 63,\n              (mmt.transaction_quantity * mmt.actual_cost * (-1))) issued_cost\n      ,DECODE(mmt.transaction_type_id, 120,\n              (SELECT DISTINCT request_number\n                  FROM mtl_txn_request_headers\n                 WHERE header_id = mmt.transaction_source_id), 63,\n              (SELECT DISTINCT request_number\n                  FROM mtl_txn_request_headers\n                 WHERE header_id = mmt.transaction_source_id), 35,\n              (SELECT DISTINCT wip_entity_name\n                  FROM wip_entities\n                 WHERE wip_entity_id = mmt.transaction_source_id)) Issued_Ref_No\n      ,NULL expense_loaded_to_project\n      ,mmt.to_project_id\n      ,mmt.transaction_quantity\n      ,(SELECT description\n          FROM mtl_transaction_types\n         WHERE transaction_type_id = mmt.transaction_type_id\n           AND mmt.transaction_action_id = transaction_action_id\n           AND transaction_source_type_id = mmt.transaction_source_type_id) transaction_type\n      ,mmt.subinventory_code\n      ,mmt.locator_id\n      ,(SELECT segment1 || '.' || segment2 || '.' || segment3\n          FROM mtl_item_locations\n         WHERE inventory_location_id = mmt.locator_id\n           AND organization_id = pa.carrying_out_organization_id) LOCATION\n      ,mmt.transaction_uom\n      ,mmt.transaction_date trans_date\n      ,ROUND(NVL(mmt.transaction_cost, mmt.actual_cost), 2) per_unit_COST\n  FROM mtl_material_transactions  mmt\n      ,pa_projects_all            pa\n      ,pa_tasks                   pt\n      ,po_lines_all               pll\n      ,pa_budget_versions_draft_v pab\n      ,mtl_txn_request_lines      mtxn\n      ,pa_budget_types            ty\n WHERE mmt.transaction_set_id = mtxn.transaction_header_id(+)\n   AND pa.project_id = mmt.project_id(+)\n   AND pt.project_id = pa.project_id\n   AND pa.project_id = pab.project_id\n   AND ty.budget_type_code = pab.budget_type_code\n   AND pt.task_id = mmt.task_id\n   AND mmt.transaction_source_id = pll.po_header_id(+)\n   AND ty.plan_type = 'BUDGET'\n   AND mmt.inventory_item_id = pll.item_id(+)\n   AND mmt.transaction_type_id NOT IN (18, 36)\n   AND pa.org_id = :p_org_id\n   AND pa.segment1 = :p_from_segment\n\/*   AND pa.project_status_code = NVL (:p_status, pa.project_status_code)\nAND pa.segment1 BETWEEN NVL (:p_from_segment, pa.segment1)\n                     AND NVL (:p_to_segment, pa.segment1)\n AND TRUNC (pa.start_date) BETWEEN NVL\n                                      (TRUNC (TO_DATE (:p_from_start_date,\n                                                       'DD-MON-RRRR'\n                                                      )\n                                             ),\n                                       TRUNC (pa.start_date)\n                                      )\n                               AND NVL (TRUNC (TO_DATE (:p_to_start_date,\n                                                        'DD-MON-RRRR'\n                                                       )\n                                              ),\n                                        TRUNC (pa.start_date)\n                                       )\n AND pab.burdened_cost > = NVL (:p_cost, pab.burdened_cost)*\/\n ORDER BY item\n         ,trans_date\n<\/pre>\n\u00a0<\/p>\n<\/HTML><\/HTML>","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29],"tags":[],"class_list":["post-18617","post","type-post","status-publish","format-standard","hentry","category-base-de-conhecimentos"],"_links":{"self":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/posts\/18617","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=18617"}],"version-history":[{"count":5,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/posts\/18617\/revisions"}],"predecessor-version":[{"id":18623,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/posts\/18617\/revisions\/18623"}],"wp:attachment":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=18617"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=18617"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=18617"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}