[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_PurchasePrice(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_item_price_rec IN dpp_txn_hdr_rec_type
,p_item_cost_tbl IN dpp_item_cost_tbl_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_PurchasePrice';
l_exe_update_rec DPP_ExecutionDetails_PVT.DPP_EXE_UPDATE_REC_TYPE;
l_status_Update_tbl DPP_ExecutionDetails_PVT.dpp_status_Update_tbl_type;
SELECT poh.segment1 po_num,
poh.currency_code currency_code,
pol.line_num line_num,
pol.quantity quantity,
poh.vendor_id vendor_id,
poh.vendor_site_id vendor_site_id,
poh.agent_id agent_id,
poh.ship_to_location_id ship_loc,
poh.bill_to_location_id bill_loc,
poh.type_lookup_code type_lookup_code,
nvl(por.revision_num ,poh.revision_num ) revision_num,
por.release_num,
pll.shipment_num,
nvl(pll.price_override,pol.unit_price) unit_price
FROM po_headers_all poh
JOIN po_lines_all pol
ON poh.po_header_id = pol.po_header_id
AND nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED')
AND nvl(poh.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED')
AND nvl(pol.cancel_flag,'N') = 'N'
AND nvl(poh.cancel_flag,'N') = 'N'
AND nvl(poh.frozen_flag,'N') = 'N'
AND poh.org_id = pol.org_id
AND poh.enabled_flag = 'Y'
AND poh.org_id = p_org_id
AND poh.vendor_id = p_vendor_id
AND poh.authorization_status IN ('APPROVED','REQUIRES REAPPROVAL')
and pol.item_id = p_inventory_item_id
AND ((nvl(pol.ALLOW_PRICE_OVERRIDE_FLAG,'N') = 'Y' AND poh.type_lookup_code = 'BLANKET')
OR (poh.type_lookup_code = 'STANDARD'))
LEFT OUTER JOIN po_line_locations_all pll
ON pol.po_line_id = pll.po_line_id
AND nvl(pll.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED')
AND pll.quantity_received = 0
AND nvl(pll.cancel_flag,'N') = 'N'
AND pol.org_id = pll.org_id
LEFT OUTER JOIN po_releases_all por
ON pll.po_release_id = por.po_release_id
AND nvl(por.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED')
AND nvl(por.frozen_flag,'N') = 'N'
AND por.authorization_status IN ('APPROVED','REQUIRES REAPPROVAL')
AND nvl(por.cancel_flag,'N') = 'N'
AND pll.org_id = por.org_id;
SELECT concatenated_segments item_number
FROM mtl_system_items_kfv msi
WHERE inventory_item_id = p_inventory_item_id
AND ROWNUM = 1;
SAVEPOINT Update_PurchasePrice_PVT;
SELECT user_name
INTO l_user_name
FROM fnd_user
WHERE user_id = l_item_price_rec.last_updated_by;
SELECT frv.responsibility_id
INTO l_responsibility_id
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_responsibility_vl frv,
fnd_user_resp_groups_direct furgd
WHERE fpo.profile_option_name IN ('ORG_ID', 'DEFAULT_ORG_ID')
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.profile_option_value = TO_CHAR (l_item_price_rec.org_id)
AND fpov.level_id = 10004
AND furgd.user_id = fpov.level_value
AND frv.application_id = 9000
AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
AND NVL (frv.end_date, TRUNC (SYSDATE))
AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date, TRUNC (SYSDATE))
AND NVL (furgd.end_date, TRUNC (SYSDATE))
AND furgd.responsibility_id = frv.responsibility_id
AND furgd.responsibility_application_id = frv.application_id
AND furgd.user_id = l_item_price_rec.last_updated_by
AND ROWNUM = 1;
SELECT frv.responsibility_id
INTO l_responsibility_id
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_responsibility_vl frv,
fnd_user_resp_groups_direct furgd,
per_security_profiles psp
WHERE fpo.profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.profile_option_value = TO_CHAR (psp.security_profile_id)
AND ((psp.view_all_organizations_flag = 'Y'
AND psp.business_group_id IS NOT NULL
AND EXISTS (SELECT 1
FROM hr_operating_units hr
WHERE hr.business_group_id = psp.business_group_id
AND hr.usable_flag IS NULL
AND hr.organization_id =
l_item_price_rec.org_id))
OR (psp.view_all_organizations_flag = 'Y'
AND psp.business_group_id IS NULL)
OR (NVL (psp.view_all_organizations_flag, 'N') <> 'Y'
AND EXISTS (SELECT 1
FROM per_organization_list per,
hr_operating_units hr
WHERE per.security_profile_id = psp.security_profile_id
AND hr.organization_id = per.organization_id
AND hr.usable_flag IS NULL
AND per.organization_id = l_item_price_rec.org_id)))
AND fpov.level_id = 10004
AND furgd.user_id = fpov.level_value
AND frv.application_id = 9000
AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
AND NVL (frv.end_date, TRUNC (SYSDATE))
AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date,TRUNC (SYSDATE))
AND NVL (furgd.end_date, TRUNC (SYSDATE))
AND furgd.responsibility_id = frv.responsibility_id
AND furgd.responsibility_application_id = frv.application_id
AND furgd.user_id = l_item_price_rec.last_updated_by
AND ROWNUM = 1;
SELECT frv.responsibility_id
INTO l_responsibility_id
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_responsibility_vl frv,
fnd_user_resp_groups_direct furgd
WHERE fpo.profile_option_name IN ('ORG_ID', 'DEFAULT_ORG_ID')
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.profile_option_value = TO_CHAR (l_item_price_rec.org_id)
AND fpov.level_id = 10003
AND frv.responsibility_id = fpov.level_value
AND frv.application_id = 9000
AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
AND NVL (frv.end_date, TRUNC (SYSDATE))
AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date, TRUNC (SYSDATE))
AND NVL (furgd.end_date, TRUNC (SYSDATE))
AND furgd.responsibility_id = frv.responsibility_id
AND furgd.responsibility_application_id = frv.application_id
AND furgd.user_id = l_item_price_rec.last_updated_by
AND ROWNUM = 1;
SELECT frv.responsibility_id
INTO l_responsibility_id
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_responsibility_vl frv,
fnd_user_resp_groups_direct furgd,
per_security_profiles psp
WHERE fpo.profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.profile_option_value = TO_CHAR (psp.security_profile_id)
AND ((psp.view_all_organizations_flag = 'Y'
AND psp.business_group_id IS NOT NULL
AND EXISTS (SELECT 1
FROM hr_operating_units hr
WHERE hr.business_group_id = psp.business_group_id
AND hr.usable_flag IS NULL
AND hr.organization_id = l_item_price_rec.org_id))
OR (psp.view_all_organizations_flag = 'Y'
AND psp.business_group_id IS NULL)
OR (NVL (psp.view_all_organizations_flag, 'N') <> 'Y'
AND EXISTS (SELECT 1
FROM per_organization_list per,
hr_operating_units hr
WHERE per.security_profile_id = psp.security_profile_id
AND hr.organization_id = per.organization_id
AND hr.usable_flag IS NULL
AND per.organization_id = l_item_price_rec.org_id)))
AND fpov.level_id = 10003
AND frv.responsibility_id = fpov.level_value
AND frv.application_id = 9000
AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
AND NVL (frv.end_date, TRUNC (SYSDATE))
AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date,TRUNC (SYSDATE))
AND NVL (furgd.end_date, TRUNC (SYSDATE))
AND furgd.responsibility_id = frv.responsibility_id
AND furgd.responsibility_application_id = frv.application_id
AND furgd.user_id = l_item_price_rec.last_updated_by
AND ROWNUM = 1;
SELECT frv.responsibility_id
INTO l_responsibility_id
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_responsibility_vl frv,
fnd_user_resp_groups_direct furgd
WHERE fpo.profile_option_name IN ('ORG_ID', 'DEFAULT_ORG_ID')
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.profile_option_value = TO_CHAR (l_item_price_rec.org_id)
AND fpov.level_id = 10002
AND frv.application_id = fpov.level_value
AND frv.application_id = 9000
AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
AND NVL (frv.end_date, TRUNC (SYSDATE))
AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date, TRUNC (SYSDATE))
AND NVL (furgd.end_date, TRUNC (SYSDATE))
AND furgd.responsibility_id = frv.responsibility_id
AND furgd.responsibility_application_id = frv.application_id
AND furgd.user_id = l_item_price_rec.last_updated_by
AND ROWNUM = 1;
SELECT frv.responsibility_id
INTO l_responsibility_id
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_responsibility_vl frv,
fnd_user_resp_groups_direct furgd,
per_security_profiles psp
WHERE fpo.profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.profile_option_value = TO_CHAR (psp.security_profile_id)
AND ((psp.view_all_organizations_flag = 'Y'
AND psp.business_group_id IS NOT NULL
AND EXISTS (SELECT 1
FROM hr_operating_units hr
WHERE hr.business_group_id = psp.business_group_id
AND hr.usable_flag IS NULL
AND hr.organization_id = l_item_price_rec.org_id))
OR (psp.view_all_organizations_flag = 'Y'
AND psp.business_group_id IS NULL)
OR (NVL (psp.view_all_organizations_flag, 'N') <> 'Y'
AND EXISTS (SELECT 1
FROM per_organization_list per,
hr_operating_units hr
WHERE per.security_profile_id = psp.security_profile_id
AND hr.organization_id = per.organization_id
AND hr.usable_flag IS NULL
AND per.organization_id = l_item_price_rec.org_id)))
AND fpov.level_id = 10002
AND frv.application_id = fpov.level_value
AND frv.application_id = 9000
AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
AND NVL (frv.end_date, TRUNC (SYSDATE))
AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date,TRUNC (SYSDATE))
AND NVL (furgd.end_date, TRUNC (SYSDATE))
AND furgd.responsibility_id = frv.responsibility_id
AND furgd.responsibility_application_id = frv.application_id
AND furgd.user_id = l_item_price_rec.last_updated_by
AND ROWNUM = 1;
SELECT frv.responsibility_id
INTO l_responsibility_id
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_responsibility_vl frv,
fnd_user_resp_groups_direct furgd
WHERE fpo.profile_option_name IN ('ORG_ID', 'DEFAULT_ORG_ID')
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.profile_option_value = TO_CHAR (l_item_price_rec.org_id)
AND fpov.level_id = 10001
AND fpov.level_value = 0
AND frv.application_id = 9000
AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
AND NVL (frv.end_date, TRUNC (SYSDATE))
AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date, TRUNC (SYSDATE))
AND NVL (furgd.end_date, TRUNC (SYSDATE))
AND furgd.responsibility_id = frv.responsibility_id
AND furgd.responsibility_application_id = frv.application_id
AND furgd.user_id = l_item_price_rec.last_updated_by
AND ROWNUM = 1;
SELECT frv.responsibility_id
INTO l_responsibility_id
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_responsibility_vl frv,
fnd_user_resp_groups_direct furgd,
per_security_profiles psp
WHERE fpo.profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.profile_option_value = TO_CHAR (psp.security_profile_id)
AND ((psp.view_all_organizations_flag = 'Y'
AND psp.business_group_id IS NOT NULL
AND EXISTS (SELECT 1
FROM hr_operating_units hr
WHERE hr.business_group_id = psp.business_group_id
AND hr.usable_flag IS NULL
AND hr.organization_id = l_item_price_rec.org_id))
OR (psp.view_all_organizations_flag = 'Y'
AND psp.business_group_id IS NULL)
OR (NVL (psp.view_all_organizations_flag, 'N') <> 'Y'
AND EXISTS (SELECT 1
FROM per_organization_list per,
hr_operating_units hr
WHERE per.security_profile_id = psp.security_profile_id
AND hr.organization_id = per.organization_id
AND hr.usable_flag IS NULL
AND per.organization_id = l_item_price_rec.org_id)))
AND fpov.level_id = 10001
AND fpov.level_value = 0
AND frv.application_id = 9000
AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
AND NVL (frv.end_date, TRUNC (SYSDATE))
AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date,TRUNC (SYSDATE))
AND NVL (furgd.end_date, TRUNC (SYSDATE))
AND furgd.responsibility_id = frv.responsibility_id
AND furgd.responsibility_application_id = frv.application_id
AND furgd.user_id = l_item_price_rec.last_updated_by
AND ROWNUM = 1;
DPP_UTILITY_PVT.debug_message('Price Protection responsibility not available for Last updated user'||l_user_name);
FND_GLOBAL.APPS_INITIALIZE(l_item_price_rec.last_updated_by,l_responsibility_id,l_dpp_application_id);
l_status_Update_tbl(i).transaction_line_id := l_item_cost_tbl(i).transaction_line_id;
l_status_Update_tbl(i).update_status := 'Y'; -- defaulting to Y so that lines without POs can be updated to Y
DPP_UTILITY_PVT.debug_message('PO Line Price Update for item: ' || l_item_cost_tbl(i).item_number
|| ' for PO Number ' ||po_rec.po_num|| 'start');
l_status_Update_tbl(i).update_status := 'N';
INSERT INTO DPP_OUTPUT_XML_GT(Item_Number,
Document_Type,
Document_Number,
Line_Number,
Reason_For_Failure)
VALUES(l_item_cost_tbl(i).item_number,
l_po_details_tbl(i).Document_Type,
l_po_details_tbl(i).Document_Number,
l_po_details_tbl(i).Line_Number,
l_po_details_tbl(i).Reason_for_failure);
DPP_UTILITY_PVT.debug_message('No update required since PO line price is same for '||po_rec.po_num);
DPP_UTILITY_PVT.debug_message('Price to be updated for item: ' || l_item_cost_tbl(i).item_number || ' is ' ||l_new_price);
l_result := PO_CHANGE_API1_S.update_po(x_po_number => po_rec.po_num,
x_release_number => po_rec.release_num,
x_revision_number => po_rec.revision_num,
x_line_number => po_rec.line_num,
x_shipment_number => po_rec.shipment_num,
new_quantity => NULL,
new_price => l_new_price,
new_promised_date => NULL,
new_need_by_date => NULL,
launch_approvals_flag => 'Y', -- launch approval through workflow
update_source => 'Oracle Price Protection',
version => 1.0,
x_override_date => NULL,
x_api_errors => l_api_errors,
p_buyer_name => NULL,
p_secondary_quantity => NULL,
p_preferred_grade => NULL,
p_org_id => l_item_price_rec.org_id
);
l_status_Update_tbl(i).update_status := 'N';
INSERT INTO DPP_OUTPUT_XML_GT(Item_Number,
Document_Type,
Document_Number,
Line_Number,
Reason_For_Failure)
VALUES(l_item_cost_tbl(i).item_number, l_po_details_tbl(i).Document_Type,
l_po_details_tbl(i).Document_Number,
l_po_details_tbl(i).Line_Number,
l_po_details_tbl(i).Reason_for_failure);
l_status_Update_tbl(i).update_status := 'Y';
DPP_UTILITY_PVT.debug_message('PO Line Price Update for item: ' || l_item_cost_tbl(i).item_number
|| ' for PO Number ' ||po_rec.po_num|| 'end');
l_exe_update_rec.execution_status := 'SUCCESS';
l_exe_update_rec.execution_status := 'WARNING';
l_queryCtx := dbms_xmlquery.newContext('SELECT '||l_Transaction_Number||' TXNNUMBER,
CURSOR (Select Item_Number ITEMNUMBER,
Document_Type POTYPE,
Document_Number PONUMBER,
Line_Number LINENUMBER,
Reason_For_Failure REASON
from DPP_OUTPUT_XML_GT
where Reason_For_Failure IS NOT NULL) TRANSACTION from dual'
);
l_queryCtx := dbms_xmlquery.newContext('SELECT '||l_Transaction_Number||' TXNNUMBER from dual');
fnd_message.set_token('ROUTINE', 'DPP_PURCHASEPRICE_PVT.Update_PurchasePrice-XML Generation');
l_exe_update_rec.Transaction_Header_ID := l_item_price_rec.Transaction_Header_ID;
l_exe_update_rec.Org_ID := l_item_price_rec.Org_ID;
l_exe_update_rec.Execution_Detail_ID := l_item_price_rec.Execution_Detail_ID;
l_exe_update_rec.Output_XML := l_output_xml;
l_exe_update_rec.Execution_End_Date := SYSDATE;
l_exe_update_rec.Provider_Process_Id := l_item_price_rec.Provider_Process_Id;
l_exe_update_rec.Provider_Process_Instance_id := l_item_price_rec.Provider_Process_Instance_id;
l_exe_update_rec.Last_Updated_By := l_item_price_rec.Last_Updated_By;
DPP_ExecutionDetails_PVT.Update_ExecutionDetails(p_api_version => l_api_version
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_EXE_UPDATE_rec => l_exe_update_rec
,p_status_Update_tbl => l_status_Update_tbl
);
DPP_UTILITY_PVT.debug_message('Status after update execution details: ' || l_return_status);
ROLLBACK TO UPDATE_PURCHASEPRICE_PVT;
ROLLBACK TO UPDATE_PURCHASEPRICE_PVT;
ROLLBACK TO UPDATE_PURCHASEPRICE_PVT;
END Update_PurchasePrice;
/* Select PO Lines with Partial Receipts */
SELECT
poh.segment1||DECODE(por.release_num,NULL,NULL,'-'||por.release_num) doc_num,
poh.type_lookup_code,
pol.line_num line_num,
flv.meaning authorization_status
FROM
po_headers_all poh
JOIN
po_lines_all pol
ON
poh.po_header_id = pol.po_header_id AND
nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
nvl(poh.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
nvl(pol.cancel_flag,'N') = 'N' AND
nvl(poh.cancel_flag,'N') = 'N' AND
poh.org_id = pol.org_id AND
poh.org_id = p_org_id AND
pol.item_id = p_inventory_item_id AND
poh.vendor_id = p_vendor_id AND
poh.enabled_flag = 'Y'
INNER JOIN
po_line_locations_all pll
ON
pol.po_line_id = pll.po_line_id AND
(pll.quantity_received > 0 OR (nvl(pol.ALLOW_PRICE_OVERRIDE_FLAG,'N') = 'N' AND poh.type_lookup_code = 'BLANKET')) AND
nvl(pll.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
nvl(pll.cancel_flag,'N') = 'N'
LEFT OUTER JOIN
po_releases_all por
ON
pll.po_release_id = por.po_release_id AND
nvl(por.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
nvl(por.cancel_flag,'N') = 'N' AND
pol.org_id = pll.org_id AND
pll.org_id = por.org_id
INNER JOIN
fnd_lookup_values flv
ON
flv.lookup_type = 'AUTHORIZATION STATUS' AND
flv.language = USERENV('LANG') AND
nvl(por.authorization_status ,poh.authorization_status) = flv.lookup_code
UNION
/* Select POs Pending Approval, Incomplete and Pre-Approved POs */
SELECT
poh.segment1 doc_num,
poh.type_lookup_code,
pol.line_num line_num,
flv.meaning authorization_status
FROM
po_headers_all poh
JOIN
po_lines_all pol
ON
poh.po_header_id = pol.po_header_id AND
nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
nvl(poh.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
NVL(poh.authorization_status,'NONE') NOT IN ('APPROVED','REQUIRES REAPPROVAL') AND
nvl(pol.cancel_flag,'N') = 'N' AND
nvl(poh.cancel_flag,'N') = 'N' AND
poh.org_id = pol.org_id AND
poh.org_id = p_org_id AND
pol.item_id = p_inventory_item_id AND
poh.vendor_id = p_vendor_id AND
poh.enabled_flag = 'Y'
INNER JOIN
fnd_lookup_values flv
ON
flv.lookup_type = 'AUTHORIZATION STATUS' AND
flv.language = USERENV('LANG') AND
nvl(poh.authorization_status,'INCOMPLETE') = flv.lookup_code
UNION
/* Select Frozen, Incomplete, In Process etc. releases */
SELECT
poh.segment1||DECODE(por.release_num,NULL,NULL,'-'||por.release_num) doc_num,
poh.type_lookup_code,
pol.line_num line_num,
flv.meaning authorization_status
FROM
po_headers_all poh,
po_lines_all pol,
po_line_locations_all pll,
po_releases_all por,
fnd_lookup_values flv
WHERE
poh.po_header_id = pol.po_header_id AND
nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
nvl(poh.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
nvl(pol.cancel_flag,'N') = 'N' AND
nvl(poh.cancel_flag,'N') = 'N' AND
poh.org_id = pol.org_id AND
poh.org_id = p_org_id AND
pol.item_id = p_inventory_item_id AND
poh.vendor_id = p_vendor_id AND
poh.enabled_flag = 'Y' AND
pol.po_line_id = pll.po_line_id AND
pll.po_release_id = por.po_release_id AND
nvl(por.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
nvl(por.cancel_flag,'N') = 'N' AND
(nvl(por.frozen_flag, 'N') = 'Y' OR NVL(por.authorization_status,'NONE') NOT IN ('APPROVED','REQUIRES REAPPROVAL')) AND
pol.org_id = pll.org_id AND
pll.org_id = por.org_id AND
flv.lookup_type = 'AUTHORIZATION STATUS' AND
flv.language = USERENV('LANG') AND
NVL(por.authorization_status,'INCOMPLETE') = flv.lookup_code
/*Select the Frozen Pos*/
UNION
SELECT
poh.segment1 doc_num,
poh.type_lookup_code,
pol.line_num line_num,
flv.meaning authorization_status
FROM
po_headers_all poh
JOIN
po_lines_all pol
ON
poh.po_header_id = pol.po_header_id AND
nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
nvl(poh.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
nvl(pol.cancel_flag,'N') = 'N' AND
nvl(poh.cancel_flag,'N') = 'N' AND
nvl(poh.frozen_flag,'N') = 'Y' AND
poh.org_id = pol.org_id AND
poh.org_id = p_org_id AND
pol.item_id = p_inventory_item_id AND
poh.vendor_id = p_vendor_id AND
poh.enabled_flag = 'Y'
INNER JOIN
fnd_lookup_values flv
ON
flv.lookup_type = 'AUTHORIZATION STATUS' AND
flv.language = USERENV('LANG') AND
nvl(poh.authorization_status,'INCOMPLETE') = flv.lookup_code
/*Select the Blanket purchase agreements */
UNION
SELECT
poh.segment1 doc_num,
poh.type_lookup_code,
pol.line_num line_num,
flv.meaning authorization_status
FROM
po_headers_all poh
JOIN
po_lines_all pol
ON
poh.po_header_id = pol.po_header_id AND
nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
nvl(poh.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
nvl(pol.cancel_flag,'N') = 'N' AND
nvl(poh.cancel_flag,'N') = 'N' AND
poh.type_lookup_code = 'BLANKET' AND
poh.org_id = pol.org_id AND
poh.org_id = p_org_id AND
pol.item_id = p_inventory_item_id AND
poh.vendor_id = p_vendor_id AND
poh.enabled_flag = 'Y'
INNER JOIN
fnd_lookup_values flv
ON
flv.lookup_type = 'AUTHORIZATION STATUS' AND
flv.language = USERENV('LANG') AND
nvl(poh.authorization_status,'INCOMPLETE') = flv.lookup_code
/* Select POs if there are pending receiving transactions for the shipment */
UNION
SELECT
poh.segment1||DECODE(por.release_num,NULL,NULL,'-'||por.release_num) doc_num,
poh.type_lookup_code,
pol.line_num line_num,
flv.meaning authorization_status
FROM
po_headers_all poh
JOIN
po_lines_all pol
ON
poh.po_header_id = pol.po_header_id AND
nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
nvl(poh.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
nvl(pol.cancel_flag,'N') = 'N' AND
nvl(poh.cancel_flag,'N') = 'N' AND
poh.org_id = pol.org_id AND
poh.org_id = p_org_id AND
pol.item_id = p_inventory_item_id AND
poh.vendor_id = p_vendor_id AND
poh.enabled_flag = 'Y'
INNER JOIN
po_line_locations_all pll
ON
pol.po_line_id = pll.po_line_id AND
pol.org_id = pll.org_id AND
nvl(pll.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
--pll.quantity_received > 0 AND
nvl(pll.cancel_flag,'N') = 'N'
INNER JOIN
rcv_transactions_interface rti
ON
rti.po_line_location_id = pll.line_location_id AND
rti.transaction_status_code = 'PENDING'
LEFT OUTER JOIN
po_releases_all por
ON
pll.po_release_id = por.po_release_id AND
nvl(por.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') AND
nvl(por.cancel_flag,'N') = 'N' AND
pll.org_id = por.org_id
INNER JOIN
fnd_lookup_values flv
ON
flv.lookup_type = 'AUTHORIZATION STATUS' AND
flv.language = USERENV('LANG') AND
nvl(por.authorization_status ,poh.authorization_status) = flv.lookup_code;
SELECT msi.concatenated_segments
FROM mtl_system_items_kfv msi
WHERE inventory_item_id = p_inventory_item_id
AND ROWNUM = 1;
SELECT name
INTO l_operating_unit_name
FROM hr_operating_units
WHERE organization_id = l_po_notify_hdr_rec.org_id;