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:
Why can't I copy these queries to use. It does not allow to copy
Please send a mail to OracleApps88@Yahoo.com
Post a Comment