Thursday, October 18, 2012

PO Queries



set pagesize 5000
set linesize 5000
spool C:\Script\OUTPUT.TXT
set colsep "|"
select * from dual ;
set colsep " "
spool off
set pagesize 50
set linesize 200


/* Formatted on 2011/02/18 10:05 (Formatter Plus v4.8.8) */
SELECT   d.table_name "Table name", d.constraint_name "Constraint name",
         DECODE (d.constraint_type,
                 'P', 'Primary Key',
                 'R', 'Foreign Key',
                 'C', 'Check/Not Null',
                 'U', 'Unique',
                 'V', 'View Cons'
                ) "Type",
         d.search_condition "Check Condition", p.table_name "Ref Table name",
         p.constraint_name "Ref by", m.column_name "Ref col",
         m.POSITION "Position", p.owner "Ref owner"
    FROM dba_constraints d LEFT JOIN dba_constraints p
         ON (d.r_owner = p.owner AND d.r_constraint_name = p.constraint_name)
         LEFT JOIN dba_cons_columns m ON (d.constraint_name =
                                                             m.constraint_name
                                         )
   WHERE d.table_name IN (
                        SELECT table_name
                          FROM dba_tables
                         WHERE owner = UPPER ('mkm')
                        UNION ALL
                        SELECT view_name
                          FROM dba_views
                         WHERE owner = UPPER ('mkm'))
ORDER BY 1, 2
===============================================================================

SELECT   ROWNUM "SL NO",
                prha.segment1 "PR NO",
                prha.creation_date "PR CREATION DATE",
               (prla.quantity*prla.unit_price) "PR VALUE",
               msib.inventory_item_id "MATERIAL CODE",
               msib.description "MATERIAL DESCRIPTION",
               prla.quantity "QTY",
               prla.unit_meas_lookup_code "UOM",
               prha.authorization_status "PR APPROVAL STATUS",
               pha.segment1 "PO NO",
               pha.creation_date "PO DATE",
               (pla.quantity*pla.unit_price) "PO VALUE"
FROM     po_requisition_headers_all prha,
              po_requisition_lines_all prla,
              mtl_system_items_b msib,
              po_headers_all pha,
              po_req_distributions_all prda,
              po_distributions_all pda,
              po_lines_all pla,
              hr_operating_units hou
WHERE prha.requisition_header_id=prla.requisition_header_id
AND       prha.org_id=msib.organization_id
AND       msib.inventory_item_id=prla.item_id
AND       prla.requisition_line_id=prda.requisition_line_id
AND       prda.distribution_id=pda.req_distribution_id
AND       pda.po_line_id=pla.po_line_id
AND       pla.po_header_id=pha.po_header_id
AND       hou.name=:Operating_Unit
AND       TRUNC(prha.creation_date)  BETWEEN NVL(:From_Creation_Date,to_date prha.creation_date,'DD-MON-RR'))
                                   AND NVL(:To_Creation_Date,to_date(prha.creation_date,'DD-MON-RR'))
AND       msib.inventory_item_id BETWEEN NVL(:From_Material_Code,msib.inventory_item_id)
                                   AND NVL(:To_Material_Code,msib.inventory_item_id)
 
--> List of PRs Converted to PO

select  ROWNUM "SL NO",
           prha.segment1 "PR NO",
           prha.creation_date "PR CREATION DATE",
           prha.approved_date "PR APPROVAL DATE",
           msib.inventory_item_id "MATERIAL CODE",
           msib.description "MATERIAL DESCRIPTION",
           prla.quantity "QTY",
           prla.unit_meas_lookup_code "UOM",
           pha.segment1 "PO NO",
           pha.creation_date "PO_CREATION_DATE",
           pha.approved_date "PO APPROVAL DATE",
           aps.vendor_name "SUPPLIER_NAME",
           papf.full_name "BUYER NAME",
          (prla.quantity*prla.unit_price) "PR VALUE",
          (pla.quantity*pla.unit_price) "PO VALUE"     
FROM  po_requisition_headers_all prha,
            po_requisition_lines_all prla,
            po_req_distributions_all prda,
            po_distributions_all pda,
            mtl_system_items_b msib,
            po_headers_all pha,
            ap_suppliers aps,
            per_all_people_f papf,
            po_lines_all pla,
            hr_operating_units hou 
