[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'x'
FROM po_requisition_headers prh,
po_requisition_lines prl,
po_req_distributions prd
WHERE prh.requisition_header_id = p_document_id
AND prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id
FOR UPDATE NOWAIT;
SELECT 'x'
FROM po_headers poh,
po_lines pol ,
po_line_locations poll,
po_distributions pod
WHERE poh.po_header_id = p_document_id
AND poh.po_header_id = pol.po_header_id
AND pol.po_header_id = poll.po_header_id
AND poll.po_header_id = pod.po_header_id
FOR UPDATE NOWAIT;
SELECT 'x'
FROM po_releases por,
po_line_locations poll,
po_distributions pod
WHERE por.po_release_id = p_document_id
AND por.po_release_id = poll.po_release_id
AND poll.po_release_id = pod.po_release_id
FOR UPDATE NOWAIT;
SELECT 'x'
FROM po_headers poh,
po_distributions pod
WHERE poh.po_header_id = p_document_id
AND poh.po_header_id = pod.po_header_id
FOR UPDATE NOWAIT;
INSERT INTO igc_cbc_po_process_excpts_all
(
document_type,
document_id,
line_id,
line_location_id,
distribution_id,
org_id,
sob_id,
process_type,
process_phase,
conc_request_id,
exception_code,
exception_reason,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by
)
VALUES
(
g_exception_tbl(l_index).document_type,
g_exception_tbl(l_index).document_id,
g_exception_tbl(l_index).line_id,
g_exception_tbl(l_index).line_location_id,
g_exception_tbl(l_index).distribution_id,
p_org_id,
p_sobid,
'YE',
p_process_phase,
p_conc_request_id,
g_exception_tbl(l_index).exception_code,
g_exception_tbl(l_index).exception_reason,
SYSDATE,
g_user_id,
g_login,
SYSDATE,
g_user_id
);
g_exception_tbl.DELETE;
PROCEDURE Insert_Exception_Record(p_document_type IN VARCHAR2 := NULL,
p_document_id IN NUMBER := NULL,
p_line_id IN NUMBER := NUll,
p_line_location_id IN NUMBER := NULL,
p_distribution_id IN NUMBER := NULL,
p_exception_reason IN VARCHAR2,
p_exception_code IN VARCHAR2 := NULL
) AS
l_full_path VARCHAR2(500) := g_path||'Insert_Exception_Record';
Put_Debug_Msg (l_full_path,p_debug_msg => '**** Insert Exception Record ****');
FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Insert_Exception_Record');
END Insert_Exception_Record;
SELECT glsob.enable_budgetary_control_flag
,glsob.chart_of_accounts_id
FROM gl_sets_of_books glsob
WHERE glsob.set_of_books_id = p_sobid ;
SELECT req_encumbrance_type_id,
purch_encumbrance_type_id,
req_encumbrance_flag,
purch_encumbrance_flag
FROM financials_system_parameters
WHERE set_of_books_id = p_sobid ;
SELECT cc_prov_encmbrnc_type_id,
cc_conf_encmbrnc_type_id,
cc_prov_encmbrnc_enable_flag,
cc_conf_encmbrnc_enable_flag
FROM igc_cc_encmbrnc_ctrls_v
WHERE org_id = p_org_id ;
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_return_status
);
DELETE FROM igc_cbc_po_process_excpts_all;
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code);
SELECT gp.period_num,
gp.period_name,
gps.start_date,
gps.end_date,
gps.closing_status gl_period_status,
pos.closing_status po_period_status,
gp.quarter_num
FROM gl_period_statuses gps,
gl_periods gp,
gl_period_statuses pos,
gl_sets_of_books gb,
fnd_application gl,
fnd_application po
WHERE gb.set_of_books_id = p_sobid
AND gp.period_set_name = gb.period_set_name
AND gp.period_type = gb.accounted_period_type
AND gps.set_of_books_id = gb.set_of_books_id
AND gps.period_name = gp.period_name
AND gps.application_id = gl.application_id
AND gps.period_num = gp.period_num
AND gl.application_short_name = 'SQLGL'
AND gp.period_year = c_p_year
AND gp.adjustment_period_flag = 'N'
AND pos.set_of_books_id = gb.set_of_books_id
AND pos.period_name = gp.period_name
AND pos.application_id = po.application_id
AND po.application_short_name = 'PO'
AND pos.period_num = gps.period_num
ORDER BY gp.period_num ASC ;
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code);
INSERT INTO igc_cbc_po_process_gt
(document_type,
po_header_id,
po_release_id,
line_id,
line_location_id,
distribution_id,
accrue_on_receipt,
quantity_ordered,
quantity_billed,
encumbered_flag,
gl_encumbered_date,
gl_encumbered_period_name
)
SELECT
DECODE(pod.po_release_id, NULL, 'PO', 'REL') document_type,
pod.po_header_id,
pod.po_release_id,
pod.po_line_id,
pod.line_location_id,
pod.po_distribution_id,
pod.accrue_on_receipt_flag,
pod.quantity_ordered,
pod.quantity_billed,
NVL(pod.encumbered_flag,'N'),
pod.gl_encumbered_date,
pod.gl_encumbered_period_name
FROM po_distributions_all pod,
po_line_locations_all poll,
po_lines_all pol
WHERE DECODE(poll.accrue_on_receipt_flag,
'N',
NVL(pod.quantity_ordered,0) - GREATEST(NVL(pod.quantity_billed,0),
NVL(pod.unencumbered_quantity,0)),
'Y',
NVL(pod.quantity_ordered,0) - GREATEST(NVL(pod.quantity_delivered,0),
NVL(pod.unencumbered_quantity,0)),
0)<> 0
AND pol.po_header_id = poll.po_header_id
AND poll.po_line_id = pol.po_line_id
AND pod.line_location_id = poll.line_location_id
AND pod.po_line_id = pol.po_line_id
AND pod.po_header_id = pol.po_header_id
AND pod.po_line_id = pol.po_line_id
AND NVL(pol.closed_code,'X') <> 'FINALLY CLOSED'
AND NVL(pol.cancel_flag,'N') = 'N'
AND pol.org_id = p_org_id
AND NVL(poll.closed_code,'X') <> 'FINALLY CLOSED'
AND poll.shipment_type IN ('STANDARD','PLANNED','BLANKET','SCHEDULED')
AND NVL(poll.cancel_flag,'N') = 'N'
AND poll.org_id = p_org_id
AND pod.prevent_encumbrance_flag = 'N'
AND pod.org_id = p_org_id
AND pod.gl_encumbered_date >= p_prev_year_start_date
AND pod.gl_encumbered_date <= p_prev_year_end_date
;
SELECT COUNT(1) INTO l_rec_count FROM igc_cbc_po_process_gt;
Put_Debug_Msg (l_full_path,p_debug_msg => 'Insert Record Count: '||l_rec_count);
DELETE FROM IGC_CBC_PO_PROCESS_GT ;
INSERT INTO igc_cbc_po_process_gt
(
document_type,
req_header_id,
line_id,
distribution_id,
encumbered_flag,
gl_encumbered_date,
gl_encumbered_period_name,
prevent_encumbrance_flag,
blanket_po_header_id
)
SELECT
'REQ',
prl.requisition_header_id,
prl.requisition_line_id,
prd.distribution_id,
NVL(prd.encumbered_flag, 'N'),
prd.gl_encumbered_date,
prd.gl_encumbered_period_name,
Nvl(prd.prevent_encumbrance_flag,'N'),
prl.blanket_po_header_id
FROM
po_requisition_lines_all prl,
po_req_distributions_all prd
WHERE
NVL(prl.closed_code, 'X') NOT IN ('CANCELLED','FINALLY CLOSED')
AND NVL(prl.cancel_flag, 'N') = 'N'
AND NVL(prl.line_location_id, -999) = -999
AND prl.source_type_code = 'VENDOR'
AND prl.org_id = p_org_id
AND (NVL(prd.prevent_encumbrance_flag, 'N') = 'N'
OR (NVL(prd.prevent_encumbrance_flag, 'N') = 'Y'
AND prl.blanket_po_header_id IS NOT NULL))
AND prd.requisition_line_id = prl.requisition_line_id
AND prd.org_id = p_org_id
AND prd.gl_encumbered_date BETWEEN p_prev_year_start_date AND p_prev_year_end_date ;
SELECT COUNT(1) INTO l_rec_count FROM igc_cbc_po_process_gt;
Put_Debug_Msg (l_full_path,p_debug_msg => 'Insert Record Count: '||l_rec_count);
DELETE FROM IGC_CBC_PO_PROCESS_GT ;
INSERT INTO igc_cbc_po_process_gt
(
document_type,
po_header_id,
line_id,
distribution_id,
quantity_ordered,
quantity_billed,
encumbered_flag,
gl_encumbered_date,
gl_encumbered_period_name
)
SELECT
'PA',
poh.po_header_id,
NULL,
pod.po_distribution_id,
pod.encumbered_amount+ pod.unencumbered_amount, --pod.amount_to_encumber,
pod.unencumbered_quantity,
Nvl(pod.encumbered_flag,'N'),
pod.gl_encumbered_date,
pod.gl_encumbered_period_name
FROM po_distributions_all pod,
po_headers_all poh
WHERE (Nvl(pod.encumbered_amount+ pod.unencumbered_amount,0) > 0
AND Nvl(pod.encumbered_amount+ pod.unencumbered_amount,0) <> Nvl(pod.unencumbered_amount,0)
OR Nvl(pod.encumbered_amount+ pod.unencumbered_amount,0) <> Nvl(poh.blanket_total_amount,0))
AND Nvl(poh.encumbrance_required_flag,'N') = 'Y'
AND poh.type_lookup_code = 'BLANKET'
AND poh.closed_date IS NULL
AND Nvl(poh.cancel_flag,'N') = 'N'
AND pod.po_header_id = poh.po_header_id
AND pod.distribution_type = 'AGREEMENT'
AND Nvl(pod.prevent_encumbrance_flag,'N') = 'N'
AND pod.org_id = p_org_id
AND poh.org_id = p_org_id
AND pod.gl_encumbered_date >= p_prev_year_start_date
AND pod.gl_encumbered_date <= p_prev_year_end_date;
SELECT COUNT(1) INTO l_rec_count FROM igc_cbc_po_process_gt;
Put_Debug_Msg (l_full_path,p_debug_msg => 'Insert Record Count: '||l_rec_count);
SELECT DISTINCT tmp.line_id,
tmp.line_location_id,
tmp.distribution_id,
ai.invoice_num,
DECODE(NVL(aid.match_status_flag, 'N'), 'N', 'IGC_PO_YEP_INV_NAPPR',
'T', 'IGC_PO_YEP_INV_NAPPR',
DECODE(NVL(ai.payment_status_flag, 'N'), 'N', 'IGC_PO_YEP_INV_NPAID',
DECODE(NVL(aip.posted_flag, 'N'), 'N', 'IGC_PO_YEP_INV_PAY_NPOST',
DECODE(SIGN(tmp.quantity_ordered - tmp.quantity_billed), -1,
'IGC_PO_YEP_PO_OVERBILLED')))) result_error_code
FROM ap_invoices ai,
ap_invoice_payments aip,
ap_invoice_distributions aid,
igc_cbc_po_process_gt tmp
WHERE ai.invoice_id = aid.invoice_id
AND aip.invoice_id(+) = ai.invoice_id
AND aid.po_distribution_id = tmp.distribution_id
AND tmp.accrue_on_receipt = 'N'
AND tmp.encumbered_flag = 'Y'
AND tmp.po_header_id = p_document_id
AND ai.cancelled_date IS NULL
ORDER BY result_error_code ASC;
SELECT DISTINCT tmp.line_id,
tmp.line_location_id,
tmp.distribution_id,
ai.invoice_num,
DECODE(NVL(aid.match_status_flag, 'N'), 'N', 'IGC_PO_YEP_INV_NAPPR',
'T', 'IGC_PO_YEP_INV_NAPPR',
DECODE(NVL(ai.payment_status_flag, 'N'), 'N', 'IGC_PO_YEP_INV_NPAID',
DECODE(NVL(aip.posted_flag, 'N'), 'N', 'IGC_PO_YEP_INV_PAY_NPOST',
DECODE(SIGN(tmp.quantity_ordered - tmp.quantity_billed), -1,
'IGC_PO_YEP_REL_OVERBILLED')))) result_error_code
FROM ap_invoices ai,
ap_invoice_payments aip,
ap_invoice_distributions aid,
igc_cbc_po_process_gt tmp
WHERE ai.invoice_id = aid.invoice_id
AND aip.invoice_id(+) = ai.invoice_id
AND aid.po_distribution_id = tmp.distribution_id
AND tmp.accrue_on_receipt = 'N'
AND tmp.encumbered_flag = 'Y'
AND tmp.encumbered_flag = 'Y'
AND tmp.po_release_id = p_document_id
AND ai.cancelled_date IS NULL
ORDER BY result_error_code ASC;
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_valid_dist_rec.result_error_code(l_index),
p_document_type => p_document_type,
p_document_id => p_document_id,
p_line_id => l_valid_dist_rec.line_id(l_index),
p_line_location_id => l_valid_dist_rec.line_location_id(l_index),
p_distribution_id => l_valid_dist_rec.distribution_id(l_index)
);
l_valid_dist_rec.distribution_id.DELETE;
SELECT gl_bc_packets_s.nextval
INTO l_packet_id
FROM DUAL;
INSERT INTO gl_bc_packets
(
packet_id,
Ledger_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
l_packet_id,
glsob.set_of_books_id,
'Purchasing',
'Requisitions',
prd.budget_account_id,
'E',
p_prev_year_end_period,
p_year,
p_prev_year_end_num,
p_prev_year_end_quarter,
glsob.currency_code,
'P',
sysdate,
g_user_id,
NULL,
fsp.req_encumbrance_type_id,
-1 * (DECODE(base_cur.minimum_accountable_unit,
NULL,
ROUND((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
/ prd.req_line_quantity)
* (GREATEST
(
DECODE
(NVL(poll.accrue_on_receipt_flag,'N'),
'N',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_billed,0),
NVL(pod.unencumbered_quantity,0)
)
),
'Y',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_delivered,0),
NVL(pod.unencumbered_quantity,0))
)
) -- DECODE
,0) -- GREATEST
),
base_cur.precision), -- ROUND
ROUND(((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
/ prd.req_line_quantity)
* ((GREATEST
(
DECODE
(NVL(poll.accrue_on_receipt_flag,'N'),
'N',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_billed,0),
NVL(pod.unencumbered_quantity,0)
)
),
'Y',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST (NVL(pod.quantity_delivered,0),
NVL(pod.unencumbered_quantity,0))
)
) -- DECODE
,0) -- GREATEST
) / base_cur.minimum_accountable_unit)
* base_cur.minimum_accountable_unit),
base_cur.precision))) Entered_Dr,
0 Entered_Cr,
-1 * (DECODE(base_cur.minimum_accountable_unit,
NULL,
ROUND((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
/ prd.req_line_quantity)
* (GREATEST
(
DECODE
(NVL(poll.accrue_on_receipt_flag,'N'),
'N',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_billed,0),
NVL(pod.unencumbered_quantity,0)
)
),
'Y',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_delivered,0),
NVL(pod.unencumbered_quantity,0))
)
) -- DECODE
,0) -- GREATEST
),
base_cur.precision), -- ROUND
ROUND(((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
/ prd.req_line_quantity)
* ((GREATEST
(
DECODE
(NVL(poll.accrue_on_receipt_flag,'N'),
'N',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_billed,0),
NVL(pod.unencumbered_quantity,0)
)
),
'Y',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_delivered,0),
NVL(pod.unencumbered_quantity,0))
)
) -- DECODE
,0) -- GREATEST
) / base_cur.minimum_accountable_unit)
* base_cur.minimum_accountable_unit),
base_cur.precision))) Accounted_Dr,
0 Accounted_Cr,
prd.ussgl_transaction_code,
'REQ',
prl.requisition_header_id,
prd.distribution_id,
prh.segment1,
prl.reference_num,
SUBSTR(prl.item_description,1,25) || '-Year End Process, Adjust Requisition Encumbrance entry'
FROM
fnd_currencies base_cur,
gl_sets_of_books glsob,
financials_system_parameters fsp,
po_requisition_lines prl,
po_req_distributions prd,
po_requisition_headers prh,
po_line_locations poll,
po_distributions pod,
po_lines pol
WHERE
NVL(prl.closed_code,'OPEN') NOT IN ('CANCELLED','FINALLY CLOSED')
AND NVL(prl.cancel_flag,'N') = 'N'
AND NVL(prd.prevent_encumbrance_flag,'N') = 'N'
AND prd.requisition_line_id = prl.requisition_line_id
AND prl.line_location_id = poll.line_location_id
AND pod.line_location_id = poll.line_location_id
AND pod.po_distribution_id = p_distribution_id_tbl(l_index)
AND poll.shipment_type IN ('STANDARD','PLANNED')
AND NVL(poll.cancel_flag,'N') = 'N'
AND NVL(poll.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND base_cur.currency_code = glsob.currency_code
AND fsp.set_of_books_id = glsob.set_of_books_id
AND pol.po_line_id = poll.po_line_id
AND prh.requisition_header_id = prl.requisition_header_id ;
Put_Debug_Msg (l_full_path,p_debug_msg => 'Completed insert to gl_bc_packets');
SELECT gl_bc_packets_s.nextval
INTO l_packet_id
FROM DUAL;
INSERT INTO gl_bc_packets
(
packet_id,
Ledger_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
l_packet_id,
glsob.set_of_books_id,
'Purchasing',
'Requisitions',
prd.budget_account_id,
'E',
p_curr_year_start_period,
p_year + 1,
p_curr_year_start_num,
p_curr_year_start_quarter,
glsob.currency_code,
'P',
sysdate,
g_user_id,
NULL,
fsp.req_encumbrance_type_id,
0 Entered_Dr,
-1 * (DECODE(base_cur.minimum_accountable_unit,
NULL,
ROUND((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
/ prd.req_line_quantity)
* (GREATEST
(
DECODE
(NVL(poll.accrue_on_receipt_flag,'N'),
'N',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_billed,0),
NVL(pod.unencumbered_quantity,0)
)
),
'Y',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_delivered,0),
NVL(pod.unencumbered_quantity,0))
)
) -- DECODE
,0) -- GREATEST
),
base_cur.precision), -- ROUND
ROUND(((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
/ prd.req_line_quantity)
* ((GREATEST
(
DECODE
(NVL(poll.accrue_on_receipt_flag,'N'),
'N',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_billed,0),
NVL(pod.unencumbered_quantity,0)
)
),
'Y',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_delivered,0),
NVL(pod.unencumbered_quantity,0))
)
) -- DECODE
,0) -- GREATEST
) / base_cur.minimum_accountable_unit)
* base_cur.minimum_accountable_unit),
base_cur.precision))) Entered_Cr,
0 Accounted_Dr,
-1 * (DECODE(base_cur.minimum_accountable_unit,
NULL,
ROUND((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
/ prd.req_line_quantity)
* (GREATEST
(
DECODE
(NVL(poll.accrue_on_receipt_flag,'N'),
'N',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_billed,0),
NVL(pod.unencumbered_quantity,0)
)
),
'Y',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_delivered,0),
NVL(pod.unencumbered_quantity,0))
)
) -- DECODE
,0) -- GREATEST
),
base_cur.precision), -- ROUND
ROUND(((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
/ prd.req_line_quantity)
* ((GREATEST
(
DECODE
(NVL(poll.accrue_on_receipt_flag,'N'),
'N',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_billed,0),
NVL(pod.unencumbered_quantity,0)
)
),
'Y',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_delivered,0),
NVL(pod.unencumbered_quantity,0))
)
) -- DECODE
,0) -- GREATEST
) / base_cur.minimum_accountable_unit)
* base_cur.minimum_accountable_unit),
base_cur.precision))) Accounted_Cr,
prd.ussgl_transaction_code,
'REQ',
prl.requisition_header_id,
prd.distribution_id,
prh.segment1,
prl.reference_num,
SUBSTR(prl.item_description,1,25) || '-Year End Process, Adjust Requisition Encumbrance entry'
FROM
fnd_currencies base_cur,
gl_sets_of_books glsob,
financials_system_parameters fsp,
po_requisition_lines prl,
po_req_distributions prd,
po_requisition_headers prh,
po_line_locations poll,
po_distributions pod,
po_lines pol
WHERE
NVL(prl.closed_code,'OPEN') NOT IN ('CANCELLED','FINALLY CLOSED')
AND NVL(prl.cancel_flag,'N') = 'N'
AND NVL(prd.prevent_encumbrance_flag,'N') = 'N'
AND prd.requisition_line_id = prl.requisition_line_id
AND prl.line_location_id = poll.line_location_id
AND pod.line_location_id = poll.line_location_id
AND pod.po_distribution_id = p_distribution_id_tbl(l_index)
AND poll.shipment_type IN ('STANDARD','PLANNED')
AND NVL(poll.cancel_flag,'N') = 'N'
AND NVL(poll.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND base_cur.currency_code = glsob.currency_code
AND fsp.set_of_books_id = glsob.set_of_books_id
AND pol.po_line_id = poll.po_line_id
AND prh.requisition_header_id = prl.requisition_header_id
;
Put_Debug_Msg (l_full_path,p_debug_msg => 'Completed insert to gl_bc_packets');
SELECT gl_bc_packets_s.nextval
INTO l_packet_id
FROM DUAL;
INSERT INTO gl_bc_packets
(
packet_id,
Ledger_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
l_packet_id,
glsob.set_of_books_id,
'Purchasing',
'Requisitions',
prd.budget_account_id,
'E',
p_prev_year_end_period,
p_year,
p_prev_year_end_num,
p_prev_year_end_quarter,
glsob.currency_code,
'P',
sysdate,
g_user_id,
NULL,
fsp.req_encumbrance_type_id,
-1 * (DECODE(base_cur.minimum_accountable_unit,
NULL,
ROUND((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
/ prd.req_line_quantity)
* (GREATEST
(
DECODE
(NVL(poll.accrue_on_receipt_flag,'N'),
'N',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_billed,0),
NVL(pod.quantity_delivered,0)
)
),
'Y',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* NVL(pod.quantity_delivered,0)
)
) -- DECODE
,0) -- GREATEST
),
base_cur.precision), -- ROUND
ROUND(((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
/ prd.req_line_quantity)
* ((GREATEST
(
DECODE
(NVL(poll.accrue_on_receipt_flag,'N'),
'N',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_billed,0),
NVL(pod.quantity_delivered,0)
)
),
'Y',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* NVL(pod.quantity_delivered,0)
)
) -- DECODE
,0) -- GREATEST
) / base_cur.minimum_accountable_unit)
* base_cur.minimum_accountable_unit),
base_cur.precision))) Entered_Dr,
0 Entered_Cr,
-1 * (DECODE(base_cur.minimum_accountable_unit,
NULL,
ROUND((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
/ prd.req_line_quantity)
* (GREATEST
(
DECODE
(NVL(poll.accrue_on_receipt_flag,'N'),
'N',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_billed,0),
NVL(pod.quantity_delivered,0)
)
),
'Y',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* NVL(pod.quantity_delivered,0)
)
) -- DECODE
,0) -- GREATEST
),
base_cur.precision), -- ROUND
ROUND(((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
/ prd.req_line_quantity)
* ((GREATEST
(
DECODE
(NVL(poll.accrue_on_receipt_flag,'N'),
'N',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_billed,0),
NVL(pod.quantity_delivered,0)
)
),
'Y',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* NVL(pod.quantity_delivered,0)
)
) -- DECODE
,0) -- GREATEST
) / base_cur.minimum_accountable_unit)
* base_cur.minimum_accountable_unit),
base_cur.precision))) Accounted_Dr,
0 Accounted_Cr,
prd.ussgl_transaction_code,
'REQ',
prl.requisition_header_id,
prd.distribution_id,
prh.segment1,
prl.reference_num,
SUBSTR(prl.item_description,1,25) || '-Year End Process, Adjust Requisition Encumbrance entry'
FROM
fnd_currencies base_cur,
gl_sets_of_books glsob,
financials_system_parameters fsp,
po_requisition_lines prl,
po_req_distributions prd,
po_requisition_headers prh,
po_line_locations poll,
po_distributions pod,
po_lines pol
WHERE
NVL(prl.closed_code,'OPEN') NOT IN ('CANCELLED','FINALLY CLOSED')
AND NVL(prl.cancel_flag,'N') = 'N'
AND NVL(prd.prevent_encumbrance_flag,'N') = 'N'
AND prd.requisition_line_id = prl.requisition_line_id
AND prl.line_location_id = poll.line_location_id
AND pod.line_location_id = poll.line_location_id
AND pod.po_distribution_id = p_distribution_id_tbl(l_index)
AND NVL(poll.cancel_flag,'N') = 'N'
AND NVL(poll.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND base_cur.currency_code = glsob.currency_code
AND fsp.set_of_books_id = glsob.set_of_books_id
AND pol.po_line_id = poll.po_line_id
AND poll.shipment_type IN ('BLANKET')
AND prh.requisition_header_id = prl.requisition_header_id
;
SELECT gl_bc_packets_s.nextval
INTO l_packet_id
FROM DUAL;
INSERT INTO gl_bc_packets
(
packet_id,
Ledger_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
l_packet_id,
glsob.set_of_books_id,
'Purchasing',
'Requisitions',
prd.budget_account_id,
'E',
p_curr_year_start_period,
p_year + 1,
p_curr_year_start_num,
p_curr_year_start_quarter,
glsob.currency_code,
'P',
sysdate,
g_user_id,
NULL,
fsp.req_encumbrance_type_id,
0 Entered_Dr,
-1 * (DECODE(base_cur.minimum_accountable_unit,
NULL,
ROUND((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
/ prd.req_line_quantity)
* (GREATEST
(
DECODE
(NVL(poll.accrue_on_receipt_flag,'N'),
'N',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_billed,0),
NVL(pod.quantity_delivered,0)
)
),
'Y',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* NVL(pod.quantity_delivered,0)
)
) -- DECODE
,0) -- GREATEST
),
base_cur.precision), -- ROUND
ROUND(((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
/ prd.req_line_quantity)
* ((GREATEST
(
DECODE
(NVL(poll.accrue_on_receipt_flag,'N'),
'N',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_billed,0),
NVL(pod.quantity_delivered,0)
)
),
'Y',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* NVL(pod.quantity_delivered,0)
)
) -- DECODE
,0) -- GREATEST
) / base_cur.minimum_accountable_unit)
* base_cur.minimum_accountable_unit),
base_cur.precision))) Entered_Cr,
0 Accounted_Dr,
-1 * (DECODE(base_cur.minimum_accountable_unit,
NULL,
ROUND((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
/ prd.req_line_quantity)
* (GREATEST
(
DECODE
(NVL(poll.accrue_on_receipt_flag,'N'),
'N',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_billed,0),
NVL(pod.quantity_delivered,0)
)
),
'Y',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* NVL(pod.quantity_delivered,0)
)
) -- DECODE
,0) -- GREATEST
),
base_cur.precision), -- ROUND
ROUND(((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
/ prd.req_line_quantity)
* ((GREATEST
(
DECODE
(NVL(poll.accrue_on_receipt_flag,'N'),
'N',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* GREATEST(NVL(pod.quantity_billed,0),
NVL(pod.quantity_delivered,0)
)
),
'Y',
(prd.req_line_quantity
- po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
prl.unit_meas_lookup_code,
prl.item_id)
* NVL(pod.quantity_delivered,0)
)
) -- DECODE
,0) -- GREATEST
) / base_cur.minimum_accountable_unit)
* base_cur.minimum_accountable_unit),
base_cur.precision))) Accounted_Cr,
prd.ussgl_transaction_code,
'REQ',
prl.requisition_header_id,
prd.distribution_id,
prh.segment1,
prl.reference_num,
SUBSTR(prl.item_description,1,25) || '-Year End Process, Adjust Requisition Encumbrance entry'
FROM
fnd_currencies base_cur,
gl_sets_of_books glsob,
financials_system_parameters fsp,
po_requisition_lines prl,
po_req_distributions prd,
po_requisition_headers prh,
po_line_locations poll,
po_distributions pod,
po_lines pol
WHERE
NVL(prl.closed_code,'OPEN') NOT IN ('CANCELLED','FINALLY CLOSED')
AND NVL(prl.cancel_flag,'N') = 'N'
AND NVL(prd.prevent_encumbrance_flag,'N') = 'N'
AND prd.requisition_line_id = prl.requisition_line_id
AND prl.line_location_id = poll.line_location_id
AND pod.line_location_id = poll.line_location_id
AND pod.po_distribution_id = p_distribution_id_tbl(l_index)
-- AND poll.shipment_type IN ('STANDARD','PLANNED')
AND NVL(poll.cancel_flag,'N') = 'N'
AND NVL(poll.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND base_cur.currency_code = glsob.currency_code
AND fsp.set_of_books_id = glsob.set_of_books_id
AND pol.po_line_id = poll.po_line_id
AND poll.shipment_type IN ('BLANKET')
AND prh.requisition_header_id = prl.requisition_header_id
;
SELECT gl_bc_packets_s.nextval
INTO l_packet_id
FROM DUAL;
INSERT INTO gl_bc_packets
(
packet_id,
Ledger_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
l_packet_id,
glsob.set_of_books_id,
'Purchasing',
'Purchases',
prd.budget_account_id,
'E',
p_prev_year_end_period,
p_year,
p_prev_year_end_num,
p_prev_year_end_quarter,
glsob.currency_code,
'P',
sysdate,
g_user_id,
NULL,
fsp.purch_encumbrance_type_id,
-1 * (DECODE(base_cur.minimum_accountable_unit,
NULL,
ROUND((poll.price_override + po_tax_sv.get_tax('PO',pod.po_distribution_id)
/ pod.quantity_ordered)
* NVL(pod.rate,1)
* (
DECODE
(NVL(prll.accrue_on_receipt_flag,'N'),
'N',
(prd.quantity_ordered
- GREATEST(NVL(prd.quantity_billed,0),
NVL(pod.quantity_delivered,0)
)
),
'Y',
(prd.quantity_ordered
- NVL(prd.quantity_delivered,0)
)
) -- DECODE
),
base_cur.precision), -- ROUND
ROUND(((poll.price_override + po_tax_sv.get_tax('PO',pod.po_distribution_id)
/ pod.quantity_ordered)
* NVL(pod.rate,1)
* ((
DECODE
(NVL(prll.accrue_on_receipt_flag,'N'),
'N',
(prd.quantity_ordered
- GREATEST(NVL(prd.quantity_billed,0),
NVL(pod.quantity_delivered,0)
)
),
'Y',
(prd.quantity_ordered
- NVL(prd.quantity_delivered,0)
)
) -- DECODE
) / base_cur.minimum_accountable_unit)
* base_cur.minimum_accountable_unit),
base_cur.precision))) Entered_Dr,
0 Entered_Cr,
-1 * (DECODE(base_cur.minimum_accountable_unit,
NULL,
ROUND((poll.price_override + po_tax_sv.get_tax('PO',pod.po_distribution_id)
/ pod.quantity_ordered)
* NVL(pod.rate,1)
* (
DECODE
(NVL(prll.accrue_on_receipt_flag,'N'),
'N',
(prd.quantity_ordered
- GREATEST(NVL(prd.quantity_billed,0),
NVL(pod.quantity_delivered,0)
)
),
'Y',
(prd.quantity_ordered
- NVL(prd.quantity_delivered,0)
)
) -- DECODE
),
base_cur.precision), -- ROUND
ROUND(((poll.price_override + po_tax_sv.get_tax('PO',pod.po_distribution_id)
/ pod.quantity_ordered)
* NVL(pod.rate,1)
* ((
DECODE
(NVL(prll.accrue_on_receipt_flag,'N'),
'N',
(prd.quantity_ordered
- GREATEST(NVL(prd.quantity_billed,0),
NVL(pod.quantity_delivered,0)
)
),
'Y',
(prd.quantity_ordered
- NVL(prd.quantity_delivered,0)
)
) -- DECODE
) / base_cur.minimum_accountable_unit)
* base_cur.minimum_accountable_unit),
base_cur.precision))) Accounted_Dr,
0 Accounted_Cr,
prd.ussgl_transaction_code,
'PO',
poh.po_header_id,
pod.po_distribution_id,
poh.segment1,
NULL,
SUBSTR(pol.item_description,1,25) || '-Year End Process, Adjust Planned PO Encumbrance entry'
FROM
fnd_currencies base_cur,
gl_sets_of_books glsob,
financials_system_parameters fsp,
po_headers poh,
po_line_locations poll,
po_line_locations prll,
po_distributions pod,
po_distributions prd,
po_lines pol
WHERE
NVL(poll.closed_code,'OPEN') <> ('FINALLY CLOSED')
AND NVL(poll.cancel_flag,'N') = 'N'
AND poh.po_header_id = poll.po_header_id
AND poll.line_location_id = pod.line_location_id
AND pod.po_distribution_id = prd.source_distribution_id
AND prd.po_distribution_id = p_distribution_id_tbl(l_index)
AND NVL(prd.prevent_encumbrance_flag,'N') = 'N'
AND NVL(prd.encumbered_flag,'N') = 'Y'
AND NVL(prll.cancel_flag,'N') = 'N'
AND NVL(prll.closed_code,'OPEN') <> ('FINALLY CLOSED')
AND prll.shipment_type IN ('SCHEDULED')
AND prll.line_location_id = prd.line_location_id
AND base_cur.currency_code = glsob.currency_code
AND fsp.set_of_books_id = glsob.set_of_books_id
AND pol.po_line_id = poll.po_line_id
;
SELECT gl_bc_packets_s.nextval
INTO l_packet_id
FROM DUAL;
INSERT INTO gl_bc_packets
(
packet_id,
Ledger_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
l_packet_id,
glsob.set_of_books_id,
'Purchasing',
'Purchases',
prd.budget_account_id,
'E',
p_curr_year_start_period,
p_year + 1,
p_curr_year_start_num,
p_curr_year_start_quarter,
glsob.currency_code,
'P',
sysdate,
g_user_id,
NULL,
fsp.purch_encumbrance_type_id,
0 Entered_Dr,
-1 * (DECODE(base_cur.minimum_accountable_unit,
NULL,
ROUND((poll.price_override + po_tax_sv.get_tax('PO',pod.po_distribution_id)
/ pod.quantity_ordered)
* NVL(pod.rate,1)
* (
DECODE
(NVL(prll.accrue_on_receipt_flag,'N'),
'N',
(prd.quantity_ordered
- GREATEST(NVL(prd.quantity_billed,0),
NVL(pod.quantity_delivered,0)
)
),
'Y',
(prd.quantity_ordered
- NVL(prd.quantity_delivered,0)
)
) -- DECODE
),
base_cur.precision), -- ROUND
ROUND(((poll.price_override + po_tax_sv.get_tax('PO',pod.po_distribution_id)
/ pod.quantity_ordered)
* NVL(pod.rate,1)
* ((
DECODE
(NVL(prll.accrue_on_receipt_flag,'N'),
'N',
(prd.quantity_ordered
- GREATEST(NVL(prd.quantity_billed,0),
NVL(pod.quantity_delivered,0)
)
),
'Y',
(prd.quantity_ordered
- NVL(prd.quantity_delivered,0)
)
) -- DECODE
) / base_cur.minimum_accountable_unit)
* base_cur.minimum_accountable_unit),
base_cur.precision))) Entered_Cr,
0 Accounted_Dr,
-1 * (DECODE(base_cur.minimum_accountable_unit,
NULL,
ROUND((poll.price_override + po_tax_sv.get_tax('PO',pod.po_distribution_id)
/ pod.quantity_ordered)
* NVL(pod.rate,1)
* (
DECODE
(NVL(prll.accrue_on_receipt_flag,'N'),
'N',
(prd.quantity_ordered
- GREATEST(NVL(prd.quantity_billed,0),
NVL(pod.quantity_delivered,0)
)
),
'Y',
(prd.quantity_ordered
- NVL(prd.quantity_delivered,0)
)
) -- DECODE
),
base_cur.precision), -- ROUND
ROUND(((poll.price_override + po_tax_sv.get_tax('PO',pod.po_distribution_id)
/ pod.quantity_ordered)
* NVL(pod.rate,1)
* ((
DECODE
(NVL(prll.accrue_on_receipt_flag,'N'),
'N',
(prd.quantity_ordered
- GREATEST(NVL(prd.quantity_billed,0),
NVL(pod.quantity_delivered,0)
)
),
'Y',
(prd.quantity_ordered
- NVL(prd.quantity_delivered,0)
)
) -- DECODE
) / base_cur.minimum_accountable_unit)
* base_cur.minimum_accountable_unit),
base_cur.precision))) Accounted_Cr,
prd.ussgl_transaction_code,
'PO',
poh.po_header_id,
pod.po_distribution_id,
poh.segment1,
NULL,
SUBSTR(pol.item_description,1,25) || '-Year End Process, Adjust Planned PO Encumbrance entry'
FROM
fnd_currencies base_cur,
gl_sets_of_books glsob,
financials_system_parameters fsp,
po_headers poh,
po_line_locations poll,
po_line_locations prll,
po_distributions pod,
po_distributions prd,
po_lines pol
WHERE
NVL(poll.closed_code,'OPEN') <> ('FINALLY CLOSED')
AND NVL(poll.cancel_flag,'N') = 'N'
AND poh.po_header_id = poll.po_header_id
AND poll.line_location_id = pod.line_location_id
AND pod.po_distribution_id = prd.source_distribution_id
AND prd.po_distribution_id = p_distribution_id_tbl(l_index)
AND NVL(prd.prevent_encumbrance_flag,'N') = 'N'
AND NVL(prd.encumbered_flag,'N') = 'Y'
AND NVL(prll.cancel_flag,'N') = 'N'
AND NVL(prll.closed_code,'OPEN') <> ('FINALLY CLOSED')
AND prll.shipment_type IN ('SCHEDULED')
AND prll.line_location_id = prd.line_location_id
AND base_cur.currency_code = glsob.currency_code
AND fsp.set_of_books_id = glsob.set_of_books_id
AND pol.po_line_id = poll.po_line_id
;
l_distribution_id_tbl.DELETE;
l_gl_enc_date_tbl.DELETE;
l_gl_enc_prd_tbl.DELETE;
OPEN c_prev_val FOR SELECT distribution_id,
gl_encumbered_date,
gl_encumbered_period_name
FROM igc_cbc_po_process_gt
WHERE encumbered_flag = 'Y'
AND po_header_id = p_document_id ;
OPEN c_prev_val FOR SELECT distribution_id,
gl_encumbered_date,
gl_encumbered_period_name
FROM igc_cbc_po_process_gt
WHERE encumbered_flag = 'Y'
AND po_release_id = p_document_id ;
OPEN c_prev_val FOR SELECT distribution_id,
gl_encumbered_date,
gl_encumbered_period_name
FROM igc_cbc_po_process_gt
WHERE (encumbered_flag = 'Y'
OR ( prevent_encumbrance_flag = 'Y'
AND blanket_po_header_id IS NOT NULL))
AND req_header_id = p_document_id ;
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code,
p_document_type => p_document_type,
p_document_id => p_document_id);
IGC_CBC_PO_GRP.update_cbc_acct_date(p_document_id => p_document_id,
p_document_type => p_document_type,
p_document_sub_type => p_document_subtype,
p_cbc_acct_date => p_prev_year_end_date,
p_api_version => 1,
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
);
Put_Debug_Msg (l_full_path,p_debug_msg => 'Completed Update CBC Acct Date');
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_return_status,
p_document_type => p_document_type,
p_document_id => p_document_id);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_return_code,
p_document_type => p_document_type,
p_document_id => p_document_id);
IGC_CBC_PO_GRP.update_cbc_acct_date(p_document_id => p_document_id,
p_document_type => p_document_type,
p_document_sub_type => p_document_subtype,
p_cbc_acct_date => p_prev_cbc_acct_date,
p_api_version => 1,
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
);
Put_Debug_Msg (l_full_path,p_debug_msg => 'completed Update CBC Acct Date - Unreserve failure');
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_return_status,
p_document_type => p_document_type,
p_document_id => p_document_id);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_document_type => p_document_type,
p_document_id => p_document_id);
UPDATE po_req_distributions prd
SET prd.gl_encumbered_date = p_curr_year_start_date,
prd.gl_encumbered_period_name = p_curr_year_start_period
WHERE prd.distribution_id = l_distribution_id_tbl(l_index);
Put_Debug_Msg (l_full_path,p_debug_msg => 'completed update of po_req_distributions table');
UPDATE po_distributions pod
SET pod.gl_encumbered_date = p_curr_year_start_date,
pod.gl_encumbered_period_name = p_curr_year_start_period
WHERE pod.po_distribution_id = l_distribution_id_tbl(l_index);
Put_Debug_Msg (l_full_path,p_debug_msg => 'completed update of po_distributions table');
IGC_CBC_PO_GRP.update_cbc_acct_date(p_document_id => p_document_id,
p_document_type => p_document_type,
p_document_sub_type => p_document_subtype,
p_cbc_acct_date => p_curr_year_start_date,
p_api_version => 1,
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
);
Put_Debug_Msg (l_full_path,p_debug_msg => 'completed Update CBC Acct Date - current year');
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_return_status,
p_document_type => p_document_type,
p_document_id => p_document_id);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_return_code,
p_document_type => p_document_type,
p_document_id => p_document_id);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code,
p_document_type => p_document_type,
p_document_id => p_document_id);
IGC_CBC_PO_GRP.update_cbc_acct_date(p_document_id => p_document_id,
p_document_type => p_document_type,
p_document_sub_type => p_document_subtype,
p_cbc_acct_date => p_prev_year_end_date,
p_api_version => 1,
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
);
Put_Debug_Msg (l_full_path,p_debug_msg => 'completed Update CBC Acct Date - Reserve failure');
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_return_status,
p_document_type => p_document_type,
p_document_id => p_document_id);
UPDATE po_req_distributions prd
SET prd.gl_encumbered_date = l_gl_enc_date_tbl(l_index),
prd.gl_encumbered_period_name = l_gl_enc_prd_tbl(l_index)
WHERE prd.distribution_id = l_distribution_id_tbl(l_index);
Put_Debug_Msg (l_full_path,p_debug_msg => 'completed 2nd update of po_req_distributions table');
UPDATE po_distributions pod
SET pod.gl_encumbered_date = l_gl_enc_date_tbl(l_index),
pod.gl_encumbered_period_name = l_gl_enc_prd_tbl(l_index)
WHERE pod.po_distribution_id = l_distribution_id_tbl(l_index);
Put_Debug_Msg (l_full_path,p_debug_msg => 'completed 2nd update of po_distributions table');
SELECT authorization_status auth_status,
hold_flag,
release_type document_subtype,
frozen_flag,
cbc_accounting_date
FROM po_releases
WHERE po_release_id = p_release_id ;
SELECT authorization_status auth_status,
user_hold_flag hold_flag,
type_lookup_code document_subtype,
frozen_flag,
cbc_accounting_date
FROM po_headers
WHERE po_header_id = p_header_id ;
SELECT authorization_status auth_status,
closed_code,
type_lookup_code document_subtype,
cbc_accounting_date
FROM po_requisition_headers
WHERE requisition_header_id = p_req_id ;
SELECT 'x'
FROM po_releases por,
igc_cbc_po_process_excpts_all ipe
WHERE por.po_header_id = p_header_id
AND ipe.document_type = 'REL'
AND ipe.document_id = por.po_release_id
AND ipe.conc_request_id = p_conc_request_id ;
SELECT 'x'
FROM po_line_locations poll,
po_requisition_lines porl,
igc_cbc_po_process_excpts_all ipe
WHERE porl.requisition_header_id = p_req_id
AND porl.line_location_id = poll.line_location_id
AND ipe.document_id = poll.po_header_id
AND ipe.conc_request_id = p_conc_request_id ;
SELECT 'x'
FROM po_distributions pod,
po_requisition_lines porl,
po_headers poh
WHERE porl.requisition_header_id = p_req_id
AND porl.line_location_id = pod.line_location_id
AND pod.gl_encumbered_date >= p_curr_year_start_date
AND pod.po_header_id = poh.po_header_id
AND (
NVL(poh.authorization_status,'INCOMPLETE') IN
('INCOMPLETE','REQUIRES REAPPROVAL','REJECTED','IN PROCESS','PRE-APPROVED')
OR poh.user_hold_flag = 'Y'
OR ( poh.frozen_flag = 'Y' AND p_process_frozen = 'N')
);
SELECT 'x'
FROM po_releases por,
po_distributions pod
WHERE por.po_header_id = p_header_id
AND por.po_release_id = pod.po_release_id
AND pod.gl_encumbered_date >= p_curr_year_start_date
AND (
NVL(por.authorization_status,'INCOMPLETE') IN
('INCOMPLETE','REQUIRES REAPPROVAL','REJECTED','RETURNED','IN PROCESS','PRE-APPROVED')
OR por.hold_flag = 'Y'
OR ( por.frozen_flag = 'Y' AND p_process_frozen = 'N')
);
SELECT 'X'
FROM igc_cbc_po_process_excpts_all ipe,
po_lines pol
WHERE pol.from_header_id = p_bpa_header_id
AND pol.po_header_id = ipe.document_id
AND ipe.document_type = 'PO'
AND ipe.conc_request_id = p_conc_request_id ;
SELECT 'X'
FROM igc_cbc_po_process_excpts_all ipe,
po_requisition_lines prl
WHERE prl.blanket_po_header_id = p_bpa_header_id
AND prl.requisition_header_id = ipe.document_id
AND ipe.document_type = 'REQ'
AND ipe.conc_request_id = p_conc_request_id ;
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_document_type => p_document_type,
p_document_id => p_po_release_id);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code,
p_document_type => p_document_type,
p_document_id => p_po_release_id);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code,
p_document_type => p_document_type,
p_document_id => p_po_release_id);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code,
p_document_type => p_document_type,
p_document_id => p_po_header_id);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code,
p_document_type => p_document_type,
p_document_id => p_po_header_id);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code,
p_document_type => p_document_type,
p_document_id => p_po_header_id);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code,
p_document_type => p_document_type,
p_document_id => p_po_header_id);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code,
p_document_type => p_document_type,
p_document_id => p_po_header_id);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code,
p_document_type => p_document_type,
p_document_id => p_req_header_id);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code,
p_document_type => p_document_type,
p_document_id => p_req_header_id);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code,
p_document_type => p_document_type,
p_document_id => p_req_header_id);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code,
p_document_type => p_document_type,
p_document_id => p_req_header_id);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code,
p_document_type => p_document_type,
p_document_id => p_po_header_id);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code,
p_document_type => p_document_type,
p_document_id => p_po_header_id);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code,
p_document_type => p_document_type,
p_document_id => p_po_header_id);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code,
p_document_type => p_document_type,
p_document_id => p_po_header_id);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code,
p_document_type => p_document_type,
p_document_id => p_po_header_id);
Insert_Exception_Record(p_exception_reason => l_msg_data,
p_exception_code => l_err_code,
p_document_type => p_document_type,
p_document_id => p_po_header_id);
SELECT DISTINCT tmp.po_release_id,
tmp.po_header_id,
tmp.req_header_id
FROM igc_cbc_po_process_gt tmp
WHERE document_type = c_p_doc_type ;
l_document_id_rec.po_header_id.DELETE;