[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT FSP.INVENTORY_ORGANIZATION_ID,
POSP.EXPENSE_ACCRUAL_CODE,
NVL(FSP.req_encumbrance_flag, 'N'), --bug2880298
NVL(POSP.enforce_vendor_hold_flag, 'N') --bug2880298
INTO x_inventory_org_id,
x_expense_accrual_code,
l_req_enc_flag, --bug2880298
l_enf_vendor_hold_flag --bug2880298
FROM FINANCIALS_SYSTEM_PARAMETERS FSP,
PO_SYSTEM_PARAMETERS POSP;
SELECT GPS.PERIOD_NAME
INTO x_period_name
FROM GL_PERIOD_STATUSES GPS,
FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE GPS.APPLICATION_ID = 101
AND GPS.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID
AND GPS.ADJUSTMENT_PERIOD_FLAG = 'N'
AND TRUNC(SYSDATE) BETWEEN TRUNC(GPS.START_DATE)
AND TRUNC(GPS.END_DATE)
AND NVL(FSP.PURCH_ENCUMBRANCE_FLAG, 'N') = 'Y'; -- Bug #2206125
select segment1 into x_req_num
from po_requisition_headers
where requisition_header_id = po_req_lines.requisition_header_id;
select line_num into x_req_line_num
from po_requisition_lines
where requisition_line_id = po_req_lines.requisition_line_id;
/*Bug 7609663: Update the approved_flag in po_releses_all and po_line_locations_all */
UPDATE po_releases_all por
SET por.authorization_status = 'INCOMPLETE',
por.approved_flag = 'N'
WHERE por.po_release_id = x_po_release_id;
UPDATE po_line_locations_all plla
SET plla.approved_flag = 'N'
WHERE plla.po_release_id = x_po_release_id;
PO_KANBAN_SV.Update_Card_Status ('IN_PROCESS',
'RELEASE',
x_po_release_id,
x_kanban_return_status);
select segment1 into x_req_num
from po_requisition_headers
where requisition_header_id = old_po_req_line.requisition_header_id;
select line_num into x_req_line_num
from po_requisition_lines
where requisition_line_id = old_po_req_line.requisition_line_id;
UPDATE po_releases_all por
SET por.authorization_status = 'INCOMPLETE',
por.approved_flag = 'N',
por.approved_date = NULL
WHERE por.po_release_id = x_po_release_id;
UPDATE po_line_locations_all plla
SET plla.approved_flag = 'N',
plla.approved_date = NULL,
plla.lcm_flag = NULL
WHERE plla.po_release_id = x_po_release_id
AND plla.lcm_flag = 'Y';
UPDATE po_distributions_all pda
SET pda.lcm_flag = NULL
WHERE pda.line_location_id = x_line_location_id
AND pda.lcm_flag = 'Y';
The CLOSE PO API does not delete the backing requisition supply. It just
creates the PO supply if it does not exist. This causes supply manipulation
order to go out of sync. Hence now moving the supply creation code before
the close PO API. The MAINTAIN_SUPPLY will delete the req supply first and
create the PO Supply. Later the Close PO API, would delete the supply if
required. Thus the supply manipulation order is maintained.
*/
MAINTAIN_SUPPLY (po_req_lines);
'UPDATE_CLOSE_STATE',
NULL, -- p_reason
'PO', -- p_calling_mode
'N',
x_return_code,
'Y'))) THEN
APP_EXCEPTION.Raise_Exception;
/*Bug 7609663: Update the approved_flag in po_releses_all and po_line_locations_all */
UPDATE po_releases_all por
SET por.authorization_status = 'INCOMPLETE',
por.approved_flag = 'N'
WHERE por.po_release_id = x_po_release_id;
UPDATE po_line_locations_all plla
SET plla.approved_flag = 'N'
WHERE plla.po_release_id = x_po_release_id;
PO_KANBAN_SV.Update_Card_Status ('IN_PROCESS',
'RELEASE',
x_po_release_id,
x_kanban_return_status);
select segment1 into x_req_num
from po_requisition_headers
where requisition_header_id = old_po_req_line.requisition_header_id;
select line_num into x_req_line_num
from po_requisition_lines
where requisition_line_id = old_po_req_line.requisition_line_id;
UPDATE po_releases_all por
SET por.authorization_status = 'INCOMPLETE',
por.approved_flag = 'N',
por.approved_date = NULL
WHERE por.po_release_id = x_po_release_id;
UPDATE po_line_locations_all plla
SET plla.approved_flag = 'N',
plla.approved_date = NULL,
plla.lcm_flag = NULL
WHERE plla.po_release_id = x_po_release_id
AND plla.lcm_flag = 'Y';
UPDATE po_distributions_all pda
SET pda.lcm_flag = NULL
WHERE pda.line_location_id = x_line_location_id
AND pda.lcm_flag = 'Y';
SELECT NVL(PURCH_ENCUMBRANCE_FLAG,'N')
INTO x_purch_encumbrance_flag
FROM FINANCIALS_SYSTEM_PARAMETERS;
SELECT Decode(acceptance_required_flag,'D','Y','Y','Y','S','Y','N')
INTO x_acceptance_required_flag
FROM po_system_parameters;
SELECT PO_RELEASES_S.NEXTVAL
INTO x_po_release_id
FROM SYS.DUAL;
SELECT NVL(MAX(RELEASE_NUM) +1,1)
INTO x_release_num
FROM PO_RELEASES
WHERE PO_HEADER_ID = req_line.blanket_po_header_id;
Same would be inserted into po_releases_all table */
SELECT PAY_ON_CODE, SHIPPING_CONTROL
INTO x_pay_on_code, x_shipping_control
FROM PO_HEADERS
WHERE PO_HEADER_ID = req_line.blanket_po_header_id;
INSERT INTO PO_RELEASES
(PO_RELEASE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
PO_HEADER_ID,
RELEASE_NUM,
PCARD_ID, --Supplier Pcard FPH
AGENT_ID,
RELEASE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REVISION_NUM,
APPROVED_FLAG,
APPROVED_DATE,
AUTHORIZATION_STATUS,
PRINT_COUNT,
CANCEL_FLAG,
RELEASE_TYPE,
PAY_ON_CODE,
GOVERNMENT_CONTEXT,
DOCUMENT_CREATION_METHOD, -- PO DBI FPJ
ORG_ID, --
tax_attribute_update_code, --< eTax Integration R12>
SHIPPING_CONTROL, /* Bug 6454219 */
ACCEPTANCE_REQUIRED_FLAG -- Bug 7668178
)
VALUES (x_po_release_id, -- :po_release_id
sysdate,
req_line.last_updated_by, -- :cpo_last_updated_by
req_line.blanket_po_header_id, -- :po_header_id
x_release_num, -- :release_num
req_line.pcard_id, -- :pcard_id Supplier Pcard FPH
req_line.agent_id, -- :agent_id
SYSDATE, --
sysdate,
req_line.last_updated_by, -- :cpo_last_updated_by
req_line.last_update_login, -- :last_update_login,
0,
DECODE(x_authorization_status,
'APPROVED','Y','N'), -- 'N'
DECODE(x_authorization_status,
'APPROVED', sysdate, NULL), -- approved date
x_authorization_status, -- :'INCOMPLETE'
0,
'N',
'BLANKET',
x_pay_on_code,
null, -- :government_context
-- Bug 3648268 Use lookup code instead of hardcoded value
'CREATE_RELEASES', -- Document Creation Method PO DBI FPJ
req_line.org_id, --
'CREATE', --
x_shipping_control, /* Bug 6454219 */
x_acceptance_required_flag -- Bug7668178
);
SELECT PO_LINE_LOCATIONS_S.NEXTVAL
INTO x_line_location_id
FROM SYS.DUAL;
SELECT NVL(MAX(SHIPMENT_NUM) +1,1)
INTO x_shipment_num
FROM PO_LINE_LOCATIONS
WHERE PO_RELEASE_ID = x_po_release_id;
SELECT NVL(SHIP_TO_LOCATION_ID,LOCATION_ID)
INTO x_ship_to_location_id
FROM HR_LOCATIONS
WHERE LOCATION_ID = req_line.deliver_to_location_id;
select location_id
into x_ship_to_location_id
FROM HZ_LOCATIONS
where location_id = req_line.deliver_to_location_id;
select decode(price_break_lookup_code, 'CUMULATIVE', 'Y', 'N'),
order_type_lookup_code,
matching_basis,
unit_meas_lookup_code
into l_price_break_type,
l_value_basis,
l_matching_basis,
l_unit_meas_lookup_code
from po_lines_all
where po_line_id = l_po_line_id;
SELECT FC.EXTENDED_PRECISION
INTO x_ext_precision
FROM PO_HEADERS POH, FND_CURRENCIES FC
WHERE POH.PO_HEADER_ID = req_line.blanket_po_header_id
AND POH.CURRENCY_CODE = FC.CURRENCY_CODE;
INSERT INTO PO_LINE_LOCATIONS(
LINE_LOCATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
PO_HEADER_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PO_LINE_ID,
QUANTITY,
QUANTITY_RECEIVED,
QUANTITY_ACCEPTED,
QUANTITY_REJECTED,
QUANTITY_BILLED,
QUANTITY_CANCELLED,
SHIP_TO_LOCATION_ID,
NEED_BY_DATE,
PROMISED_DATE,
--togeorge 09/28/2000
--added note to receiver
note_to_receiver,
APPROVED_FLAG,
APPROVED_DATE,
PO_RELEASE_ID,
CANCEL_FLAG,
CLOSED_CODE,
PRICE_OVERRIDE,
ENCUMBERED_FLAG,
SHIPMENT_TYPE,
SHIPMENT_NUM,
INSPECTION_REQUIRED_FLAG,
RECEIPT_REQUIRED_FLAG,
GOVERNMENT_CONTEXT,
DAYS_EARLY_RECEIPT_ALLOWED,
DAYS_LATE_RECEIPT_ALLOWED,
ENFORCE_SHIP_TO_LOCATION_CODE,
SHIP_TO_ORGANIZATION_ID,
INVOICE_CLOSE_TOLERANCE,
RECEIVE_CLOSE_TOLERANCE,
ACCRUE_ON_RECEIPT_FLAG,
RECEIVING_ROUTING_ID,
QTY_RCV_TOLERANCE,
ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
QTY_RCV_EXCEPTION_CODE,
RECEIPT_DAYS_EXCEPTION_CODE,
MATCH_OPTION, -- bgu, Dec. 11, 98
COUNTRY_OF_ORIGIN_CODE, --frkhan 1/12/99
SECONDARY_UNIT_OF_MEASURE,
SECONDARY_QUANTITY,
PREFERRED_GRADE,
SECONDARY_QUANTITY_RECEIVED,
SECONDARY_QUANTITY_ACCEPTED,
SECONDARY_QUANTITY_REJECTED,
SECONDARY_QUANTITY_CANCELLED,
VMI_FLAG, -- VMI FPH
DROP_SHIP_FLAG, --
ORG_ID, --
tax_attribute_update_code, --
outsourced_assembly, -- bug 4865023
value_basis, --bug 4896950
matching_basis, --bug 4896950
unit_meas_lookup_code --bug 4896950
)
VALUES (x_line_location_id, -- :line_location_id
sysdate,
req_line.last_updated_by, -- :cpo_last_updated_by
req_line.blanket_po_header_id, -- :po_header_id
sysdate,
req_line.last_updated_by, -- :cpo_last_updated_by
req_line.last_update_login, -- :last_update_login
req_line.blanket_po_line_id, -- :po_line_id
decode(x_conversion_rate,1,req_line.quantity,round(req_line.quantity * x_conversion_rate,5)),--:quantity
0,
0,
0,
0,
0,
x_ship_to_location_id, -- :ship_to_location_id,
req_line.need_by_date, -- :need_by_date
l_promised_date, --
--togeorge 09/28/2000
--added note to receiver
req_line.note_to_receiver,
DECODE(x_authorization_status,
'APPROVED','Y','N'), -- 'N'
--Bug #1057095 insert sysdate only
--if the shipment is approved
DECODE(x_authorization_status,
'APPROVED', sysdate, NULL), -- approved date
x_po_release_id, -- :po_release_id,
'N',
'OPEN',
x_best_price, /* 9168321 fix ROUND(x_best_price,x_ext_precision), */
-- :best_price
'N',
'BLANKET', -- :shipment_type,
x_shipment_num, -- :shipment_num,
rcv_controls.inspection_required_flag,
-- :inspection_required_flag,
rcv_controls.receipt_required_flag,
--:receipt_rqd_flag,
null, -- :government_context,
rcv_controls.days_early_receipt_allowed,
-- :days_early_receipt,
rcv_controls.days_late_receipt_allowed,
-- :days_late_receipt,
rcv_controls.enforce_ship_to_location,
-- :enforce_ship_to_location,
req_line.destination_organization_id, -- :dest_org
rcv_controls.invoice_close_tolerance,
-- :invoice_close_tolerance,
rcv_controls.receipt_close_tolerance,
-- :receive_close_tolerance,
DECODE(req_line.destination_type_code, --:dst_code
'EXPENSE',
DECODE(rcv_controls.receipt_required_flag,
-- :receipt_required_flag
'N', 'N',
DECODE(x_expense_accrual_code,
-- :expense_accrual_code
'PERIOD END', 'N', 'Y')),
'Y'),
rcv_controls.receiving_routing_id,
rcv_controls.qty_rcv_tolerance,
rcv_controls.allow_substitute_receipts_flag,
rcv_controls.qty_rcv_exception_code,
rcv_controls.receipt_days_exception_code,
x_invoice_match_option, --bgu, Dec. 11, 98
X_COUNTRY_OF_ORIGIN_CODE, --frkhan 1/12/99
--
x_secondary_unit_of_measure,
x_secondary_quantity,
req_line.preferred_grade,
DECODE(x_secondary_unit_of_measure,NULL,NULL,0),
DECODE(x_secondary_unit_of_measure,NULL,NULL,0),
DECODE(x_secondary_unit_of_measure,NULL,NULL,0),
DECODE(x_secondary_unit_of_measure,NULL,NULL,0),
--
req_line.vmi_flag, -- VMI FPH
req_line.drop_ship_flag, --
req_line.org_id, --
'CREATE', --
l_outsourced_assembly, -- bug 4865023
l_value_basis, --bug 4896950
l_matching_basis, --bug 4896950
l_unit_meas_lookup_code --bug 4896950
);
UPDATE PO_REQUISITION_LINES
SET line_location_id = x_line_location_id,
reqs_in_pool_flag = NULL, --
last_update_date = SYSDATE
WHERE requisition_line_id = req_line.requisition_line_id;
/* Bug# 3104460 - Do not update PO_LINES.QUANTITY with released amount
UPDATE SQL deleted */
/* Bug 947709
** Adding code to copy attachments from Requisition
** to Release.
*/
-- Calling API to copy attachments from Requisition Lines to
-- Release Shipments
fnd_attached_documents2_pkg.copy_attachments('REQ_LINES',
req_line.requisition_line_id,
'',
'',
'',
'',
'PO_SHIPMENTS',
x_line_location_id,
'',
'',
'',
'',
req_line.last_updated_by,
req_line.last_update_login,
'',
'',
'');
req_line.last_updated_by,
req_line.last_update_login,
'',
'',
'');
INSERT INTO PO_DISTRIBUTIONS
(PO_DISTRIBUTION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
PO_HEADER_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PO_LINE_ID,
LINE_LOCATION_ID,
PO_RELEASE_ID,
REQ_DISTRIBUTION_ID,
SET_OF_BOOKS_ID,
CODE_COMBINATION_ID,
DELIVER_TO_LOCATION_ID,
DELIVER_TO_PERSON_ID,
QUANTITY_ORDERED,
QUANTITY_DELIVERED,
QUANTITY_BILLED,
QUANTITY_CANCELLED,
RATE_DATE,
RATE,
ACCRUED_FLAG,
ENCUMBERED_FLAG,
GL_ENCUMBERED_DATE,
GL_ENCUMBERED_PERIOD_NAME,
DISTRIBUTION_NUM,
DESTINATION_TYPE_CODE,
DESTINATION_ORGANIZATION_ID,
DESTINATION_SUBINVENTORY,
BUDGET_ACCOUNT_ID,
ACCRUAL_ACCOUNT_ID,
VARIANCE_ACCOUNT_ID,
WIP_ENTITY_ID,
WIP_LINE_ID,
WIP_REPETITIVE_SCHEDULE_ID,
WIP_OPERATION_SEQ_NUM,
WIP_RESOURCE_SEQ_NUM,
BOM_RESOURCE_ID,
GOVERNMENT_CONTEXT,
PREVENT_ENCUMBRANCE_FLAG,
PROJECT_ID,
TASK_ID,
AWARD_ID, -- OGM_0.0
EXPENDITURE_TYPE,
PROJECT_ACCOUNTING_CONTEXT,
DESTINATION_CONTEXT,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_ITEM_DATE,
ACCRUE_ON_RECEIPT_FLAG,
KANBAN_CARD_ID,
TAX_RECOVERY_OVERRIDE_FLAG, --
RECOVERY_RATE,
--togeorge 10/05/2000
--added oke columns
oke_contract_line_id,
oke_contract_deliverable_id,
--spangulu 09/16/2003
--added distribution_type for encumb. rewrite
distribution_type,
Org_Id --
)
SELECT PO_DISTRIBUTIONS_S.NEXTVAL,
sysdate,
req_line.last_updated_by, -- :cpo_last_updated_by
req_line.blanket_po_header_id, --:po_header_id
sysdate,
req_line.last_updated_by, -- :cpo_last_updated_by,
req_line.last_update_login, -- :last_update_login,
req_line.blanket_po_line_id, -- :po_line_id,
x_line_location_id, -- :line_location_id
x_po_release_id, -- :po_release_id
PRD.DISTRIBUTION_ID,
PRD.SET_OF_BOOKS_ID,
PRD.CODE_COMBINATION_ID,
req_line.deliver_to_location_id, --:deliver_to_loc_id
req_line.deliver_to_person_id, --:deliver_to_per_id
decode(x_conversion_rate,1,prd.req_line_quantity,round(prd.req_line_quantity * x_conversion_rate,5)),
-- :div by rate????
0,
0,
0,
ph.rate_date,
ph.rate,
'N',
'N'
--
-- If Req encumbrance is on, copy the Req period.
-- Otherwise, if PO enc is on and SYSDATE is open
-- (x_period_name tries to tell us this, but is buggy)
-- then use SYSDATE. Otherwise, NULL.
-- gl_encumbered_date =
, DECODE( p_req_enc_flag
, 'Y', PRD.gl_encumbered_date
, DECODE( x_period_name
, '', TO_DATE(NULL)
, TRUNC(SYSDATE)
)
)
-- gl_encumbered_period_name =
, DECODE( p_req_enc_flag
, 'Y', PRD.gl_encumbered_period_name
, x_period_name
)
, PRD.DISTRIBUTION_NUM, -- (:distribution_num + ROWNUM),
PRL.DESTINATION_TYPE_CODE,
PRL.DESTINATION_ORGANIZATION_ID,
PRL.DESTINATION_SUBINVENTORY,
PRD.BUDGET_ACCOUNT_ID,
PRD.ACCRUAL_ACCOUNT_ID,
PRD.VARIANCE_ACCOUNT_ID,
PRL.WIP_ENTITY_ID,
PRL.WIP_LINE_ID,
PRL.WIP_REPETITIVE_SCHEDULE_ID,
PRL.WIP_OPERATION_SEQ_NUM,
PRL.WIP_RESOURCE_SEQ_NUM,
PRL.BOM_RESOURCE_ID,
PH.GOVERNMENT_CONTEXT
--
-- prevent_encumbrance_flag =
/*, DECODE( PRL.destination_type_code
, g_dest_type_code_SHOP_FLOOR, 'Y'
, 'N'
) */
, DECODE( PRL.destination_type_code
, g_dest_type_code_SHOP_FLOOR
, decode((select entity_type
from wip_entities
where wip_entity_id= PRL.wip_entity_id),6, 'N', 'Y')
, 'N'
) /* Encumbrance Project - To enable encumbrance for destination type Shop Floor and WIP entity type EAM */
, PRD.PROJECT_ID,
PRD.TASK_ID,
PRD.AWARD_ID, -- OGM_0.0 Change
PRD.EXPENDITURE_TYPE,
PRD.PROJECT_ACCOUNTING_CONTEXT,
PRL.DESTINATION_CONTEXT,
PRD.EXPENDITURE_ORGANIZATION_ID,
PRD.EXPENDITURE_ITEM_DATE,
PLL.ACCRUE_ON_RECEIPT_FLAG,
PRL.KANBAN_CARD_ID,
nvl(PRD.TAX_RECOVERY_OVERRIDE_FLAG, 'N'),
decode(PRD.TAX_RECOVERY_OVERRIDE_FLAG, 'Y', PRD.RECOVERY_RATE, null),--
--togeorge 10/05/2000
--added oke columns
PRD.oke_contract_line_id,
PRD.oke_contract_deliverable_id,
--spangulu 09/16/2003
--added distribution_type for encumb. rewrite
PLL.shipment_type,
PH.Org_Id --
FROM PO_REQ_DISTRIBUTIONS PRD,
PO_REQUISITION_LINES PRL,
PO_HEADERS PH,
PO_LINE_LOCATIONS PLL
WHERE PRD.REQUISITION_LINE_ID = req_line.requisition_line_id
AND PRL.REQUISITION_LINE_ID = req_line.requisition_line_id
AND PH.PO_HEADER_ID = req_line.blanket_po_header_id
AND PLL.LINE_LOCATION_ID = x_line_location_id;
SELECT POLL.quantity
INTO x_shipment_quantity
FROM PO_LINE_LOCATIONS POLL,
PO_REQUISITION_LINES PORL
WHERE POLL.LINE_LOCATION_ID = PORL.LINE_LOCATION_ID
AND PORL.REQUISITION_LINE_ID
= req_line.requisition_line_id;
SELECT SUM(POD.QUANTITY_ORDERED)
INTO x_dist_quantity
FROM PO_LINE_LOCATIONS POLL,
PO_REQUISITION_LINES PORL,
PO_DISTRIBUTIONS POD
WHERE POLL.LINE_LOCATION_ID = PORL.LINE_LOCATION_ID
AND PORL.REQUISITION_LINE_ID
= req_line.requisition_line_id
AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID;
UPDATE PO_DISTRIBUTIONS POD
SET POD.QUANTITY_ORDERED
= POD.QUANTITY_ORDERED + x_qty_difference
WHERE POD.PO_DISTRIBUTION_ID =
(SELECT POD2.PO_DISTRIBUTION_ID
FROM PO_DISTRIBUTIONS POD2,
PO_LINE_LOCATIONS POLL,
PO_REQUISITION_LINES PORL
WHERE POD2.LINE_LOCATION_ID
= POLL.LINE_LOCATION_ID
AND POLL.LINE_LOCATION_ID
= PORL.LINE_LOCATION_ID
AND PORL.REQUISITION_LINE_ID
= req_line.requisition_line_id
AND POD2.distribution_num=1);
select nvl(rcv_controls.inspection_required_flag,
msi.INSPECTION_REQUIRED_FLAG),
nvl(rcv_controls.days_early_receipt_allowed,
msi.DAYS_EARLY_RECEIPT_ALLOWED),
nvl(rcv_controls.days_late_receipt_allowed,
msi.DAYS_LATE_RECEIPT_ALLOWED),
nvl(rcv_controls.enforce_ship_to_location,
msi.ENFORCE_SHIP_TO_LOCATION_CODE),
nvl(rcv_controls.invoice_close_tolerance,
msi.INVOICE_CLOSE_TOLERANCE),
nvl(rcv_controls.receipt_close_tolerance,
msi.RECEIVE_CLOSE_TOLERANCE),
nvl(rcv_controls.receiving_routing_id,
msi.RECEIVING_ROUTING_ID),
nvl(rcv_controls.qty_rcv_tolerance,
msi.QTY_RCV_TOLERANCE),
nvl(rcv_controls.allow_substitute_receipts_flag,
msi.ALLOW_SUBSTITUTE_RECEIPTS_FLAG),
nvl(rcv_controls.qty_rcv_exception_code,
msi.QTY_RCV_EXCEPTION_CODE),
nvl(rcv_controls.receipt_required_flag,
msi.RECEIPT_REQUIRED_FLAG),
nvl(rcv_controls.receipt_days_exception_code,
msi.RECEIPT_DAYS_EXCEPTION_CODE)
into rcv_controls.inspection_required_flag,
rcv_controls.days_early_receipt_allowed,
rcv_controls.days_late_receipt_allowed,
rcv_controls.enforce_ship_to_location,
rcv_controls.invoice_close_tolerance,
rcv_controls.receipt_close_tolerance,
rcv_controls.receiving_routing_id,
rcv_controls.qty_rcv_tolerance,
rcv_controls.allow_substitute_receipts_flag,
rcv_controls.qty_rcv_exception_code,
rcv_controls.receipt_required_flag,
rcv_controls.receipt_days_exception_code
from mtl_system_items msi
where msi.inventory_item_id = req_line.item_id
and msi.organization_id = req_line.destination_organization_id;
select nvl(rcv_controls.receipt_close_tolerance,
receive_close_tolerance),
nvl(rcv_controls.invoice_close_tolerance,
invoice_close_tolerance),
nvl(rcv_controls.receipt_required_flag,
receipt_required_flag),
nvl(rcv_controls.inspection_required_flag,
inspection_required_flag)
into rcv_controls.receipt_close_tolerance,
rcv_controls.invoice_close_tolerance,
rcv_controls.receipt_required_flag,
rcv_controls.inspection_required_flag
from mtl_system_items
where organization_id = x_inventory_org_id
and inventory_item_id = req_line.item_id;
select nvl(rcv_controls.receipt_required_flag,plt.receiving_flag)
into rcv_controls.receipt_required_flag
from po_lines pol,
po_line_types plt
where pol.po_line_id = req_line.blanket_po_line_id
and pol.line_type_id = plt.line_type_id;
select nvl(rcv_controls.receipt_close_tolerance, plt.receive_close_tolerance)
into rcv_controls.receipt_close_tolerance
from po_lines pol,
po_line_types plt
where pol.po_line_id = req_line.blanket_po_line_id
and pol.line_type_id = plt.line_type_id;
select nvl(rcv_controls.inspection_required_flag,
pov.INSPECTION_REQUIRED_FLAG),
nvl(rcv_controls.days_early_receipt_allowed,
pov.DAYS_EARLY_RECEIPT_ALLOWED),
nvl(rcv_controls.days_late_receipt_allowed,
pov.DAYS_LATE_RECEIPT_ALLOWED),
nvl(rcv_controls.enforce_ship_to_location,
pov.ENFORCE_SHIP_TO_LOCATION_CODE),
nvl(rcv_controls.receiving_routing_id,
pov.RECEIVING_ROUTING_ID),
nvl(rcv_controls.qty_rcv_tolerance,
pov.QTY_RCV_TOLERANCE),
nvl(rcv_controls.allow_substitute_receipts_flag,
pov.ALLOW_SUBSTITUTE_RECEIPTS_FLAG),
nvl(rcv_controls.qty_rcv_exception_code,
pov.QTY_RCV_EXCEPTION_CODE),
nvl(rcv_controls.receipt_required_flag,
pov.RECEIPT_REQUIRED_FLAG),
nvl(rcv_controls.receipt_days_exception_code,
pov.RECEIPT_DAYS_EXCEPTION_CODE)
into rcv_controls.inspection_required_flag,
rcv_controls.days_early_receipt_allowed,
rcv_controls.days_late_receipt_allowed,
rcv_controls.enforce_ship_to_location,
rcv_controls.receiving_routing_id,
rcv_controls.qty_rcv_tolerance,
rcv_controls.allow_substitute_receipts_flag,
rcv_controls.qty_rcv_exception_code,
rcv_controls.receipt_required_flag,
rcv_controls.receipt_days_exception_code
from po_vendors pov
where pov.vendor_id = req_line.vendor_id;
select nvl(rcv_controls.days_early_receipt_allowed,
rp.DAYS_EARLY_RECEIPT_ALLOWED),
nvl(rcv_controls.days_late_receipt_allowed,
rp.DAYS_LATE_RECEIPT_ALLOWED),
nvl(rcv_controls.enforce_ship_to_location,
rp.ENFORCE_SHIP_TO_LOCATION_CODE),
nvl(rcv_controls.receiving_routing_id,
rp.RECEIVING_ROUTING_ID),
nvl(rcv_controls.qty_rcv_tolerance,
rp.QTY_RCV_TOLERANCE),
nvl(rcv_controls.allow_substitute_receipts_flag,
rp.ALLOW_SUBSTITUTE_RECEIPTS_FLAG),
nvl(rcv_controls.qty_rcv_exception_code,
rp.QTY_RCV_EXCEPTION_CODE),
nvl(rcv_controls.receipt_days_exception_code,
rp.RECEIPT_DAYS_EXCEPTION_CODE)
into rcv_controls.days_early_receipt_allowed,
rcv_controls.days_late_receipt_allowed,
rcv_controls.enforce_ship_to_location,
rcv_controls.receiving_routing_id,
rcv_controls.qty_rcv_tolerance,
rcv_controls.allow_substitute_receipts_flag,
rcv_controls.qty_rcv_exception_code,
rcv_controls.receipt_days_exception_code
from rcv_parameters rp
where rp.organization_id = req_line.destination_organization_id;
select nvl(rcv_controls.inspection_required_flag,
posp.INSPECTION_REQUIRED_FLAG),
nvl(rcv_controls.receipt_required_flag,
posp.RECEIVING_FLAG),
nvl(rcv_controls.invoice_close_tolerance,
posp.INVOICE_CLOSE_TOLERANCE),
nvl(rcv_controls.receipt_close_tolerance,
posp.RECEIVE_CLOSE_TOLERANCE)
into rcv_controls.inspection_required_flag,
rcv_controls.receipt_required_flag,
rcv_controls.invoice_close_tolerance,
rcv_controls.receipt_close_tolerance
from po_system_parameters posp;
SELECT match_option
INTO x_invoice_match_option
FROM po_vendor_sites
WHERE vendor_site_id = req_line.vendor_site_id;
SELECT match_option
INTO x_invoice_match_option
FROM po_vendors
WHERE vendor_id = req_line.vendor_id;
SELECT fsp.match_option
INTO x_invoice_match_option
FROM financials_system_parameters fsp;
ALGR: If the release created is approved, delete req supply and create
Po supply
===========================================================================*/
PROCEDURE MAINTAIN_SUPPLY(req_line IN requisition_lines_cursor%rowtype)
IS
BEGIN
if (x_authorization_status = 'APPROVED') THEN
DELETE FROM MTL_SUPPLY
WHERE SUPPLY_TYPE_CODE = 'REQ'
AND SUPPLY_SOURCE_ID = req_line.requisition_line_id;
INSERT INTO MTL_SUPPLY
(supply_type_code,
supply_source_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
po_header_id,
po_release_id,
po_line_id,
po_line_location_id,
po_distribution_id,
item_id,
item_revision,
quantity,
unit_of_measure,
receipt_date,
need_by_date,
destination_type_code,
location_id,
to_organization_id,
to_subinventory,
change_flag)
select 'PO',
pod.po_distribution_id,
pod.last_updated_by,
pod.last_update_date,
pod.last_update_login,
pod.created_by,
pod.creation_date,
pod.po_header_id,
x_po_release_id, -- :po_release_id
pod.po_line_id,
pod.line_location_id,
pod.po_distribution_id,
pol.item_id,
pol.item_revision,
pod.quantity_ordered,
pol.unit_meas_lookup_code,
nvl(poll.promised_date,poll.need_by_date),
poll.need_by_date,
pod.destination_type_code,
pod.deliver_to_location_id,
pod.destination_organization_id,
pod.destination_subinventory,
'Y'
from po_distributions pod,
po_line_locations poll,
po_lines pol
where poll.line_location_id = x_line_location_id
and nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
and nvl(poll.closed_code, 'OPEN') <> 'CLOSED'
and nvl(poll.closed_code, 'OPEN') <> 'CLOSED FOR RECEIVING'
and nvl(poll.cancel_flag, 'N') = 'N'
and pod.line_location_id = poll.line_location_id
and pol.po_line_id = pod.po_line_id
and nvl(poll.approved_flag, 'Y') = 'Y'
and not exists
(select 'Supply Exists'
from mtl_supply ms1
where ms1.supply_type_code = 'PO'
and ms1.supply_source_id = pod.po_distribution_id);
DESC: insert into the notifications table and the action history table
ARGS: IN : req_line IN requisition_lines_cursor%rowtype
ALGR: If the release is not approved, insert appropriate rows into
PO_NOTIFICATIONS
else insert appropriate rows into PO_ACTION_HISTORY
===========================================================================*/
PROCEDURE WRAPUP(req_line IN requisition_lines_cursor%rowtype)
IS
BEGIN
IF (x_authorization_status <> 'APPROVED') THEN
/* obsolete in R11
INSERT INTO PO_NOTIFICATIONS
(EMPLOYEE_ID,
OBJECT_TYPE_LOOKUP_CODE,
OBJECT_ID,
OBJECT_CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
ACTION_LOOKUP_CODE)
SELECT AGENT_ID,
'RELEASE',
PO_RELEASE_ID,
TRUNC(CREATION_DATE),
sysdate,
req_line.last_updated_by,
req_line.last_update_login,
sysdate,
req_line.last_updated_by,
DECODE(HOLD_FLAG,
'Y','ON_HOLD',
DECODE(APPROVED_FLAG,
'R','REQUIRES_REAPPROVAL',
'F','FAILED_APPROVAL',
'NEVER_APPROVED'))
FROM PO_RELEASES
WHERE NVL(CANCEL_FLAG,'N') = 'N'
AND NVL(APPROVED_FLAG,'N') <> 'Y'
AND PO_RELEASE_ID = x_po_release_id; */
INSERT into PO_ACTION_HISTORY
(object_id,
object_type_code,
object_sub_type_code,
sequence_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
action_code,
action_date,
employee_id,
note,
object_revision_num,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
approval_path_id,
offline_code)
VALUES
(x_po_release_id,
'RELEASE',
'BLANKET',
1, -- Bug 13579433
sysdate,
req_line.last_updated_by,
sysdate,
req_line.last_updated_by,
'SUBMIT',
sysdate,
req_line.agent_id,
'AUTO RELEASE',
0,
req_line.last_update_login,
0,
0,
0,
'',
null,
null);
INSERT into PO_ACTION_HISTORY
(object_id,
object_type_code,
object_sub_type_code,
sequence_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
action_code,
action_date,
employee_id,
note,
object_revision_num,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
approval_path_id,
offline_code)
VALUES
(x_po_release_id,
'RELEASE',
'BLANKET',
2, -- Bug 13579433
sysdate,
req_line.last_updated_by,
sysdate,
req_line.last_updated_by,
'APPROVE',
sysdate,
req_line.agent_id,
'AUTO RELEASE',
0,
req_line.last_update_login,
0,
0,
0,
'',
null,
null);
SELECT PRICE_BREAK_LOOKUP_CODE
INTO x_price_break_type
FROM PO_LINES
WHERE PO_LINE_ID = req_line.blanket_po_line_id;
SELECT nvl(SUM(QUANTITY - nvl(QUANTITY_CANCELLED, 0)), 0)
INTO x_price_break_quantity
FROM PO_LINE_LOCATIONS
WHERE PO_LINE_ID = req_line.blanket_po_line_id
AND SHIPMENT_TYPE <> 'PRICE BREAK';
SELECT UNIT_PRICE
INTO x_po_line_price
FROM PO_LINES
WHERE PO_LINE_ID = req_line.blanket_po_line_id;
SELECT LEAST(NVL(MIN(PRICE_OVERRIDE), x_po_line_price), x_po_line_price)
INTO x_best_price
FROM PO_LINE_LOCATIONS
WHERE SHIPMENT_TYPE = 'PRICE BREAK'
AND PO_LINE_ID = req_line.blanket_po_line_id
AND QUANTITY <= x_price_break_quantity
AND (SHIP_TO_LOCATION_ID = NVL(x_ship_to_location_id,
SHIP_TO_LOCATION_ID)
OR
SHIP_TO_LOCATION_ID IS NULL)
AND (SHIP_TO_ORGANIZATION_ID
= NVL(req_line.destination_organization_id,
SHIP_TO_ORGANIZATION_ID)
OR
SHIP_TO_ORGANIZATION_ID IS NULL);
SELECT nvl(sum(poll.quantity * poll.price_override),0)
INTO release_amount
FROM po_line_locations poll
WHERE poll.po_release_id = x_po_release_id
AND poll.shipment_type = 'BLANKET';
SELECT nvl(sum(poll.quantity * poll.price_override),0)
INTO total_release_amount
FROM po_line_locations poll,
po_releases por,
po_headers poh
WHERE poh.po_header_id = x_old_po_header_id
AND poll.po_header_id = poh.po_header_id
AND poll.shipment_type = 'BLANKET'
AND poll.po_release_id = por.po_release_id
AND (nvl(por.approved_flag,'N') = 'Y'
OR por.po_release_id = x_po_release_id)
AND nvl(poll.cancel_flag,'N') = 'N';
SELECT nvl(poh.min_release_amount,0)
INTO min_release_amount
FROM po_headers poh
WHERE po_header_id = x_old_po_header_id;
SELECT nvl(poh.amount_limit,-1),segment1
INTO max_release_amount,x_po_num
FROM po_headers poh
WHERE po_header_id = x_old_po_header_id;
update the so_drop_ship_sources table with PO info
ARGS: IN : req_line IN requisition_lines_cursor%rowtype
===========================================================================*/
PROCEDURE OE_DROP_SHIP(req_line IN requisition_lines_cursor%rowtype)
IS
x_p_api_version number:='';
SELECT requisition_header_id
INTO x_p_req_header_id
FROM po_requisition_lines
WHERE requisition_line_id = req_line.requisition_line_id;
oe_drop_ship_grp.update_po_info(x_p_api_version,
x_p_return_status,
x_p_msg_count,
x_p_msg_data,
x_p_req_header_id,
x_p_req_line_id,
x_p_po_header_id,
x_p_po_line_id,
x_p_line_location_id,
x_p_po_release_id
);
SELECT prh.segment1, prl.line_num
INTO x_req_num, x_req_line_num
FROM po_distributions pod,
po_req_distributions prd,
po_requisition_lines prl,
po_requisition_headers prh
WHERE pod.po_distribution_id = p_po_distribution_id
AND pod.req_distribution_id = prd.distribution_id
AND prl.requisition_line_id = prd.requisition_line_id
AND prh.requisition_header_id = prl.requisition_header_id;
SELECT min(pod.po_distribution_id)
INTO l_po_distribution_id
FROM po_distributions pod
WHERE pod.line_location_id = p_line_location_id;
* Modifies: Inserts error msgs in the concurrent program log.
* Effects: This procedure checks that the release shipment price is
* within the tolerance of the requisition line.
* Returns:
* x_check_status: FND_API.G_RET_STS_SUCCESS if release passes all
* the tolerance checks
* FND_API.G_RET_STS_ERROR if at least one check fails
*/
PROCEDURE check_rel_reqprice(x_check_status OUT NOCOPY VARCHAR2) IS
l_textline po_online_report_text.text_line%TYPE := NULL;
** Setup the Release select cursor
** Select shipment price and convert it to base currency.
** this is done by taking the distribution rate and applying
** it evenly over all distributions. Additionally get the
** shipment unit of measure, quantity, and item_id to be
** passed to the UomC function. Get the shipment_num and
** line_num to be passed to the pooinsingle function.
*/
CURSOR rel_shipment_cursor (p_document_id NUMBER) IS
SELECT nvl(max(POLL.price_override) *
sum(decode(plt.order_type_lookup_code,'AMOUNT',1,nvl(POD.rate,1))*
(POD.quantity_ordered -
nvl(POD.quantity_cancelled, 0))) /
(max(POLL.quantity) -
nvl(max(POLL.quantity_cancelled),0)), -1) Price,
POL.unit_meas_lookup_code uom,
nvl(POLL.shipment_num,0) ship_num,
nvl(POL.line_num,0) line_num,
nvl(POLL.quantity,0) quantity,
nvl(POL.item_id,0) item_id,
nvl( POLL.line_location_id,0) line_loc_id
FROM PO_LINE_LOCATIONS POLL,
PO_LINE_TYPES PLT,
PO_LINES POL,
PO_DISTRIBUTIONS POD
WHERE POLL.po_line_id = POL.po_line_id
AND POLL.line_location_id = POD.line_location_id
AND POLL.po_release_id = p_document_id
AND POL.line_type_id = PLT.line_type_id
AND nvl(POLL.cancel_flag,'N') <> 'Y'
AND nvl(POLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
GROUP BY POL.unit_meas_lookup_code, nvl(POLL.shipment_num,0),
nvl(POL.line_num,0), nvl(POLL.quantity,0),
nvl(POL.item_id,0), POLL.price_override,
nvl(POLL.line_location_id,0);
SELECT min(PRL.unit_price),
PRL.unit_meas_lookup_code,
min(POL.line_num),
min(POLL.shipment_num)
FROM PO_REQUISITION_LINES PRL,
PO_LINE_LOCATIONS POLL,
PO_LINES POL
WHERE PRL.line_location_id = POLL.line_location_id
AND POLL.line_location_id = p_line_location_id
AND PRL.unit_price >= 0
AND POLL.po_line_id = POL.po_line_id
GROUP BY PRL.unit_meas_LOOKUP_code;
SELECT min(PRL.unit_price),
PRL.unit_meas_lookup_code,
sum(PD.quantity_ordered),
min(POL.line_num),
min(POLL.shipment_num)
FROM PO_REQUISITION_LINES PRL,
PO_LINE_LOCATIONS POLL,
PO_LINES POL,
PO_DISTRIBUTIONS PD,
PO_REQ_DISTRIBUTIONS PRD
WHERE POLL.line_location_id = p_line_location_id
AND POLL.po_line_id = POL.po_line_id
AND PRL.unit_price >= 0
AND POLL.line_location_id = PD.line_location_id
AND PD.req_distribution_id = PRD.distribution_id
AND PRD.requisition_line_id = PRL.requisition_line_id
GROUP BY PRL.requisition_line_id, PRL.unit_meas_lookup_code;
SELECT nvl(enforce_price_change_allowance, 'N'),
nvl(enforce_price_change_amount, 'N'),
nvl(price_change_amount, -1)
INTO l_enforce_price_tolerance,
l_enforce_price_amount,
l_amount_tolerance
FROM po_system_parameters;
SELECT round(l_ship_price_in_base_curr(shipment_line),nvl(FND.extended_precision,5))
INTO l_ship_price_ext_precn
FROM FND_CURRENCIES FND, PO_HEADERS POH,
PO_LINE_LOCATIONS POLL
WHERE POH.po_header_id = POLL.po_header_id
AND POH.currency_code = FND.currency_code
AND POLL.line_location_id = l_line_location_id(shipment_line);
SELECT NVL(MSI.price_tolerance_percent/100,
NVL(POSP.price_change_allowance/100,-1))
INTO l_price_tolerance_allowed
FROM MTL_SYSTEM_ITEMS MSI,
PO_SYSTEM_PARAMETERS POSP,
FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE msi.inventory_item_id(+) = l_item_id(shipment_line)
AND MSI.organization_id(+) = FSP.inventory_organization_id;
** insert into the Online Report Text Table.
**
** The following formula will cost precision erro when the
** increase equals to the tolerance.
** Patched as part of bug 432746.
**
**if ((((ship_price_in_base_curr * rate) /
** req_line_unit_price[i]) -1) <= tolerance)
*/
/* Bug 638073
the formula for tolerance check should be
ship_price_in_base_curr/ req_line_unit_pric e[i] *rate
since rate is the conversion from shipment uom to req uom
*/
/* svaidyan 09/10/98 726568 Modified the price tolerance
to check against tolerance + 1.000001. This is because,
the reqs sourced to a blanket store the unit price rounded
to 5 decimal places and hence we compare only upto the 5th
decimal place.
*/
IF (((l_ship_price_ext_precn) /
(l_req_line_unit_price(req_line) *
l_shipment_to_req_rate ))
> (l_price_tolerance_allowed + 1.000001))
THEN
l_progress := '009';
/*SELECT substr(g_reqmsg||g_delim||p_req_num||g_delim||
g_linemsg||g_delim||p_req_line_num||
g_delim||l_textline||g_delim||POL.min_release_amount,1,240)*/
SELECT substr(g_bpamsg||g_delim||POH.segment1||g_delim||
g_linemsg||g_delim||POL.line_num||
g_delim||l_textline||g_delim||POL.min_release_amount,1,240)
--Bug 12553671 end
BULK COLLECT INTO l_error_messages
FROM PO_LINES POL,PO_RELEASES POR,PO_LINE_LOCATIONS PLL
, PO_HEADERS POH --Bug 12553671, Added table
WHERE POH.po_header_id = POL.po_header_id --Bug 12553671
AND PLL.po_release_id = POR.po_release_id
AND PLL.po_release_id = x_po_release_id
AND POL.po_line_id = PLL.po_line_id
AND POL.min_release_amount is not null
AND POL.min_release_amount >
--Bug 10403684 start. Sync the following portion code with the submition check in PO_DOCUMENT_CHECKS_PVT.check_releases()
( SELECT decode ( sum ( decode ( PLL2.quantity /*Bug 5028960 pol.quantity */
, NULL , PLL2.amount - nvl(PLL2.amount_cancelled,0)
, PLL2.quantity - nvl(PLL2.quantity_cancelled,0)
)
)
, 0 , POL.min_release_amount
, sum ( decode ( PLL2.quantity /*Bug 5028960 pol.quantity */
, NULL , PLL2.amount - nvl(PLL2.amount_cancelled,0)
, ( ( PLL2.quantity - nvl(PLL2.quantity_cancelled,0) )
* PLL2.price_override
)
)
)
)
FROM PO_LINE_LOCATIONS PLL2
WHERE PLL2.po_line_id = POL.po_line_id
AND PLL2.po_release_id = POR.po_release_id
AND PLL2.shipment_type in ('BLANKET', 'SCHEDULED')
)
--Bug 10403684 end
/* (SELECT
decode(sum(nvl(PLL2.quantity,0)-nvl(PLL2.quantity_cancelled,0)),
0,POL.min_release_amount,
sum((nvl(PLL2.quantity,0)-nvl(PLL2.quantity_cancelled,0))
*PLL2.price_override))
FROM PO_LINE_LOCATIONS PLL2
WHERE PLL2.po_line_id = POL.po_line_id
AND PLL2.po_release_id = POR.po_release_id
AND PLL2.shipment_type in ('BLANKET', 'SCHEDULED'))*/
GROUP BY POH.segment1, POL.line_num,POL.min_release_amount; --12553671
SELECT NVL(purch_encumbrance_flag,'N'), set_of_books_id
INTO l_purch_enc_flag, l_sob_id
FROM FINANCIALS_SYSTEM_PARAMETERS;
SELECT substr(l_textline,1,240),
POD.po_distribution_id
BULK COLLECT INTO l_error_messages, l_dist_id
FROM PO_DISTRIBUTIONS POD, PO_LINE_LOCATIONS PLL, PO_LINES POL
WHERE POD.line_location_id = PLL.line_location_id
AND PLL.po_release_id = x_po_release_id
AND POL.po_line_id = PLL.po_line_id
AND nvl(POD.encumbered_flag,'N') = 'N'
AND nvl(PLL.cancel_flag,'N') = 'N'
AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND not exists
(SELECT 'find if the GL date is not within Open period'
from GL_PERIOD_STATUSES PS1, GL_PERIOD_STATUSES PS2,
GL_SETS_OF_BOOKS GSOB
WHERE PS1.application_id = 101
AND PS1.set_of_books_id = l_sob_id
AND PS1.closing_status IN ('O','F')
AND trunc(nvl(POD.GL_ENCUMBERED_DATE,PS1.start_date))
BETWEEN trunc(PS1.start_date) AND trunc(PS1.end_date)
AND PS1.period_year <= GSOB.latest_encumbrance_year
AND PS1.period_name = PS2.period_name
AND PS2.application_id = 201
AND PS2.closing_status = 'O'
AND PS2.set_of_books_id = l_sob_id
AND GSOB.set_of_books_id = l_sob_id);
SELECT substr(l_textline||g_delim||
MTL1.uom_class||' , '||MTL2.uom_class,1,240),
POLL.line_location_id
BULK COLLECT INTO l_error_messages, l_line_location_id
FROM MTL_UOM_CLASS_CONVERSIONS MOU, PO_LINE_LOCATIONS POLL,
PO_LINES POL, MTL_UOM_CLASSES_TL MTL1,
MTL_UOM_CLASSES_TL MTL2
WHERE MOU.inventory_item_id = POL.item_id
AND (NVL(MOU.disable_date, TRUNC(SYSDATE)) + 1) < TRUNC(SYSDATE)
AND POL.po_line_id = POLL.po_line_id
AND POLL.po_release_id = x_po_release_id
AND MOU.from_uom_class = MTL1.uom_class
AND MOU.to_uom_class = MTL2.uom_class
AND EXISTS
(SELECT 'uom conversion exists'
FROM MTL_UNITS_OF_MEASURE MUM
WHERE POL.unit_meas_lookup_code = MUM.unit_of_measure
AND MOU.to_uom_class = MUM.uom_class);
SELECT substr(g_reqmsg||g_delim||p_req_num||g_delim||
g_linemsg||g_delim||p_req_line_num||
g_delim||l_textline,1,240)
BULK COLLECT INTO l_error_messages
FROM MTL_SYSTEM_ITEMS MSI, PO_LINE_LOCATIONS PLL,
PO_RELEASES POR,PO_LINES POL, PO_HEADERS POH,
FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE POR.po_release_id = x_po_release_id
AND POR.po_header_id = POH.po_header_id
AND POR.po_header_id = POL.po_header_id
AND POL.po_line_id = PLL.po_line_id
AND POR.po_release_id = PLL.po_release_id
AND MSI.organization_id = PLL.SHIP_TO_ORGANIZATION_id
AND MSI.inventory_item_id = POL.item_id
AND POL.item_id is not null
AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND nvl(POL.cancel_flag,'N') = 'N'
AND nvl(PLL.cancel_flag,'N') = 'N'
AND nvl(MSI.must_use_approved_vendor_flag,'N') = 'Y'
AND not exists
(SELECT sum(decode(ASR.allow_action_flag, 'Y', 1, -100))
FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES ASR
WHERE ASL.using_organization_id in (PLL.ship_to_organization_id, -1)
AND ASL.vendor_id = POH.vendor_id
AND nvl(ASL.vendor_site_id, POH.vendor_site_id) = POH.vendor_site_id
AND ASL.item_id = POL.item_id
AND ASL.asl_status_id = ASR.status_id
AND ASR.business_rule = '1_PO_APPROVAL'
HAVING sum(decode(ASR.allow_action_flag, 'Y', 1, -100)) > 0
UNION ALL
SELECT sum(decode(ASR.allow_action_flag, 'Y', 1, -100))
FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES ASR
WHERE ASL.using_organization_id in (PLL.ship_to_organization_id , -1)
AND ASL.vendor_id = POH.vendor_id
AND nvl(ASL.vendor_site_id, POH.vendor_site_id) = POH.vendor_site_id
AND ASL.item_id is NULL
AND not exists
(SELECT ASL1.ASL_ID
FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL1
WHERE ASL1.ITEM_ID = POL.item_id
AND ASL1.using_organization_id in (PLL.ship_to_organization_id, -1))
AND ASL.category_id in
(SELECT MIC.category_id
FROM MTL_ITEM_CATEGORIES MIC
WHERE MIC.inventory_item_id = POL.item_id
AND MIC.organization_id = PLL.ship_to_organization_id)
AND ASL.asl_status_id = ASR.status_id
AND ASR.business_rule = '1_PO_APPROVAL'
HAVING sum(decode(ASR.allow_action_flag, 'Y', 1, -100)) > 0);
SELECT substr(g_reqmsg||g_delim||p_req_num||g_delim||
g_linemsg||g_delim||p_req_line_num||
g_delim||l_textline,1,240)
BULK COLLECT INTO l_error_messages
FROM MTL_SYSTEM_ITEMS MSI, PO_LINE_LOCATIONS PLL,
PO_RELEASES POR,PO_LINES POL, PO_HEADERS POH,
FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE POR.po_release_id = x_po_release_id
AND POR.po_header_id = POH.po_header_id
AND POR.po_header_id = POL.po_header_id
AND POL.po_line_id = PLL.po_line_id
AND POR.po_release_id = PLL.po_release_id
AND MSI.organization_id = PLL.ship_to_organization_id
AND MSI.inventory_item_id = POL.item_id
AND POL.item_id is not null
AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND nvl(POL.cancel_flag,'N') = 'N'
AND nvl(PLL.cancel_flag,'N') = 'N'
AND nvl(MSI.must_use_approved_vendor_flag,'N') = 'Y'
AND exists
(SELECT sum(decode(ASR.allow_action_flag, 'Y', 1, -100))
FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES ASR
WHERE ASL.using_organization_id in (PLL.ship_to_organization_id, -1)
AND ASL.vendor_id = POH.vendor_id
AND nvl(ASL.vendor_site_id, POH.vendor_site_id) = POH.vendor_site_id
AND ASL.item_id = POL.item_id
AND ASL.asl_status_id = ASR.status_id
AND ASR.business_rule = '1_PO_APPROVAL'
HAVING sum(decode(ASR.allow_action_flag, 'Y', 1, -100)) < 0
UNION ALL
SELECT sum(decode(ASR.allow_action_flag, 'Y', 1, -100))
FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES ASR
WHERE ASL.using_organization_id in (PLL.ship_to_organization_id , -1)
AND ASL.vendor_id = POH.vendor_id
AND nvl(ASL.vendor_site_id, POH.vendor_site_id) = POH.vendor_site_id
AND ASL.item_id is NULL
AND ASL.category_id in
(SELECT MIC.category_id
FROM MTL_ITEM_CATEGORIES MIC
WHERE MIC.inventory_item_id = POL.item_id
AND MIC.organization_id = PLL.ship_to_organization_id)
AND ASL.asl_status_id = ASR.status_id
AND ASR.business_rule = '1_PO_APPROVAL'
HAVING sum(decode(ASR.allow_action_flag, 'Y', 1, -100)) < 0);
SELECT vendor_id, vendor_site_id
INTO l_vendor_id, l_vendor_site_id
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
SELECT substr (g_shipmsg||g_delim||PLL.shipment_num||g_delim||l_textline,1,240)
BULK COLLECT INTO l_error_messages
FROM PO_RELEASES_ALL POR,
PO_LINE_LOCATIONS_ALL PLL
WHERE POR.po_release_id = PLL.po_release_id
AND POR.po_release_id = x_po_release_id
AND Nvl(PLL.LCM_FLAG,'N') = 'Y'
AND Nvl(PLL.match_option,'P') <> 'R';
SELECT substr (g_shipmsg||g_delim||PLL.shipment_num||g_delim||g_distmsg||g_delim||
POD.distribution_num||g_delim||l_textline, 1,240)
BULK COLLECT INTO l_error_messages
FROM PO_RELEASES_GT POR,
PO_LINE_LOCATIONS_GT PLL,
PO_DISTRIBUTIONS_GT POD
WHERE POR.po_release_id = POD.po_release_id
AND POD.line_location_id = PLL.line_location_id
AND POR.po_release_id = x_po_release_id
AND Nvl(POD.LCM_FLAG,'N') = 'Y'
AND POD.DESTINATION_TYPE_CODE <> 'INVENTORY';
PO_DELREC_PVT.create_update_delrec
(
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_action => 'APPROVE',
p_doc_type => 'RELEASE',
p_doc_subtype => 'BLANKET',
p_doc_id => p_release_id,
p_line_id => NULL,
p_line_location_id => NULL
);
SELECT
po_distribution_id,
distribution_num,
project_id,
task_id,
award_id,
NULL
BULK COLLECT INTO
l_gms_po_interface_obj.distribution_id,
l_gms_po_interface_obj.distribution_num,
l_gms_po_interface_obj.project_id,
l_gms_po_interface_obj.task_id,
l_gms_po_interface_obj.award_set_id_in,
l_gms_po_interface_obj.award_set_id_out
FROM PO_DISTRIBUTIONS
WHERE line_location_id = x_line_location_id AND
award_id IS NOT NULL;
UPDATE po_distributions
SET award_id = l_gms_po_interface_obj.award_set_id_out(i)
WHERE po_distribution_id = l_gms_po_interface_obj.distribution_id(i);
SELECT asl_id
INTO l_aslid
FROM (SELECT asl_id
FROM (SELECT paa2.asl_id,Nvl(paa2.item_id, -1) item_id,
Nvl(paa2.category_id, -1) category_id,
Decode(item_id, NULL, '2-CATEGORY',
'1-ITEM') item_cat,
paa2.using_organization_id,
paa2.vendor_id,
Nvl(paa2.vendor_site_id, -1) vendor_site_id
FROM po_asl_attributes_val_v paa2
WHERE p_item_id IS NOT NULL
AND paa2.item_id = p_item_id
AND paa2.vendor_id = p_vendor_id
AND paa2.using_organization_id IN
( -1, p_destination_organization_id
)
AND (paa2.vendor_site_id IS NULL
OR
paa2.vendor_site_id = Nvl(p_vendor_site_id, -1)
)
and paa2.release_generation_method in ('CREATE','CREATE_AND_APPROVE')
UNION
SELECT paa3.asl_id,Nvl(paa3.item_id, -1) item_id,
Nvl(paa3.category_id, -1) category_id,
Decode(item_id, NULL, '2-CATEGORY',
'1-ITEM') item_cat,
paa3.using_organization_id,
paa3.vendor_id vendor_id,
Nvl(paa3.vendor_site_id, -1) vendor_site_id
FROM po_asl_attributes_val_v paa3
WHERE p_item_id IS NULL
AND paa3.category_id = p_category_id
AND paa3.vendor_id = p_vendor_id
AND paa3.using_organization_id IN
( -1, p_destination_organization_id
)
AND (paa3.vendor_site_id IS NULL
OR
paa3.vendor_site_id = Nvl(p_vendor_site_id, -1)
)
and paa3.release_generation_method in ('CREATE','CREATE_AND_APPROVE') )
ORDER BY ITEM_CAT ASC,Nvl(item_id, -1),
Nvl(category_id, -1),
using_organization_id DESC,
Nvl(vendor_site_id, -1))
WHERE rownum < 2 ;