WHERE  prha.requisition_header_id=prla.requisition_header_id
AND        prla.requisition_line_id=prda.requisition_line_id
AND        prda.distribution_id=pda.req_distribution_id
AND        pda.po_header_id=pha.po_header_id
AND        prla.item_id= msib.inventory_item_id
AND        msib.organization_id=prha.org_id
AND        pha.vendor_id=aps.vendor_id
AND        pha.agent_id=papf.person_id
AND        pha.po_header_id=pla.po_header_id
AND        hou.name=:Operating_Unit
AND        TRUNC(prha.creation_date) BETWEEN NVL(:From_Creation_Date,to_date
              (prha.creation_date,'DD-MON-RR')) AND NVL(:To_Creation_Date,to_date
              (prha.creation_date,'DD-MON-RR'))
AND       msib.inventory_item_id BETWEEN NVL (:From_Material_Code,msib.inventory_item_id)
             AND NVL (:To_Material_code, msib.inventory_item_id)     
AND   aps.vendor_name=:Vendor
AND   papf.full_name=:Buyer
 
--> List of Rejected PR''s

select   ROWNUM "SL NO",
           prha.segment1 "PR NO",
           prha.creation_date "PR CREATION DATE",
           prha.approved_date "PR APPROVAL DATE",
           msib.inventory_item_id "MATERIAL CODE",
           msib.description "MATERIAL DESCRIPTION",
           prla.quantity "QTY",
           prla.unit_meas_lookup_code "UOM" ,
           prla.unit_price "UNIT_VALUE",
           (prla.quantity*prla.unit_price) "TOTAL VALUE",
           plla.need_by_date,
           papf.full_name "REQUESTOR",
           pha.vendor_site_id "REQUESTOR AT SITE"     
FROM po_requisition_headers_all prha,
           po_requisition_lines_all prla,
           po_req_distributions_all prda,
           po_distributions_all pda,
           mtl_system_items_b msib,
           po_headers_all pha,
           po_line_locations_all plla,
           per_all_people_f papf,
           hr_operating_units hou
WHERE prha.requisition_header_id=prla.requisition_header_id
AND     prla.requisition_line_id=prda.requisition_line_id
AND     prda.distribution_id=pda.req_distribution_id
AND     pda.po_header_id=pha.po_header_id
AND     prla.item_id=msib.inventory_item_id
AND     msib.organization_id=prha.org_id
AND     pha.po_header_id=plla.po_header_id
AND     pha.agent_id=papf.person_id
AND     hou.name=:Operating_Unit
AND    TRUNC(prha.creation_date) BETWEEN NVL(:From_Creation_Date,to_date
           (prha.creation_date,'DD-MON-RR')) AND NVL(:To_Creation_Date,to_date
           (prha.creation_date,'DD-MON-RR'))
AND     msib.inventory_item_id BETWEEN NVL (:From_Material_Code,msib.inventory_item_id)
            AND NVL (:To_Material_code, msib.inventory_item_id)
AND   papf.full_name=:Requestor                                    
                                 
 
--> PR''s Converted to RFQ

select  ROWNUM "SL NO",
           prha.segment1 "PR NO",
           prha.creation_date "PR CREATION DATE",
           prha.approved_date "PR APPROVAL DATE",
           msib.inventory_item_id "MATERIAL CODE",
           msib.description "MATERIAL DESCRIPTION",
           prla.quantity "QTY",
           prla.unit_meas_lookup_code "UOM",
           pha.segment1 "RFQ NO",
           pha.creation_date "RFQ CREATION DATE"    
FROM po_requisition_headers_all prha,
           po_requisition_lines_all prla,
           po_req_distributions_all prda,
           po_distributions_all pda,
           mtl_system_items_b msib,
           po_headers_all pha,
           hr_operating_units hou
WHERE prha.requisition_header_id=prla.requisition_header_id
AND     prla.requisition_line_id=prda.requisition_line_id
AND     prda.distribution_id=pda.req_distribution_id
AND     pda.po_header_id=pha.po_header_id
AND      prla.item_id= msib.inventory_item_id
AND      msib.organization_id=prha.org_id
AND      hou.organization_id(+)=pha.org_id
AND      hou.name=:Operating_Unit
AND      TRUNC(prha.creation_date) BETWEEN NVL(:From_Creation_Date,to_date
            (prha.creation_date,'DD-MON-RR')) AND NVL(:To_Creation_Date,to_date
            (prha.creation_date,'DD-MON-RR'))
