1. PO DETAIL REPORT( DFF)- Excel:-
Following query used to get all detail of "PURCHASE ORDER "
/* Formatted on 2013/06/01 12:06 (Formatter Plus v4.8.8) */
SELECT ppf.first_name || ' ' || ppf.last_name buyer,
a.authorization_status, a.match_option invoice_match,
a.closed_code closet_status, a.bill_to_location,
mca.segment1 || '.' || mca.segment2 catgory, a.promised_date,
pvs.VENDOR_SITE_CODE dff_project,
a.ship_to_organization shipto_org,
NVL (SUM (b.tax_amount), 0) totaltax,
TRUNC (( a.price_override
+ NVL ((SUM (b.tax_amount) / a.quantity), 0)
),
2
) rate,
TRUNC ( ( a.price_override
+ NVL ((SUM (b.tax_amount) / a.quantity), 0)
)
* (a.quantity - NVL (a.quantity_cancelled, 0)),
2
) amount,
--Billed Amount--g-
TRUNC (DECODE (a.quantity_billed,
0, 0,
NULL, 0,
( a.price_override
+ NVL ((SUM (b.tax_amount) / a.quantity),
0)
)
* a.quantity_billed
),
2
) billed_amt,
TRUNC (DECODE (a.quantity_received,
0, 0,
NULL, 0,
( a.price_override
+ NVL ((SUM (b.tax_amount) / a.quantity),
0)
)
* a.quantity_received
),
2
) received_amt,
a.price_override, a.po_num, pvs.vendor_site_code_alt project,
TO_DATE (a.order_date) po_date, a.ship_to_location shtoloc,
a.vendor_name vendor_name, COUNT (b.tax_line_no),
mca.segment1, pol.item_number item_code, a.item_description,
ROUND (a.quantity - NVL (a.quantity_cancelled, 0)) quantity,
a.quantity_cancelled,
ROUND (a.quantity_received) quantity_received,
ROUND (a.quantity_billed) quantity_billed, a.item_id,
a.revision_num, a.unit_meas_lookup_code unit,
a.currency_code, a.agent_id, a.line_location_id,
a.ship_to_organization_id, a.po_header_id ,poh.attribute14 contract_type
FROM jai_po_taxes b,
po_headers_all poh,
po_line_locations_inq_v a,
po_lines_v pol,
hr_org_units_no_join hrou,
per_people_f ppf,
po_vendor_sites_all pvs,
po_vendors pov,
mtl_categories mca
WHERE poh.org_id = a.owning_org_id
--AND a.po_num IN (100000707,100000052)
AND b.line_location_id(+) = a.line_location_id
AND poh.vendor_site_id = a.vendor_site_id
---------------------------------------------
AND a.po_num = poh.segment1
--AND poh.attribute1 IS NOT NULL
AND a.currency_code = 'INR'--:p_curr
AND poh.po_header_id = pol.po_header_id
AND b.po_header_id(+) = a.po_header_id
AND b.po_line_id(+) = a.po_line_id
AND pol.po_line_id = a.po_line_id
AND hrou.organization_id = a.ship_to_organization_id
AND ppf.person_id(+) = a.agent_id
--AND ppf.employee_number IS NOT NULL
AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND pvs.vendor_site_id = poh.vendor_site_id
AND pov.vendor_id = poh.vendor_id
AND mca.category_id = a.category_id
----------------------------------------------
AND nvl(poh.attribute14,1) = NVL (:contract_type, nvl(poh.attribute14,1))
---------------------------------------------------------------------
-- AND poh.attribute1 BETWEEN NVL (:from_site, poh.attribute1)
-- AND NVL (:to_site, poh.attribute1)
AND pvs.VENDOR_SITE_CODE BETWEEN NVL (:from_site, pvs.VENDOR_SITE_CODE)
AND NVL (:to_site, pvs.VENDOR_SITE_CODE)
-- METRO-PR
-----------------------------------------------------------------------
AND a.order_date BETWEEN NVL (:from_date, a.order_date)
AND NVL (:P_TO_DATE, SYSDATE)
AND a.vendor_name BETWEEN NVL (:from_vendor, a.vendor_name)
AND NVL (:to_vendor, a.vendor_name)
AND (:from_vendor IS NULL OR pov.vendor_name >= :from_vendor)
AND (:to_vendor IS NULL OR pov.vendor_name <= :to_vendor)
----------------------------------------------------------------------
AND ( DECODE (NVL (:p_item_flag, 'Y'), 'N', 'N') = 'N'
AND pol.item_number IS NULL
OR DECODE (NVL (:p_item_flag, 'Y'), 'Y', 'Y') = 'Y'
AND pol.item_number BETWEEN NVL (:from_item,
pol.item_number
)
AND NVL (:to_item,
pol.item_number
)
)
------------------------------------------------------------------------
and poh.ATTRIBUTE2 = DECODE (NVL (:p_item_flag, 'Y'), 'Y', 'POL-Local','Work Order')
AND mca.segment1 BETWEEN NVL (:p_category_from, mca.segment1)
AND NVL (:p_category_to, mca.segment1)
AND mca.segment2 BETWEEN NVL (:p_subcategory_from, mca.segment2)
AND NVL (:p_subcategory_to, mca.segment2)
AND a.ship_to_organization =
NVL (:p_shpto_org, a.ship_to_organization)
AND a.ship_to_location_id =
NVL (:p_shp_loc, a.ship_to_location_id)
AND ppf.person_id = NVL (:p_buyer, ppf.person_id)
--------------------------------------------------------------
GROUP BY a.item_id,
a.price_override,
a.quantity,
a.po_num,
a.order_date,
a.ship_to_location,
a.vendor_name,
pvs.VENDOR_SITE_CODE,
mca.segment1,
pol.item_number,
a.item_description,
a.item_id,
a.unit_meas_lookup_code,
a.quantity,
a.quantity_cancelled,
a.currency_code,
pvs.vendor_site_code_alt,
a.agent_id,
a.line_location_id,
a.ship_to_organization_id,
a.po_header_id,
a.quantity_received,
a.quantity_billed,
a.revision_num,
ppf.first_name,
ppf.last_name,
a.authorization_status,
a.match_option,
a.closed_code,
a.bill_to_location,
mca.segment1,
mca.segment2,
a.promised_date,
poh.attribute1,
a.ship_to_organization,
poh.attribute14
ORDER BY a.po_num
2. Report for Material Recived and Not Billed
In this report we get detail all po with how much material is recived and how much billed.
SELECT *
FROM (SELECT TO_CHAR (b.creation_date, 'MON-YYYY') "MONTH",
(CASE
WHEN ( d.vendor_site_code LIKE 'BI%'
OR d.vendor_site_code LIKE 'BL%'
)
THEN 'BLR'
WHEN ( d.vendor_site_code LIKE 'DI%'
OR d.vendor_site_code LIKE 'DL%'
)
THEN 'DELHI'
WHEN ( d.vendor_site_code LIKE 'GI%'
OR d.vendor_site_code LIKE 'GL%'
)
THEN 'GUJ'
WHEN ( d.vendor_site_code LIKE 'HI%'
OR d.vendor_site_code LIKE 'HL%'
)
THEN 'HYD'
WHEN ( d.vendor_site_code LIKE 'KI%'
OR d.vendor_site_code LIKE 'KL%'
)
THEN 'KOL'
WHEN ( d.vendor_site_code LIKE 'MI%'
OR d.vendor_site_code LIKE 'ML%'
)
THEN 'MUM'
WHEN d.vendor_site_code LIKE 'ADM%'
THEN 'ADMIN'
WHEN d.vendor_site_code LIKE 'OTH%'
THEN 'OTH SITE'
ELSE 'MISC'
END
) "REGION",
c.vendor_name "VENDOR", d.vendor_site_code "SITE",
f.description "SITE_NAME", b.segment1 "PO_NUM",
b.attribute2 "ORD_TYPE", e.line_num,
ROUND (a.quantity - NVL (a.quantity_cancelled, 0)) "QUANTITY",
a.quantity_received "RECEIVED_QTY",
a.quantity_billed "BILLED_QTY",
(a.quantity_received - a.quantity_billed) "UNBILLED_QTY",
a.price_override "PRICE",
( (a.quantity_received - a.quantity_billed)
* a.price_override
) "UNBILLED_AMOUNT",
b.currency_code "CURRENCY", g.user_name "BUYER",
a.closed_code
FROM po_line_locations a,
po_headers b,
po_vendors c,
po_lines e,
po_vendor_sites d LEFT OUTER JOIN fnd_flex_values_vl f
ON d.vendor_site_code = f.flex_value
AND f.flex_value_set_id = '1014848'
,
fnd_user g
WHERE a.po_header_id = b.po_header_id
AND b.vendor_id = c.vendor_id
AND b.vendor_site_id = d.vendor_site_id
AND a.po_line_id = e.po_line_id
AND b.created_by = g.user_id
AND a.quantity_received > 0
AND a.quantity_received != a.quantity_billed
AND b.creation_date BETWEEN NVL (from_date, '01-Apr-05')
AND NVL (to_dte, SYSDATE)
AND d.vendor_site_code = NVL (p_site, d.vendor_site_code)
AND b.vendor_id = NVL (p_vendor_id, b.vendor_id)
AND ( b.attribute2 = NVL (ord_type, b.attribute2)
OR b.attribute2 IS NULL
)
--AND B.CURRENCY_CODE = NVL(:CURRENCY,B.CURRENCY_CODE)
AND b.created_by = NVL (p_buyer, b.created_by)
AND b.currency_code != 'INR')
WHERE region = NVL (p_region, region)
ORDER BY TO_NUMBER (po_num);
3. SPCL - DEBIT RATE REPORT
In this Report we get all detail PROJECT for which item is purchased for project and how many time items purchased and its total purchase rate and total tax amount on that item on using this info we calculate AVERAGE purchase amount for particular ITEM.
SELECT pvs.vendor_site_code dff_project,
pol.item_number item_code,
a.unit_meas_lookup_code unit,
-- nvl(pvs.vendor_site_code_alt,pvs.vendor_site_code) project,
sum( ROUND (a.quantity - NVL (a.quantity_cancelled, 0))) quantity,
sum(pol.unit_price) unit_price_sum,
sum(tax_amt) tax_amt,
COUNT (pol.item_number) purchase_time
FROM po_headers_all poh,
-- jai_po_taxes b,
po_line_locations_inq_v a,
po_lines_v pol,
-- hr_org_units_no_join hrou,
po_vendor_sites_all pvs,
po_vendors pov,
(select sum(b.TAX_AMOUNT) tax_amt,b.PO_LINE_ID po_line_id
from jai_po_taxes b
group by b.PO_HEADER_ID,b.PO_LINE_ID) c
WHERE poh.org_id = a.owning_org_id
AND poh.vendor_site_id = a.vendor_site_id
and a.PO_LINE_ID=c.po_line_id(+)
---------------------------------------------
--AND pvs.vendor_site_code_alt='TCS-NEPTUNE THANE'--:project_name
AND pvs.vendor_site_code =:p_vendor_site
--METRO-PR
and ROUND (a.quantity - NVL (a.quantity_cancelled, 0))<>0
and pol.item_number is not null
and pol.item_number in('IN03053','IN11084')
-- and b.PO_LINE_ID(+)=a.PO_LINE_ID
-- 'MI131823'
---------------------------------------------
AND a.po_num = poh.segment1
-- AND poh.attribute1 IS NOT NULL
-- AND a.currency_code = 'INR'--:p_curr
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = a.po_line_id
-- AND hrou.organization_id = a.ship_to_organization_id
AND pvs.vendor_site_id = poh.vendor_site_id
AND pov.vendor_id = poh.vendor_id
---------------------------------------------
GROUP BY pvs.vendor_site_code , --a.item_id,
pol.item_number,
a.unit_meas_lookup_code
-----------------------------------------------------------------------------------------------
4. Purchasing Summery Report:-
query to get purchaser order summer with invoice no amount tax amount
SELECT AI.ATTRIBUTE1 LOCATION
,AI.ATTRIBUTE13 STATE
,PV.VENDOR_NAME
,PVS.VENDOR_SITE_CODE
,ai.INVOICE_NUM,AI.INVOICE_DATE,ROUND(SUM(AID.AMOUNT)) Inv_Amount
-- jiatd.TAX_AMOUNT
,JITC.TAX_RATE
,jitc.TAX_NAME
,SUM(jiatd.TAX_AMOUNT) T_TAX
,AI.INVOICE_AMOUNT
FROM po_vendors pv,
po_vendor_sites_all pvs,
ja_in_ap_tax_distributions jiatd,
ja_in_tax_codes jitc,
ap_invoices_all ai
,ap_invoice_distributions_all aid
WHERE pv.vendor_id =pvs.vendor_id
AND ai.vendor_id =pvs.vendor_id
AND ai.vendor_site_id =pvs.vendor_site_id
AND jitc.tax_id = jiatd.tax_id
AND ai.invoice_id = jiatd.invoice_id
AND ai.invoice_id = aid.invoice_id
AND JIATD.INVOICE_ID = AID.INVOICE_ID
AND JIATD.PO_DISTRIBUTION_ID = AID.PO_DISTRIBUTION_ID
AND JIATD.PARENT_INVOICE_DISTRIBUTION_ID = AID.INVOICE_DISTRIBUTION_ID
AND aid.LINE_TYPE_LOOKUP_CODE LIKE 'ITEM'
AND ai.CANCELLED_DATE IS NULL
--AND ai.ATTRIBUTE1 =NVL(:P_Location,ai.ATTRIBUTE1)
AND ai.set_of_books_id = :P_SET_OF_BOOKS_ID
--AND AI.INVOICE_NUM LIKE 'RA-1/11915'--'RA-01/11741'--858'--'RA-2/10980'
--AND AI.INVOICE_DATE like '30-MAR-08'--'10-SEP-07'
AND ai.ATTRIBUTE1=NVL(:P_LOCATION,ai.ATTRIBUTE1)
AND (ai.ATTRIBUTE13)=nvl(:P_STATE,ai.ATTRIBUTE13)
AND ai.set_of_books_id = :P_SET_OF_BOOKS_ID
AND ai.INVOICE_DATE BETWEEN NVL(:p_from_date,ai.INVOICE_DATE) AND nvl(:p_to_date,ai.INVOICE_DATE)
AND pvs.vendor_id=nvl(:p_vendor_id,pvs.vendor_id)
AND pvs.vendor_site_id= nvl(:p_vendor_site_id,pvs.vendor_site_id)
AND pv.VENDOR_TYPE_LOOKUP_CODE = NVL(:p_vndor_class,pv.vendor_type_lookup_code)
AND jitc.TAX_NAME = NVL(:p_tax_type,jitc.tax_name)
AND PVS.VENDOR_SITE_CODE =NVL( :p_site,pvs.vendor_site_code)
GROUP BY AI.INVOICE_NUM,--jiatd.TAX_AMOUNT,
jitc.TAX_NAME,
JITC.TAX_RATE,
ai.INVOICE_NUM,JIATD.INVOICE_ID,JIATD.TASK_ID
,AI.INVOICE_AMOUNT
,AI.INVOICE_DATE
,AI.ATTRIBUTE1
,AI.ATTRIBUTE13
,PV.VENDOR_NAME
,PVS.VENDOR_SITE_CODE
Note:- Only Purchase Requisitions will be converted to the Purchase orders.
select pha.SEGMENT1 po_num,pha.AUTHORIZATION_STATUS po_status,pha.CLOSED_CODE close_status,trunc(pha.CREATION_DATE) po_date
, pla.LINE_NUM po_line_number,gcc.SEGMENT2 cost_center,gl_flexfields_pkg.get_description_sql (50354,
2,
gcc.segment2
) cost_center_desc,
pla.UNIT_PRICE,pla.QUANTITY,(pla.UNIT_PRICE*pla.QUANTITY) Base_amount,
(select nvl(sum(jpt.TAX_AMOUNT),0) from jai_po_taxes jpt,jai_cmn_taxes_all jcta
where jpt.PO_HEADER_ID=pla.PO_HEADER_ID
and jpt.PO_LINE_ID=pla.PO_LINE_ID
and jcta.TAX_ID=jpt.TAX_ID
and jcta.INCLUSIVE_TAX_FLAG='N'
) tax_amt,
((pla.UNIT_PRICE*pla.QUANTITY) +
(select nvl(sum(jpt.TAX_AMOUNT),0) from jai_po_taxes jpt,jai_cmn_taxes_all jcta
where jpt.PO_HEADER_ID=pla.PO_HEADER_ID
and jpt.PO_LINE_ID=pla.PO_LINE_ID
and jcta.TAX_ID=jpt.TAX_ID
and jcta.INCLUSIVE_TAX_FLAG='N'
)
) total_amt
from po_headers_all pha
,po_lines_all pla
,po_distributions_all pda
,po_line_locations_all plla
,gl_code_combinations gcc
where pha.PO_HEADER_ID=pla.PO_HEADER_ID
and pla.PO_LINE_ID=pda.PO_LINE_ID
and pda.PO_HEADER_ID=pha.PO_HEADER_ID
and pda.LINE_LOCATION_ID=plla.LINE_LOCATION_ID
and pda.CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
and pha.SEGMENT1=4101140001456
order by 1,5
Following query used to get all detail of "PURCHASE ORDER "
/* Formatted on 2013/06/01 12:06 (Formatter Plus v4.8.8) */
SELECT ppf.first_name || ' ' || ppf.last_name buyer,
a.authorization_status, a.match_option invoice_match,
a.closed_code closet_status, a.bill_to_location,
mca.segment1 || '.' || mca.segment2 catgory, a.promised_date,
pvs.VENDOR_SITE_CODE dff_project,
a.ship_to_organization shipto_org,
NVL (SUM (b.tax_amount), 0) totaltax,
TRUNC (( a.price_override
+ NVL ((SUM (b.tax_amount) / a.quantity), 0)
),
2
) rate,
TRUNC ( ( a.price_override
+ NVL ((SUM (b.tax_amount) / a.quantity), 0)
)
* (a.quantity - NVL (a.quantity_cancelled, 0)),
2
) amount,
--Billed Amount--g-
TRUNC (DECODE (a.quantity_billed,
0, 0,
NULL, 0,
( a.price_override
+ NVL ((SUM (b.tax_amount) / a.quantity),
0)
)
* a.quantity_billed
),
2
) billed_amt,
TRUNC (DECODE (a.quantity_received,
0, 0,
NULL, 0,
( a.price_override
+ NVL ((SUM (b.tax_amount) / a.quantity),
0)
)
* a.quantity_received
),
2
) received_amt,
a.price_override, a.po_num, pvs.vendor_site_code_alt project,
TO_DATE (a.order_date) po_date, a.ship_to_location shtoloc,
a.vendor_name vendor_name, COUNT (b.tax_line_no),
mca.segment1, pol.item_number item_code, a.item_description,
ROUND (a.quantity - NVL (a.quantity_cancelled, 0)) quantity,
a.quantity_cancelled,
ROUND (a.quantity_received) quantity_received,
ROUND (a.quantity_billed) quantity_billed, a.item_id,
a.revision_num, a.unit_meas_lookup_code unit,
a.currency_code, a.agent_id, a.line_location_id,
a.ship_to_organization_id, a.po_header_id ,poh.attribute14 contract_type
FROM jai_po_taxes b,
po_headers_all poh,
po_line_locations_inq_v a,
po_lines_v pol,
hr_org_units_no_join hrou,
per_people_f ppf,
po_vendor_sites_all pvs,
po_vendors pov,
mtl_categories mca
WHERE poh.org_id = a.owning_org_id
--AND a.po_num IN (100000707,100000052)
AND b.line_location_id(+) = a.line_location_id
AND poh.vendor_site_id = a.vendor_site_id
---------------------------------------------
AND a.po_num = poh.segment1
--AND poh.attribute1 IS NOT NULL
AND a.currency_code = 'INR'--:p_curr
AND poh.po_header_id = pol.po_header_id
AND b.po_header_id(+) = a.po_header_id
AND b.po_line_id(+) = a.po_line_id
AND pol.po_line_id = a.po_line_id
AND hrou.organization_id = a.ship_to_organization_id
AND ppf.person_id(+) = a.agent_id
--AND ppf.employee_number IS NOT NULL
AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND pvs.vendor_site_id = poh.vendor_site_id
AND pov.vendor_id = poh.vendor_id
AND mca.category_id = a.category_id
----------------------------------------------
AND nvl(poh.attribute14,1) = NVL (:contract_type, nvl(poh.attribute14,1))
---------------------------------------------------------------------
-- AND poh.attribute1 BETWEEN NVL (:from_site, poh.attribute1)
-- AND NVL (:to_site, poh.attribute1)
AND pvs.VENDOR_SITE_CODE BETWEEN NVL (:from_site, pvs.VENDOR_SITE_CODE)
AND NVL (:to_site, pvs.VENDOR_SITE_CODE)
-- METRO-PR
-----------------------------------------------------------------------
AND a.order_date BETWEEN NVL (:from_date, a.order_date)
AND NVL (:P_TO_DATE, SYSDATE)
AND a.vendor_name BETWEEN NVL (:from_vendor, a.vendor_name)
AND NVL (:to_vendor, a.vendor_name)
AND (:from_vendor IS NULL OR pov.vendor_name >= :from_vendor)
AND (:to_vendor IS NULL OR pov.vendor_name <= :to_vendor)
----------------------------------------------------------------------
AND ( DECODE (NVL (:p_item_flag, 'Y'), 'N', 'N') = 'N'
AND pol.item_number IS NULL
OR DECODE (NVL (:p_item_flag, 'Y'), 'Y', 'Y') = 'Y'
AND pol.item_number BETWEEN NVL (:from_item,
pol.item_number
)
AND NVL (:to_item,
pol.item_number
)
)
------------------------------------------------------------------------
and poh.ATTRIBUTE2 = DECODE (NVL (:p_item_flag, 'Y'), 'Y', 'POL-Local','Work Order')
AND mca.segment1 BETWEEN NVL (:p_category_from, mca.segment1)
AND NVL (:p_category_to, mca.segment1)
AND mca.segment2 BETWEEN NVL (:p_subcategory_from, mca.segment2)
AND NVL (:p_subcategory_to, mca.segment2)
AND a.ship_to_organization =
NVL (:p_shpto_org, a.ship_to_organization)
AND a.ship_to_location_id =
NVL (:p_shp_loc, a.ship_to_location_id)
AND ppf.person_id = NVL (:p_buyer, ppf.person_id)
--------------------------------------------------------------
GROUP BY a.item_id,
a.price_override,
a.quantity,
a.po_num,
a.order_date,
a.ship_to_location,
a.vendor_name,
pvs.VENDOR_SITE_CODE,
mca.segment1,
pol.item_number,
a.item_description,
a.item_id,
a.unit_meas_lookup_code,
a.quantity,
a.quantity_cancelled,
a.currency_code,
pvs.vendor_site_code_alt,
a.agent_id,
a.line_location_id,
a.ship_to_organization_id,
a.po_header_id,
a.quantity_received,
a.quantity_billed,
a.revision_num,
ppf.first_name,
ppf.last_name,
a.authorization_status,
a.match_option,
a.closed_code,
a.bill_to_location,
mca.segment1,
mca.segment2,
a.promised_date,
poh.attribute1,
a.ship_to_organization,
poh.attribute14
ORDER BY a.po_num
2. Report for Material Recived and Not Billed
In this report we get detail all po with how much material is recived and how much billed.
SELECT *
FROM (SELECT TO_CHAR (b.creation_date, 'MON-YYYY') "MONTH",
(CASE
WHEN ( d.vendor_site_code LIKE 'BI%'
OR d.vendor_site_code LIKE 'BL%'
)
THEN 'BLR'
WHEN ( d.vendor_site_code LIKE 'DI%'
OR d.vendor_site_code LIKE 'DL%'
)
THEN 'DELHI'
WHEN ( d.vendor_site_code LIKE 'GI%'
OR d.vendor_site_code LIKE 'GL%'
)
THEN 'GUJ'
WHEN ( d.vendor_site_code LIKE 'HI%'
OR d.vendor_site_code LIKE 'HL%'
)
THEN 'HYD'
WHEN ( d.vendor_site_code LIKE 'KI%'
OR d.vendor_site_code LIKE 'KL%'
)
THEN 'KOL'
WHEN ( d.vendor_site_code LIKE 'MI%'
OR d.vendor_site_code LIKE 'ML%'
)
THEN 'MUM'
WHEN d.vendor_site_code LIKE 'ADM%'
THEN 'ADMIN'
WHEN d.vendor_site_code LIKE 'OTH%'
THEN 'OTH SITE'
ELSE 'MISC'
END
) "REGION",
c.vendor_name "VENDOR", d.vendor_site_code "SITE",
f.description "SITE_NAME", b.segment1 "PO_NUM",
b.attribute2 "ORD_TYPE", e.line_num,
ROUND (a.quantity - NVL (a.quantity_cancelled, 0)) "QUANTITY",
a.quantity_received "RECEIVED_QTY",
a.quantity_billed "BILLED_QTY",
(a.quantity_received - a.quantity_billed) "UNBILLED_QTY",
a.price_override "PRICE",
( (a.quantity_received - a.quantity_billed)
* a.price_override
) "UNBILLED_AMOUNT",
b.currency_code "CURRENCY", g.user_name "BUYER",
a.closed_code
FROM po_line_locations a,
po_headers b,
po_vendors c,
po_lines e,
po_vendor_sites d LEFT OUTER JOIN fnd_flex_values_vl f
ON d.vendor_site_code = f.flex_value
AND f.flex_value_set_id = '1014848'
,
fnd_user g
WHERE a.po_header_id = b.po_header_id
AND b.vendor_id = c.vendor_id
AND b.vendor_site_id = d.vendor_site_id
AND a.po_line_id = e.po_line_id
AND b.created_by = g.user_id
AND a.quantity_received > 0
AND a.quantity_received != a.quantity_billed
AND b.creation_date BETWEEN NVL (from_date, '01-Apr-05')
AND NVL (to_dte, SYSDATE)
AND d.vendor_site_code = NVL (p_site, d.vendor_site_code)
AND b.vendor_id = NVL (p_vendor_id, b.vendor_id)
AND ( b.attribute2 = NVL (ord_type, b.attribute2)
OR b.attribute2 IS NULL
)
--AND B.CURRENCY_CODE = NVL(:CURRENCY,B.CURRENCY_CODE)
AND b.created_by = NVL (p_buyer, b.created_by)
AND b.currency_code != 'INR')
WHERE region = NVL (p_region, region)
ORDER BY TO_NUMBER (po_num);
3. SPCL - DEBIT RATE REPORT
In this Report we get all detail PROJECT for which item is purchased for project and how many time items purchased and its total purchase rate and total tax amount on that item on using this info we calculate AVERAGE purchase amount for particular ITEM.
SELECT pvs.vendor_site_code dff_project,
pol.item_number item_code,
a.unit_meas_lookup_code unit,
-- nvl(pvs.vendor_site_code_alt,pvs.vendor_site_code) project,
sum( ROUND (a.quantity - NVL (a.quantity_cancelled, 0))) quantity,
sum(pol.unit_price) unit_price_sum,
sum(tax_amt) tax_amt,
COUNT (pol.item_number) purchase_time
FROM po_headers_all poh,
-- jai_po_taxes b,
po_line_locations_inq_v a,
po_lines_v pol,
-- hr_org_units_no_join hrou,
po_vendor_sites_all pvs,
po_vendors pov,
(select sum(b.TAX_AMOUNT) tax_amt,b.PO_LINE_ID po_line_id
from jai_po_taxes b
group by b.PO_HEADER_ID,b.PO_LINE_ID) c
WHERE poh.org_id = a.owning_org_id
AND poh.vendor_site_id = a.vendor_site_id
and a.PO_LINE_ID=c.po_line_id(+)
---------------------------------------------
--AND pvs.vendor_site_code_alt='TCS-NEPTUNE THANE'--:project_name
AND pvs.vendor_site_code =:p_vendor_site
--METRO-PR
and ROUND (a.quantity - NVL (a.quantity_cancelled, 0))<>0
and pol.item_number is not null
and pol.item_number in('IN03053','IN11084')
-- and b.PO_LINE_ID(+)=a.PO_LINE_ID
-- 'MI131823'
---------------------------------------------
AND a.po_num = poh.segment1
-- AND poh.attribute1 IS NOT NULL
-- AND a.currency_code = 'INR'--:p_curr
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = a.po_line_id
-- AND hrou.organization_id = a.ship_to_organization_id
AND pvs.vendor_site_id = poh.vendor_site_id
AND pov.vendor_id = poh.vendor_id
---------------------------------------------
GROUP BY pvs.vendor_site_code , --a.item_id,
pol.item_number,
a.unit_meas_lookup_code
-----------------------------------------------------------------------------------------------
4. Purchasing Summery Report:-
query to get purchaser order summer with invoice no amount tax amount
SELECT AI.ATTRIBUTE1 LOCATION
,AI.ATTRIBUTE13 STATE
,PV.VENDOR_NAME
,PVS.VENDOR_SITE_CODE
,ai.INVOICE_NUM,AI.INVOICE_DATE,ROUND(SUM(AID.AMOUNT)) Inv_Amount
-- jiatd.TAX_AMOUNT
,JITC.TAX_RATE
,jitc.TAX_NAME
,SUM(jiatd.TAX_AMOUNT) T_TAX
,AI.INVOICE_AMOUNT
FROM po_vendors pv,
po_vendor_sites_all pvs,
ja_in_ap_tax_distributions jiatd,
ja_in_tax_codes jitc,
ap_invoices_all ai
,ap_invoice_distributions_all aid
WHERE pv.vendor_id =pvs.vendor_id
AND ai.vendor_id =pvs.vendor_id
AND ai.vendor_site_id =pvs.vendor_site_id
AND jitc.tax_id = jiatd.tax_id
AND ai.invoice_id = jiatd.invoice_id
AND ai.invoice_id = aid.invoice_id
AND JIATD.INVOICE_ID = AID.INVOICE_ID
AND JIATD.PO_DISTRIBUTION_ID = AID.PO_DISTRIBUTION_ID
AND JIATD.PARENT_INVOICE_DISTRIBUTION_ID = AID.INVOICE_DISTRIBUTION_ID
AND aid.LINE_TYPE_LOOKUP_CODE LIKE 'ITEM'
AND ai.CANCELLED_DATE IS NULL
--AND ai.ATTRIBUTE1 =NVL(:P_Location,ai.ATTRIBUTE1)
AND ai.set_of_books_id = :P_SET_OF_BOOKS_ID
--AND AI.INVOICE_NUM LIKE 'RA-1/11915'--'RA-01/11741'--858'--'RA-2/10980'
--AND AI.INVOICE_DATE like '30-MAR-08'--'10-SEP-07'
AND ai.ATTRIBUTE1=NVL(:P_LOCATION,ai.ATTRIBUTE1)
AND (ai.ATTRIBUTE13)=nvl(:P_STATE,ai.ATTRIBUTE13)
AND ai.set_of_books_id = :P_SET_OF_BOOKS_ID
AND ai.INVOICE_DATE BETWEEN NVL(:p_from_date,ai.INVOICE_DATE) AND nvl(:p_to_date,ai.INVOICE_DATE)
AND pvs.vendor_id=nvl(:p_vendor_id,pvs.vendor_id)
AND pvs.vendor_site_id= nvl(:p_vendor_site_id,pvs.vendor_site_id)
AND pv.VENDOR_TYPE_LOOKUP_CODE = NVL(:p_vndor_class,pv.vendor_type_lookup_code)
AND jitc.TAX_NAME = NVL(:p_tax_type,jitc.tax_name)
AND PVS.VENDOR_SITE_CODE =NVL( :p_site,pvs.vendor_site_code)
GROUP BY AI.INVOICE_NUM,--jiatd.TAX_AMOUNT,
jitc.TAX_NAME,
JITC.TAX_RATE,
ai.INVOICE_NUM,JIATD.INVOICE_ID,JIATD.TASK_ID
,AI.INVOICE_AMOUNT
,AI.INVOICE_DATE
,AI.ATTRIBUTE1
,AI.ATTRIBUTE13
,PV.VENDOR_NAME
,PVS.VENDOR_SITE_CODE
5.How to get the Cancelled Requisitions?
You can get all the Canceled Requisitions in the PO Module using the following Query.
SELECT
prha.requisition_header_id "requisition_header_id"
,prha.segment1 "Requisition Number"
,prha.preparer_id "preparer_id"
,TRUNC(prha.creation_date) "creation_date"
,prha.description "description"
,prha.note_to_authorizer "note_to_authorizer"
FROM
po_requisition_headers_all prha
,po_action_history pah
WHERE action_code='CANCEL'
AND pah.object_type_code='
AND pah.object_id=prha.
SELECT
prha.requisition_header_id "requisition_header_id"
,prha.segment1 "Requisition Number"
,prha.preparer_id "preparer_id"
,TRUNC(prha.creation_date) "creation_date"
,prha.description "description"
,prha.note_to_authorizer "note_to_authorizer"
FROM
po_requisition_headers_all prha
,po_action_history pah
WHERE action_code='CANCEL'
AND pah.object_type_code='
AND pah.object_id=prha.
4. Query to Get PO detail with his tax amount and item amount details
Note:- Only Purchase Requisitions will be converted to the Purchase orders.
select pha.SEGMENT1 po_num,pha.AUTHORIZATION_STATUS po_status,pha.CLOSED_CODE close_status,trunc(pha.CREATION_DATE) po_date
, pla.LINE_NUM po_line_number,gcc.SEGMENT2 cost_center,gl_flexfields_pkg.get_description_sql (50354,
2,
gcc.segment2
) cost_center_desc,
pla.UNIT_PRICE,pla.QUANTITY,(pla.UNIT_PRICE*pla.QUANTITY) Base_amount,
(select nvl(sum(jpt.TAX_AMOUNT),0) from jai_po_taxes jpt,jai_cmn_taxes_all jcta
where jpt.PO_HEADER_ID=pla.PO_HEADER_ID
and jpt.PO_LINE_ID=pla.PO_LINE_ID
and jcta.TAX_ID=jpt.TAX_ID
and jcta.INCLUSIVE_TAX_FLAG='N'
) tax_amt,
((pla.UNIT_PRICE*pla.QUANTITY) +
(select nvl(sum(jpt.TAX_AMOUNT),0) from jai_po_taxes jpt,jai_cmn_taxes_all jcta
where jpt.PO_HEADER_ID=pla.PO_HEADER_ID
and jpt.PO_LINE_ID=pla.PO_LINE_ID
and jcta.TAX_ID=jpt.TAX_ID
and jcta.INCLUSIVE_TAX_FLAG='N'
)
) total_amt
from po_headers_all pha
,po_lines_all pla
,po_distributions_all pda
,po_line_locations_all plla
,gl_code_combinations gcc
where pha.PO_HEADER_ID=pla.PO_HEADER_ID
and pla.PO_LINE_ID=pda.PO_LINE_ID
and pda.PO_HEADER_ID=pha.PO_HEADER_ID
and pda.LINE_LOCATION_ID=plla.LINE_LOCATION_ID
and pda.CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
and pha.SEGMENT1=4101140001456
order by 1,5
No comments:
Post a Comment