[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT prs.requisition_header_id,
prs.requisition_line_id,
prs.auction_header_id,
prs.bid_number,
prs.bid_line_number,
prs.allocated_qty,
prs.requisition_line_qty,
prs.new_req_line_id,
prs.new_line_num,
prs.total_alloc_req_line_qty,
prs.min_bid_number,
prs.record_status
BULK COLLECT INTO
requisition_header_id_dbg_tbl,
requisition_line_id_dbg_tbl,
auction_header_id_dbg_tbl,
bid_number_dbg_tbl,
bid_line_number_dbg_tbl,
allocated_qty_dbg_tbl,
requisition_line_qty_dbg_tbl,
new_req_line_id_dbg_tbl,
new_line_num_dbg_tbl,
totalloc_req_line_qty_dbg_tbl,
min_bid_number_dbg_tbl,
record_status_dbg_tbl
FROM po_req_split_lines_gt prs
ORDER BY prs.requisition_header_id,prs.requisition_line_id;
* Modifies: Inserts new req lines and their distributions, For parent
* req lines, update requisition_lines table to modified_by_agent_flag='Y'.
* Also sets prevent encumbrace flag to 'Y' in the po_req_distributions table.
* Effects: This api split the requisition lines, if needed, depending on the
* allocation done by the sourcing user. This api uses a global temp. table
* to massage the input given by sourcing and inserts records into
* po_requisition_lines_all and po_req_distributions_all table. This api also
* handles the encumbrace effect of splitting requisition lines. This api would
* be called from ORacle sourcing workflow.
*
* Returns:
* x_return_status - FND_API.G_RET_STS_SUCCESS if action succeeds
* FND_API.G_RET_STS_ERROR if action fails
* FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
* x_msg_count returns count of messages in the stack.
* x_msg_data returns message only if 1 message.
*
* Possible values for PO_REQ_SPLIT_LINES_GT.record_status:
* 'S' - Split;
SELECT prl.requisition_line_id,quantity
FROM po_requisition_lines_all prl, --
po_req_split_lines_gt prs
WHERE prl.requisition_line_id = prs.requisition_line_id
FOR UPDATE OF prl.quantity NOWAIT;
SELECT distribution_id,
project_id,
task_id,
award_id,
expenditure_type,
expenditure_item_date
FROM po_req_distributions_all
WHERE requisition_line_id = l_req_line_id;
SELECT DISTINCT prs.requisition_header_id
FROM po_req_split_lines_gt prs;
'before update po_req_split_lines_gt for quantity and
status ');
UPDATE po_req_split_lines_gt prs
SET (prs.requisition_line_qty,
prs.record_status)=
(SELECT quantity,
--decode(cancel_flag,'Y','C',null)
decode(cancel_flag,'Y','C',decode(closed_code,'FINALLY CLOSED',
'C',NULL))
FROM po_requisition_lines_all prl --
WHERE prl.requisition_line_id=prs.requisition_line_id
);
'after update po_req_split_lines_gt for quantity and
status; updated '||sql%rowcount||' rows');
'Exception: update po_req_split_lines_gt for
quantity and status ');
'before update po_req_split_lines_gt for withdrawn lines');
UPDATE po_req_split_lines_gt prs
SET prs.record_status='C'
WHERE NOT EXISTS
(SELECT requisition_line_id
FROM po_requisition_lines_all prl --
WHERE prl.requisition_line_id= prs.requisition_line_id
);
'after update po_req_split_lines_gt for withdrawn lines;
updated '||sql%rowcount||' rows');
'Exception: update po_req_split_lines_gt for
withdrawn lines ');
'before update po_req_split_lines_gt for Services lines');
UPDATE po_req_split_lines_gt PRS
SET ( PRS.record_status
, PRS.new_req_line_id ) = ( SELECT 'T'
, PRL.requisition_line_id
FROM po_requisition_lines_all PRL
, po_line_types_b PLT
WHERE PRL.requisition_line_id = PRS.requisition_line_id
AND PRL.line_type_id = PLT.line_type_id
AND PLT.order_type_lookup_code IN ('RATE','FIXED PRICE')
)
-- Bug 3345861: without the following WHERE clause, lines with record_status 'C'
-- will be overwritten
WHERE nvl(PRS.record_status, 'NOVAL') <> 'C';
'after update po_req_split_lines_gt for Services lines;
updated '||sql%rowcount||' rows');
FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module, 'Exception: update po_req_split_lines_gt for Services lines');
'before select sum of allocated_qty and min of bid_number
');
SELECT requisition_line_id,nvl(sum(allocated_qty),0),min(bid_number)
BULK COLLECT INTO
requisition_line_id_tbl,
total_alloc_req_line_qty_tbl,
min_bid_number_tbl
FROM po_req_split_lines_gt
WHERE nvl(record_status,'NOVAL') NOT IN ('C','T') --
GROUP BY requisition_line_id;
'after select sum of allocated_qty and min of bid_number;
selected '||requisition_line_id_tbl.COUNT||' rows');
'Exception: select sum of allocated_qty and min of
bid_number ');
UPDATE po_req_split_lines_gt
SET min_bid_number= min_bid_number_tbl(qty_rollup_index),
total_alloc_req_line_qty=
total_alloc_req_line_qty_tbl(qty_rollup_index),
record_status =decode(nvl(allocated_qty,0),requisition_line_qty,
'E',0,'I',
-- Bug 3345861: Do not split lines where requisition_line_qty is 0
-- Assign 'C' as the record_status to such lines
decode(requisition_line_qty, 0, 'C', 'S')),
new_req_line_id=decode(nvl(allocated_qty,0),requisition_line_qty,
requisition_line_id,0,null,po_requisition_lines_s.nextval)
WHERE requisition_line_id = requisition_line_id_tbl(qty_rollup_index);
allocated_qty and min of bid_number etc..; updated '
'before inserting remainder req lines into
po_req_split_lines_gt ');
INSERT INTO po_req_split_lines_gt
( auction_header_id,
bid_number,
bid_line_number,
requisition_header_id,
requisition_line_id,
allocated_qty,
new_req_line_id,
total_alloc_req_line_qty,
requisition_line_qty,
min_bid_number,
record_status
)
SELECT auction_header_id,
NULL,
bid_line_number,
requisition_header_id,
requisition_line_id,
(requisition_line_qty-total_alloc_req_line_qty),
po_requisition_lines_s.nextval,
NULL,
requisition_line_qty,
NULL,
'N'
FROM po_req_split_lines_gt
WHERE total_alloc_req_line_qty < requisition_line_qty
AND record_status NOT IN ('I','T') --
AND bid_number=min_bid_number;
'after inserting remainder req lines into
po_req_split_lines_gt inserted '||sql%rowcount||' rows');
'Exception: inserting remainder req lines into
po_req_split_lines_gt ');
UPDATE po_requisition_lines_all prl --
SET (bid_number,
bid_line_number)=
(SELECT prs.bid_number,
prs.bid_line_number
FROM po_req_split_lines_gt prs
WHERE prl.requisition_line_id=prs.requisition_line_id
AND prs.record_status IN ('E','T') --
)
WHERE prl.requisition_line_id in
(SELECT prs1.requisition_line_id
FROM po_req_split_lines_gt prs1
WHERE prs1.record_status IN ('E','T') ); --
'before stamping bid info for equal allocation updated'
|| sql%rowcount||' requisition lines ');
UPDATE po_req_split_lines_gt prs
SET prs.new_line_num=
(SELECT max(prl.line_num)
FROM po_requisition_lines_all prl --
WHERE prl.requisition_header_id=prs.requisition_header_id)
WHERE prs.record_status in ('S','N');
Updated '|| sql%rowcount||' rows ');
'before selecting rowid and new_line_num to memory ');
SELECT prs.requisition_header_id,
prs.requisition_line_id,
prs.bid_number,
prs.bid_line_number,
prs.new_line_num,
prs.rowid
BULK COLLECT INTO
requisition_header_id_lnm_tbl,
requisition_line_id_lnm_tbl,
bid_number_lnm_tbl,
bid_line_number_lnm_tbl,
new_line_num_lnm_tbl,
row_id_lnm_tbl
FROM po_req_split_lines_gt prs
WHERE prs.record_status in ('S','N')
ORDER BY prs.requisition_header_id,prs.requisition_line_id;
'after selecting rowid and new_line_num to memory;
selected '||row_id_lnm_tbl.COUNT||' rows');
'Exception: selecting rowid and new_line_num to memory '
);
'Before: update po_req_split_lines_gt toset new_line_num');
UPDATE po_req_split_lines_gt
SET new_line_num=new_line_num_lnm_tbl(l_line_num_upd_index)
WHERE rowid=row_id_lnm_tbl(l_line_num_upd_index);
'After: update po_req_split_lines_gt toset new_line_num;
Updated '||sql%rowcount||' rows');
'Exception: update po_req_split_lines_gt to set
new_line_num');
SELECT prs.auction_header_id,
prs.bid_number,
prs.bid_line_number,
prs.requisition_header_id,
prs.requisition_line_id,
prs.allocated_qty,
prs.new_req_line_id,
prs.new_line_num,
prs.total_alloc_req_line_qty,
prs.requisition_line_qty,
prs.min_bid_number,
prs.record_status,
NVL(fsp.req_encumbrance_flag, 'N') -- Bug 4723367
BULK COLLECT INTO
auction_header_id_rslt_tbl,
bid_number_rslt_tbl,
bid_line_number_rslt_tbl,
requisition_header_id_rslt_tbl,
requisition_line_id_rslt_tbl,
allocated_qty_rslt_tbl,
new_req_line_id_rslt_tbl,
new_line_num_rslt_tbl,
totallc_req_line_qty_rslt_tbl,
requisition_line_qty_rslt_tbl,
min_bid_number_rslt_tbl,
record_status_rslt_tbl,
encumbrance_flag_rslt_tbl -- Bug 4723367
FROM po_req_split_lines_gt prs,
po_requisition_lines_all prl, --
financials_system_parameters fsp -- Bug 4723367
-- Bug 5467617: Removed the joins to PO_VENDORS and PO_VENDOR_SITES_ALL
-- These are not required anymore because they we used to fetch the
-- rounding rule from the site level. Now the tax rounding is done by
-- recalculating the tax at the end of this flow.
WHERE record_status in ('S','N')
AND prs.requisition_line_id = prl.requisition_line_id
AND nvl(prl.org_id, -99) = nvl(fsp.org_id, -99); -- Bug 4723367
'split; Selected '||requisition_line_id_rslt_tbl.COUNT
FORALL l_insert_line_index IN
1.. requisition_line_id_rslt_tbl.COUNT
INSERT INTO po_requisition_lines_all --
(
requisition_line_id,
requisition_header_id,
line_num,
line_type_id,
category_id,
item_description,
unit_meas_lookup_code,
unit_price,
quantity,
deliver_to_location_id,
to_person_id,
last_update_date,
last_updated_by,
source_type_code,
last_update_login,
creation_date,
created_by,
item_id,
item_revision,
quantity_delivered,
suggested_buyer_id,
encumbered_flag,
rfq_required_flag,
need_by_date,
line_location_id,
modified_by_agent_flag,
parent_req_line_id,
justification,
note_to_agent,
note_to_receiver,
purchasing_agent_id,
document_type_code,
blanket_po_header_id,
blanket_po_line_num,
currency_code,
rate_type,
rate_date,
rate,
currency_unit_price,
suggested_vendor_name,
suggested_vendor_location,
suggested_vendor_contact,
suggested_vendor_phone,
suggested_vendor_product_code,
un_number_id,
hazard_class_id,
must_use_sugg_vendor_flag,
reference_num,
on_rfq_flag,
urgent_flag,
cancel_flag,
source_organization_id,
source_subinventory,
destination_type_code,
destination_organization_id,
destination_subinventory,
quantity_cancelled,
cancel_date,
cancel_reason,
closed_code,
agent_return_note,
changed_after_research_flag,
vendor_id,
vendor_site_id,
vendor_contact_id,
research_agent_id,
on_line_flag,
wip_entity_id,
wip_line_id,
wip_repetitive_schedule_id,
wip_operation_seq_num,
wip_resource_seq_num,
attribute_category,
destination_context,
inventory_source_context,
vendor_source_context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
bom_resource_id,
government_context,
closed_reason,
closed_date,
transaction_reason_code,
quantity_received,
tax_code_id,
tax_user_override_flag,
oke_contract_header_id,
oke_contract_version_id,
secondary_unit_of_measure,
secondary_quantity,
preferred_grade,
secondary_quantity_received,
secondary_quantity_cancelled,
auction_header_id,
auction_display_number,
auction_line_number,
reqs_in_pool_flag,
vmi_flag,
bid_number,
bid_line_number,
order_type_lookup_code,
purchase_basis,
matching_basis,
org_id, --
tax_attribute_update_code --
)
SELECT new_req_line_id_rslt_tbl(l_insert_line_index),
prl.requisition_header_id,
new_line_num_rslt_tbl(l_insert_line_index),
prl.line_type_id,
prl.category_id,
prl.item_description,
prl.unit_meas_lookup_code,
prl.unit_price,
allocated_qty_rslt_tbl(l_insert_line_index),
prl.deliver_to_location_id,
prl.to_person_id,
prl.last_update_date,
prl.last_updated_by,
prl.source_type_code,
prl.last_update_login,
prl.creation_date,
prl.created_by,
prl.item_id,
prl.item_revision,
prl.quantity_delivered,
prl.suggested_buyer_id,
prl.encumbered_flag,
prl.rfq_required_flag,
prl.need_by_date,
prl.line_location_id,
prl.modified_by_agent_flag,
prl.parent_req_line_id,
prl.justification,
prl.note_to_agent,
prl.note_to_receiver,
prl.purchasing_agent_id,
prl.document_type_code,
prl.blanket_po_header_id,
prl.blanket_po_line_num,
prl.currency_code,
prl.rate_type,
prl.rate_date,
prl.rate,
prl.currency_unit_price,
prl.suggested_vendor_name,
prl.suggested_vendor_location,
prl.suggested_vendor_contact,
prl.suggested_vendor_phone,
prl.suggested_vendor_product_code,
prl.un_number_id,
prl.hazard_class_id,
prl.must_use_sugg_vendor_flag,
prl.reference_num,
prl.on_rfq_flag,
prl.urgent_flag,
prl.cancel_flag,
prl.source_organization_id,
prl.source_subinventory,
prl.destination_type_code,
prl.destination_organization_id,
prl.destination_subinventory,
prl.quantity_cancelled,
prl.cancel_date,
prl.cancel_reason,
prl.closed_code,
prl.agent_return_note,
prl.changed_after_research_flag,
prl.vendor_id,
prl.vendor_site_id,
prl.vendor_contact_id,
prl.research_agent_id,
prl.on_line_flag,
prl.wip_entity_id,
prl.wip_line_id,
prl.wip_repetitive_schedule_id,
prl.wip_operation_seq_num,
prl.wip_resource_seq_num,
prl.attribute_category,
prl.destination_context,
prl.inventory_source_context,
prl.vendor_source_context,
prl.attribute1,
prl.attribute2,
prl.attribute3,
prl.attribute4,
prl.attribute5,
prl.attribute6,
prl.attribute7,
prl.attribute8,
prl.attribute9,
prl.attribute10,
prl.attribute11,
prl.attribute12,
prl.attribute13,
prl.attribute14,
prl.attribute15,
prl.bom_resource_id,
prl.government_context,
prl.closed_reason,
prl.closed_date,
prl.transaction_reason_code,
prl.quantity_received,
prl.tax_code_id,
prl.tax_user_override_flag,
prl.oke_contract_header_id,
prl.oke_contract_version_id,
prl.secondary_unit_of_measure,
prl.secondary_quantity,
prl.preferred_grade,
prl.secondary_quantity_received,
prl.secondary_quantity_cancelled,
prl.auction_header_id,
prl.auction_display_number,
prl.auction_line_number,
'Y', --new reqs are placed back in pool after splitting
prl.vmi_flag,
bid_number_rslt_tbl(l_insert_line_index),
decode(record_status_rslt_tbl(l_insert_line_index),'N',NULL,
bid_line_number_rslt_tbl(l_insert_line_index)),
prl.order_type_lookup_code,
prl.purchase_basis,
prl.matching_basis,
prl.org_id, --
'CREATE' --
FROM po_requisition_lines_all prl --
WHERE prl.requisition_line_id=
requisition_line_id_rslt_tbl(l_insert_line_index);
'Before: Create all the new requisition lines; Inserted '
UPDATE po_requisition_lines_all --
SET modified_by_agent_flag = 'Y',
reqs_in_pool_flag = NULL, --
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE requisition_line_id =
requisition_line_id_rslt_tbl(l_mod_buyer_index)
AND record_status_rslt_tbl(l_mod_buyer_index)='S';
'After: Mark parent req lines as modified by agent;Updated'
'Before: Update MTL_SUPPLY');
FORALL l_update_mtl_supply_index in 1.. new_req_line_id_rslt_tbl.COUNT
-- Insert New Supply for each new Line created by the Reqsplit
INSERT INTO mtl_supply(supply_type_code,
supply_source_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
req_header_id,
req_line_id,
item_id,
item_revision,
quantity,
unit_of_measure,
receipt_date,
need_by_date,
destination_type_code,
location_id,
from_organization_id,
from_subinventory,
to_organization_id,
to_subinventory,
change_flag,
to_org_primary_quantity,
change_type,
to_org_primary_uom,
expected_delivery_date)
SELECT 'REQ',
prl.requisition_line_id,
prl.last_updated_by,
prl.last_update_date,
prl.last_update_login,
prl.created_by,
prl.creation_date,
prl.requisition_header_id,
prl.requisition_line_id,
prl.item_id,
prl.item_revision,
prl.quantity - (nvl(prl.quantity_cancelled, 0) +
nvl(prl.quantity_delivered, 0)),
prl.unit_meas_lookup_code,
prl.need_by_date,
prl.need_by_date,
prl.destination_type_code,
prl.deliver_to_location_id,
prl.source_organization_id,
prl.source_subinventory,
prl.destination_organization_id,
prl.destination_subinventory,
null,
prl.quantity - (nvl(prl.quantity_cancelled, 0) +
nvl(prl.quantity_delivered, 0)),
null,
prl.unit_meas_lookup_code,
decode(prl.item_id, null, null, prl.need_by_date + nvl(msi.postprocessing_lead_time,0))
FROM po_requisition_lines_all prl, --
mtl_system_items msi
WHERE prl.requisition_line_id =
new_req_line_id_rslt_tbl(l_update_mtl_supply_index)
AND prl.destination_organization_id = msi.organization_id(+)
AND prl.item_id = msi.inventory_item_id(+)
AND EXISTS
(select 'Supply Exists'
from mtl_supply
where supply_type_code = 'REQ'
AND supply_source_id =
requisition_line_id_rslt_tbl(l_update_mtl_supply_index));
FORALL l_delete_mtl_supply_index in
1.. requisition_line_id_rslt_tbl.COUNT
DELETE FROM mtl_supply
WHERE supply_type_code = 'REQ'
AND supply_source_id =
requisition_line_id_rslt_tbl(l_delete_mtl_supply_index);
'After: Update MTL_SUPPLY;Updated'
'Exception: Update MTL_SUPPLY');
X_last_update_login => NULL ,
X_program_application_id => NULL ,
X_program_id => NULL ,
X_request_id => NULL ,
X_automatically_added_flag=> NULL
);
SELECT nvl(org_id, -99)
INTO l_line_requesting_ou_id
FROM po_requisition_lines_all
WHERE requisition_line_id = requisition_line_id_rslt_tbl(l_create_dist_index);
INSERT INTO po_req_distributions_all --
(DISTRIBUTION_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
REQUISITION_LINE_ID ,
SET_OF_BOOKS_ID ,
CODE_COMBINATION_ID ,
REQ_LINE_QUANTITY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
ENCUMBERED_FLAG ,
GL_ENCUMBERED_DATE ,
GL_ENCUMBERED_PERIOD_NAME ,
GL_CANCELLED_DATE ,
FAILED_FUNDS_LOOKUP_CODE ,
ENCUMBERED_AMOUNT ,
BUDGET_ACCOUNT_ID ,
ACCRUAL_ACCOUNT_ID ,
ORG_ID ,
VARIANCE_ACCOUNT_ID ,
PREVENT_ENCUMBRANCE_FLAG ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
GOVERNMENT_CONTEXT ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
PROJECT_ID ,
TASK_ID ,
EXPENDITURE_TYPE ,
PROJECT_ACCOUNTING_CONTEXT ,
EXPENDITURE_ORGANIZATION_ID ,
GL_CLOSED_DATE ,
SOURCE_REQ_DISTRIBUTION_ID ,
DISTRIBUTION_NUM ,
PROJECT_RELATED_FLAG ,
EXPENDITURE_ITEM_DATE ,
ALLOCATION_TYPE ,
ALLOCATION_VALUE ,
END_ITEM_UNIT_NUMBER ,
RECOVERABLE_TAX ,
NONRECOVERABLE_TAX ,
RECOVERY_RATE ,
TAX_RECOVERY_OVERRIDE_FLAG ,
AWARD_ID ,
OKE_CONTRACT_LINE_ID ,
OKE_CONTRACT_DELIVERABLE_ID
)
SELECT po_req_distributions_s.nextval,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
new_req_line_id_rslt_tbl(l_create_dist_index),
SET_OF_BOOKS_ID ,
CODE_COMBINATION_ID ,
round(((req_line_quantity/requisition_line_qty_rslt_tbl(l_create_dist_index))* allocated_qty_rslt_tbl(l_create_dist_index)),13),
--enter req form, dist screen uses 13 places to round.
--suggested by PM.
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
--purposely encumbered flag is copied from the parent dist
--line instead of assigning a null values. This is required
--as there is no parent dist id on the distributions table.
ENCUMBERED_FLAG ,
GL_ENCUMBERED_DATE ,
GL_ENCUMBERED_PERIOD_NAME ,
GL_CANCELLED_DATE ,
FAILED_FUNDS_LOOKUP_CODE ,
--bug#2728152, the new lines should have 0 encumbered amt.
--as encumbrance api is looking at this value.
--ENCUMBERED_AMOUNT ,
0,
BUDGET_ACCOUNT_ID ,
ACCRUAL_ACCOUNT_ID ,
ORG_ID ,
VARIANCE_ACCOUNT_ID ,
PREVENT_ENCUMBRANCE_FLAG ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
GOVERNMENT_CONTEXT ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
PROJECT_ID ,
TASK_ID ,
EXPENDITURE_TYPE ,
PROJECT_ACCOUNTING_CONTEXT ,
EXPENDITURE_ORGANIZATION_ID ,
GL_CLOSED_DATE ,
SOURCE_REQ_DISTRIBUTION_ID ,
DISTRIBUTION_NUM ,
PROJECT_RELATED_FLAG ,
EXPENDITURE_ITEM_DATE ,
ALLOCATION_TYPE ,
ALLOCATION_VALUE ,
END_ITEM_UNIT_NUMBER ,
-- recoverable and nonrecoverable
-- tax is recalculated instead of being prorated
null,
null,
RECOVERY_RATE ,
TAX_RECOVERY_OVERRIDE_FLAG ,
AWARD_ID ,
OKE_CONTRACT_LINE_ID ,
OKE_CONTRACT_DELIVERABLE_ID
FROM po_req_distributions_all --
WHERE requisition_line_id=
requisition_line_id_rslt_tbl(l_create_dist_index);
'calling GMS_POR_API.when_insert_line :'||
'l_distribution_id '||l_distribution_id||
'l_project_id '||l_project_id||
'l_task_id '||l_task_id ||
'l_award_id '||l_award_id ||
'l_expenditure_type '||l_expenditure_type ||
'l_expenditure_item_date '||
to_char(l_expenditure_item_date,'DD-MON-YYYY'));
GMS_POR_API.when_insert_line (
X_distribution_id => l_distribution_id,
X_project_id => l_project_id,
X_task_id => l_task_id,
X_award_id => l_award_id,
X_expenditure_type => l_expenditure_type,
X_expenditure_item_date=> l_expenditure_item_date,
X_award_set_id => l_award_set_id, --OUT
X_status => l_status) ;
'After when insert line : Out values '||
'l_award_set_id '||l_award_set_id ||
'l_status '||l_status );
SELECT prd.requisition_line_id,
MIN(prd.distribution_id),
SUM(prd.req_line_quantity),
MAX(prs.allocated_qty) --this would be always one record.
BULK COLLECT INTO
req_line_id_round_tbl,
min_dist_id_round_tbl,
sum_req_line_qty_round_tbl,
req_line_qty_round_tbl
FROM po_req_distributions_all prd, --
po_req_split_lines_gt prs
WHERE prd.requisition_line_id = prs.new_req_line_id
AND prs.record_status in ('S','N')
GROUP BY prd.requisition_line_id;
'Before: update to take care of rounding ');
UPDATE po_req_distributions_all --
SET req_line_quantity = req_line_quantity+
(req_line_qty_round_tbl(l_qty_rounding_index)-
sum_req_line_qty_round_tbl(l_qty_rounding_index))
WHERE distribution_id=
min_dist_id_round_tbl(l_qty_rounding_index);
'After: update to take care of rounding; Updated '
'Exception: update to take care of rounding ');
'Before: select req_encumbrance_flag '
);
'After: select req_encumbrance_flag '
);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
SELECT NVL(REVISION_NUM,0)
INTO l_po_revision
FROM po_headers
WHERE PO_HEADER_ID = l_document_id;
SELECT NVL(consume_req_demand_flag,'N')
INTO l_consume_req_demand_flag
FROM po_headers
WHERE po_header_id = l_document_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
* Effects: This procedure updates
* suggested_vendor_name, suggested_vendor_location,
* document_type_code, blanket_po_header_id and blanket_po_line_num on
* po_requisition_lines if the user wanted to consume the req demand based
* on the bid and auction information on the blanket which is undergoing
* approval.
* Returns: std. workflow out parameters
*/
procedure Place_SourcingInfoOnReq
( itemtype IN VARCHAR2 ,
itemkey IN VARCHAR2 ,
actid IN NUMBER ,
funcmode IN VARCHAR2 ,
resultout OUT NOCOPY VARCHAR2
) IS
l_orgid number;
SELECT line_num,auction_header_id,auction_line_number,
bid_number,bid_line_number
FROM po_lines
WHERE po_header_id=l_document_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'l_document_id'
||to_char(l_document_id)||'l_document_type='||l_document_type
||'l_vendor_id='||to_char(l_vendor_id)||'l_supplier='
||l_vendor_name||'l_vendor_site_id='
||to_char(l_vendor_site_id));
SELECT poh.vendor_id,
pov.vendor_name,
poh.vendor_site_id,
povs.vendor_site_code
INTO l_vendor_id,l_vendor_name,l_vendor_site_id,l_vendor_site_code
FROM po_headers poh ,po_vendors pov,po_vendor_sites povs
WHERE po_header_id = l_document_id
AND poh.vendor_id=pov.vendor_id
AND poh.vendor_site_id=povs.vendor_site_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,
'before placing info on req- l_document_id='
||to_char(l_document_id)||'blanket_po_line_num='
||to_char(l_document_lines_index.line_num)
||'auction_header_id='
||to_char(l_document_lines_index.auction_header_id)
||'bid_line_number='
||to_char(l_document_lines_index.bid_line_number)
||'bid_number='||to_char(l_document_lines_index.bid_number));
UPDATE po_requisition_lines
SET suggested_vendor_name=l_vendor_name,
suggested_vendor_location = l_vendor_site_code,
document_type_code=l_document_subtype,
blanket_po_header_id= l_document_id,
blanket_po_line_num=l_document_lines_index.line_num
-- suggested_vendor_id = l_vendor_id,
-- suggested_vendor_site_id=l_vendor_site_id
WHERE auction_header_id = l_document_lines_index.auction_header_id
AND bid_line_number=l_document_lines_index.bid_line_number
AND bid_number =l_document_lines_index.bid_number--placed on anotherneg
AND line_location_id is null --placed on another po doc
AND nvl(cancel_flag,'N')= 'N' --Cancelled
AND nvl(closed_code,'OPEN') <> 'FINALLY CLOSED' --finally closed
AND nvl(modified_by_agent_flag,'N') <> 'Y'; --buyer modified the req.
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress||sql%rowcount);
select WF_CREATEDOC_ITEMTYPE,WF_CREATEDOC_PROCESS
from po_document_types
where DOCUMENT_TYPE_CODE= 'REQUISITION'
and DOCUMENT_SUBTYPE = 'PURCHASE';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'l_orgid='||to_char(l_orgid)||'l_user_id='||to_char(l_user_id)||'l_resp_id='||to_char(l_resp_id)||'l_appl_id='||to_char(l_appl_id)||'l_document_id='||to_char(l_document_id));
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'apps_init');
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'setting org');
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
select to_char(PO_WF_ITEMKEY_S.nextval) into l_dummy from sys.dual;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(Itemtype,Itemkey,x_progress);
po_wf_debug_pkg.insert_debug(Itemtype,Itemkey,x_progress);
po_wf_debug_pkg.insert_debug(Itemtype,Itemkey,x_progress);
SELECT distinct org_id bulk collect
INTO l_req_org_id
FROM po_requisition_headers_all
WHERE requisition_header_id IN
(SELECT DISTINCT requisition_header_id
FROM po_req_split_lines_gt);
'Before: select BEFORE req distributions for encumb. adjustmnets');
SELECT prd.distribution_id
BULK COLLECT INTO
l_before_dist_ids_tbl
FROM po_req_distributions prd,
po_req_split_lines_gt prs
WHERE prd.requisition_line_id = prs.requisition_line_id
AND (prs.bid_number = prs.min_bid_number
OR
-- when called from autocreate req modify bid number
-- would be null
prs.bid_number IS NULL
)
AND prs.record_status = 'S'
AND nvl(prd.prevent_encumbrance_flag,'N') <> 'Y'
AND nvl(prd.encumbered_flag,'N') ='Y';
'Before: select AFTER req distributions for encumb. adjustmnets');
SELECT prd.distribution_id
BULK COLLECT INTO
l_after_dist_ids_tbl
FROM po_req_distributions prd,
po_req_split_lines_gt prs
WHERE prd.requisition_line_id = prs.new_req_line_id
AND prs.record_status in ('S','N')
AND nvl(prd.prevent_encumbrance_flag,'N') <> 'Y'
AND nvl(prd.encumbered_flag,'N') ='Y';
'Exception: select req distributions for encumb. adjustmnets');
SELECT SUM(prd.recoverable_tax),
SUM(prd.nonrecoverable_tax),
MIN(prd.distribution_id)
BULK COLLECT INTO
sum_new_line_r_tax_tbl,
sum_new_line_nr_tax_tbl,
min_dist_id_tax_tbl
FROM po_req_distributions prd, po_req_split_lines_gt prs
WHERE prd.requisition_line_id = prs.new_req_line_id
AND prs.record_status in ('S','N')
GROUP BY prs.requisition_line_id,
prd.code_combination_id;
SELECT prs.requisition_line_id,
SUM(prd.recoverable_tax),
SUM(prd.nonrecoverable_tax)
BULK COLLECT INTO
req_line_id_tax_tbl,
sum_orig_line_r_tax_tbl,
sum_orig_line_nr_tax_tbl
FROM po_req_distributions prd, po_req_split_lines_gt prs
WHERE prd.requisition_line_id = prs.requisition_line_id
AND (prs.bid_number = prs.min_bid_number OR
-- when called from autocreate req modify bid number would be null
prs.bid_number IS NULL)
AND prs.record_status = 'S'
GROUP BY prs.requisition_line_id,
prd.code_combination_id;
'Before: update to take care of TAX rounding ');
UPDATE PO_REQ_DISTRIBUTIONS
SET recoverable_tax = recoverable_tax+
(sum_orig_line_r_tax_tbl(l_tax_rounding_index) -
sum_new_line_r_tax_tbl(l_tax_rounding_index)),
nonrecoverable_tax = nonrecoverable_tax+
(sum_orig_line_nr_tax_tbl(l_tax_rounding_index) -
sum_new_line_nr_tax_tbl(l_tax_rounding_index))
WHERE distribution_id=
min_dist_id_tax_tbl(l_tax_rounding_index);
'After: update to take care of TAX rounding; Updated '
'Exception: update to take care of TAX rounding ');