AND      msib.inventory_item_id BETWEEN NVL (:From_Material_Code,
             msib.inventory_item_id) AND NVL (:To_Material_code, msib.inventory_item_id)
            
                                       

 
--> Oracle Apps HRMS Interview Questions

1. Important Tables in HRMS
Per_all_people_F,
per_person_types,
per_person_type_usages,
per_addresses,
per_contact_Relationships,
per_periods_of_service
Per_all_assignments_f,
per_pay_groups,
per_jobs,
per_job_Definitions,
per_grades,
per_grade_definitions,
hr_all_positions,
hr_all_position_definitions,
hr_all_locations,
pay_all_payrolls_F
pay_element_entry_values_F,
pay_element_entries_F,
pay_elements_links_F,
pay_element_types_F

2. Key Flexfields (KFFs) in HRMS
Job KFF,
Grade KFF,
People Group KFF,
Position KFF,
Cost Allocation KFF,
Comptence KFF

3.What are Date Track Tables?
Every update in the Table, we will save the change in the form of a Record to provide the facility to find the information at any point of time.

These tables are post fixed by _F

4. What are secured Views?
The Views which do not have the _all to be said as secured views.
Per_all_people_F
per_people_f
per_all_assignemtns_f
per_assignments_f
pay_all_payrolls_F
per_payrolls_f

5. The differece between both secured views and non secured views is :
1.Secured views display information only for the current period
2.Unsecured views is used to get the information from the entire rows

6. APIs in HRMS
API are used in HR to insert the data into the Base tables. As its very secured system, the user does nothave the facility to copy the data directly into the Base tables.When we write the inbound interfaces / use WebAdI, the systems will use the APIs to store the data into system.The API are published by oracle with number of parameters.The different types of parameters are IN / INOUT / OUT.Of these parameters few are mandatory, with out which the process wont complete.Generally when we use API we give data for : Object Version Number, Effective Date, P_Validate

HR_EMOYEE_api ex: hr_employee_api.create_employee
hr_PERSON_api
hr_organization_api Ex: hr_organization_api.create_organization
hr_applicant_apI
hr_assignment_api

7. What are the reports which you have done in HR?
HR PAYROLL COSTING :
This report is used to display the information about the employees, the assignements which were given to the employees along with the payroll details including the Hours Paid, Salary, NI, Pension, Car Allowances and Other Allowances.

 
Imported Supply Purchase Order

SELECT         DISTINCT pva.segment1 "VENDOR CODE",
                    plla.ship_to_organization_id,
                    pha.po_header_id,
                    hla.inventory_organization_id,
                    pva.VENDOR_NAME,
                    pvsa.address_line1,
                    pvsa.address_line2,
                    pvsa.city,
                    pvsa.state,
                    pvsa.ZIP "PINCODE",
                   (PVC.first_name|| pvc.last_name) "SUPPLIER's CONTACT PERSON",
                       PVSA.phone "Mobile No./Phone No. ",
                        PVC.fax "FAX NO. ",
                   PVC.email_address "EMAIL ID ",
                  (pha.segment1||'/'||pha.revision_num) "PO/REV No",
                  (pra.release_num||'/'||pha.revision_num) "Rel/REV No.",
                  prha.segment1 "PR NO.",
                  papf.full_name "BUYER NAME ",
                  hla.address_line_1 "ADDRESS LINE 1 ",
                  hla.address_line_2 "ADDRESS LINE 2",
                  hla.LOCATION_CODE,
                  hla.town_or_city "CITY /STATE",
                  hla.telephone_number_1 "PHONE NO./FAX NO. ",
                  rownum "SL NO.",
                  msib.segment1 "ITEM_CODE",
                  pla.item_description,
                  plla.quantity "SHIPMENT QTY",
                  pla.UNIT_MEAS_LOOKUP_CODE "UOM",
                  plla.NEED_BY_DATE "NEED BY DATE ",
                  pha.currency_code "CURRENCY",
                  pla.unit_price "UNIT BASIC PRICE",
                 (pla.unit_price * plla.quantity) "TOTAL BASIC PRICE",
                 pla.po_header_id,
                 msib.organization_id,
                 plla.tax_name "SEA WORTHY PACKING",
                 pha.fob_lookup_code "DELIVERY TERMS",
                 pha.freight_terms_lookup_code "MODE OF TRANSPORT",
                 trm.name "PAYMENT TERMS",
                 papf.full_name,
                 jcvs.CST_REG_NO "CST NO.",
                 jcvs.VAT_REG_NO "VAT/ TIN NO.",
                 jcvs.EC_CODE "ECC NO.",
                 (fdt.title||fdt.description ) "ATTACHMENT",
                 hou.NAME "OPERATING UNIT"
