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"
FROMdba_constraints d LEFT JOIN dba_constraints p
ON (d.r_owner = p.owner AND d.r_constraint_name = p.constraint_name)
LEFT JOINdba_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"
FROMpo_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
No comments:
Post a Comment