8 Replies Latest reply on Jun 29, 2010 9:57 AM by makdutakdu

    to include MTL_MATERIAL_TRANSACTIONS

    makdutakdu
      hi

      i have this query where i ve calculated the number of days from pr approve to po submit
      and po submit to po approve
      SELECT   porh.segment1, porh.description, porl.line_num,
               popr.action_date pr_action_dates, popr.action_code pr_action,
               poha.segment1 po_num, prall.full_name pr_ppl,
               porh.authorization_status pr_auth_status,
               porh.approved_date pr_approved_date,
               CASE
                  WHEN porh.authorization_status = 'APPROVED'
                  AND poph.action_code = 'SUBMIT'
                     THEN poph.action_date - porh.approved_date
               END days_pr,
               poph.action_code po_action, poph.action_date po_action_dates,
               poall.full_name po_ppl,
               CASE
                  WHEN poha.authorization_status = 'APPROVED'
                  AND poph.action_code = 'SUBMIT'
                     THEN ROUND (poha.approved_date - poph.action_date)
               END days_po,
               poha.authorization_status po_auth_status,
               poha.approved_date po_approved_date
          FROM po_requisition_headers_all porh,
               po_requisition_lines_all porl,
               po_req_distributions_all prda,
               po_distributions_all poda,
               po_headers_all poha,
               po_action_history popr,
               po_action_history poph,
               hr.per_all_people_f poall,
               hr.per_all_people_f prall,
               hr.per_all_assignments_f asgpo,
               hr.per_all_assignments_f asgpr,
               per_grades_vl grdpo,
               per_grades_vl grdpr
         WHERE poph.employee_id = poall.person_id
           AND popr.employee_id = prall.person_id
           AND poall.person_id = asgpo.person_id
           AND prall.person_id = asgpr.person_id
           AND asgpr.grade_id = grdpr.grade_id(+)
           AND asgpo.grade_id = grdpo.grade_id(+)
           AND prda.distribution_id = poda.req_distribution_id
           AND poha.po_header_id = poda.po_header_id
           AND prda.requisition_line_id = porl.requisition_line_id
           AND porh.requisition_header_id = porl.requisition_header_id
           AND porh.requisition_header_id = popr.object_id
           AND poph.object_type_code = 'PO'
           AND poha.po_header_id = poph.object_id
           AND SYSDATE BETWEEN poall.effective_start_date AND poall.effective_end_date
           AND SYSDATE BETWEEN prall.effective_start_date AND prall.effective_end_date
           AND SYSDATE BETWEEN asgpo.effective_start_date AND asgpo.effective_end_date
           AND SYSDATE BETWEEN asgpr.effective_start_date AND asgpr.effective_end_date
           AND porh.segment1 = '4078'
      ORDER BY 4, 10, 14
      i wanted to calculated the days from pr approve to transaction_date and po approve to transaction_date

      transaction date from MTL_MATERIAL_TRANSACTIONS
      how do i include this MTL_MATERIAL_TRANSACTIONS  into my query ,with which column should i join

      kindly help

      Edited by: makdutakdu on Jun 27, 2010 12:12 PM

      Edited by: makdutakdu on Jun 27, 2010 12:36 PM

      Edited by: makdutakdu on Jun 28, 2010 7:16 AM
        • 1. Re: to include MTL_MATERIAL_TRANSACTIONS
          makdutakdu
          hi

          could someone please guide me in joining mtl_material_transactions to the any of the po tables in the query (eg:po_requisition_headers_all porh)?


          kindly guide me

          thanking in advance
          • 2. Re: to include MTL_MATERIAL_TRANSACTIONS
            makdutakdu
            hi


            i need to obtain the transaction_date from mtl_material_transactions to calculate the transaction date of each material correspomding to each PO

            kindly help
            • 3. Re: to include MTL_MATERIAL_TRANSACTIONS
              rioman
              You can join RCV_TRANSACTIONS with PO_DISTRIBUTIONS_ALL thru PO_DISTRIBUTION_ID. There is a transaction date in that table, so maybe you don't need to join to MTL_MATERIAL_TRANSACTIONS. If you do, you can yoin MTL_MATERIAL_TRANSACTIONS.RCV_TRANSACTION_ID with RCV_TRANSACTIONS.TRANSACTION_ID. Use grouping function to avoid multiple rows for each PO distribution.

              Hope it helps.
              • 4. Re: to include MTL_MATERIAL_TRANSACTIONS
                makdutakdu
                hi

                thanks a lot
                do i have to join this rcv_transaction data with po_requisition_headers_all porh,po_headers_all poha,po_action_history poph

                i mean to calculate pr approveddate-rcv transaction date=pr_tran_Days and po approved date - rcv transaction date=po_tran_days


                kindly help
                thanks
                • 5. Re: to include MTL_MATERIAL_TRANSACTIONS
                  rioman
                  You could join it or you could create a subquery in the select side, using something like
                  SELECT   porh.segment1, porh.description, porl.line_num,
                  ...
                    , (SELECT MAX(transaction_date)
                       FROM rcv_transactions
                       WHERE po_distribution_id = poda.po_distribution_id
                      )
                      - pha.approved_date
                  ...
                     FROM po_requisition_headers_all porh,
                  ...
                  <your query>
                  ...
                   ORDER BY 4, 10, 14
                  • 6. Re: to include MTL_MATERIAL_TRANSACTIONS
                    makdutakdu
                    hi

                    thanks its done
                    thanks a lot
                    i just wanted to know as to why shud we do max(transaction_date)
                    cant we just do transaction date -approved date


                    i did
                    CASE
                                WHEN poha.authorization_status = 'APPROVED'
                                   THEN ROUND (rcv.transaction_date - poha.approved_date
                                              )
                             END days_po_trans, ---days from po_approved  to transaction date
                             CASE
                                WHEN porh.authorization_status = 'APPROVED'
                                   THEN rcv.transaction_date - porh.approved_date
                             END days_pr_trans,------days from pr _approved to transaction
                    thanks again
                    • 7. Re: to include MTL_MATERIAL_TRANSACTIONS
                      rioman
                      I wanted to avoid the subquery returning multiple records. In fact, I should have included at least an extra condition because the receipts are done in several steps. So, it would be better with something like this:
                      AND rcv.transaction_type = 'DELIVER'
                      But even so, you could still end up with multiple receipts (and multiple receipt dates) for the same PO. In that case, you have to decide (well, it's more a business call) how to deal with them. In my case, I was using the latest of the receiving dates (MAX).

                      Regards.
                      • 8. Re: to include MTL_MATERIAL_TRANSACTIONS
                        makdutakdu
                        hi

                        wht do the different transaction types in rcv_transactions mean

                        is it compulsory to include the status as deliver in the query ,i would like to know

                        this is my query
                        SELECT   porh.segment1 pr_num, porh.description, porl.line_num,rcv.transaction_type,
                                 poda.req_distribution_id, popr.action_date pr_action_dates,
                                 popr.action_code pr_action, poha.segment1 po_num,
                                 prall.full_name pr_ppl, porh.authorization_status pr_auth_status,
                                 porh.approved_date pr_approved_date,
                                 CASE
                                    WHEN porh.authorization_status = 'APPROVED'
                                    AND poph.action_code = 'SUBMIT'
                                       THEN ROUND (poph.action_date - porh.approved_date)
                                 END days_pr,
                                 poph.action_code po_action, poph.action_date po_action_dates,
                                 poall.full_name po_ppl,
                                 CASE
                                    WHEN poha.authorization_status = 'APPROVED'
                                    AND poph.action_code = 'SUBMIT'
                                       THEN ROUND (poha.approved_date - poph.action_date)
                                 END days_po,
                                 rcv.transaction_date,
                                 CASE
                                    WHEN poha.authorization_status = 'APPROVED'
                                       THEN ROUND (rcv.transaction_date - poha.approved_date
                                                  )
                                 END days_po_trans,
                                 CASE
                                    WHEN porh.authorization_status = 'APPROVED'
                                       THEN ROUND (rcv.transaction_date - porh.approved_date
                                                  )
                                 END days_pr_trans,
                                 poha.authorization_status po_auth_status,
                                 poha.approved_date po_approved_date
                            FROM po_requisition_headers_all porh,
                                 po_requisition_lines_all porl,
                                 po_req_distributions_all prda,
                                 po_distributions_all poda,
                                 po_headers_all poha,
                                 po_action_history popr,
                                 po_action_history poph,
                                 hr.per_all_people_f poall,
                                 hr.per_all_people_f prall,
                                 hr.per_all_assignments_f asgpo,
                                 hr.per_all_assignments_f asgpr,
                                 per_grades_vl grdpo,
                                 per_grades_vl grdpr,
                                 rcv_transactions rcv
                           WHERE poph.employee_id = poall.person_id
                             AND popr.employee_id = prall.person_id
                             AND poall.person_id = asgpo.person_id
                             AND prall.person_id = asgpr.person_id
                             AND asgpr.grade_id = grdpr.grade_id(+)
                             AND asgpo.grade_id = grdpo.grade_id(+)
                             AND prda.distribution_id = poda.req_distribution_id
                             AND rcv.po_distribution_id = poda.po_distribution_id
                             AND poha.po_header_id = poda.po_header_id
                             AND prda.requisition_line_id = porl.requisition_line_id
                             AND porh.requisition_header_id = porl.requisition_header_id
                             AND porh.requisition_header_id = popr.object_id
                             AND poph.object_type_code = 'PO'
                             AND poha.po_header_id = poph.object_id
                             AND SYSDATE BETWEEN poall.effective_start_date AND poall.effective_end_date
                             AND SYSDATE BETWEEN prall.effective_start_date AND prall.effective_end_date
                             AND SYSDATE BETWEEN asgpo.effective_start_date AND asgpo.effective_end_date
                           
                             AND SYSDATE BETWEEN asgpr.effective_start_date AND asgpr.effective_end_date
                              
                             
                        ORDER BY 4, 10, 14
                        thanks a lot

                        regards
                        lincy