FROM        po_headers_all pha,
                 po_releases_all pra,
                 ap_suppliers pva,
                 ap_supplier_sites_all pvsa,
                 ap_supplier_contacts pvc,
                 per_all_people_f papf,
                 po_line_locations_all plla,
                 po_distributions_all pda,
                 po_req_distributions_all prda,
                 po_requisition_lines_all prla,
                 po_requisition_headers_all prha,
                 hr_locations_all hla,
                 po_lines_all pla,
                 mtl_system_items_b msib,
                    JAI_CMN_VENDOR_SITES JCVS,
                 hr_operating_units  hou,
                ap_terms_tl trm,
                fnd_documents_tl fdt,
                fnd_attached_documents fad
WHERE  pha.po_header_id= pra.po_header_id
AND        pva.vendor_id= pvsa.vendor_id
AND       pvc.vendor_site_id= pvsa.vendor_site_id
AND       pha.org_id=pvsa.org_id(+)
AND       pha.agent_id=papf.person_id
AND       pha.po_header_id=plla.po_header_id
AND      plla.ship_to_location_id=hla.location_id
AND      plla.po_line_id=pla.po_line_id
AND       pla.item_id=msib.inventory_item_id(+)
AND      msib.organization_id=hla.inventory_organization_id
AND      jcvs.vendor_site_id(+)=pvsa.vendor_site_id
AND      hou.organization_id(+)=pha.org_id
AND      pda.line_location_id(+)=plla.line_location_id
AND      pra.po_release_id(+)=pda.po_release_id
AND      prda.distribution_id=pda.req_distribution_id
AND      prla.requisition_line_id=prda.requisition_line_id
AND      prha.requisition_header_id=prla.requisition_header_id
AND      trm.term_id=pha.terms_id
AND      fad.pk1_value(+) = TO_CHAR(pha.po_header_id)
AND      fdt.document_id (+)=fad.document_id
AND      pva.segment1 BETWEEN NVL(:FROM_VENDOR_CODE,pva.segment1) 
                AND NVL(:TO_VENDOR_CODE,pva.segment1)
AND     pha.currency_code<>'INR'
   

 
--> APIL payments Invoice using Vendor

SELECT   pha.segment1 "PO_NO" ,
               pha.creation_date "PO_DATE",
               pv.vendor_name,
                  SUM(pla.quantity) "PO_QTY",
               rsh.receipt_num "GRN_NO",
               rt.transaction_date "GRN_DATE",
                   SUM(plla.quantity_received) "RECEIVED_QTY",
                    SUM(plla.quantity_accepted) "ACCEPTED_QTY",
               SUM(aila.quantity_invoiced) "INVOICED_QTY",
               aia.amount_paid "PAYMENT_AMT",        
               apt.name "PAYMENT_TERMS",
               apsa.due_date "PAYMENT_DATE",
               aca.payment_method_code "PAYMENT_MODE",
                    DECODE(aca.payment_method_code,'CHECK',aca.check_number,
                    'NETTING',aca.check_number) "CHECK_NUMBER",
                  DECODE(aca.payment_method_code,'CHECK',aca.check_date,
                    'NETTING',aca.check_date) "CHECK_DATE"      
