[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_sql_insert VARCHAR2(10000);
FUNCTION po_err_insert RETURN BOOLEAN;
/* If Funds Check succeeds, update the Source Distributions; otherwise */
select distinct 'Y'
from gl_bc_packets
where exists
(select 'Y'
from gl_bc_packets
where reference1 = 'PO'
and reference5 is not null
and packet_id = packet_id);
select 'Y'
from financials_system_parameters
where req_encumbrance_flag = 'Y';
for all the records inserted in the previous step using the
packet_id. */
SELECT FC.MINIMUM_ACCOUNTABLE_UNIT, FC.PRECISION
INTO l_min_acct_unit , l_precision
FROM GL_SETS_OF_BOOKS GLSOB,
FINANCIALS_SYSTEM_PARAMETERS FSP,
FND_CURRENCIES FC
WHERE GLSOB.set_of_books_id = FSP.set_of_books_id
AND FC.currency_code = GLSOB.currency_code;
UPDATE GL_BC_PACKETS
SET ENTERED_DR = ROUND(ENTERED_DR/l_min_acct_unit) * l_min_acct_unit ,
ENTERED_CR = ROUND(ENTERED_CR/l_min_acct_unit) * l_min_acct_unit,
ACCOUNTED_DR = ROUND(ACCOUNTED_DR/l_min_acct_unit ) * l_min_acct_unit ,
ACCOUNTED_CR = ROUND(ACCOUNTED_CR/l_min_acct_unit) * l_min_acct_unit
WHERE PACKET_ID = g_packetid;
UPDATE GL_BC_PACKETS
SET ENTERED_DR = ROUND(ENTERED_DR,l_precision),
ENTERED_CR = ROUND(ENTERED_CR,l_precision),
ACCOUNTED_DR = ROUND(ACCOUNTED_DR,l_precision),
ACCOUNTED_CR = ROUND(ACCOUNTED_CR,l_precision)
WHERE PACKET_ID = g_packetid;
if not po_err_insert then
PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
token1 => 'FILE',
value1 => 'PO_FUNDS_CHECKER',
token2 => 'ERR_NUMBER',
value2 => '035',
token3 => 'SUBROUTINE',
value3 => 'PO_FUNDS_CONTROL()');
if not po_err_insert then
PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
token1 => 'FILE',
value1 => 'PO_FUNDS_CHECKER',
token2 => 'ERR_NUMBER',
value2 => '040',
token3 => 'SUBROUTINE',
value3 => 'PO_FUNDS_CONTROL()');
/* Insert into the Funds Checker queue */
/* */
/* ----------------------------------------------------------------------- */
-- Parameters :
-- p_doctyp : Header Type
-- p_docsubtyp : Header Subtype
-- p_lineid : Line ID
-- p_shipid : Shipment ID
-- p_distid : Distribution ID
-- p_action : Action
-- p_override_period : Override Period
-- p_recreate_demand : Recreate Demand ?
-- p_packetid : Funds Checker Queue Packet ID
FUNCTION po_fc_ins(p_docid IN NUMBER,
p_doctyp IN VARCHAR2,
p_docsubtyp IN VARCHAR2,
p_lineid IN NUMBER,
p_shipid IN NUMBER,
p_distid IN NUMBER DEFAULT 0,
p_action IN VARCHAR2,
p_override_period IN VARCHAR2,
p_recreate_demand IN VARCHAR2,
p_packetid IN OUT NOCOPY NUMBER) RETURN BOOLEAN IS
l_goodstmt BOOLEAN := FALSE;
g_sql_insert := 'insert into gl_bc_packets ' ||
'(packet_id, ' ||
'set_of_books_id, ' ||
'je_source_name, ' ||
'je_category_name, ' ||
'code_combination_id, ' ||
'actual_flag, ' ||
'period_name, ' ||
'period_year, ' ||
'period_num, ' ||
'quarter_num, ' ||
'currency_code, ' ||
'status_code, ' ||
'last_update_date, ' ||
'last_updated_by, ' ||
'budget_version_id, ' ||
'encumbrance_type_id, ' ||
'entered_dr, ' ||
'entered_cr, ' ||
'accounted_dr, ' ||
'accounted_cr, ' ||
'ussgl_transaction_code, ' ||
'reference1, ' ||
'reference2, ' ||
'reference3, ' ||
'reference4, ' ||
'reference5, ' ||
'je_line_description) ';
select gl_bc_packets_s.nextval,
fsp.set_of_books_id
from financials_system_parameters fsp;
l_stmt := 'select :packet_id, ' ||
'glsob.set_of_books_id, ' ||
'''Purchasing'', ' ||
'''Requisitions'', ' ||
'pord.budget_account_id, ' ||
'''E'', ' ||
'glp.period_name, ' ||
'glp.period_year, ' ||
'glp.period_num, ' ||
'glp.quarter_num, ' ||
'glsob.currency_code, ' ||
':status_code, ' ||
'sysdate, ' ||
':user_id, ' ||
'null, ' ||
'fsp.req_encumbrance_type_id, ' ||
'decode(base_cur.minimum_accountable_unit, null, ' ||
'round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
'/ PORD.req_line_quantity) * :dr_quantity, ' ||
'base_cur.precision), round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
'/ PORD.req_line_quantity) * :dr_quantity / ' ||
'base_cur.minimum_accountable_unit) * ' ||
'base_cur.minimum_accountable_unit), ' ||
'decode(base_cur.minimum_accountable_unit, null, ' ||
'round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
'/ PORD.req_line_quantity) * :cr_quantity, ' ||
'base_cur.precision), round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
'/ PORD.req_line_quantity) * :cr_quantity / ' ||
'base_cur.minimum_accountable_unit) * ' ||
'base_cur.minimum_accountable_unit), ' ||
'decode(base_cur.minimum_accountable_unit, null, ' ||
'round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
'/ PORD.req_line_quantity) * :dr_quantity, ' ||
'base_cur.precision), round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
'/ PORD.req_line_quantity) * :dr_quantity / ' ||
'base_cur.minimum_accountable_unit) * ' ||
'base_cur.minimum_accountable_unit), ' ||
'decode(base_cur.minimum_accountable_unit, null, ' ||
'round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
'/ PORD.req_line_quantity) * :cr_quantity, ' ||
'base_cur.precision), round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
'/ PORD.req_line_quantity) * :cr_quantity / ' ||
'base_cur.minimum_accountable_unit) * ' ||
'base_cur.minimum_accountable_unit), ' ||
'pord.ussgl_transaction_code, ' ||
'''REQ'', ' ||
'porl.requisition_header_id, ' ||
'pord.distribution_id, ' ||
'prh.segment1, ' ||
'porl.reference_num, ' ||
'substr(porl.item_description, 1, 40) ' ||
'from gl_periods glp, ' ||
'gl_sets_of_books glsob, ' ||
'financials_system_parameters fsp, ' ||
'fnd_currencies base_cur, ' ||
'po_req_distributions pord, ' ||
'po_requisition_lines porl, ' ||
'po_requisition_headers prh ' ||
'where glsob.set_of_books_id = fsp.set_of_books_id ' ||
'and glp.period_set_name = glsob.period_set_name ' ||
'and glp.period_name = nvl(:override_period, pord.gl_encumbered_period_name) ' ||
'and base_cur.currency_code = glsob.currency_code ' ||
'and nvl(pord.encumbered_flag, ''N'') = '':encumbrance_state'' ' ||
'and nvl(porl.cancel_flag, ''N'') = '':cancel_state'' ' ||
'and porl.line_location_id is null ' ||
'and :entity_level = :object_id ' ||
'and porl.requisition_line_id = pord.requisition_line_id ' ||
'and nvl(pord.prevent_encumbrance_flag, ''N'') = ''N'' ' ||
'and nvl(fsp.req_encumbrance_flag, ''N'') = ''Y'' ' ||
'and porl.requisition_header_id = prh.requisition_header_id';
g_sql_insert := g_sql_insert ||
l_stmt;
after inserting the records in the gl_bc_packets in the procedure
po_funds_control after the call po_fc_ins(insertion of gl_bc_packet).
Similar changes made in the function po_fc_selblnkrel and po_fc_selschrel */
l_stmt := 'select :packet_id, ' ||
'glsob.set_of_books_id, ' ||
'''Purchasing'', ' ||
'''Purchases'', ' ||
'pod.budget_account_id, ' ||
'''E'', ' ||
'glp.period_name, ' ||
'glp.period_year, ' ||
'glp.period_num, ' ||
'glp.quarter_num, ' ||
'glsob.currency_code, ' ||
':status_code, ' ||
'sysdate, ' ||
':user_id, ' ||
'null, ' ||
'fsp.purch_encumbrance_type_id, ' ||
'decode(doc_cur.minimum_accountable_unit, null, ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :dr_quantity ' ||
', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :dr_quantity' ||
' / doc_cur.minimum_accountable_unit) ' ||
' * doc_cur.minimum_accountable_unit' ||
' * nvl(pod.rate, 1) ), ' ||
'decode(doc_cur.minimum_accountable_unit, null, ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :cr_quantity ' ||
', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :cr_quantity' ||
' / doc_cur.minimum_accountable_unit) ' ||
' * doc_cur.minimum_accountable_unit' ||
' * nvl(pod.rate, 1) ), ' ||
'decode(doc_cur.minimum_accountable_unit, null, ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :dr_quantity ' ||
', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :dr_quantity' ||
' / doc_cur.minimum_accountable_unit) ' ||
' * doc_cur.minimum_accountable_unit' ||
' * nvl(pod.rate, 1) ), ' ||
'decode(doc_cur.minimum_accountable_unit, null, ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :cr_quantity ' ||
', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :cr_quantity' ||
' / doc_cur.minimum_accountable_unit) ' ||
' * doc_cur.minimum_accountable_unit' ||
' * nvl(pod.rate, 1) ), ' ||
'pod.ussgl_transaction_code, ' ||
'''PO'', ' ||
'poll.po_header_id, ' ||
'pod.po_distribution_id, ' ||
'poh.segment1, ' ||
'decode(pod.req_distribution_id, null, null, ' ||
'prh.segment1), ' ||
'substr(pol.item_description, 1, 40) ' ||
'from gl_periods glp, ' ||
'gl_sets_of_books glsob, ' ||
'financials_system_parameters fsp, ' ||
'fnd_currencies doc_cur, ' ||
'po_distributions pod, ' ||
'po_line_locations poll, ' ||
'po_lines pol, ' ||
'po_headers poh, ' ||
'po_requisition_headers prh, ' ||
'po_requisition_lines porl, ' ||
'po_req_distributions pord ' ||
'where glsob.set_of_books_id = fsp.set_of_books_id ' ||
'and glp.period_set_name = glsob.period_set_name ' ||
'and glp.period_name = nvl(:override_period, pod.gl_encumbered_period_name) ' ||
'and doc_cur.currency_code = poh.currency_code ' ||
'and poll.po_header_id = poh.po_header_id ' ||
'and poll.shipment_type in (''STANDARD'', ''PLANNED'') ' ||
'and pod.line_location_id = poll.line_location_id ' ||
'and pod.po_line_id = pol.po_line_id ' ||
'and nvl(pod.encumbered_flag, ''N'') = '':encumbrance_state'' ' ||
'and nvl(poll.cancel_flag, ''N'') = '':cancel_state'' ' ||
'and nvl(pod.prevent_encumbrance_flag, ''N'') = ''N'' ' ||
'and :entity_level = :object_id ' ||
'and pod.req_distribution_id = pord.distribution_id(+) ' ||
'and pord.requisition_line_id = porl.requisition_line_id(+) ' ||
'and porl.requisition_header_id = prh.requisition_header_id (+)';
l_bstmt := 'select :packet_id, ' ||
'glsob.set_of_books_id, ' ||
'''Purchasing'', ' ||
'''Requisitions'', ' ||
'pord.budget_account_id, ' ||
'''E'', ' ||
'glp.period_name, ' ||
'glp.period_year, ' ||
'glp.period_num, ' ||
'glp.quarter_num, ' ||
'glsob.currency_code, ' ||
':status_code, ' ||
'sysdate, ' ||
':user_id, ' ||
'null, ' ||
'fsp.req_encumbrance_type_id, ' ||
'decode(base_cur.minimum_accountable_unit, null, ' ||
'round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) '||
'/ PORD.req_line_quantity) * :dr_quantity, ' ||
'base_cur.precision), ' ||
'round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) '||
'/ PORD.req_line_quantity) * :dr_quantity / ' ||
'base_cur.minimum_accountable_unit) * ' ||
'base_cur.minimum_accountable_unit), ' ||
'decode(base_cur.minimum_accountable_unit, null, ' ||
'round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) '||
'/ PORD.req_line_quantity) * :cr_quantity, ' ||
'base_cur.precision), ' ||
'round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) '||
'/ PORD.req_line_quantity) * :cr_quantity / ' ||
'base_cur.minimum_accountable_unit) * ' ||
'base_cur.minimum_accountable_unit), ' ||
'decode(base_cur.minimum_accountable_unit, null, ' ||
'round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) '||
'/ PORD.req_line_quantity) * :dr_quantity, ' ||
'base_cur.precision), ' ||
'round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) '||
'/ PORD.req_line_quantity) * :dr_quantity / ' ||
'base_cur.minimum_accountable_unit) * ' ||
'base_cur.minimum_accountable_unit), ' ||
'decode(base_cur.minimum_accountable_unit, null, ' ||
'round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) '||
'/ PORD.req_line_quantity) * :cr_quantity, ' ||
'base_cur.precision), ' ||
'round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) '||
'/ PORD.req_line_quantity) * :cr_quantity / ' ||
'base_cur.minimum_accountable_unit) * ' ||
'base_cur.minimum_accountable_unit), ' ||
'pord.ussgl_transaction_code, ' ||
'''REQ'', ' ||
'porl.requisition_header_id, ' ||
'pord.distribution_id, ' ||
'prh.segment1, ' ||
'porl.reference_num, ' ||
'substr(porl.item_description, 1, 40) ' ||
'from gl_periods glp, ' ||
'gl_sets_of_books glsob, ' ||
'financials_system_parameters fsp, ' ||
'fnd_currencies base_cur, ' ||
'po_req_distributions pord, ' ||
'po_requisition_lines porl, ' ||
'po_requisition_headers prh, ' ||
'po_distributions pod, ' ||
'po_line_locations poll ' ||
'where glsob.set_of_books_id = fsp.set_of_books_id ' ||
'and glp.period_set_name = glsob.period_set_name ' ||
'and glp.period_name = nvl(:override_period, pod.gl_encumbered_period_name) ' ||
'and base_cur.currency_code = glsob.currency_code ' ||
'and poll.shipment_type in (''STANDARD'', ''PLANNED'') ' ||
'and pod.line_location_id = poll.line_location_id ' ||
'and nvl(pod.encumbered_flag, ''N'') = '':encumbrance_state'' ' ||
'and nvl(poll.cancel_flag, ''N'') = '':cancel_state'' ' ||
'and nvl(pod.prevent_encumbrance_flag, ''N'') = ''N'' ' ||
'and :entity_level = :object_id ' ||
'and :backing_doc_join_column = pod.req_distribution_id ' ||
'and porl.requisition_line_id = pord.requisition_line_id ' ||
'and nvl(fsp.req_encumbrance_flag, ''N'') = ''Y'' ' ||
'and nvl(pord.prevent_encumbrance_flag, ''N'') = ''N'' ' ||
'and porl.requisition_header_id = prh.requisition_header_id ' ||
'and nvl(:recreate_demand, ''Y'') = ''Y''';
g_sql_insert := g_sql_insert ||
l_stmt || ' UNION ALL ' ||
l_bstmt;
l_stmt := 'select :packet_id, ' ||
'glsob.set_of_books_id, ' ||
'''Purchasing'', ' ||
'''Purchases'', ' ||
'pod.budget_account_id, ' ||
'''E'', ' ||
'glp.period_name, ' ||
'glp.period_year, ' ||
'glp.period_num, ' ||
'glp.quarter_num, ' ||
'glsob.currency_code, ' ||
':status_code, ' ||
'sysdate, ' ||
':user_id, ' ||
'null, ' ||
'fsp.purch_encumbrance_type_id, ' ||
'decode(doc_cur.minimum_accountable_unit, null, ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''RELEASE'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :dr_quantity ' ||
', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''RELEASE'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :dr_quantity' ||
' / doc_cur.minimum_accountable_unit) ' ||
' * doc_cur.minimum_accountable_unit' ||
' * nvl(pod.rate, 1) ), ' ||
'decode(doc_cur.minimum_accountable_unit, null, ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''RELEASE'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :cr_quantity ' ||
', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''RELEASE'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :cr_quantity' ||
' / doc_cur.minimum_accountable_unit) ' ||
' * doc_cur.minimum_accountable_unit' ||
' * nvl(pod.rate, 1) ), ' ||
'decode(doc_cur.minimum_accountable_unit, null, ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''RELEASE'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :dr_quantity ' ||
', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''RELEASE'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :dr_quantity' ||
' / doc_cur.minimum_accountable_unit) ' ||
' * doc_cur.minimum_accountable_unit' ||
' * nvl(pod.rate, 1) ), ' ||
'decode(doc_cur.minimum_accountable_unit, null, ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''RELEASE'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :cr_quantity ' ||
', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''RELEASE'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :cr_quantity' ||
' / doc_cur.minimum_accountable_unit) ' ||
' * doc_cur.minimum_accountable_unit' ||
' * nvl(pod.rate, 1) ), ' ||
'pod.ussgl_transaction_code, ' ||
'''PO'', ' ||
'poll.po_header_id, ' ||
'pod.po_distribution_id, ' ||
'poh.segment1, ' ||
'decode(pod.req_distribution_id, null, null, ' ||
'prh.segment1), ' ||
'substr(pol.item_description, 1, 40) ' ||
'from gl_periods glp, ' ||
'gl_sets_of_books glsob, ' ||
'financials_system_parameters fsp, ' ||
'fnd_currencies doc_cur, ' ||
'po_distributions pod, ' ||
'po_line_locations poll, ' ||
'po_lines pol, ' ||
'po_releases por, ' ||
'po_headers poh, ' ||
'po_requisition_headers prh, ' ||
'po_requisition_lines porl, ' ||
'po_req_distributions pord ' ||
'where glsob.set_of_books_id = fsp.set_of_books_id ' ||
'and glp.period_set_name = glsob.period_set_name ' ||
'and glp.period_name = nvl(:override_period, pod.gl_encumbered_period_name) ' ||
'and doc_cur.currency_code = poh.currency_code ' ||
'and poll.po_release_id = por.po_release_id ' ||
'and por.po_header_id = poh.po_header_id ' ||
'and poll.shipment_type = ''BLANKET'' ' ||
'and pod.line_location_id = poll.line_location_id ' ||
'and pod.po_line_id = pol.po_line_id ' ||
'and nvl(pod.encumbered_flag, ''N'') = '':encumbrance_state'' ' ||
'and nvl(poll.cancel_flag, ''N'') = '':cancel_state'' ' ||
'and nvl(pod.prevent_encumbrance_flag, ''N'') = ''N'' ' ||
'and :entity_level = :object_id ' ||
'and pod.req_distribution_id = pord.distribution_id(+) ' ||
'and pord.requisition_line_id = porl.requisition_line_id(+) ' ||
'and porl.requisition_header_id = prh.requisition_header_id (+)';
l_bstmt := 'select :packet_id, ' ||
'glsob.set_of_books_id, ' ||
'''Purchasing'', ' ||
'''Requisitions'', ' ||
'pord.budget_account_id, ' ||
'''E'', ' ||
'glp.period_name, ' ||
'glp.period_year, ' ||
'glp.period_num, ' ||
'glp.quarter_num, ' ||
'glsob.currency_code, ' ||
':status_code, ' ||
'sysdate, ' ||
':user_id, ' ||
'null, ' ||
'fsp.req_encumbrance_type_id, ' ||
'decode(base_cur.minimum_accountable_unit, null, ' ||
'round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
'/ PORD.req_line_quantity) * :dr_quantity, ' ||
'base_cur.precision), ' ||
'round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
'/ PORD.req_line_quantity) * :dr_quantity / ' ||
'base_cur.minimum_accountable_unit) * ' ||
'base_cur.minimum_accountable_unit), ' ||
'decode(base_cur.minimum_accountable_unit, null, ' ||
'round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
'/ PORD.req_line_quantity) * :cr_quantity, ' ||
'base_cur.precision), ' ||
'round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
'/ PORD.req_line_quantity) * :cr_quantity / ' ||
'base_cur.minimum_accountable_unit) * ' ||
'base_cur.minimum_accountable_unit), ' ||
'decode(base_cur.minimum_accountable_unit, null, ' ||
'round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
'/ PORD.req_line_quantity) * :dr_quantity, ' ||
'base_cur.precision), ' ||
'round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
'/ PORD.req_line_quantity) * :dr_quantity / ' ||
'base_cur.minimum_accountable_unit) * ' ||
'base_cur.minimum_accountable_unit), ' ||
'decode(base_cur.minimum_accountable_unit, null, ' ||
'round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
'/ PORD.req_line_quantity) * :cr_quantity, ' ||
'base_cur.precision), ' ||
'round((porl.unit_price + ' ||
'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
'/ PORD.req_line_quantity) * :cr_quantity / ' ||
'base_cur.minimum_accountable_unit) * ' ||
'base_cur.minimum_accountable_unit), ' ||
'pord.ussgl_transaction_code, ' ||
'''REQ'', ' ||
'porl.requisition_header_id, ' ||
'pord.distribution_id, ' ||
'prh.segment1, ' ||
'porl.reference_num, ' ||
'substr(porl.item_description, 1, 40) ' ||
'from gl_periods glp, ' ||
'gl_sets_of_books glsob, ' ||
'financials_system_parameters fsp, ' ||
'fnd_currencies base_cur, ' ||
'po_req_distributions pord, ' ||
'po_requisition_lines porl, ' ||
'po_requisition_headers prh, ' ||
'po_distributions pod, ' ||
'po_line_locations poll ' ||
'where glsob.set_of_books_id = fsp.set_of_books_id ' ||
'and glp.period_set_name = glsob.period_set_name ' ||
'and glp.period_name = nvl(:override_period, pod.gl_encumbered_period_name) ' ||
'and base_cur.currency_code = glsob.currency_code ' ||
'and poll.shipment_type = ''BLANKET'' ' ||
'and pod.line_location_id = poll.line_location_id ' ||
'and nvl(pod.encumbered_flag, ''N'') = '':encumbrance_state'' ' ||
'and nvl(poll.cancel_flag, ''N'') = '':cancel_state'' ' ||
'and nvl(pod.prevent_encumbrance_flag, ''N'') = ''N'' ' ||
'and :entity_level = :object_id ' ||
'and :backing_doc_join_column = pod.req_distribution_id ' ||
'and porl.requisition_line_id = pord.requisition_line_id ' ||
'and nvl(fsp.req_encumbrance_flag, ''N'') = ''Y'' ' ||
'and nvl(pord.prevent_encumbrance_flag, ''N'') = ''N'' ' ||
'and porl.requisition_header_id = prh.requisition_header_id ' ||
'and nvl(:recreate_demand, ''Y'') = ''Y''';
g_sql_insert := g_sql_insert ||
l_stmt || ' UNION ALL ' ||
l_bstmt;
l_stmt := 'select :packet_id, ' ||
'glsob.set_of_books_id, ' ||
'''Purchasing'', ' ||
'''Purchases'', ' ||
'prd.budget_account_id, ' ||
'''E'', ' ||
'glp.period_name, ' ||
'glp.period_year, ' ||
'glp.period_num, ' ||
'glp.quarter_num, ' ||
'glsob.currency_code, ' ||
':status_code, ' ||
'sysdate, ' ||
':user_id, ' ||
'null, ' ||
'fsp.purch_encumbrance_type_id, ' ||
'decode(doc_cur.minimum_accountable_unit, null, ' ||
'round((prll.price_override + ' ||
'po_tax_sv.get_tax(''RELEASE'',PRD.po_distribution_id)'||
'/ PRD.quantity_ordered) * :dr_quantity ' ||
', doc_cur.precision) * nvl(prd.rate, 1) , ' ||
'round((prll.price_override + ' ||
'po_tax_sv.get_tax(''RELEASE'',PRD.po_distribution_id)'||
'/ PRD.quantity_ordered) * :dr_quantity' ||
' / doc_cur.minimum_accountable_unit) ' ||
' * doc_cur.minimum_accountable_unit' ||
' * nvl(prd.rate, 1) ), ' ||
'decode(doc_cur.minimum_accountable_unit, null, ' ||
'round((prll.price_override + ' ||
'po_tax_sv.get_tax(''RELEASE'',PRD.po_distribution_id)'||
'/ PRD.quantity_ordered) * :cr_quantity ' ||
', doc_cur.precision) * nvl(prd.rate, 1) , ' ||
'round((prll.price_override + ' ||
'po_tax_sv.get_tax(''RELEASE'',PRD.po_distribution_id)'||
'/ PRD.quantity_ordered) * :cr_quantity' ||
' / doc_cur.minimum_accountable_unit) ' ||
' * doc_cur.minimum_accountable_unit' ||
' * nvl(prd.rate, 1) ), ' ||
'decode(doc_cur.minimum_accountable_unit, null, ' ||
'round((prll.price_override + ' ||
'po_tax_sv.get_tax(''RELEASE'',PRD.po_distribution_id)'||
'/ PRD.quantity_ordered) * :dr_quantity ' ||
', doc_cur.precision) * nvl(prd.rate, 1) , ' ||
'round((prll.price_override + ' ||
'po_tax_sv.get_tax(''RELEASE'',PRD.po_distribution_id)'||
'/ PRD.quantity_ordered) * :dr_quantity' ||
' / doc_cur.minimum_accountable_unit) ' ||
' * doc_cur.minimum_accountable_unit' ||
' * nvl(prd.rate, 1) ), ' ||
'decode(doc_cur.minimum_accountable_unit, null, ' ||
'round((prll.price_override + ' ||
'po_tax_sv.get_tax(''RELEASE'',PRD.po_distribution_id)'||
'/ PRD.quantity_ordered) * :cr_quantity ' ||
', doc_cur.precision) * nvl(prd.rate, 1) , ' ||
'round((prll.price_override + ' ||
'po_tax_sv.get_tax(''RELEASE'',PRD.po_distribution_id)'||
'/ PRD.quantity_ordered) * :cr_quantity' ||
' / doc_cur.minimum_accountable_unit) ' ||
' * doc_cur.minimum_accountable_unit' ||
' * nvl(prd.rate, 1) ), ' ||
'prd.ussgl_transaction_code, ' ||
'''PO'', ' ||
'prll.po_header_id, ' ||
'prd.po_distribution_id, ' ||
'poh.segment1, ' ||
'decode(pod.req_distribution_id, null, null, ' ||
'prh.segment1), ' ||
'substr(pol.item_description, 1, 40) ' ||
'from gl_periods glp, ' ||
'gl_sets_of_books glsob, ' ||
'financials_system_parameters fsp, ' ||
'fnd_currencies doc_cur, ' ||
'po_distributions prd, ' ||
'po_line_locations prll, ' ||
'po_lines pol, ' ||
'po_headers poh, ' ||
'po_releases por, ' ||
'po_distributions pod, ' ||
'po_requisition_headers prh, ' ||
'po_requisition_lines porl, ' ||
'po_req_distributions pord ' ||
'where glsob.set_of_books_id = fsp.set_of_books_id ' ||
'and glp.period_set_name = glsob.period_set_name ' ||
'and glp.period_name = nvl(:override_period, prd.gl_encumbered_period_name) ' ||
'and doc_cur.currency_code = poh.currency_code ' ||
'and prll.po_release_id = por.po_release_id ' ||
'and por.po_header_id = poh.po_header_id ' ||
'and prll.shipment_type = ''SCHEDULED'' ' ||
'and prd.line_location_id = prll.line_location_id ' ||
'and prd.po_line_id = pol.po_line_id ' ||
'and nvl(prd.encumbered_flag, ''N'') = '':encumbrance_state'' ' ||
'and nvl(prll.cancel_flag, ''N'') = '':cancel_state'' ' ||
'and nvl(prd.prevent_encumbrance_flag, ''N'') = ''N'' ' ||
'and :entity_level = :object_id ' ||
'and prd.source_distribution_id = pod.po_distribution_id(+) ' ||
'and pod.req_distribution_id = pord.distribution_id(+) ' ||
'and pord.requisition_line_id = porl.requisition_line_id(+) ' ||
'and porl.requisition_header_id = prh.requisition_header_id (+)';
l_bstmt := 'select :packet_id, ' ||
'glsob.set_of_books_id, ' ||
'''Purchasing'', ' ||
'''Purchases'', ' ||
'pod.budget_account_id, ' ||
'''E'', ' ||
'glp.period_name, ' ||
'glp.period_year, ' ||
'glp.period_num, ' ||
'glp.quarter_num, ' ||
'glsob.currency_code, ' ||
':status_code, ' ||
'sysdate, ' ||
':user_id, ' ||
'null, ' ||
'fsp.purch_encumbrance_type_id, ' ||
'decode(doc_cur.minimum_accountable_unit, null, ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :dr_quantity ' ||
', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :dr_quantity' ||
' / doc_cur.minimum_accountable_unit) ' ||
' * doc_cur.minimum_accountable_unit' ||
' * nvl(pod.rate, 1) ), ' ||
'decode(doc_cur.minimum_accountable_unit, null, ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :cr_quantity ' ||
', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :cr_quantity' ||
' / doc_cur.minimum_accountable_unit) ' ||
' * doc_cur.minimum_accountable_unit' ||
' * nvl(pod.rate, 1) ), ' ||
'decode(doc_cur.minimum_accountable_unit, null, ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :dr_quantity ' ||
', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :dr_quantity' ||
' / doc_cur.minimum_accountable_unit) ' ||
' * doc_cur.minimum_accountable_unit' ||
' * nvl(pod.rate, 1) ), ' ||
'decode(doc_cur.minimum_accountable_unit, null, ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :cr_quantity ' ||
', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
'round((poll.price_override + ' ||
'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
'/ POD.quantity_ordered) * :cr_quantity' ||
' / doc_cur.minimum_accountable_unit) ' ||
' * doc_cur.minimum_accountable_unit' ||
' * nvl(pod.rate, 1) ), ' ||
'pod.ussgl_transaction_code, ' ||
'''PO'', ' ||
'poll.po_header_id, ' ||
'pod.po_distribution_id, ' ||
'poh.segment1, ' ||
'decode(pod.req_distribution_id, null, null, ' ||
'prh.segment1), ' ||
'substr(porl.item_description, 1, 40) ' ||
'from gl_periods glp, ' ||
'gl_sets_of_books glsob, ' ||
'financials_system_parameters fsp, ' ||
'fnd_currencies doc_cur, ' ||
'po_distributions prd, ' ||
'po_line_locations prll, ' ||
'po_lines pol, ' ||
'po_headers poh, ' ||
'po_releases por, ' ||
'po_distributions pod, ' ||
'po_line_locations poll, ' ||
'po_requisition_headers prh, ' ||
'po_requisition_lines porl, ' ||
'po_req_distributions pord ' ||
'where glsob.set_of_books_id = fsp.set_of_books_id ' ||
'and glp.period_set_name = glsob.period_set_name ' ||
'and glp.period_name = nvl(:override_period, pod.gl_encumbered_period_name) ' ||
'and doc_cur.currency_code = poh.currency_code ' ||
'and prll.po_release_id = por.po_release_id ' ||
'and por.po_header_id = poh.po_header_id ' ||
'and prll.shipment_type = ''SCHEDULED'' ' ||
'and prd.line_location_id = prll.line_location_id ' ||
'and nvl(prd.encumbered_flag, ''N'') = '':encumbrance_state'' ' ||
'and nvl(prll.cancel_flag, ''N'') = '':cancel_state'' ' ||
'and nvl(prd.prevent_encumbrance_flag, ''N'') = ''N'' ' ||
'and :entity_level = :object_id ' ||
'and pod.po_distribution_id = prd.source_distribution_id ' ||
'and poll.line_location_id = pod.line_location_id ' ||
'and pol.po_line_id = pod.po_line_id ' ||
'and pod.req_distribution_id = pord.distribution_id(+) ' ||
'and pord.requisition_line_id = porl.requisition_line_id(+) ' ||
'and porl.requisition_header_id = prh.requisition_header_id (+)';
g_sql_insert := g_sql_insert ||
l_stmt || ' UNION ALL ' ||
l_bstmt;
cur_insert INTEGER;
num_insert INTEGER;
select gl_bc_packets_s.nextval
from dual;
cur_insert := dbms_sql.open_cursor;
dbms_sql.parse(cur_insert, g_sql_insert, dbms_sql.v7);
dbms_sql.bind_variable(cur_insert, 'packet_id', g_packetid);
dbms_sql.bind_variable(cur_insert, 'status_code', l_status);
dbms_sql.bind_variable(cur_insert, 'user_id', g_userid);
dbms_sql.bind_variable(cur_insert, 'override_period', g_override_period);
dbms_sql.bind_variable(cur_insert, 'object_id', l_objectid);
if INSTR(g_sql_insert, ':recreate_demand', 1) > 0 then
dbms_sql.bind_variable(cur_insert, 'recreate_demand', g_recreate_demand);
num_insert := dbms_sql.execute(cur_insert);
dbms_sql.close_cursor(cur_insert);
'Inserted ' || num_insert || ' Records into gl_bc_packets' ||
g_delim;
if dbms_sql.is_open(cur_insert) then
dbms_sql.close_cursor(cur_insert);
update po_req_distributions
set encumbered_flag = decode(g_fcmode, 'RESERVE', 'Y', 'N')
where requisition_line_id in
(select porl.requisition_line_id
from po_requisition_lines porl
where porl.requisition_header_id = g_docid
and porl.line_location_id is null);
update po_req_distributions
set encumbered_flag = decode(g_fcmode, 'RESERVE', 'Y', 'N')
where requisition_line_id = g_lineid;
update po_requisition_lines porl
set encumbered_flag =
(select decode(count(pord.distribution_id), 0, 'Y', 'N')
from po_req_distributions pord
where pord.requisition_line_id = porl.requisition_line_id
and pord.encumbered_flag = 'N')
where porl.requisition_header_id = g_docid;
update po_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'Y', 'REVERSE', 'N',
'LIQUIDATE', 'N', 'REJECT',
encumbered_flag, 'N')
where po_header_id = g_docid;
update po_req_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
'REVERSE', 'Y', encumbered_flag)
where distribution_id in
(select req_distribution_id
from po_distributions
where po_header_id = g_docid);
update po_req_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
'REVERSE', 'Y', encumbered_flag)
where source_req_distribution_id in
(select req_distribution_id
from po_distributions
where po_header_id = g_docid);
update po_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'Y', 'REVERSE', 'N',
'LIQUIDATE', 'N', 'REJECT',
encumbered_flag, 'N')
where po_line_id = g_lineid;
update po_req_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
'REVERSE', 'Y', encumbered_flag)
where distribution_id in
(select req_distribution_id
from po_distributions
where po_line_id = g_lineid);
update po_req_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
'REVERSE', 'Y', encumbered_flag)
where source_req_distribution_id in
(select req_distribution_id
from po_distributions
where po_line_id = g_lineid);
update po_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'Y', 'REVERSE', 'N',
'LIQUIDATE', 'N', 'REJECT',
encumbered_flag, 'N')
where line_location_id = g_shipid;
update po_req_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
'REVERSE', 'Y', encumbered_flag)
where distribution_id in
(select req_distribution_id
from po_distributions
where line_location_id = g_shipid);
update po_req_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
'REVERSE', 'Y', encumbered_flag)
where source_req_distribution_id in
(select req_distribution_id
from po_distributions
where line_location_id = g_shipid);
update po_line_locations poll
set encumbered_flag =
(select decode(count(pod.po_distribution_id), 0, 'Y', 'N')
from po_distributions pod
where pod.line_location_id = poll.line_location_id
and pod.encumbered_flag = 'N')
where poll.po_header_id = g_docid;
update po_requisition_lines porl
set encumbered_flag =
(select decode(count(pord.distribution_id), 0, 'Y', 'N')
from po_req_distributions pord
where pord.requisition_line_id = porl.requisition_line_id
and pord.encumbered_flag = 'N')
where porl.requisition_line_id in
(select prd.requisition_line_id
from po_requisition_lines prl,
po_req_distributions prd,
po_distributions pod
where prd.requisition_line_id = prl.requisition_line_id
and prd.distribution_id = pod.req_distribution_id
and pod.po_header_id = g_docid);
update po_requisition_lines porl
set encumbered_flag =
(select decode(count(pord.distribution_id), 0, 'Y', 'N')
from po_req_distributions pord
where pord.requisition_line_id = porl.requisition_line_id
and pord.encumbered_flag = 'N')
where porl.requisition_line_id in
(select prd.requisition_line_id
from po_requisition_lines prl,
po_req_distributions prd,
po_distributions pod
where prd.requisition_line_id = prl.requisition_line_id
and prd.source_req_distribution_id = pod.req_distribution_id
and pod.po_header_id = g_docid);
update po_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'Y', 'REVERSE', 'N',
'LIQUIDATE', 'N', 'REJECT',
encumbered_flag, 'N')
where po_release_id = g_docid;
update po_req_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
'REVERSE', 'Y', encumbered_flag)
where distribution_id in
(select req_distribution_id
from po_distributions
where po_release_id = g_docid);
update po_req_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
'REVERSE', 'Y', encumbered_flag)
where source_req_distribution_id in
(select req_distribution_id
from po_distributions
where po_release_id = g_docid);
update po_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'Y', 'REVERSE', 'N',
'LIQUIDATE', 'N', 'REJECT',
encumbered_flag, 'N')
where po_line_id = g_lineid;
update po_req_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
'REVERSE', 'Y', encumbered_flag)
where distribution_id in
(select req_distribution_id
from po_distributions
where po_line_id = g_lineid);
update po_req_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
'REVERSE', 'Y', encumbered_flag)
where source_req_distribution_id in
(select req_distribution_id
from po_distributions
where po_line_id = g_lineid);
update po_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'Y', 'REVERSE', 'N',
'LIQUIDATE', 'N', 'REJECT',
encumbered_flag, 'N')
where line_location_id = g_shipid;
update po_req_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
'REVERSE', 'Y', encumbered_flag)
where distribution_id in
(select req_distribution_id
from po_distributions
where line_location_id = g_shipid);
update po_req_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
'REVERSE', 'Y', encumbered_flag)
where source_req_distribution_id in
(select req_distribution_id
from po_distributions
where line_location_id = g_shipid);
update po_line_locations poll
set encumbered_flag =
(select decode(count(pod.po_distribution_id), 0, 'Y', 'N')
from po_distributions pod
where pod.line_location_id = poll.line_location_id
and pod.encumbered_flag = 'N')
where poll.po_release_id = g_docid;
update po_requisition_lines porl
set encumbered_flag =
(select decode(count(pord.distribution_id), 0, 'Y', 'N')
from po_req_distributions pord
where pord.requisition_line_id = porl.requisition_line_id
and pord.encumbered_flag = 'N')
where porl.requisition_line_id in
(select prd.requisition_line_id
from po_requisition_lines prl,
po_req_distributions prd,
po_distributions pod
where prd.requisition_line_id = prl.requisition_line_id
and prd.distribution_id = pod.req_distribution_id
and pod.po_release_id = g_docid);
update po_requisition_lines porl
set encumbered_flag =
(select decode(count(pord.distribution_id), 0, 'Y', 'N')
from po_req_distributions pord
where pord.requisition_line_id = porl.requisition_line_id
and pord.encumbered_flag = 'N')
where porl.requisition_line_id in
(select prd.requisition_line_id
from po_requisition_lines prl,
po_req_distributions prd,
po_distributions pod
where prd.requisition_line_id = prl.requisition_line_id
and prd.source_req_distribution_id = pod.req_distribution_id
and pod.po_release_id = g_docid);
update po_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'Y', 'REVERSE', 'N',
'LIQUIDATE', 'N', 'REJECT',
encumbered_flag, 'N')
where po_release_id = g_docid;
update po_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
'REVERSE', 'Y', encumbered_flag)
where po_distribution_id in
(select po_distribution_id
from po_distributions
where po_release_id = g_docid);
update po_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'Y', 'REVERSE', 'N',
'LIQUIDATE', 'N', 'REJECT',
encumbered_flag, 'N')
where po_line_id = g_lineid;
update po_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
'REVERSE', 'Y', encumbered_flag)
where po_distribution_id in
(select po_distribution_id
from po_distributions
where po_line_id = g_lineid);
update po_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'Y', 'REVERSE', 'N',
'LIQUIDATE', 'N', 'REJECT',
encumbered_flag, 'N')
where line_location_id = g_shipid;
update po_distributions
set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
'REVERSE', 'Y', encumbered_flag)
where po_distribution_id in
(select po_distribution_id
from po_distributions
where line_location_id = g_shipid);
update po_line_locations poll
set encumbered_flag =
(select decode(count(pod.po_distribution_id), 0, 'Y', 'N')
from po_distributions pod
where pod.line_location_id = poll.line_location_id
and pod.encumbered_flag = 'N')
where poll.po_release_id = g_docid;
update po_line_locations poll
set encumbered_flag =
(select decode(count(pod.po_distribution_id), 0, 'Y', 'N')
from po_distributions pod
where pod.line_location_id = poll.line_location_id
and pod.encumbered_flag = 'N')
where poll.line_location_id in
(select pod.line_location_id
from po_distributions pod,
po_distributions prd
where pod.po_distribution_id = prd.source_distribution_id
and prd.po_release_id = g_docid);
select glbp.reference1 doc_type,
to_number(glbp.reference2) doc_id,
to_number(glbp.reference3) dist_id,
glbp.result_code,
glbp.status_code,
glbp.funds_check_level_code,
glbp.accounted_dr,
glbp.accounted_cr,
glbp.automatic_encumbrance_flag
from gl_bc_packets glbp
where glbp.originating_rowid is null
and glbp.packet_id = dist_packet_id
and glbp.template_id is NULL
order by packet_id, to_number(reference3);
update po_distributions
set encumbered_flag =
decode(sign(c_pkt.accounted_dr - c_pkt.accounted_cr), 1, 'Y', 0, encumbered_flag, 'N')
where po_distribution_id = c_pkt.dist_id;
update po_distributions
set encumbered_amount =
round((nvl(encumbered_amount, 0) + c_pkt.accounted_dr - c_pkt.accounted_cr), 3)
where po_distribution_id = c_pkt.dist_id;
update po_distributions
set encumbered_flag =
decode(sign(c_pkt.accounted_dr - c_pkt.accounted_cr), 1, 'N', 0, encumbered_flag, 'Y')
where po_distribution_id = c_pkt.dist_id;
update po_req_distributions
set encumbered_flag =
decode(sign(c_pkt.accounted_dr - c_pkt.accounted_cr), 1, 'Y', 0, encumbered_flag, 'N')
where distribution_id = c_pkt.dist_id;
update po_req_distributions
set encumbered_amount =
round((nvl(encumbered_amount, 0) + c_pkt.accounted_dr - c_pkt.accounted_cr), 3)
where distribution_id = c_pkt.dist_id;
update po_req_distributions
set encumbered_flag =
decode(sign(c_pkt.accounted_dr - c_pkt.accounted_cr), 1, 'N', 0, encumbered_flag, 'Y')
where distribution_id = c_pkt.dist_id;
update po_distributions
set failed_funds_lookup_code = c_pkt.result_code
where po_distribution_id = c_pkt.dist_id;
update po_req_distributions
set failed_funds_lookup_code = c_pkt.result_code
where distribution_id = c_pkt.dist_id;
FUNCTION po_err_insert RETURN BOOLEAN IS
l_linemsg VARCHAR2(25);
select po_online_report_text_s.nextval
from dual;
insert into po_online_report_text(online_report_id,
last_update_login,
last_updated_by,
last_update_date,
created_by,
creation_date,
line_num,
shipment_num,
distribution_num,
sequence,
text_line)
select l_reportid,
g_loginid,
g_userid,
sysdate,
g_userid,
sysdate,
prl.line_num,
0,
prd.distribution_num,
rownum,
l_linemsg || to_char(prl.line_num) ||
' ' || l_distmsg ||
to_char(prd.distribution_num) || ' ' ||
gll.meaning
from gl_bc_packets gbp,
gl_lookups gll,
po_requisition_lines prl,
po_req_distributions prd
where gbp.packet_id = g_packetid
and gbp.status_code in ('R', 'F', 'T')
and gbp.result_code = gll.lookup_code
and gll.lookup_type = 'FUNDS_CHECK_RESULT_CODE'
and gbp.reference3 = prd.distribution_id
and prd.requisition_line_id = prl.requisition_line_id;
'Inserted ' || SQL%ROWCOUNT || ' Records into po_online_report_text' || g_delim;
insert into po_online_report_text(online_report_id,
last_update_login,
last_updated_by,
last_update_date,
created_by,
creation_date,
line_num,
shipment_num,
distribution_num,
sequence,
text_line)
select l_reportid,
g_loginid,
g_userid,
sysdate,
g_userid,
sysdate,
pol.line_num,
poll.shipment_num,
pod.distribution_num,
rownum,
l_linemsg || to_char(pol.line_num) ||
' ' || l_shipmsg ||
to_char(poll.shipment_num) || ' ' ||
l_distmsg ||
to_char(pod.distribution_num) || ' ' ||
gll.meaning
from gl_bc_packets gbp,
gl_lookups gll,
po_lines pol,
po_line_locations poll,
po_distributions pod
where gbp.packet_id = g_packetid
and gbp.status_code in ('R', 'F', 'T')
and gbp.result_code = gll.lookup_code
and gll.lookup_type = 'FUNDS_CHECK_RESULT_CODE'
and gbp.reference3 = pod.po_distribution_id
and pod.po_line_id = pol.po_line_id
and pod.line_location_id = poll.line_location_id;
'Inserted ' || SQL%ROWCOUNT || ' Records into po_online_report_text' || g_delim;
END po_err_insert;