PO Module
1.
SELECT
pov.vendor_name,
decode(pha.type_lookup_code, 'STANDARD',
pha.segment1, 'BLANKET', pha.segment1
|| '-'
|| pra.release_num,
pha.segment1) purchase_order,
pla.item_description,
decode(pha.type_lookup_code, 'STANDARD',
pla.line_num, 'BLANKET', plla.shipment_num,
pla.line_num) shipment_or_line_number,
pha.type_lookup_code,
pha.creation_date,
plla.need_by_date,
( plla.quantity - plla.quantity_received ) open_quantity,
plla.quantity_received,
prha.segment1 requisition,
(
SELECT DISTINCT
full_name
FROM
per_all_people_f
WHERE
person_id = pha.agent_id
) contact,
(
SELECT DISTINCT
full_name
FROM
per_all_people_f
WHERE
person_id = prha.preparer_id
) requestor,
(
SELECT
pa_tasks.task_number
FROM
pa_tasks
WHERE
pa_tasks.task_id = pda.task_id
) task_number
FROM
po_vendors pov,
po_headers_all pha,
po_releases_all pra,
po_lines_all pla,
po_line_locations_all plla,
po_distributions_all pda,
po_req_distributions_all prda,
po_requisition_lines_all prla,
po_requisition_headers_all prha,
per_all_people_f papf
WHERE
1 = 1
AND pov.vendor_id = pha.vendor_id
AND pha.po_header_id = pra.po_header_id (+)
AND pha.po_header_id = pla.po_header_id
AND pla.po_header_id = plla.po_header_id
AND pda.po_release_id (+) = pra.po_release_id
AND pda.req_distribution_id = prda.distribution_id --(+)
AND prda.requisition_line_id = prla.requisition_line_id --(+)
AND prla.requisition_header_id = prha.requisition_header_id --(+)
AND papf.person_id = pha.agent_id
AND pha.type_lookup_code = 'BLANKET'
AND pha.closed_code = 'OPEN'
AND to_date(pha.creation_date, 'DD-MON-YY')
BETWEEN to_date(to_char(to_date(:from_po_date, 'DD-MON-YY')), 'DD-MON-YY')
AND to_date(to_char(to_date(:po_date_to, 'DD-MON-YY')), 'DD-MON-YY');
2.
SELECT
pla.line_num,
pla.item_id,
pra.release_num,
(
SELECT DISTINCT
msib.description
FROM
mtl_system_items_b msib
WHERE
msib.inventory_item_id = pla.item_id
) discription,
plla.need_by_date,
pla.quantity,
pla.base_uom,
pla.unit_price,
( pla.quantity * pla.amount ) amount,
(
SELECT
hou.name
FROM
hr_operating_units hou
WHERE
hou.organization_id = pla.org_id
) organization_name,
pla.taxable_flag,
rsl.quantity_shipped,
rsl.quantity_received,
gccf.concatenated_segments
FROM
po_headers_all pha,
po_lines_all pla,
po_releases_all pra,
po_line_locations_all plla,
rcv_shipment_lines rsl,
po_distributions_all pda,
gl_code_combinations_kfv gccf
WHERE
1 = 1
AND pha.po_header_id = pla.po_header_id
AND pha.po_header_id = pra.po_header_id
AND pla.po_line_id = plla.po_line_id
AND rsl.po_line_id = plla.po_line_id
AND rsl.po_distribution_id = pda.po_distribution_id
AND plla.po_header_id = pha.po_header_id
AND rsl.po_header_id = pha.po_header_id
AND pda.po_header_id = pha.po_header_id
AND pda.code_combination_id = gccf.code_combination_id
AND pha.segment1 = '1000289' -- po_number
AND pha.org_id = 184
3.
SELECT
prha.segment1 requisition_number,
prla.line_num requisition_line_number,
prla.item_description requisition_item_description,
prla.unit_price requisition_item_amount,
(
SELECT DISTINCT
papf.full_name
FROM
per_all_people_f papf
WHERE
papf.person_id = pha.agent_id
) created_by,
prha.approved_date pr_approved_date,
decode(pha.type_lookup_code, 'STANDARD',
pha.segment1, 'BLANKET', pha.segment1
|| '-'
|| pra.release_num,
pha.segment1) purchase_order,
pha.creation_date po_creation_date,
pha.approved_date po_approved_date,
decode(pha.type_lookup_code, 'STANDARD',
pla.line_num, 'BLANKET', plla.shipment_num,
pla.line_num) shipment_or_line_number,
pla.item_description po_item_description,
pha.authorization_status po_status,
prha.authorization_status pr_status,
pla.unit_price po_item_amount
FROM
po_requisition_headers_all prha,
po_requisition_lines_all prla,
po_headers_all pha,
po_releases_all pra,
po_lines_all pla,
po_line_locations_all plla,
po_distributions_all pda,
po_req_distributions_all prda
WHERE
1 = 1
AND prha.requisition_header_id = prla.requisition_header_id
AND pha.po_header_id = pda.po_header_id
AND pha.po_header_id = pla.po_header_id
AND plla.line_location_id = pda.line_location_id
AND pda.po_release_id (+) = pra.po_release_id
AND pda.po_line_id = pla.po_line_id
AND pda.req_distribution_id = prda.distribution_id
AND prda.requisition_line_id = prla.requisition_line_id
AND to_date(pha.creation_date, 'DD-MON-YY')
BETWEEN to_date(to_char(to_date(:from_po_date, 'DD-MON-YY')), 'DD-MON-YY')
AND to_date(to_char(to_date(:po_date_to, 'DD-MON-YY')), 'DD-MON-YY');
4.
SELECT
prha.segment1 "RFQ NUMBER",
msib.segment1 "ITEM",
prla.item_description "ITEM DESCRIPTION",
prha.authorization_status " STATUS",
prha.creation_date "CREATE DATE",
pv.vendor_name "VENDOR"
FROM
po_requisition_headers_all prha,
mtl_system_items_b msib,
po_requisition_lines_all prla,
po_req_distributions_all prda,
po_distributions_all pda,
po_headers_all pha,
po_vendors pv
WHERE
prha.org_id = msib.organization_id
AND prla.requisition_header_id = prha.requisition_header_id
AND prda.requisition_line_id = prla.requisition_line_id
AND pda.req_distribution_id = prda.distribution_id
AND pha.po_header_id = pda.po_header_id
AND pha.vendor_id = pv.vendor_id
5.
SELECT
pt.task_number "WORK ORDER NUMBER",
pt.description "WORK ORDER DESCRIPTION",
pha.segment1 "PURCHASE ORDER NUMBER",
pha.creation_date "PO CREATION DATE",
pha.comments "PO DESCRIPTION",
pla.item_description "ITEM DESCRIPTION",
pv.vendor_name "VENDOR NUMBER",
pha.type_lookup_code "PO TYPE",
gcc.segment1 "GL CORP",
gcc.segment2 "GL ACCNT",
gcc.segment3 "GL DIV",
gcc.segment4 "GL CNTR",
prha.segment1 "REQUISION NUMBER",
pla.unit_price "UNIT PRICE",
pla.quantity "QUANTITY ORDER",
( pla.unit_price * pla.quantity ) "ITEM COST",
pla.taxable_flag "TAX",
( pla.unit_price * pla.quantity ) "PO LINE AMOUNT",
rct.amount,
rsh.tax_amount,
apss.vendor_site_code
FROM
pa_tasks pt,
po_req_distributions_all prda,
po_distributions_all pda,
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
po_vendors pv,
po_requisition_headers_all prha,
po_requisition_lines_all prla,
gl_code_combinations gcc,
rcv_transactions rct,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh,
ap_supplier_sites_all apss
WHERE
pt.task_id = pda.task_id
AND pda.req_distribution_id = prda.distribution_id
AND pda.line_location_id = plla.line_location_id
AND plla.po_line_id = pla.po_line_id
AND pla.po_header_id = pha.po_header_id
AND pha.vendor_id = pv.vendor_id
AND prda.requisition_line_id = prla.requisition_line_id
AND prla.requisition_header_id = prha.requisition_header_id
AND pda.code_combination_id = gcc.code_combination_id
AND pda.po_distribution_id = rct.po_distribution_id
AND rct.shipment_line_id = rsl.shipment_line_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND pha.vendor_site_id = apss.vendor_site_id
6.
SELECT
tax_rate,
tax_amt
( SELECT
lines.tax_rate,
lines.tax_amt
FROM
po_headers_all poh,
po_lines_all pol,
po_line_locations_all plla,
zx_lines lines
--WHERE (poh.segment1 = :p_po_no OR :p_po_no IS NULL)
WHERE
poh.po_header_id = :po_header_id1
AND pol.po_line_id = :l_po_line_id
AND poh.org_id = :p_org_id
AND pol.po_header_id = poh.po_header_id
AND pol.org_id = poh.org_id
AND lines.trx_id = poh.po_header_id
AND lines.trx_line_id = plla.line_location_id
AND pol.po_line_id = plla.po_line_id
--AND poh.authorization_status = 'APPROVED'
AND :p_report_type = 'STANDARD'
AND pol.quantity > 0 UNION ALL
SELECT
lines.tax_rate,
lines.tax_amt
FROM
po_headers_all poh,
po_lines_all pol,
po_distributions_all pod,
po_releases_all prl,
po_line_locations_all pll,
zx_lines lines
WHERE
1 = 1
AND poh.po_header_id = :po_header_id1
AND poh.org_id = :p_org_id
AND poh.authorization_status = 'APPROVED'
AND pol.po_header_id = poh.po_header_id
AND pol.org_id = poh.org_id
AND pod.po_header_id = pol.po_header_id
AND pod.po_line_id = pol.po_line_id
AND pod.org_id = pol.org_id
AND prl.po_release_id = pod.po_release_id
AND prl.po_header_id = pod.po_header_id
AND prl.org_id = pod.org_id
AND lines.trx_id = prl.po_release_id
AND lines.trx_line_id = pll.line_location_id
AND :p_report_type = 'BLANKET'
AND ( prl.po_release_id = nvl(:po_release_id1, prl.po_release_id) )
AND pol.po_line_id = :l_po_line_id
AND prl.release_num = nvl(:p_release_no, prl.release_num)
AND pll.po_line_id = pol.po_line_id
AND pll.po_header_id = pol.po_header_id
AND pll.line_location_id = pod.line_location_id
AND nvl(pll.cancel_flag, 'N') = 'N'
7. Concurrent Program Query
SELECT
fcpt.user_concurrent_program_name,
fcp.concurrent_program_name short_name,
fat.application_name program_application_name,
fet.executable_name,
fat1.application_name executable_application_name,
flv.meaning execution_method,
fet.execution_file_name,
fcp.enable_trace
FROM
fnd_concurrent_programs_tl fcpt,
fnd_concurrent_programs fcp,
fnd_application_tl fat,
fnd_executables fet,
fnd_application_tl fat1,
fnd_lookup_values flv
WHERE
1 = 1
AND fcpt.user_concurrent_program_name = 'Report'
AND fcpt.concurrent_program_id = fcp.concurrent_program_id
AND fcpt.application_id = fcp.application_id
AND fcp.application_id = fat.application_id
AND fcpt.application_id = fat.application_id
AND fcp.executable_id = fet.executable_id
AND fcp.executable_application_id = fet.application_id
AND fet.application_id = fat1.application_id
AND flv.lookup_code = fet.execution_method_code
AND flv.lookup_type = 'CP_EXECUTION_METHOD_CODE';
8.
SELECT DISTINCT
fcp.user_concurrent_program_name "Concurrent Program Name",
fcp.description "Concurrent Program Description",
fef.executable_name "Executable Name",
fef.description "Executable Description",
fef.execution_file_name "Procedure Name"
FROM
fnd_executables_form_v fef,
fnd_concurrent_programs_vl fcp
WHERE
fcp.application_id = fef.application_id
AND fef.executable_id = fcp.executable_id
--AND fef.executable_name='XX_EXECUTABLE'---Your Executable Name
AND fcp.user_concurrent_program_name = 'Report'--Your Consurrent Program name
;
SELECT
fcpl.user_concurrent_program_name "Concurrent Program Name",
fcp.concurrent_program_name "Short Name",
fat.application_name "Application Name",
fa.application_short_name "Module/Application Short Name",
fe.executable_name "Executable",
fe.execution_file_name "Executable Short Name",
decode(fcp.execution_method_code, 'X', 'FlexRpt', 'F', 'FlexSql',
'H', 'Host', 'S', 'Immediate', 'K',
'Java Concurrent Program', 'J',
'Java Stored Procedure', 'M',
'Multi Language Function',
'P', 'Oracle Reports', 'I',
'PL/SQL Stored Procedure', 'E',
'Perl Concurrent Program', 'B',
'Request Set Stage Function', 'L',
'SQL*Loader',
'Q', 'SQL*Plus', 'R', 'SQL*Report', 'Z',
'Shutdown Callback', 'A', 'Spawned') "Execution Method",
fcpl.description "Program Description",
fdfcuv.column_seq_num "Column Seq Number",
fdfcuv.end_user_column_name "Parameter Name",
fdfcuv.form_left_prompt "Prompt",
fdfcuv.enabled_flag " Enabled Flag",
fdfcuv.required_flag "Required Flag",
fdfcuv.display_flag "Display Flag",
ffvs.flex_value_set_name "Value Set Name"
FROM
fnd_executables fe,
fnd_concurrent_programs_tl fcpl,
fnd_application fa,
fnd_concurrent_programs fcp,
fnd_descr_flex_col_usage_vl fdfcuv,
fnd_flex_value_sets ffvs,
fnd_lookup_values flv,
fnd_application_tl fat
WHERE
1 = 1
AND fa.application_id = fat.application_id
AND fat.language = userenv('LANG')
AND fe.executable_id = fcp.executable_id
AND fcpl.application_id = fa.application_id
AND fcpl.concurrent_program_id = fcp.concurrent_program_id
AND user_concurrent_program_name = 'Report' -- change Concurrent program name here..
AND fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
AND flv.lookup_type (+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code (+) = fdfcuv.default_type
AND fcpl.language = 'US'
AND flv.language (+) = userenv('LANG')
ORDER BY
fdfcuv.column_seq_num;
SELECT
*
FROM
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fctp
WHERE
fctp.user_concurrent_program_name = 'Document Report'
AND fcpt.concurrent_program_id = fcp.concurrent_program_id;
AND fcpt.application_id =
fcp.application_id;
SELECT
fa.application_id "Application ID",
fat.application_name "Application Name",
fa.application_short_name "Application Short Name",
fa.basepath "Basepath"
FROM
fnd_application fa,
fnd_application_tl fat
WHERE
fa.application_id = fat.application_id
AND fat.language = userenv('LANG')
--and fa.application_short_name like 'AOL'
AND fat.application_name = 'Inventory' -- change it
ORDER BY
fat.application_name;