FROM     po_headers_all pha,
               po_lines_all pla,
               po_line_locations_all plla,
               po_distributions_all pda,
               po_vendors pv,
               rcv_transactions rt,
               rcv_shipment_headers rsh,
               rcv_shipment_lines rsl,
               ap_invoices_all aia,
               ap_invoice_lines_all aila,
               ap_invoice_distributions_all aida,
               ap_invoice_payments_all aipa,
               ap_payment_schedules_all apsa,
               ap_checks_all aca,
               ap_terms apt,
               mtl_system_items_b msib,
               org_organization_definitions ood
WHERE  pha.po_header_id=pla.po_header_id
AND        pla.po_line_id=plla.po_line_id
AND        plla.line_location_id=pda.line_location_id
AND        pv.vendor_id=pha.vendor_id
AND        rsh.shipment_header_id=rsl.shipment_header_id
AND        pda.po_distribution_id=rsl.po_distribution_id
AND        rsl.shipment_line_id=rt.shipment_line_id
AND        aia.invoice_id=aila.invoice_id
AND       aia.invoice_id=aida.invoice_id
AND       aipa.invoice_id(+)=aia.invoice_id
AND       aia.invoice_id=apsa.invoice_id
AND       aipa.check_id=aca.check_id(+)
AND       apt.term_id=aia.terms_id
AND       pla.item_id=msib.inventory_item_id(+)
AND       ood.organization_id(+)=msib.organization_id
AND       rt.transaction_id=aida.rcv_transaction_id
AND      TRUNC(rt.transaction_date) BETWEEN NVL(:FROM_RECEIPT_DATE,to_date
         (rt.transaction_date,'DD-MON-RR')) AND NVL(:TO_RECEIPT_DATE,to_date
         (rt.transaction_date,'DD-MON-RR'))
AND      pv.vendor_name=NVL(:VENDOR_NAME,pv.vendor_name)   
AND      aca.payment_method_code='CHECK'
GROUP BY pha.segment1,
                  pha.creation_date,
                  pv.vendor_name,
                  rsh.receipt_num,
                  rt.transaction_date,
                  aia.amount_paid,
                  apt.name,
                  apsa.due_date,
                  aca.payment_method_code,
                  aca.check_number,
                  aca.check_date


--> List of Open PO

SELECT     pha.segment1 "PO_NO",
               pha.creation_date "PO_CREATION_DATE",
               aps.segment1 "VENDOR_CODE",
               msib.segment1 "MATERIAL_CODE",
               msib.description "MATERIAL_DESCRIPTION",
               plla.need_by_date,
               pla.unit_meas_lookup_code "UOM",
               plla.quantity "PO_QTY",
                SUM(plla.quantity_received) "RECEIVED_QTY",
                   SUM(plla.quantity_accepted) "ACCEPTED_QTY",
                   SUM(plla.quantity_rejected) "REJECTED_QTY",
              (plla.quantity-plla.quantity_received) "BALANCE_QTY_TO_BE_RECEIVED"
FROM      po_headers_all pha,
              po_lines_all pla,
              po_line_locations_all plla,
              po_distributions_all pda,
              mtl_system_items_b msib,
              ap_suppliers aps
WHERE pha.vendor_id=aps.vendor_id
AND     pha.po_header_id=pla.po_header_id
AND     pla.po_line_id=plla.po_line_id
AND     pla.item_id=msib.inventory_item_id
AND     pha.org_id=:ORGANIZATION
AND     TRUNC (pha.creation_date) BETWEEN NVL (:FROM_RECEIPT_DATE,TO_DATE
         (pha.creation_date,'DD-MON-RR'))AND NVL (:TO_RECEIPT_DATE,TO_DATE
         (pha.creation_date,'DD-MON-RR'))                    
AND     pha.vendor_id=:VENDOR
AND     pha.segment1='7015'
GROUP BY    pha.segment1,
                  pha.creation_date,
                  aps.segment1,
                  msib.segment1,
                  msib.description,
                  plla.quantity,
                  plla.need_by_date,
                  pla.unit_meas_lookup_code,
                  plla.quantity,
                  plla.quantity_received,
                  plla.quantity_accepted,
                  plla.quantity_rejected,
                  plla.quantity,
                  plla.quantity_received

2 comments:

Sherif Owolabi said...

Why can't I copy these queries to use. It does not allow to copy

Raju Chinthapatla said...

Please send a mail to OracleApps88@Yahoo.com

Post a Comment

Best Blogger TipsGet Flower Effect