[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT charge_account_id,
accrual_account_id ,
variance_account_id,
encumbered_flag,
budget_account_id
FROM po_distributions_interface pdi
WHERE pdi.interface_header_id = po_autocreate_params.g_interface_header_id
AND pdi.interface_line_id = p_intf_line_id
ORDER BY pdi.interface_distribution_id;
SELECT prl.fund_source_not_known
INTO l_fund_source_not_known
FROM PO_REQUISITION_LINES_ALL prl
WHERE prl.requisition_line_id = p_lines.requisition_line_id_tbl(i);
SELECT NVL(MAX(distribution_num), 0)
INTO x_distribution_num
FROM po_distributions_merge_v --Add req to mod project
WHERE line_location_id = p_lines.line_loc_id_tbl(i)
AND draft_id = po_autocreate_params.g_draft_id;
SELECT NVL(MAX(distribution_num), 0)
INTO x_distribution_num
FROM po_distributions_merge_v --
WHERE line_location_id = p_lines.line_loc_id_tbl(i)
AND draft_id = po_autocreate_params.g_draft_id;
SELECT order_type_lookup_code
INTO x_order_type_lookup_code
FROM po_line_types
WHERE line_type_id = p_lines.line_type_id_tbl(i);
SELECT set_of_books_id
INTO x_sob_id
FROM financials_system_params_all --
WHERE NVL(org_id, -99) = NVL(PO_AUTOCREATE_PARAMS.g_purchasing_ou_id, -99);
SELECT KANBAN_CARD_ID
INTO x_kanban_card_id
FROM po_requisition_lines_all pol --
WHERE pol.REQUISITION_LINE_ID = p_lines.requisition_line_id_tbl(i);
SELECT application_id
INTO x_po_appl_id
FROM fnd_application
WHERE application_short_name = 'PO';
SELECT application_id
INTO x_gl_appl_id
FROM fnd_application
WHERE application_short_name = 'SQLGL';
SELECT poh.type_lookup_code
INTO l_from_type_lookup_code
FROM po_headers_all poh
WHERE poh.po_header_id=p_lines.from_header_id_tbl(i) ;
SELECT unit_meas_lookup_code
INTO x_po_uom
FROM po_lines_all
WHERE po_line_id = p_lines.from_line_id_tbl(i);
/* before inserting into the distributions table get the conversion rate to convert
into the BPA uom if the uom's on the req and BPA are different .
This conversion is done only if he Convert UOM profile option is set to Yes. */
IF p_lines.unit_of_measure_tbl(i) <> x_po_uom THEN
l_progress := '150';
--update po_distributions interface
UPDATE po_distributions_interface
SET po_distribution_id = po_distributions_s.NEXTVAL,
distribution_num = x_distribution_num + rownum
WHERE interface_header_id = p_lines.intf_header_id_tbl(i)
AND interface_line_id = p_lines.intf_line_id_tbl(i);
update_award_distributions(p_intf_header_id => p_lines.intf_header_id_tbl(i),
p_intf_line_id => p_lines.intf_line_id_tbl(i));
SELECT NVL(drop_ship_flag,'N')
INTO l_drop_ship_flag
FROM po_line_locations_draft_all
WHERE line_location_id= p_lines.line_loc_id_tbl(i)
AND draft_id = po_autocreate_params.g_draft_id;
SELECT pdi.amount_ordered
INTO l_amount_ordered
FROM po_distributions_interface pdi,
po_line_locations_draft_all poll
WHERE pdi.interface_header_id = p_lines.intf_header_id_tbl(i)
AND pdi.interface_line_id = p_lines.intf_line_id_tbl(i)
AND poll.line_location_id = p_lines.line_loc_id_tbl(i)
AND poll.draft_id = po_autocreate_params.g_draft_id;
SELECT PRL.currency_code,
NVL(PRL.rate,1)
INTO x_req_currency_code,
x_req_rate
FROM po_requisition_lines_all PRL
WHERE PRL.requisition_line_id = p_lines.requisition_line_id_tbl(i);
SELECT COUNT(*)
INTO l_dist_count
FROM po_distributions_interface pdi,
po_line_locations_draft_all poll
WHERE pdi.interface_header_id = p_lines.intf_header_id_tbl(i)
AND pdi.interface_line_id = p_lines.intf_line_id_tbl(i)
AND poll.draft_id =po_autocreate_params.g_draft_id
AND poll.line_location_id = p_lines.line_loc_id_tbl(i);
INSERT
INTO po_distributions_draft_all --
(
po_distribution_id,
draft_id,
--who columns
---------------------------------------------------------------
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
---------------------------------------------------------------
po_header_id,
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,
amount_ordered, --
amount_delivered, --
amount_cancelled, --
amount_billed, --
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,
--< Shared Proc FPJ Start >
dest_charge_account_id,
dest_variance_account_id,
--< Shared Proc FPJ End >
wip_entity_id,
wip_line_id,
wip_repetitive_schedule_id,
wip_operation_seq_num,
wip_resource_seq_num,
bom_resource_id,
prevent_encumbrance_flag,
project_id,
task_id,
end_item_unit_number,
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,
award_id,
--togeorge 09/27/2000
--added oke columns
oke_contract_line_id,
oke_contract_deliverable_id,
org_id, --
distribution_type, --
tax_attribute_update_code, --
--interface_distribution_ref --
--partial funding attributes
partial_funded_flag,
funded_value,
quantity_funded,
amount_funded ,
change_in_funded_value,
group_line_id,
clm_misc_loa,
clm_defence_funding,
clm_fms_case_number,
clm_agency_acct_identifier,
change_status
)
SELECT pdi.po_distribution_id, --
po_autocreate_params.g_draft_id,
--default who columns
---------------------------------------------------------------
NVL(p_lines.last_update_date_tbl(i), sysdate),
NVL(p_lines.last_updated_by_tbl(i), FND_GLOBAL.user_id),
NVL(p_lines.last_update_login_tbl(i), FND_GLOBAL.login_id),
NVL(p_lines.creation_date_tbl(i), sysdate),
NVL(p_lines.created_by_tbl(i), FND_GLOBAL.user_id),
NVL(p_lines.request_id_tbl(i), FND_GLOBAL.conc_request_id),
NVL(p_lines.program_application_id_tbl(i), FND_GLOBAL.prog_appl_id),
NVL(p_lines.program_id_tbl(i), FND_GLOBAL.conc_program_id),
NVL(p_lines.program_update_date_tbl(i), sysdate),
---------------------------------------------------------------
p_lines.po_header_id_tbl(i),
p_lines.po_line_id_tbl(i),
p_lines.line_loc_id_tbl(i),
DECODE(PO_AUTOCREATE_PARAMS.g_document_subtype,'RELEASE',p_lines.po_release_id_tbl(i),''),
pdi.req_distribution_id,
NVL(x_sob_id, pdi.set_of_books_id), --
pdi.charge_account_id,
pdi.deliver_to_location_id,
-- if the drop_ship_flag is 'Y' then we
--need to pass null
DECODE(l_drop_ship_flag,'Y',NULL,pdi.deliver_to_person_id),
DECODE( x_order_type_lookup_code,
'QUANTITY', ROUND(DECODE(po_autocreate_params.g_process_code,
'ADD_FUNDS', (DECODE(poll.PRICE_OVERRIDE,
NULL,pdi.quantity_ordered,
0,pdi.quantity_ordered,
pdi.funded_value/poll.PRICE_OVERRIDE)),
pdi.quantity_ordered * x_conversion_rate), 15),
--Commenting out, as in case of grouping distribution count will exceed 1
/*(DECODE(l_dist_count,
1, poll.quantity, pdi.quantity_ordered)) * x_conversion_rate), 15),*/
'AMOUNT', ROUND( (DECODE(po_autocreate_params.g_process_code,
'ADD_FUNDS',pdi.quantity_ordered,
pdi.quantity_ordered) * x_conversion_rate / NVL(x_rate,1)),
/*DECODE(l_dist_count,
1, poll.quantity, pdi.quantity_ordered)) * x_conversion_rate / NVL(x_rate,1)),*/ -- <>
NVL(x_ext_precision, 15) ), NULL ),
--
0,
0,
0,
--
DECODE ( x_order_type_lookup_code -- amount_ordered
, 'RATE' ,ROUND ( ( pdi.amount_ordered * l_uom_conversion_rate / l_currency_conversion_rate) , x_precision ) ,
'FIXED PRICE',ROUND ( ( pdi.amount_ordered / l_currency_conversion_rate) , x_precision ) ,NULL ),
0, -- amount_delivered
0, -- amount_cancelled
0, -- amount_billed
--
pdi.rate_date,
pdi.rate,
x_accrued_flag,
'N'
--
-- If Req encumbrance is on and the profile option requests
-- that the Req's GL date be used, use the Req's GL date.
-- Otherwise, if PO enc is on, use SYSDATE.
-- if PO enc is not on, use NULL.
-- gl_encumbered_date =
,
NVL( DECODE( PO_AUTOCREATE_PARAMS.g_sys.req_encumbrance_flag ,
'Y', DECODE( x_gl_date_option ,
'REQ GL DATE', pdi.gl_encumbered_date , NULL ) ,
NULL ) , DECODE( PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag , 'Y', TRUNC(SYSDATE) , NULL ) )
-- gl_encumbered_period_name =
,
NVL( DECODE( PO_AUTOCREATE_PARAMS.g_sys.req_encumbrance_flag ,
'Y', DECODE(x_gl_date_option ,
'REQ GL DATE', pdi.gl_encumbered_period_name , NULL ) ,
NULL ) , DECODE( PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag , 'Y', PO_AUTOCREATE_PARAMS.g_sys.period_name , NULL ) ) ,
pdi.distribution_num, --
pdi.destination_type_code,
pdi.destination_organization_id,
pdi.destination_subinventory,
pdi.budget_account_id,
pdi.accrual_account_id,
pdi.variance_account_id,
--< Shared Proc FPJ Start >
-- Copy the receiving accounts from the interface table to
-- the PO table.
pdi.dest_charge_account_id,
pdi.dest_variance_account_id,
--< Shared Proc FPJ End >
pdi.wip_entity_id,
pdi.wip_line_id,
pdi.wip_repetitive_schedule_id,
pdi.wip_operation_seq_num,
pdi.wip_resource_seq_num,
pdi.bom_resource_id
--
-- prevent_encumbrance_flag =
,
DECODE( pdi.destination_type_code , PO_AUTOCREATE_PARAMS.g_dest_type_code_SHOP_FLOOR, 'Y' , 'N' ) ,
pdi.project_id,
pdi.task_id,
pdi.end_item_unit_number,
pdi.expenditure_type,
pdi.project_accounting_context,
pdi.destination_context,
pdi.expenditure_organization_id,
pdi.expenditure_item_date,
poll.accrue_on_receipt_flag, -- : Should come from shipment
x_kanban_card_id,
pdi.tax_recovery_override_flag, --
DECODE(pdi.tax_recovery_override_flag, 'Y', pdi.recovery_rate, NULL), --
pdi.award_id, -- OGM_0.0 changes..
--togeorge 09/27/2000
--added oke columns
pdi.oke_contract_line_id,
pdi.oke_contract_deliverable_id,
PO_AUTOCREATE_PARAMS.g_purchasing_ou_id, --
poll.shipment_type, --
nvl2(PO_AUTOCREATE_PARAMS.g_calculate_tax_flag, 'CREATE', NULL), --
--pdi.interface_distribution_ref --
--partial funding attributes
pdi.partial_funded_flag,
pdi.funded_value,
DECODE( x_order_type_lookup_code,
'QUANTITY', DECODE(po_autocreate_params.g_process_code,
'ADD_FUNDS', ROUND((DECODE(poll.PRICE_OVERRIDE,
NULL,pdi.quantity_funded,
0,pdi.quantity_funded,
pdi.funded_value/poll.PRICE_OVERRIDE)), 15),
pdi.quantity_funded), pdi.quantity_funded), -- Bug 11792824 changes
pdi.amount_funded, --
pdi.funded_value,
pdi.group_line_id,
pdi.clm_misc_loa,
pdi.clm_defence_funding,
pdi.clm_fms_case_number,
pdi.clm_agency_acct_identifier,
'NEW'
FROM po_distributions_interface pdi,
po_line_locations_draft_all poll
WHERE pdi.interface_header_id = p_lines.intf_header_id_tbl(i)
AND pdi.interface_line_id = p_lines.intf_line_id_tbl(i)
AND poll.draft_id =po_autocreate_params.g_draft_id
AND poll.line_location_id = p_lines.line_loc_id_tbl(i) ; --
SELECT orig_from_req_flag INTO l_orig_from_req_flag
FROM po_lines_interface
WHERE interface_line_id = p_lines.intf_line_id_tbl(i);
SELECT pod.po_distribution_id BULK COLLECT
INTO l_dist_id_tbl
FROM po_distributions_draft_all pod
WHERE pod.po_line_id = p_lines.po_line_id_tbl(i);
SELECT pod.po_distribution_id ,
pod.project_id ,
pod.task_id ,
pod.award_id ,
pod.expenditure_type ,
pod.expenditure_item_date ,
pod.expenditure_organization_id ,
pod.destination_type_code ,
pod.destination_organization_id ,
pod.destination_subinventory ,
pod.deliver_to_location_id ,
pod.deliver_to_person_id ,
pod.gl_encumbered_date ,
poll.price_override ,
poll.payment_type ,
pod.distribution_type ,
pod.rate
/*Bug 13598209 : PO_LINE_LOCATIONS_ALL & PO_DISTRIBUTIONS_ALL has still not been populated
Hence po_distributions_draft_all & po_line_locations_draft_all needs to be used
*/
FROM po_distributions_draft_all pod,
po_line_locations_draft_all poll
WHERE poll.po_line_id = p_po_line_id
AND pod.line_location_id = poll.line_location_id
AND pod.req_distribution_id IS NULL;
SELECT prd.set_of_books_id
INTO l_sob_id
FROM po_req_distributions_all prd
WHERE prd.requisition_line_id = p_lines.requisition_line_id_tbl(i)
AND ROWNUM = 1;
INSERT
INTO po_distributions_draft_all
(
po_distribution_id ,
draft_id ,
last_update_date ,
last_updated_by ,
last_update_login ,
creation_date ,
created_by ,
po_header_id ,
po_line_id ,
line_location_id ,
distribution_num ,
req_distribution_id ,
set_of_books_id ,
code_combination_id ,
deliver_to_location_id ,
deliver_to_person_id ,
destination_type_code ,
destination_organization_id ,
destination_subinventory ,
project_id ,
task_id ,
award_id ,
end_item_unit_number ,
expenditure_type ,
project_accounting_context ,
destination_context ,
expenditure_organization_id ,
expenditure_item_date ,
rate ,
rate_date ,
budget_account_id ,
accrual_account_id ,
variance_account_id ,
accrued_flag ,
encumbered_flag ,
prevent_encumbrance_flag ,
gl_encumbered_date ,
gl_encumbered_period_name ,
recovery_rate ,
accrue_on_receipt_flag ,
kanban_card_id ,
org_id ,
distribution_type ,
quantity_ordered ,
amount_ordered ,
tax_attribute_update_code, --
--partial funding attributes
partial_funded_flag,
funded_value,
quantity_funded,
amount_funded ,
change_in_funded_value
)
SELECT PO_DISTRIBUTIONS_S.NEXTVAL ,
po_autocreate_params.g_draft_id ,
p_lines.last_update_date_tbl(i) ,
p_lines.last_updated_by_tbl(i) ,
p_lines.last_update_login_tbl(i) ,
p_lines.creation_date_tbl(i) ,
p_lines.created_by_tbl(i) ,
p_lines.po_header_id_tbl(i) ,
p_lines.po_line_id_tbl(i) ,
poll.line_location_id ,
prd.distribution_num ,
prd.distribution_id ,
prd.set_of_books_id ,
prd.code_combination_id ,
prl.deliver_to_location_id ,
prl.to_person_id ,
prl.destination_type_code ,
prl.destination_organization_id ,
prl.destination_subinventory ,
prd.project_id ,
prd.task_id ,
prd.award_id ,
prd.end_item_unit_number ,
prd.expenditure_type ,
prd.project_accounting_context ,
prl.destination_context ,
prd.expenditure_organization_id ,
prd.expenditure_item_date ,
p_lines.hd_rate_tbl(i) ,
p_lines.hd_rate_date_tbl(i) ,
DECODE(poll.shipment_type, 'PREPAYMENT', NULL, prd.budget_account_id) ,
prd.accrual_account_id ,
prd.variance_account_id ,
'N' -- accrued_flag
,
'N' -- encumbered_flag
,
DECODE(PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag, 'Y', DECODE(poll.shipment_type, 'PREPAYMENT', 'Y', 'N'), NULL) -- prevent_encumbrance_flag
,
(
CASE -- gl_encumbered_date
WHEN (PO_AUTOCREATE_PARAMS.g_sys.req_encumbrance_flag = 'Y'
AND l_gl_date_option = 'REQ GL DATE')
THEN prd.gl_encumbered_date
WHEN (PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag = 'Y')
THEN TRUNC(SYSDATE)
ELSE NULL
END) ,
(
CASE -- gl_encumbered_period_name
WHEN (PO_AUTOCREATE_PARAMS.g_sys.req_encumbrance_flag = 'Y'
AND l_gl_date_option = 'REQ GL DATE')
THEN prd.gl_encumbered_period_name
WHEN (PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag = 'Y')
THEN PO_AUTOCREATE_PARAMS.g_sys.period_name
ELSE NULL
END) ,
prd.recovery_rate ,
poll.accrue_on_receipt_flag ,
prl.kanban_card_id ,
PO_AUTOCREATE_PARAMS.g_purchasing_ou_id ,
poll.shipment_type ,
(
CASE -- quantity_ordered
WHEN poll.value_basis <> 'QUANTITY'
THEN NULL
WHEN poll.payment_type IN ('MILESTONE', 'DELIVERY')
THEN ROUND((prd.req_line_quantity / prl.quantity) * poll.quantity, 15)
WHEN poll.payment_type = 'RATE'
THEN ROUND((prd.req_line_amount / prl.amount) * poll.quantity, 15)
END) ,
(
CASE -- amount_ordered
WHEN poll.value_basis <> 'FIXED PRICE'
THEN NULL
ELSE ROUND((prd.req_line_amount / prl.amount) * poll.amount, x_precision)
END) ,
nvl2(PO_AUTOCREATE_PARAMS.g_calculate_tax_flag, 'CREATE', NULL) ,--
--partial funding attributes
prd.partial_funded_flag,
prd.funded_value,
NULL, --prd.quantity_funded,
NULL, --prd.amount_funded,
prd.funded_value
/*Bug 13598209 : PO_LINE_LOCATIONS_ALL has still not been populated
Hence PO_LINE_LOCATIONS_DRAFT_ALL needs to be used*/
FROM PO_LINE_LOCATIONS_DRAFT_ALL poll ,
po_req_distributions_all prd ,
po_requisition_lines_all prl
WHERE poll.po_line_id = p_lines.po_line_id_tbl(i)
AND prd.requisition_line_id = p_lines.requisition_line_id_tbl(i)
AND prl.requisition_line_id = prd.requisition_line_id
AND poll.payment_type <> 'ADVANCE';
INSERT
INTO po_distributions_draft_all
(
po_distribution_id ,
draft_id ,
last_update_date ,
last_updated_by ,
last_update_login ,
creation_date ,
created_by ,
po_header_id ,
po_line_id ,
line_location_id ,
distribution_num ,
req_distribution_id ,
deliver_to_location_id ,
deliver_to_person_id ,
destination_type_code ,
destination_organization_id ,
destination_subinventory ,
rate ,
rate_date ,
accrued_flag ,
encumbered_flag ,
prevent_encumbrance_flag ,
gl_encumbered_date ,
gl_encumbered_period_name ,
accrue_on_receipt_flag ,
org_id ,
distribution_type ,
project_id ,
task_id ,
award_id ,
end_item_unit_number ,
expenditure_type ,
project_accounting_context ,
destination_context ,
expenditure_organization_id ,
expenditure_item_date ,
quantity_ordered ,
amount_ordered ,
set_of_books_id ,
tax_attribute_update_code --
--partial funding attributes
,
partial_funded_flag ,
funded_value ,
quantity_funded ,
amount_funded ,
change_in_funded_value
)
SELECT PO_DISTRIBUTIONS_S.NEXTVAL ,
po_autocreate_params.g_draft_id ,
p_lines.last_update_date_tbl(i) ,
p_lines.last_updated_by_tbl(i) ,
p_lines.last_update_login_tbl(i) ,
p_lines.creation_date_tbl(i) ,
p_lines.created_by_tbl(i) ,
p_lines.po_header_id_tbl(i) ,
p_lines.po_line_id_tbl(i) ,
poll.line_location_id ,
1 -- distribution_num
,
NULL -- req_distribution_id
,
poll.ship_to_location_id ,
NULL -- deliver_to_person_id
,
'EXPENSE' -- destination_type_code
,
poll.ship_to_organization_id ,
NULL -- destination_subinventory
,
p_lines.hd_rate_tbl(i) ,
p_lines.hd_rate_date_tbl(i) ,
'N' -- accrued_flag
,
'N' -- encumbered_flag
,
DECODE(PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag,
'Y', DECODE(poll.shipment_type, 'PREPAYMENT', 'Y', 'N'), NULL) -- prevent_encumbrance_flag
,
DECODE(PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag, 'Y', TRUNC(SYSDATE), NULL) ,
DECODE(PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag, 'Y', PO_AUTOCREATE_PARAMS.g_sys.period_name, NULL) ,
poll.accrue_on_receipt_flag ,
PO_AUTOCREATE_PARAMS.g_purchasing_ou_id ,
poll.shipment_type ,
polli.project_id ,
polli.task_id ,
polli.award_id ,
NULL -- end_item_unit_number
,
polli.expenditure_type ,
NULL -- project_accounting_context
,
'EXPENSE' -- destination_context
,
polli.expenditure_organization_id ,
polli.expenditure_item_date ,
poll.quantity ,
poll.amount ,
PO_AUTOCREATE_PARAMS.g_sys.sob_id ,
nvl2(PO_AUTOCREATE_PARAMS.g_calculate_tax_flag, 'CREATE', NULL) --
--partial funding attributes
,
'N' ,
NULL ,
DECODE(p_lines.order_type_lookup_code_tbl(i),'QUANTITY',poll.quantity) ,
DECODE(p_lines.order_type_lookup_code_tbl(i),'AMOUNT',poll.amount) ,
NULL
/*Bug 13598209 : PO_LINE_LOCATIONS_ALL has still not been populated
Hence PO_LINE_LOCATIONS_DRAFT_ALL needs to be used*/
FROM PO_LINE_LOCATIONS_DRAFT_ALL poll ,
po_line_locations_interface polli
WHERE poll.po_line_id = p_lines.po_line_id_tbl(i)
AND poll.line_location_id = polli.line_location_id
AND poll.payment_type <> 'ADVANCE';
INSERT
INTO po_distributions_draft_all
(
po_distribution_id ,
draft_id ,
last_update_date ,
last_updated_by ,
last_update_login ,
creation_date ,
created_by ,
po_header_id ,
po_line_id ,
line_location_id ,
distribution_num ,
req_distribution_id ,
deliver_to_location_id ,
deliver_to_person_id ,
destination_type_code ,
destination_organization_id ,
destination_subinventory ,
rate ,
rate_date ,
accrued_flag ,
encumbered_flag ,
prevent_encumbrance_flag ,
gl_encumbered_date ,
gl_encumbered_period_name ,
accrue_on_receipt_flag ,
org_id ,
distribution_type ,
amount_ordered ,
quantity_ordered ,
project_id ,
task_id ,
award_id ,
end_item_unit_number ,
expenditure_type ,
project_accounting_context ,
destination_context ,
expenditure_organization_id ,
expenditure_item_date ,
set_of_books_id ,
tax_attribute_update_code --
)
SELECT PO_DISTRIBUTIONS_S.NEXTVAL ,
po_autocreate_params.g_draft_id ,
p_lines.last_update_date_tbl(i) ,
p_lines.last_updated_by_tbl(i) ,
p_lines.last_update_login_tbl(i) ,
p_lines.creation_date_tbl(i) ,
p_lines.created_by_tbl(i) ,
p_lines.po_header_id_tbl(i) ,
p_lines.po_line_id_tbl(i) ,
adv.line_location_id ,
pod.distribution_num -- distribution_num
,
NULL -- req_distribution_id
,
pod.deliver_to_location_id ,
pod.deliver_to_person_id ,
pod.destination_type_code ,
pod.destination_organization_id ,
pod.destination_subinventory ,
pod.rate ,
pod.rate_date ,
pod.accrued_flag ,
pod.encumbered_flag ,
DECODE(PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag, 'Y', 'Y', NULL) --prevent_enc_flag
,
NULL -- gl_encumbered_date
,
NULL -- gl_encumbered_period_name
,
adv.accrue_on_receipt_flag ,
pod.org_id ,
adv.shipment_type ,
ROUND( -- amount_ordered
(NVL(pod.amount_ordered, deliv.price_override * pod.quantity_ordered) / NVL(deliv.amount, deliv.price_override * deliv.quantity)) * adv.amount, 15) ,
NULL -- quantity_ordered
,
pod.project_id ,
pod.task_id ,
pod.award_id ,
pod.end_item_unit_number ,
pod.expenditure_type ,
pod.project_accounting_context ,
pod.destination_context ,
pod.expenditure_organization_id ,
pod.expenditure_item_date ,
PO_AUTOCREATE_PARAMS.g_sys.sob_id ,
nvl2(PO_AUTOCREATE_PARAMS.g_calculate_tax_flag, 'CREATE', NULL) --
/*Bug 13598209 : PO_LINE_LOCATIONS_ALL has still not been populated
Hence PO_LINE_LOCATIONS_DRAFT_ALL needs to be used
Similarly for po_distributions_all*/
FROM PO_LINE_LOCATIONS_DRAFT_ALL adv,
PO_LINE_LOCATIONS_DRAFT_ALL deliv,
po_distributions_draft_all pod
WHERE adv.po_line_id = p_lines.po_line_id_tbl(i)
AND adv.payment_type = 'ADVANCE'
AND deliv.line_location_id =
(SELECT poll.line_location_id
FROM PO_LINE_LOCATIONS_DRAFT_ALL poll
WHERE poll.po_line_id = p_lines.po_line_id_tbl(i)
AND poll.shipment_type = 'STANDARD'
AND poll.shipment_num =
(SELECT MIN(poll2.shipment_num)
FROM PO_LINE_LOCATIONS_DRAFT_ALL poll2
WHERE poll2.po_line_id = poll.po_line_id
AND poll2.shipment_type = 'STANDARD'
)
)
AND pod.line_location_id = deliv.line_location_id;
SELECT poll.line_location_id,
poll.value_basis BULK COLLECT
INTO l_line_loc_id_tbl,
l_line_loc_value_basis_tbl
/*Bug 13598209 : PO_LINE_LOCATIONS_ALL has still not been populated
Hence PO_LINE_LOCATIONS_DRAFT_ALL needs to be used
*/
FROM po_line_locations_draft_all poll
WHERE poll.po_line_id = p_lines.po_line_id_tbl(i);
SELECT agent_id
INTO l_agent_id
FROM po_headers_draft_all
WHERE po_header_id = p_lines.po_header_id_tbl(i);
UPDATE po_distributions_draft_all pod
SET pod.code_combination_id = l_code_combination_id ,
pod.budget_account_id = DECODE(NVL(PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag, 'N'), 'Y', l_budget_account_id, NULL) ,
pod.accrual_account_id = l_accrual_account_id ,
pod.variance_account_id = l_variance_account_id
WHERE pod.po_distribution_id = payitem_acct_rec.po_distribution_id
AND pod.draft_id =po_autocreate_params.g_draft_id;
DELETE
FROM po_distributions_draft_all pod
WHERE pod.po_distribution_id = payitem_acct_rec.po_distribution_id
AND pod.draft_id =po_autocreate_params.g_draft_id;
PO_LOG.stmt(d_module, d_progress, 'Calling update_award_distributions');
update_award_distributions( p_lines.intf_header_id_tbl(i) ,
p_lines.intf_line_id_tbl(i) ,
p_table_type => 'ALL' ,
p_po_line_id => p_lines.po_line_id_tbl(i) );
SELECT pod.po_distribution_id BULK COLLECT
INTO l_dist_id_tbl
FROM po_distributions_draft_all pod
WHERE pod.po_line_id = p_lines.po_line_id_tbl(i)
AND pod.draft_id =po_autocreate_params.g_draft_id;
Name: update_award_distributions
Pre-reqs:
None
Modifies:
PO_DISTRIBUTIONS_INTERFACE
GMS_AWARD_DISTRIBUTIONS
Locks:
None
Function:
Calls Grants Accounting API to create new award distributions lines
when a requisition with distributions that reference awards is
autocreated into a PO.
Parameters:
: Add p_table_type and p_po_line_id
p_table_type
'INTERFACE' - query/update interface tables (default)
'ALL - query/update _ALL tables
p_po_line_id
Only necessary if p_table_type = 'ALL', this is the line for
which to update the award distributions for.
p_intf_header_id (IN) the interface_header_id
p_intf_line_id the (IN) interface_line_id
Both the above parameters are Used when p_table_type is INTERFACE.
Returns:
None
Testing:
None
Caller of the Procedure:
-derive_and_default_dists
-create_payitem_dists
==============================================================================*/
PROCEDURE update_award_distributions
(
p_intf_header_id NUMBER ,
p_intf_line_id NUMBER ,
p_table_type IN VARCHAR2 DEFAULT 'INTERFACE' ,
p_po_line_id IN NUMBER DEFAULT NULL )
IS
l_api_name CONSTANT VARCHAR(30) := 'update_award_distributions';
SELECT pod.po_distribution_id,
pod.distribution_num,
pod.project_id,
pod.task_id,
pod.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_draft_all pod
WHERE pod.po_line_id = p_po_line_id
AND pod.award_id IS NOT NULL
AND pod.draft_id =po_autocreate_params.g_draft_id;
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_INTERFACE
WHERE interface_header_id = l_intf_header_id
AND interface_line_id = l_intf_line_id
AND award_id IS NOT NULL;
UPDATE po_distributions_draft_all
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)
AND draft_id =po_autocreate_params.g_draft_id;
UPDATE po_distributions_interface
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);
END update_award_distributions;
SELECT SUM(amount_ordered) ,
MAX(po_distribution_id)
INTO l_sum_dist_amounts ,
l_last_distribution_id
FROM po_distributions_draft_all
WHERE line_location_id = p_line_location_id
AND draft_id =po_autocreate_params.g_draft_id;
SELECT amount
INTO l_shipment_amount
FROM po_line_locations_draft_all
WHERE line_location_id = p_line_location_id;
UPDATE po_distributions_draft_all
SET amount_ordered = l_shipment_amount - (l_sum_dist_amounts - amount_ordered)
WHERE po_distribution_id = l_last_distribution_id
AND draft_id =po_autocreate_params.g_draft_id RETURNING amount_ordered
INTO l_last_dist_amount;
SELECT SUM(pod.quantity_ordered),
MAX(pod.po_distribution_id)
INTO l_sum_dist_quantities,
l_last_dist_id
FROM po_distributions_draft_all pod
WHERE pod.line_location_id = p_line_location_id
AND pod.draft_id =po_autocreate_params.g_draft_id;
SELECT poll.quantity
INTO l_shipment_quantity
FROM po_line_locations_draft_all poll
WHERE poll.line_location_id = p_line_location_id
AND draft_id =po_autocreate_params.g_draft_id;
UPDATE po_distributions_draft_all pod
SET pod.quantity_ordered = l_shipment_quantity - (l_sum_dist_quantities - pod.quantity_ordered)
WHERE pod.po_distribution_id = l_last_dist_id
AND draft_id =po_autocreate_params.g_draft_id RETURNING pod.quantity_ordered
INTO l_last_dist_qty;
SELECT pdd.po_distribution_id,
pdd.code_combination_id,
NVL(pdd.ACRN, '')
, CLM_MISC_LOA,CHANGE_STATUS
BULK COLLECT
INTO l_dist_id_tbl,
l_charge_acc_tbl,
l_ACRN_tbl
,L_LOA_TBL,L_CHANGE_STAT_TBL
FROM po_distributions_draft_all pdd
WHERE pdd.po_header_id = PO_AUTOCREATE_PARAMS.g_po_header_id
AND pdd.draft_id = PO_AUTOCREATE_PARAMS.g_draft_id
ORDER BY pdd.po_distribution_id ;
UPDATE po_distributions_draft_all
SET ACRN = l_ACRN_tbl(j)
WHERE po_distribution_id = l_dist_id_tbl(j)
AND l_ACRN_tbl(j) <> '';