select
x.ledger,
x.operating_unit,
x.organization_code,
x.category,
x.item,
x.description item_description,
x.vendor,
x.buyer,
x.po_number_release po_number,
x.currency,
x.line,
x.shipment,
trunc(x.receipt_date) receipt_date,
x.receipt_number,
x.quantity_received,
x.unit,
x.unit_price po_unit_price,
x.po_functional_price,
x.std_unit_cost_f std_unit_cost,
x.material_cost_f material_unit_cost,
x.moh_absorbed_per_unit material_overhead_unit_cost,
x.c_price_variance purchase_price_variance,
x.rct_id rcv_transaction_id
from
(
select
pol.ledger,
pol.operating_unit,
mp.organization_code,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', mca.structure_id, null, mca.category_id, 'ALL', 'Y', 'VALUE') category,
msi.concatenated_segments item,
msi.description description,
pov.vendor_name vendor,
papf.full_name buyer,
decode(poh.type_lookup_code, 'BLANKET', poh.segment1||' - '||por.release_num, 'PLANNED', poh.segment1||' - '||por.release_num, poh.segment1) po_number_release,
poh.currency_code currency,
pol.line_num line,
rsh.shipment_num shipment,
rct.transaction_date receipt_date,
rsh.receipt_num receipt_number,
round(mmt.primary_quantity,:p_qty_precision) quantity_received,
rct.primary_unit_of_measure unit,
round((nvl(mmt.transaction_cost,0)/nvl(mmt.currency_conversion_rate,1)),pol.extended_precision) unit_price,
round(nvl(mmt.transaction_cost,0) , pol.extended_precision) po_functional_price,
round(nvl(mmt.actual_cost,0),pol.extended_precision) std_unit_cost_f,
round(nvl(mcacd1.actual_cost,0),pol.extended_precision) material_cost_f,
round(decode(mta.accounting_line_type, 3, nvl(mcacd2.actual_cost,0), 0) , pol.extended_precision) moh_absorbed_per_unit,
po_poxrcppv_xmlp_pkg.c_price_varianceformula( round ( nvl ( mmt.transaction_cost , 0 ) , pol.extended_precision ), round ( nvl ( mmt.actual_cost , 0 ) , pol.extended_precision ), round ( decode ( mta.accounting_line_type , 3 , nvl ( mcacd2.actual_cost , 0 ) , 0 ) , pol.extended_precision ), round ( mmt.primary_quantity , :p_qty_precision ), pol.precision) c_price_variance,
mp.process_enabled_flag,
rct.transaction_id rct_id,
pol.item_id
from
po_distributions_all pod,
po_line_locations_all pll,
(select
pla.*,
(select
fspa.inventory_organization_id
from
financials_system_params_all fspa
where
hou.set_of_books_id=fspa.set_of_books_id and
pla.org_id=fspa.org_id) inventory_organization_id,
gl.name ledger,
hou.name operating_unit,
fc.precision,
nvl(fc.extended_precision,fc.precision) extended_precision
from
po_lines_all pla,
hr_operating_units hou,
gl_ledgers gl,
fnd_currencies fc
where
2=2 and
pla.org_id=hou.organization_id and
hou.set_of_books_id=gl.ledger_id and
gl.currency_code=fc.currency_code
) pol,
po_headers_all poh,
po_releases_all por,
mtl_material_transactions mmt,
mtl_transaction_accounts mta,
mtl_cst_actual_cost_details mcacd1,
mtl_cst_actual_cost_details mcacd2,
mtl_parameters mp,
rcv_shipment_headers rsh,
rcv_transactions rct,
ap_suppliers pov,
mtl_system_items_vl msi,
mtl_categories mca,
hr_locations_no_join hrl,
per_all_people_f papf
where
3=3 and
mmt.rcv_transaction_id = rct.transaction_id and
mmt.organization_id = rct.organization_id and
mmt.transaction_id = mta.transaction_id (+) and
mta.accounting_line_type (+) = 3 and
mcacd1.transaction_id (+) = mmt.transaction_id and
mcacd1.organization_id (+) = mmt.organization_id and
mcacd1.layer_id (+) = -1 and
mcacd1.cost_element_id (+) = 1 and
mcacd1.level_type (+) = 1 and
mcacd1.transaction_action_id (+) = mmt.transaction_action_id and
mcacd2.transaction_id (+) = mmt.transaction_id and
mcacd2.organization_id (+) = mmt.organization_id and
mcacd2.layer_id (+) = -1 and
mcacd2.cost_element_id (+) = 2 and
mcacd2.level_type (+) = 1 and
mcacd2.transaction_action_id (+) = mmt.transaction_action_id and
mp.organization_id = rct.organization_id and
mp.process_enabled_flag = 'N' and
rct.shipment_header_id = rsh.shipment_header_id and
rct.po_line_id = pol.po_line_id and
rct.po_header_id = poh.po_header_id and
rct.po_line_location_id = pll.line_location_id and
rct.po_distribution_id = pod.po_distribution_id and
pod.line_location_id = pll.line_location_id and
pod.destination_type_code = 'INVENTORY' and
pll.po_release_id = por.po_release_id(+) and
pol.item_id = msi.inventory_item_id(+) and
pol.inventory_organization_id = msi.organization_id(+) and
pol.category_id = mca.category_id and
rsh.vendor_id = poh.vendor_id and
poh.vendor_id = pov.vendor_id and
papf.person_id = poh.agent_id and
(papf.employee_number is not null or papf.npw_number is not null) and
trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and
decode(hr_security.view_all ,'Y' , 'TRUE', hr_security.show_record('PER_ALL_PEOPLE_F',papf.person_id, papf.person_type_id, papf.employee_number,papf.applicant_number )) = 'TRUE' and
decode(hr_general.get_xbg_profile,'Y', papf.business_group_id , hr_general.get_business_group_id) = papf.business_group_id and
pod.deliver_to_location_id = hrl.location_id(+) and
exists
(select
1
from
mtl_transaction_accounts mta1
where
mta1.transaction_id = mmt.transaction_id and
mta1.accounting_line_type = 6
)
union
select
pol.ledger,
pol.operating_unit,
mp.organization_code,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', mca.structure_id, null, mca.category_id, 'ALL', 'Y', 'VALUE') category,
msi.concatenated_segments item,
msi.description description,
pov.vendor_name vendor,
papf.full_name buyer,
decode(poh.type_lookup_code, 'BLANKET', poh.segment1||' - '||por.release_num, 'PLANNED', poh.segment1||' - '||por.release_num, poh.segment1) po_number_release,
poh.currency_code currency,
pol.line_num line,
rsh.shipment_num shipment,
rct.transaction_date receipt_date,
rsh.receipt_num receipt_number,
round(decode(rct.transaction_type, 'RETURN TO RECEIVING', rct.primary_quantity * -1, rct.primary_quantity), :p_qty_precision) quantity_received,
rct.primary_unit_of_measure unit,
rct.po_unit_price * (rct.source_doc_quantity / rct.primary_quantity ) + ((nvl(pod.nonrecoverable_tax,0)/ decode (pod.quantity_ordered,0,1,pod.quantity_ordered) )*(rct.source_doc_quantity/rct.primary_quantity))unit_price,
round(nvl(rct.currency_conversion_rate,1) * nvl(rct.po_unit_price* (rct.source_doc_quantity / rct.primary_quantity),0) + (( (nvl(pod.nonrecoverable_tax,0) * nvl(rct.currency_conversion_rate,1))/decode (pod.quantity_ordered,0,1, pod.quantity_ordered) ) *(rct.source_doc_quantity/rct.primary_quantity)), pol.extended_precision) po_functional_price,
round ( &p_select_wip, pol.extended_precision ) std_unit_cost_f,
0 material_cost_f,
0 moh_absorbed_per_unit,
po_poxrcppv_xmlp_pkg.c_price_varianceformula(round(nvl(rct.currency_conversion_rate,1) * nvl(rct.po_unit_price* (rct.source_doc_quantity / rct.primary_quantity),0) + (( (nvl(pod.nonrecoverable_tax,0) * nvl(rct.currency_conversion_rate,1))/decode (pod.quantity_ordered,0,1,pod.quantity_ordered)) *(rct.source_doc_quantity/rct.primary_quantity)), pol.extended_precision), round ( &p_select_wip, pol.extended_precision ), 0, round(decode(rct.transaction_type,'RETURN TO RECEIVING', rct.primary_quantity * -1,rct.primary_quantity), :p_qty_precision), pol.precision) c_price_variance,
mp.process_enabled_flag,
rct.transaction_id rct_id,
pol.item_id
from
po_distributions_all pod,
po_line_locations_all pll,
(select
pla.*,
(select
fspa.inventory_organization_id
from
financials_system_params_all fspa
where
hou.set_of_books_id=fspa.set_of_books_id and
pla.org_id=fspa.org_id) inventory_organization_id,
gl.name ledger,
hou.name operating_unit,
fc.precision,
nvl(fc.extended_precision,fc.precision) extended_precision
from
po_lines_all pla,
hr_operating_units hou,
gl_ledgers gl,
fnd_currencies fc
where
2=2 and
pla.org_id=hou.organization_id and
hou.set_of_books_id=gl.ledger_id and
gl.currency_code=fc.currency_code
) pol,
po_headers_all poh,
po_releases_all por,
rcv_transactions rct,
rcv_shipment_headers rsh,
ap_suppliers pov,
mtl_system_items_vl msi,
mtl_categories mca,
hr_locations_no_join hrl,
per_all_people_f papf,
mtl_parameters mp &p_from_wip
where
3=3 and
rct.shipment_header_id = rsh.shipment_header_id and
rct.po_line_id = pol.po_line_id and
rct.po_header_id = poh.po_header_id and
rct.po_line_location_id = pll.line_location_id and
pod.line_location_id = pll.line_location_id and
pod.po_distribution_id = rct.po_distribution_id and
pod.destination_type_code = 'SHOP FLOOR' and
pll.po_release_id = por.po_release_id(+) and
pol.item_id = msi.inventory_item_id(+) and
pol.inventory_organization_id = msi.organization_id(+) and
pol.category_id = mca.category_id and
rsh.vendor_id = poh.vendor_id and
poh.vendor_id = pov.vendor_id and
papf.person_id = poh.agent_id and
(papf.employee_number is not null or papf.npw_number is not null) and
trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and
decode(hr_security.view_all ,'Y' , 'TRUE', hr_security.show_record('PER_ALL_PEOPLE_F',papf.person_id, papf.person_type_id, papf.employee_number,papf.applicant_number )) = 'TRUE' and
decode(hr_general.get_xbg_profile,'Y', papf.business_group_id , hr_general.get_business_group_id) = papf.business_group_id and
pod.deliver_to_location_id = hrl.location_id(+) &p_where_wip and
mp.organization_id = rct.organization_id and
mp.process_enabled_flag='N'
union all
select
pol.ledger,
pol.operating_unit,
mp.organization_code,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', mca.structure_id, null, mca.category_id, 'ALL', 'Y', 'VALUE') category,
msi.concatenated_segments item,
msi.description description,
pov.vendor_name vendor,
papf.full_name buyer,
decode(poh.type_lookup_code, 'BLANKET', poh.segment1||' - '||por.release_num, 'PLANNED', poh.segment1||' - '||por.release_num, poh.segment1) po_number_release,
poh.currency_code currency,
pol.line_num line,
rsh.shipment_num shipment,
rct.transaction_date receipt_date,
rsh.receipt_num receipt_number,
round(decode(rct.transaction_type , 'RETURN TO RECEIVING' , rct.primary_quantity * -1 , 'RETURN TO VENDOR',rct.primary_quantity * -1, rct.primary_quantity) , :p_qty_precision) quantity_received,
rct.primary_unit_of_measure unit,
rct.po_unit_price * (rct.source_doc_quantity / rct.primary_quantity ) + ((nvl(pod.nonrecoverable_tax,0)/decode (pod.quantity_ordered,0,1, pod.quantity_ordered) )*(rct.source_doc_quantity/rct.primary_quantity)) unit_price,
round(nvl(rct.currency_conversion_rate,1) * nvl(rct.po_unit_price* (rct.source_doc_quantity / rct.primary_quantity),0) + (( (nvl(pod.nonrecoverable_tax,0) * nvl(rct.currency_conversion_rate,1))/decode (pod.quantity_ordered,0,1, pod.quantity_ordered) ) *(rct.source_doc_quantity/rct.primary_quantity)), pol.extended_precision) po_functional_price,
po_poxrcppv_xmlp_pkg.std_unit_cost_fformula(pol.item_id, decode(mp.process_enabled_flag, 'Y', rct.organization_id, pol.inventory_organization_id), rct.transaction_date, mp.process_enabled_flag, 0 , pol.extended_precision) std_unit_cost_f,
0 material_cost_f,
0 moh_absorbed_per_unit,
po_poxrcppv_xmlp_pkg.c_price_varianceformula( round(nvl(rct.currency_conversion_rate,1) * nvl(rct.po_unit_price* (rct.source_doc_quantity / rct.primary_quantity),0) + (( (nvl(pod.nonrecoverable_tax,0) * nvl(rct.currency_conversion_rate,1))/decode (pod.quantity_ordered,0,1,pod.quantity_ordered)) *(rct.source_doc_quantity/rct.primary_quantity)), pol.extended_precision), po_poxrcppv_xmlp_pkg.std_unit_cost_fformula(pol.item_id, decode(mp.process_enabled_flag, 'Y', rct.organization_id, pol.inventory_organization_id), rct.transaction_date, mp.process_enabled_flag, 0, pol.extended_precision), 0, round(decode(rct.transaction_type,'RETURN TO RECEIVING', rct.primary_quantity * -1,rct.primary_quantity), :p_qty_precision), pol.precision) c_price_variance,
mp.process_enabled_flag,
rct.transaction_id rct_id,
pol.item_id
from
po_distributions_all pod,
po_line_locations_all pll,
(select
pla.*,
(select
fspa.inventory_organization_id
from
financials_system_params_all fspa
where
hou.set_of_books_id=fspa.set_of_books_id and
pla.org_id=fspa.org_id) inventory_organization_id,
gl.name ledger,
hou.name operating_unit,
fc.precision,
nvl(fc.extended_precision,fc.precision) extended_precision
from
po_lines_all pla,
hr_operating_units hou,
gl_ledgers gl,
fnd_currencies fc
where
2=2 and
pla.org_id=hou.organization_id and
hou.set_of_books_id=gl.ledger_id and
gl.currency_code=fc.currency_code
) pol,
po_headers_all poh,
po_releases_all por,
rcv_shipment_headers rsh,
rcv_transactions rct,
ap_suppliers pov,
mtl_system_items_vl msi,
mtl_categories mca,
hr_locations_no_join hrl,
per_all_people_f papf,
mtl_parameters mp
where
3=3 and
rct.shipment_header_id = rsh.shipment_header_id and
rct.po_line_id = pol.po_line_id and
rct.po_header_id = poh.po_header_id and
rct.po_line_location_id = pll.line_location_id and
rct.po_distribution_id = pod.po_distribution_id and
pod.line_location_id = pll.line_location_id and
(nvl(pll.lcm_flag,'N') = 'N' or ( nvl(pll.lcm_flag,'N') = 'Y' and rct.lcm_shipment_line_id is null ) ) and
pod.destination_type_code in ('INVENTORY','SHOP FLOOR') and
rct.destination_type_code <> 'RECEIVING' and
pll.po_release_id = por.po_release_id(+) and
pol.item_id = msi.inventory_item_id(+) and
pol.inventory_organization_id = msi.organization_id(+) and
pol.category_id = mca.category_id and
rsh.vendor_id = poh.vendor_id and
poh.vendor_id = pov.vendor_id and
papf.person_id = poh.agent_id and
(papf.employee_number is not null or papf.npw_number is not null) and
trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and
decode(hr_security.view_all ,'Y' , 'TRUE', hr_security.show_record('PER_ALL_PEOPLE_F',papf.person_id, papf.person_type_id, papf.employee_number,papf.applicant_number )) = 'TRUE' and
decode(hr_general.get_xbg_profile,'Y', papf.business_group_id , hr_general.get_business_group_id) = papf.business_group_id and
pod.deliver_to_location_id = hrl.location_id(+) and
rct.organization_id = mp.organization_id and
mp.process_enabled_flag = 'Y'
union all
/* lcm-opm integration added below query bug 8642337, pmarada */
select distinct
pol.ledger,
pol.operating_unit,
mp.organization_code,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', mca.structure_id, null, mca.category_id, 'ALL', 'Y', 'VALUE') category,
msi.concatenated_segments item,
msi.description description,
pov.vendor_name vendor,
papf.full_name buyer,
decode(poh.type_lookup_code, 'BLANKET', poh.segment1||' - '||por.release_num, 'PLANNED', poh.segment1||' - '||por.release_num, poh.segment1) po_number_release,
poh.currency_code currency,
pol.line_num line,
rsh.shipment_num shipment,
glat.transaction_date receipt_date,
rsh.receipt_num receipt_number,
round(glat.primary_quantity,:p_qty_precision) quantity_received,
glat.primary_uom_code unit,
rct.po_unit_price * (rct.source_doc_quantity / rct.primary_quantity) + ((nvl(pod.nonrecoverable_tax,0)/pod.quantity_ordered)*(rct.source_doc_quantity/rct.primary_quantity)) unit_price,
round(nvl(rct.currency_conversion_rate,1) * nvl(rct.po_unit_price* (rct.source_doc_quantity / rct.primary_quantity),0) + (( (nvl(pod.nonrecoverable_tax,0) * nvl(rct.currency_conversion_rate,1))/pod.quantity_ordered) *(rct.source_doc_quantity/rct.primary_quantity)), pol.extended_precision) po_functional_price,
po_poxrcppv_xmlp_pkg.std_unit_cost_fformula(pol.item_id, decode(mp.process_enabled_flag, 'Y', rct.organization_id, pol.inventory_organization_id), rct.transaction_date, mp.process_enabled_flag, 0 , pol.extended_precision) std_unit_cost_f,
0 material_cost_f,
0 moh_absorbed_per_unit,
po_poxrcppv_xmlp_pkg.c_price_varianceformula( round(nvl(rct.currency_conversion_rate,1) * nvl(rct.po_unit_price* (rct.source_doc_quantity / rct.primary_quantity),0) + (( (nvl(pod.nonrecoverable_tax,0) * nvl(rct.currency_conversion_rate,1))/decode (pod.quantity_ordered,0,1,pod.quantity_ordered)) *(rct.source_doc_quantity/rct.primary_quantity)), pol.extended_precision), po_poxrcppv_xmlp_pkg.std_unit_cost_fformula(pol.item_id, decode(mp.process_enabled_flag, 'Y', rct.organization_id, pol.inventory_organization_id), rct.transaction_date, mp.process_enabled_flag, 0, pol.extended_precision), 0, round(decode(rct.transaction_type,'RETURN TO RECEIVING', rct.primary_quantity * -1,rct.primary_quantity), :p_qty_precision), pol.precision) c_price_variance,
mp.process_enabled_flag,
rct.transaction_id rct_id,
pol.item_id
from
po_distributions_all pod,
po_line_locations_all pll,
(select
pla.*,
(select
fspa.inventory_organization_id
from
financials_system_params_all fspa
where
hou.set_of_books_id=fspa.set_of_books_id and
pla.org_id=fspa.org_id) inventory_organization_id,
gl.name ledger,
hou.name operating_unit,
fc.precision,
nvl(fc.extended_precision,fc.precision) extended_precision
from
po_lines_all pla,
hr_operating_units hou,
gl_ledgers gl,
fnd_currencies fc
where
2=2 and
pla.org_id=hou.organization_id and
hou.set_of_books_id=gl.ledger_id and
gl.currency_code=fc.currency_code
) pol,
po_headers_all poh,
po_releases_all por,
gmf_lc_adj_transactions glat,
mtl_parameters mp,
rcv_shipment_headers rsh,
rcv_transactions rct,
ap_suppliers pov,
mtl_system_items_vl msi,
mtl_categories mca,
per_all_people_f papf
where
3=3 and
4=4 and
glat.rcv_transaction_id = rct.transaction_id and
glat.event_type in (16,17) and
mp.organization_id = glat.organization_id and
mp.process_enabled_flag = 'Y' and
rct.shipment_header_id = rsh.shipment_header_id and
rct.po_line_id = pol.po_line_id and
rct.po_header_id = poh.po_header_id and
rct.po_line_location_id = pll.line_location_id and
nvl(pll.lcm_flag,'N') = 'Y' and
rct.po_distribution_id = pod.po_distribution_id and
pll.po_release_id = por.po_release_id(+) and
pod.destination_type_code in ('INVENTORY') and
rct.destination_type_code <> 'RECEIVING' and
pol.item_id = msi.inventory_item_id(+) and
pol.inventory_organization_id = msi.organization_id(+) and
pol.category_id = mca.category_id and
rsh.vendor_id = pov.vendor_id and
papf.person_id = poh.agent_id and
trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and
decode(hr_security.view_all ,'Y' , 'TRUE', hr_security.show_record('PER_ALL_PEOPLE_F',papf.person_id, papf.person_type_id, papf.employee_number,papf.applicant_number )) = 'TRUE' and
decode(hr_general.get_xbg_profile,'Y', papf.business_group_id , hr_general.get_business_group_id) = papf.business_group_id
) x |
