[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** validation and insert/update of the Lines.
**
** - Group lines in po_lines_interface
** - Fetch data from po_lines_interface
** - Derive Line
** - Default Line
** - Validate Line
** - Insert into po_lines_draft_all
**
** The procedures of this package are called from PO_AUTOCREATE_MAINPROC_PVT.process_line
**
** HISTORY
** 06/11/09 serukull Created
============================================================================ */
g_pkg_name CONSTANT VARCHAR2(1000) := 'PO_AUTO_LINE_PROCESS_PVT';
SELECT destination_type_code,
destination_organization_id,
deliver_to_location_id
FROM po_distributions_interface
WHERE interface_header_id = po_autocreate_params.x_interface_header_id
AND interface_line_id = p_intf_line_id
ORDER BY interface_distribution_id;
SELECT req_fsp.set_of_books_id
INTO l_req_ou_sob_id
FROM financials_system_params_all req_fsp
WHERE req_fsp.org_id = p_requesting_ou_id;
SELECT po_fsp.set_of_books_id
INTO l_po_ou_sob_id
FROM financials_system_params_all po_fsp
WHERE po_fsp.org_id = p_purchasing_ou_id;
SELECT default_rate_type
INTO l_rate_type
FROM po_system_parameters_all psp
WHERE psp.org_id = p_purchasing_ou_id;
SELECT INTERFACE_LINE_ID INTERFACE_LINE_ID,
group_line_id group_line_id
FROM po_lines_interface
WHERE group_line_id IS NOT NULL
AND interface_header_id =PO_AUTOCREATE_PARAMS.x_interface_header_id;
SELECT INTERFACE_LINE_ID
FROM po_lines_interface
WHERE 1 =1
AND interface_header_id=PO_AUTOCREATE_PARAMS.x_interface_header_id
AND REQUISITION_LINE_ID=p_req_line_id;
SELECT INTERFACE_LINE_ID,
CLM_BASE_LINE_NUM
FROM po_lines_interface
WHERE clm_base_line_num IS NOT NULL
AND interface_header_id =PO_AUTOCREATE_PARAMS.x_interface_header_id;
SELECT INTERFACE_LINE_ID
FROM po_lines_interface
WHERE 1 =1
AND interface_header_id=PO_AUTOCREATE_PARAMS.x_interface_header_id
AND REQUISITION_LINE_ID=p_req_line_id;
SELECT pli.interface_header_id,
pli.interface_line_id,
pli.item_id,
pli.line_type_id,
pli.quantity,
pli.amount,
pli.category_id,
pli.ship_to_location_id,
pli.ship_to_organization_id,
phi.vendor_id,
phi.vendor_site_id,
phi.agent_id,
phi.rate,
phi.rate_date,
phi.document_subtype,
pli.unit_price
FROM po_lines_interface pli,
po_headers_interface phi,
po_line_types plt
WHERE phi.interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id
AND phi.interface_header_id = pli.interface_header_id
AND pli.requisition_line_id IS NULL
AND plt.line_type_id = pli.line_type_id
AND Nvl(PLI.orig_from_req_flag, 'N') <> 'S';
UPDATE po_lines_interface pli
SET pli.requisition_line_id = NULL
WHERE pli.interface_header_id= PO_AUTOCREATE_PARAMS.x_interface_header_id
AND EXISTS
(SELECT requisition_line_id
FROM po_requisition_lines_all prl
WHERE prl.requisition_line_id= pli.requisition_line_id
/* Bug : 13695551 : When autocreated from sourcing,
* a req line should be allowed to be placed on multiple awards.
*/
AND (--prl.line_location_id IS NOT NULL OR
prl.cancel_flag ='Y'
OR prl.closed_code ='FINALLY CLOSED'
OR (prl.auction_header_id <>pli.auction_header_id
AND prl.auction_line_number <>pli.auction_line_number) )
);
PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Before the update for withdrawn reqs');
UPDATE po_lines_interface pli
SET pli.requisition_line_id = NULL
WHERE pli.interface_header_id= PO_AUTOCREATE_PARAMS.x_interface_header_id
AND NOT EXISTS
(SELECT requisition_line_id
FROM po_requisition_lines_all prl --
WHERE prl.requisition_line_id= pli.requisition_line_id
);
UPDATE po_lines_interface pli2
SET
(
line_num,
item_id,
job_id,
category_id,
item_description,
unit_of_measure,
list_price_per_unit,
market_price,
base_unit_price,
unit_price,
quantity,
amount,
taxable_flag,
type_1099,
negotiated_by_preparer_flag,
closed_code,
item_revision,
un_number_id,
hazard_class_id,
line_type_id,
vendor_product_num,
qty_rcv_tolerance,
over_tolerance_error_flag,
firm_flag,
min_release_amount,
price_type,
transaction_reason_code,
line_location_id,
need_by_date,
ship_to_organization_id,
note_to_receiver,
from_header_id,
from_line_id,
receipt_required_flag,
tax_status_indicator,
note_to_vendor,
oke_contract_header_id,
oke_contract_version_id,
secondary_unit_of_measure,
secondary_quantity,
preferred_grade,
drop_ship_flag,
vmi_flag,
supplier_ref_number,
effective_date,
expiration_date,
contractor_first_name,
contractor_last_name,
--CLM CLIN/SLIN changes START
line_num_display,
group_line_id,
clm_info_flag,
clm_option_indicator ,
clm_option_num ,
clm_option_from_date ,
clm_option_to_date ,
clm_funded_flag ,
clm_base_line_num ,
--clm clin/slin changes end
-- complex pricing changes start
contract_type,
cost_constraint
-- Complex Pricing Changes End
)
=
(SELECT pli.line_num,
NVL(pli.item_id,prl.item_id),
NVL(pli.job_id, prl.job_id),
NVL(pli.category_id,prl.category_id),
NVL(pli.item_description,prl.item_description),
NVL(pli.unit_of_measure,prl.unit_meas_lookup_code),
pli.list_price_per_unit,
pli.market_price,
NVL(pli.base_unit_price,prl.base_unit_price),
NVL(pli.unit_price,prl.unit_price),
DECODE ( prl.order_type_lookup_code , 'FIXED PRICE' , NULL , 'RATE' , NULL , NVL(pli.quantity,prl.quantity) ),
NVL(pli.amount, prl.amount),
pli.taxable_flag,
pli.type_1099,
NVL(pli.negotiated_by_preparer_flag,'N'),
DECODE(PO_AUTOCREATE_PARAMS.g_document_type, 'PO', NVL(pli.closed_code,'OPEN'), NULL),
NVL(pli.item_revision,prl.item_revision),
NVL(pli.un_number_id,prl.un_number_id),
NVL(pli.hazard_class_id,prl.hazard_class_id),
NVL(pli.line_type_id,prl.line_type_id),
NVL(pli.vendor_product_num,prl.suggested_vendor_product_code),
pli.qty_rcv_tolerance,
pli.over_tolerance_error_flag,
NVL(pli.firm_flag,'N'),
--
DECODE(PO_AUTOCREATE_PARAMS.g_document_type,
'PO', NVL(pli.min_release_amount,PO_AUTOCREATE_PARAMS.g_sys.min_rel_amount),
'PA',NVL(pli.min_release_amount,PO_AUTOCREATE_PARAMS.g_sys.min_rel_amount),NULL),
DECODE(PO_AUTOCREATE_PARAMS.g_document_type, 'PO', NVL(pli.price_type,PO_AUTOCREATE_PARAMS.g_sys.price_type_lookup_code), NULL),
NVL(pli.transaction_reason_code,prl.transaction_reason_code),
pli.line_location_id,
NVL(pli.need_by_date,prl.need_by_date),
NVL(pli.ship_to_organization_id,prl.destination_organization_id),
NVL(pli.note_to_receiver,prl.note_to_receiver),
pli.from_header_id,
pli.from_line_id,
NVL(pli.receipt_required_flag,plt.receiving_flag),
prl.tax_status_indicator,
NVL(pli.note_to_vendor, prl.note_to_vendor),
DECODE(pli.consigned_flag,'Y',NULL, NVL(pli.oke_contract_header_id,prl.oke_contract_header_id)),
DECODE(pli.consigned_flag,'Y',NULL, NVL(pli.oke_contract_version_id,prl.oke_contract_version_id)),
NVL(pli.secondary_unit_of_measure,prl.secondary_unit_of_measure),
NVL(pli.secondary_quantity,prl.secondary_quantity),
NVL(pli.preferred_grade,prl.preferred_grade),
prl.drop_ship_flag, --
prl.vmi_flag,
prl.supplier_ref_number, --
--
NVL(pli.effective_date, prl.assignment_start_date),
NVL(pli.expiration_date, prl.assignment_end_date),
NVL(pli.contractor_first_name, prl.candidate_first_name),
NVL(pli.contractor_last_name, prl.candidate_last_name)
--
,
pli.line_num_display ,
NVL(pli.group_line_id,prl.group_line_id) ,
NVL(pli.clm_info_flag, prl.clm_info_flag) ,
NVL(pli.clm_option_indicator, prl.clm_option_indicator) ,
NVL(pli.clm_option_num, prl.clm_option_num) ,
NVL(pli.clm_option_from_date, prl.clm_option_from_date) ,
NVL(pli.clm_option_to_date, prl.clm_option_to_date) ,
NVL(pli.clm_funded_flag, prl.clm_funded_flag) ,
NVL(pli.clm_base_line_num,prl.clm_base_line_num) ,
NVL(pli.contract_type,prl.contract_type) ,
NVL(pli.cost_constraint,prl.cost_constraint)
FROM po_lines_interface pli,
po_headers_interface phi,
po_requisition_lines_all prl,
po_line_types plt
WHERE pli.interface_line_id = pli2.interface_line_id
AND pli.interface_header_id = phi.interface_header_id
AND phi.interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id
AND pli.requisition_line_id = prl.requisition_line_id(+)
AND plt.line_type_id = NVL(prl.line_type_id,pli.line_type_id)
)
WHERE pli2.interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id
AND NVL(pli2.clm_info_flag,'N') <> 'Y';
UPDATE po_lines_interface pli2
SET
(
line_num,
item_id,
job_id,
category_id,
item_description,
unit_of_measure,
list_price_per_unit,
market_price,
base_unit_price,
unit_price,
quantity,
amount,
taxable_flag,
type_1099,
negotiated_by_preparer_flag,
closed_code,
item_revision,
un_number_id,
hazard_class_id,
line_type_id,
vendor_product_num,
qty_rcv_tolerance,
over_tolerance_error_flag,
firm_flag,
min_release_amount,
price_type,
transaction_reason_code,
line_location_id,
need_by_date,
ship_to_organization_id,
note_to_receiver,
from_header_id,
from_line_id,
tax_status_indicator,
note_to_vendor,
oke_contract_header_id,
oke_contract_version_id,
secondary_unit_of_measure,
secondary_quantity,
preferred_grade,
drop_ship_flag, --
vmi_flag,
supplier_ref_number, --
effective_date,
expiration_date,
contractor_first_name,
contractor_last_name,
--CLM CLIN/SLIN changes START
line_num_display,
group_line_id,
clm_info_flag,
clm_option_indicator ,
clm_option_num ,
clm_option_from_date ,
clm_option_to_date ,
clm_funded_flag ,
clm_base_line_num
-- CONTRACT_TYPE -- Info lines will not have any pricing info.
)
= --CLM CLIN/SLIN changes END
(SELECT pli.line_num,
NULL,
NVL(pli.job_id, prl.job_id),
NULL,
NVL(pli.item_description,prl.item_description),
NULL,
pli.list_price_per_unit,
pli.market_price,
NVL(pli.base_unit_price,prl.base_unit_price), --
NULL,
DECODE ( prl.order_type_lookup_code , 'FIXED PRICE' , NULL , 'RATE' , NULL , NVL(pli.quantity,prl.quantity) ),
NULL,
NULL,
pli.type_1099,
NVL(pli.negotiated_by_preparer_flag,'N'),
DECODE(PO_AUTOCREATE_PARAMS.g_document_type, 'PO', NVL(pli.closed_code,'OPEN'), NULL),
NULL,
NVL(pli.un_number_id,prl.un_number_id),
NVL(pli.hazard_class_id,prl.hazard_class_id),
NULL,
/*For Info Lines Line Type not required */
NVL(pli.vendor_product_num,prl.suggested_vendor_product_code),
pli.qty_rcv_tolerance,
pli.over_tolerance_error_flag,
NVL(pli.firm_flag,'N'),
DECODE(PO_AUTOCREATE_PARAMS.g_document_type,
'PO', NVL(pli.min_release_amount,PO_AUTOCREATE_PARAMS.g_sys.min_rel_amount),
'PA',NVL(pli.min_release_amount,PO_AUTOCREATE_PARAMS.g_sys.min_rel_amount),NULL),
DECODE(PO_AUTOCREATE_PARAMS.g_document_type, 'PO', NVL(pli.price_type,PO_AUTOCREATE_PARAMS.g_sys.price_type_lookup_code), NULL),
NVL(pli.transaction_reason_code,prl.transaction_reason_code),
pli.line_location_id,
NVL(pli.need_by_date,prl.need_by_date),
NVL(pli.ship_to_organization_id,prl.destination_organization_id),
NVL(pli.note_to_receiver,prl.note_to_receiver),
pli.from_header_id,
pli.from_line_id,
prl.tax_status_indicator,
NVL(pli.note_to_vendor, prl.note_to_vendor),
DECODE(pli.consigned_flag,'Y',NULL, NVL(pli.oke_contract_header_id,prl.oke_contract_header_id)),
DECODE(pli.consigned_flag,'Y',NULL, NVL(pli.oke_contract_version_id,prl.oke_contract_version_id)),
NVL(pli.secondary_unit_of_measure,prl.secondary_unit_of_measure),
NVL(pli.secondary_quantity,prl.secondary_quantity),
NVL(pli.preferred_grade,prl.preferred_grade),
prl.drop_ship_flag, --
prl.vmi_flag,
prl.supplier_ref_number, --
--
NVL(pli.effective_date, prl.assignment_start_date),
NVL(pli.expiration_date, prl.assignment_end_date),
NVL(pli.contractor_first_name, prl.candidate_first_name),
NVL(pli.contractor_last_name, prl.candidate_last_name) ,
PLI.LINE_NUM_DISPLAY ,
NVL(PLI.GROUP_LINE_ID,prl.GROUP_LINE_ID) ,
NVL(pli.CLM_INFO_FLAG, prl.CLM_INFO_FLAG) ,
NVL(pli.CLM_OPTION_INDICATOR, prl.CLM_OPTION_INDICATOR) ,
NVL(pli.CLM_OPTION_NUM, prl.CLM_OPTION_NUM) ,
NVL(pli.CLM_OPTION_FROM_DATE, prl.CLM_OPTION_FROM_DATE) ,
NVL(pli.CLM_OPTION_TO_DATE, prl.CLM_OPTION_TO_DATE) ,
NVL(pli.CLM_FUNDED_FLAG, prl.CLM_FUNDED_FLAG) ,
NVL(pli.CLM_BASE_LINE_NUM,prl.CLM_BASE_LINE_NUM)
--
FROM po_lines_interface pli,
po_headers_interface phi,
po_requisition_lines_all prl --
WHERE pli.interface_line_id = pli2.interface_line_id
AND pli.interface_header_id = phi.interface_header_id
AND phi.interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id
AND pli.requisition_line_id = prl.requisition_line_id(+)
)
WHERE pli2.interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id
AND ( NVL(pli2.clm_info_flag,'N') = 'Y');
c) Update Slin's Group Line Id with the Line Id from step2.
3. Get All options
4. For each Option Line.
a) Get the Base Line Id (clm_base_line_num)
b) Find the Line Id of line in Interface table whose Req Line Matches the above Base Line Id
c) Update Slin's clm_base_line_num with the Line Id from step2.
*/
FOR crec IN c_slin
LOOP
l_progress :='061';
UPDATE po_lines_interface
SET group_line_id = l_parent_line_id
WHERE INTERFACE_LINE_ID = crec.INTERFACE_LINE_ID
AND INTERFACE_HEADER_ID = PO_AUTOCREATE_PARAMS.x_interface_header_id;
SELECT line_num_display
INTO l_clin_num_display
FROM PO_LINES_merge_v
WHERE PO_HEADER_ID = po_autocreate_params.g_po_header_id
AND draft_id = po_autocreate_params.g_draft_id
AND LINE_NUM =
(SELECT line_num
FROM po_lines_interface
WHERE interface_line_id = l_parent_line_id
AND INTERFACE_HEADER_ID = PO_AUTOCREATE_PARAMS.x_interface_header_id
);
SELECT LPad(l_slin_count + 1,6,'0') INTO l_slin_num_display FROM dual;
UPDATE po_lines_interface
SET line_num_display = l_slin_num_display
WHERE INTERFACE_LINE_ID = crec.INTERFACE_LINE_ID
AND INTERFACE_HEADER_ID = PO_AUTOCREATE_PARAMS.x_interface_header_id
AND clm_info_flag ='Y';
UPDATE po_lines_interface
SET CLM_BASE_LINE_NUM = l_parent_line_id
WHERE INTERFACE_LINE_ID = crec.INTERFACE_LINE_ID
AND INTERFACE_HEADER_ID = PO_AUTOCREATE_PARAMS.x_interface_header_id;
PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Before insert into Distribution interface');
INSERT
INTO po_distributions_interface
(
interface_header_id,
interface_line_id,
interface_distribution_id,
distribution_num,
charge_account_id,
set_of_books_id,
quantity_ordered,
amount_ordered,
rate,
rate_date,
req_distribution_id,
deliver_to_location_id,
deliver_to_person_id,
encumbered_flag,
gl_encumbered_date,
gl_encumbered_period_name,
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,
tax_recovery_override_flag, --
recovery_rate,
recoverable_tax,
nonrecoverable_tax,
award_id,
oke_contract_line_id,
oke_contract_deliverable_id,
group_line_id,
funded_value,
partial_funded_flag,
quantity_funded, --
amount_funded --
)
SELECT pli.interface_header_id,
pli.interface_line_id,
po_distributions_interface_s.nextval,
prd.distribution_num,
prd.code_combination_id,
prd.set_of_books_id,
/* Bug : 13695551 : For clm sourcing, we donot split, so we need to prorate distribution quantities for quantity and amount based lines */
Decode(PO_AUTOCREATE_PARAMS.g_interface_source_code , 'SOURCING' , prd.req_line_quantity * pli.quantity/prl.quantity, prd.req_line_quantity),
DECODE ( PO_AUTOCREATE_PARAMS.g_interface_source_code , 'SOURCING' , prd.req_line_amount * pli.amount/prl.amount , prd.req_line_amount ),
phi.rate,
phi.rate_date,
prd.distribution_id,
prl.deliver_to_location_id,
prl.to_person_id,
prd.encumbered_flag,
prd.gl_encumbered_date,
prd.gl_encumbered_period_name,
prl.destination_type_code,
prl.destination_organization_id,
prl.destination_subinventory,
prd.budget_account_id,
prd.accrual_account_id,
prd.variance_account_id,
--< Shared Proc FPJ Start >
-- For non SPS case (common case), set Destination Accounts to NULL
NULL, -- dest_charge_account_id
NULL, -- dest_variance_account_id
--< Shared Proc FPJ End >
prl.wip_entity_id,
prl.wip_line_id,
prl.wip_repetitive_schedule_id,
prl.wip_operation_seq_num,
prl.wip_resource_seq_num,
prl.bom_resource_id,
prd.prevent_encumbrance_flag,
prd.project_id,
prd.task_id,
prd.end_item_unit_number,
prd.expenditure_type,
prd.project_accounting_context,
prl.destination_context,
prd.expenditure_organization_id,
prd.expenditure_item_date,
prd.tax_recovery_override_flag, --
prd.recovery_rate,
prd.recoverable_tax,
prd.nonrecoverable_tax,
prd.award_id,
DECODE(pli.consigned_flag,'Y',NULL, prd.oke_contract_line_id),
DECODE(pli.consigned_flag,'Y',NULL, prd.oke_contract_deliverable_id),
prd.info_line_id,
prd.funded_value,
prd.partial_funded_flag,
prd.quantity_funded, --
prd.amount_funded --
FROM po_requisition_lines_all prl, --
po_req_distributions_all prd, --
po_lines_interface pli,
po_headers_interface phi
WHERE prd.requisition_line_id = prl.requisition_line_id
AND prl.requisition_line_id = pli.requisition_line_id
AND pli.interface_header_id = phi.interface_header_id
AND phi.interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id
AND
/* Bug : 13695551 : From sourcing, one req distribution should be funding multiple award line distributions*/
( (po_autocreate_params.g_is_clm_po ='Y' AND PO_AUTOCREATE_PARAMS.g_interface_source_code = 'SOURCING')
OR
(NOT EXISTS (SELECT 'Y'
FROM po_distributions_all pda
WHERE pda.req_distribution_id = prd.distribution_id))
);
p_message => 'After insert into Distribution interface');
do the insert. We get the deliver to information
from the distribution record.
*/
IF po_autocreate_params.g_is_clm_po ='Y' THEN
l_progress :='090';
PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'insert into p_dists record for Option Line');
INSERT
INTO po_distributions_interface
(
interface_header_id,
interface_line_id,
interface_distribution_id,
deliver_to_location_id,
deliver_to_person_id,
destination_type_code,
destination_organization_id,
destination_subinventory,
destination_context
)
SELECT pli.interface_header_id,
pli.interface_line_id,
po_distributions_interface_s.nextval,
prl.deliver_to_location_id,
prl.to_person_id,
prl.destination_type_code,
prl.destination_organization_id,
prl.destination_subinventory,
prl.destination_context
FROM po_requisition_lines_all prl,
po_lines_interface pli --option
WHERE pli.interface_header_id = po_autocreate_params.x_interface_header_id
AND (( pli.clm_option_indicator = 'O' AND NVL(pli.clm_exercised_flag,'N') = 'N') -- Bug 9960752
OR prl.fund_source_not_known = 'Y' )
--CLM Phase 2 Changes : PR Lines with no distributions should be handled like
--option lines
AND NVL(pli.clm_info_flag,'N') = 'N'
AND prl.requisition_line_id = pli.requisition_line_id;
p_message => 'After insert into p_dists record for Option Line');
SELECT COUNT(*)
INTO l_count_dist
FROM po_distributions_interface
WHERE interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id;
UPDATE po_distributions_interface pdi
SET group_line_id =
(SELECT interface_line_id
FROM po_lines_interface pi
WHERE pi.requisition_line_id = pdi.group_line_id
AND pi.interface_header_id = po_autocreate_params.x_interface_header_id
)
WHERE pdi.interface_header_id = po_autocreate_params.x_interface_header_id;
p_message => 'No of Distribution records updated : ' || SQL%ROWCOUNT);
SELECT hrl.inventory_organization_id
INTO l_ship_to_org_id
FROM hr_locations_all hrl
WHERE hrl.location_id = i.ship_to_location_id
AND hrl.ship_to_site_flag = 'Y';
INSERT
INTO po_distributions_interface
(
interface_header_id ,
interface_line_id ,
interface_distribution_id ,
destination_type_code ,
deliver_to_location_id ,
destination_organization_id
)
VALUES
(
i.interface_header_id ,
i.interface_line_id ,
PO_DISTRIBUTIONS_INTERFACE_S.nextval ,
'EXPENSE' ,
i.ship_to_location_id ,
l_ship_to_org_id
);
PO_LOG.stmt( g_log_head || l_api_name, 190, 'Num rows inserted', SQL%ROWCOUNT);
SELECT intf_lines.interface_line_id,
intf_lines.interface_header_id,
intf_lines.po_header_id,
NULL, -- intf_lines.po_line_id,
intf_lines.action,
intf_lines.document_num,
intf_lines.item,
intf_lines.vendor_product_num,
intf_lines.supplier_part_auxid,
intf_lines.item_id,
intf_lines.item_revision,
intf_lines.job_business_group_name,
intf_lines.job_business_group_id,
intf_lines.job_name,
intf_lines.job_id,
intf_lines.category,
intf_lines.category_id,
intf_lines.ip_category_name,
intf_lines.ip_category_id,
intf_lines.uom_code,
intf_lines.unit_of_measure,
intf_lines.line_type,
intf_lines.line_type_id,
intf_lines.un_number,
intf_lines.un_number_id,
intf_lines.hazard_class,
intf_lines.hazard_class_id,
intf_lines.template_name,
intf_lines.template_id,
intf_lines.item_description,
intf_lines.unit_price,
intf_lines.base_unit_price,
intf_lines.from_header_id,
intf_lines.from_line_id,
intf_lines.list_price_per_unit,
intf_lines.market_price,
intf_lines.capital_expense_flag,
intf_lines.min_release_amount,
intf_lines.allow_price_override_flag,
intf_lines.price_type,
intf_lines.price_break_lookup_code,
intf_lines.closed_code,
intf_lines.quantity,
intf_lines.line_num,
intf_lines.shipment_num,
intf_lines.price_chg_accept_flag,
intf_lines.effective_date,
intf_lines.expiration_date,
intf_lines.line_attribute14,
intf_lines.price_update_tolerance,
intf_lines.line_loc_populated_flag,
intf_lines.negotiated_by_preparer_flag,
intf_lines.amount,
intf_lines.contractor_last_name,
intf_lines.contractor_first_name,
intf_lines.over_tolerance_error_flag,
intf_lines.not_to_exceed_price,
intf_lines.po_release_id,
intf_lines.release_num,
intf_lines.source_shipment_id,
intf_lines.contract_num,
intf_lines.contract_id,
intf_lines.type_1099,
intf_lines.closed_by,
intf_lines.closed_date,
intf_lines.committed_amount,
intf_lines.qty_rcv_exception_code,
intf_lines.weight_uom_code,
intf_lines.volume_uom_code,
intf_lines.secondary_unit_of_measure,
intf_lines.secondary_quantity,
intf_lines.preferred_grade,
intf_lines.process_code,
NULL, -- parent_interface_line_id
intf_lines.file_line_language,
intf_lines.requisition_line_id,
intf_lines.group_line_id,
intf_lines.line_num_display,
intf_lines.clm_info_flag,
intf_lines.clm_option_indicator,
intf_lines.clm_base_line_num ,
intf_lines.clm_option_num ,
intf_lines.clm_option_from_date ,
intf_lines.clm_option_to_date ,
intf_lines.clm_funded_flag ,
intf_lines.contract_type ,
intf_lines.cost_constraint ,
intf_lines.clm_idc_type, -- clm idc type
intf_lines.need_by_date ,
intf_lines.transaction_reason_code,
intf_lines.retainage_rate ,
intf_lines.consigned_flag ,
intf_lines.oke_contract_version_id,
intf_lines.oke_contract_header_id ,
intf_lines.note_to_vendor ,
intf_lines.qty_rcv_tolerance ,
intf_lines.from_line_location_id ,
intf_lines.auction_header_id,
intf_lines.auction_display_number ,
intf_lines.auction_line_number ,
intf_lines.bid_number,
intf_lines.bid_line_number,
intf_lines.supplier_ref_number,
intf_lines.max_retainage_amount,
intf_lines.progress_payment_rate,
intf_lines.recoupment_rate ,
intf_lines.catalog_name ,
intf_lines.firm_flag,
intf_lines.drop_ship_flag,
intf_lines.tax_code_id,
intf_lines.transaction_flow_header_id,
intf_lines.receipt_required_flag,
intf_lines.note_to_receiver,
intf_lines.vmi_flag,
intf_lines.ship_to_organization_id,
intf_lines.ship_to_location_id,
intf_lines.promised_date,
NVL2(intf_lines.advance_amount, 'Y', 'N'),
intf_lines.advance_amount ,
NVL(intf_lines.line_loc_populated_flag, 'N'),
intf_lines.price_discount ,
intf_lines.terms_id ,
intf_lines.Shipment_Type ,
-- standard who columns
intf_lines.last_updated_by,
intf_lines.last_update_date,
intf_lines.last_update_login,
intf_lines.creation_date,
intf_lines.created_by,
intf_lines.request_id,
intf_lines.program_application_id,
intf_lines.program_id,
intf_lines.program_update_date,
-- attributes read from headers
intf_headers.draft_id,
intf_headers.action,
intf_headers.po_header_id,
draft_headers.vendor_id,
draft_headers.vendor_site_id,
draft_headers.min_release_amount,
draft_headers.start_date,
draft_headers.end_date,
draft_headers.global_agreement_flag,
draft_headers.currency_code,
draft_headers.created_language,
draft_headers.style_id,
draft_headers.rate_type,
draft_headers.rate_date,
draft_headers.rate,
intf_headers.pcard_id,
-- txn table columns
NULL, -- order_type_lookup_code
NULL, -- purchase_basis
NULL, -- matching_basis
NULL, -- unordered_flag
NULL, -- cancel_flag
NULL, -- quantity_committed
NULL, -- tax_attribute_update_code
FND_API.g_false, -- error_flag_tbl
FND_API.g_false, -- need_to_reject_flag_tbl
FND_API.g_false, -- create_line_loc_tbl
-1, -- group_num
NULL, -- origin_line_num
FND_API.g_false, -- match_line_found
NULL , -- allow_desc_update_flag_tbl
NULL, -- destination_type_code_tbl
NULL, -- dest_organization_id_tbl
NULL, -- deliver_to_loc_id_tbl
NULL, -- rc_enforce_shipto_loc_code_tbl
NULL, -- rc_subst_receipt_flag_tbl
NULL, -- rc_receiving_routing_id_tbl
NULL, -- rc_qty_rcv_tolerance_tbl
NULL, -- rc_qty_rcv_exception_code_tbl
NULL, -- rc_days_early_recpt_tbl
NULL, -- rc_days_late_recpt_tbl
NULL, -- rc_receipt_days_excep_code_tbl
NULL, -- it_list_price_per_unit_tbl
NULL, -- it_market_price_tbl
NULL, -- it_taxable_flag_tbl
NULL, -- it_unit_meas_lookup_code_tbl
NULL, -- it_inspect_req_flag_tbl
NULL, -- it_receipt_req_flag_tbl
NULL, -- it_invoice_tolerance_tbl
NULL, -- it_rcv_tolerance_tbl
NULL, -- it_secondary_uom_code_tbl
NULL, -- it_grade_control_flag_tbl
NULL, -- rate_for_req_fields_tbl
NULL, -- taxable_flag_tbl
NULL, -- org_id__tbl
NULL, -- po_line_loc_id
--CLM specific columns
intf_lines.clm_min_total_amount,
intf_lines.clm_max_total_amount,
intf_lines.clm_min_total_quantity,
intf_lines.clm_max_total_quantity,
intf_lines.clm_min_order_amount,
intf_lines.clm_max_order_amount,
intf_lines.clm_min_order_quantity,
intf_lines.clm_max_order_quantity,
intf_lines.clm_total_amount_ordered,
intf_lines.clm_total_quantity_ordered,
intf_lines.clm_period_perf_end_date,
intf_lines.clm_period_perf_start_date,
intf_lines.clm_exercised_flag, -- Bug 9960752
intf_lines.clm_exercised_date, -- Bug 9960752
--CLM Phase4 Changes
intf_lines.clm_exhibit_name,
intf_lines.clm_delivery_event_code,
intf_lines.clm_delivery_period,
intf_lines.clm_promise_period,
intf_lines.clm_pop_duration,
intf_lines.clm_delivery_period_uom,
intf_lines.clm_promise_period_uom,
intf_lines.clm_pop_duration_uom
BULK COLLECT
INTO p_lines.intf_line_id_tbl,
p_lines.intf_header_id_tbl,
p_lines.po_header_id_tbl,
p_lines.po_line_id_tbl,
p_lines.action_tbl,
p_lines.document_num_tbl,
p_lines.item_tbl,
p_lines.vendor_product_num_tbl,
p_lines.supplier_part_auxid_tbl,
p_lines.item_id_tbl,
p_lines.item_revision_tbl,
p_lines.job_business_group_name_tbl,
p_lines.job_business_group_id_tbl,
p_lines.job_name_tbl,
p_lines.job_id_tbl,
p_lines.category_tbl,
p_lines.category_id_tbl,
p_lines.ip_category_tbl,
p_lines.ip_category_id_tbl,
p_lines.uom_code_tbl,
p_lines.unit_of_measure_tbl,
p_lines.line_type_tbl,
p_lines.line_type_id_tbl,
p_lines.un_number_tbl,
p_lines.un_number_id_tbl,
p_lines.hazard_class_tbl,
p_lines.hazard_class_id_tbl,
p_lines.template_name_tbl,
p_lines.template_id_tbl,
p_lines.item_desc_tbl,
p_lines.unit_price_tbl,
p_lines.base_unit_price_tbl,
p_lines.from_header_id_tbl,
p_lines.from_line_id_tbl,
p_lines.list_price_per_unit_tbl,
p_lines.market_price_tbl,
p_lines.capital_expense_flag_tbl,
p_lines.min_release_amount_tbl,
p_lines.allow_price_override_flag_tbl,
p_lines.price_type_tbl,
p_lines.price_break_lookup_code_tbl,
p_lines.closed_code_tbl,
p_lines.quantity_tbl,
p_lines.line_num_tbl,
p_lines.shipment_num_tbl,
p_lines.price_chg_accept_flag_tbl,
p_lines.effective_date_tbl,
p_lines.expiration_date_tbl,
p_lines.attribute14_tbl,
p_lines.price_update_tolerance_tbl,
p_lines.line_loc_populated_flag_tbl,
p_lines.negotiated_flag_tbl,
p_lines.amount_tbl,
p_lines.contractor_last_name_tbl,
p_lines.contractor_first_name_tbl,
p_lines.over_tolerance_err_flag_tbl,
p_lines.not_to_exceed_price_tbl,
p_lines.po_release_id_tbl,
p_lines.release_num_tbl,
p_lines.source_shipment_id_tbl,
p_lines.contract_num_tbl,
p_lines.contract_id_tbl,
p_lines.type_1099_tbl,
p_lines.closed_by_tbl,
p_lines.closed_date_tbl,
p_lines.committed_amount_tbl,
p_lines.qty_rcv_exception_code_tbl,
p_lines.weight_uom_code_tbl,
p_lines.volume_uom_code_tbl,
p_lines.secondary_unit_of_meas_tbl,
p_lines.secondary_quantity_tbl,
p_lines.preferred_grade_tbl,
p_lines.process_code_tbl,
p_lines.parent_interface_line_id_tbl,
p_lines.file_line_language_tbl,
p_lines.requisition_line_id_tbl,
p_lines.group_line_id_tbl,
p_lines.line_num_display_tbl,
p_lines.clm_info_flag_tbl,
p_lines.clm_option_indicator_tbl,
p_lines.clm_base_line_num_tbl,
p_lines.clm_option_num_tbl,
p_lines.clm_option_from_date_tbl,
p_lines.clm_option_to_date_tbl,
p_lines.clm_funded_flag_tbl,
p_lines.contract_type_tbl,
p_lines.cost_constraint_tbl,
p_lines.clm_idc_type_tbl,
p_lines.need_by_date_tbl,
p_lines.transaction_reason_code_tbl,
p_lines.retainage_rate_tbl,
p_lines.consigned_flag_tbl,
p_lines.oke_contract_version_id_tbl,
p_lines.oke_contract_header_id_tbl,
p_lines.note_to_vendor_tbl,
p_lines.qty_rcv_tolerance_tbl,
p_lines.from_line_location_id_tbl,
p_lines.auction_header_id_tbl,
p_lines.auction_display_number_tbl,
p_lines.auction_line_number_tbl,
p_lines.bid_number_tbl,
p_lines.bid_line_number_tbl ,
p_lines.supplier_ref_number_tbl ,
p_lines.max_retainage_amount_tbl,
p_lines.progress_payment_rate_tbl,
p_lines.recoupment_rate_tbl ,
p_lines.catalog_name_tbl ,
p_lines.firm_status_lookup_code_tbl,
p_lines.drop_ship_flag_tbl,
p_lines.tax_code_id_tbl,
p_lines.txn_flow_header_id_tbl,
p_lines.receipt_required_flag_tbl,
p_lines.note_to_receiver_tbl,
p_lines.vmi_flag_tbl,
p_lines.line_ship_to_org_id_tbl,
p_lines.line_ship_to_loc_id_tbl,
p_lines.promised_date_tbl,
p_lines.has_advance_flag_tbl,
p_lines.advance_amount_tbl,
p_lines.poll_intf_pop_flag_tbl,
p_lines.price_discount_tbl,
p_lines.terms_id_tbl,
p_lines.Shipment_Type_tbl,
-- standard who columns
p_lines.last_updated_by_tbl,
p_lines.last_update_date_tbl,
p_lines.last_update_login_tbl,
p_lines.creation_date_tbl,
p_lines.created_by_tbl,
p_lines.request_id_tbl,
p_lines.program_application_id_tbl,
p_lines.program_id_tbl,
p_lines.program_update_date_tbl,
-- attributes read from headers
p_lines.draft_id_tbl,
p_lines.hd_action_tbl,
p_lines.hd_po_header_id_tbl,
p_lines.hd_vendor_id_tbl,
p_lines.hd_vendor_site_id_tbl,
p_lines.hd_min_release_amount_tbl,
p_lines.hd_start_date_tbl,
p_lines.hd_end_date_tbl,
p_lines.hd_global_agreement_flag_tbl,
p_lines.hd_currency_code_tbl,
p_lines.hd_created_language_tbl,
p_lines.hd_style_id_tbl,
p_lines.hd_rate_type_tbl,
p_lines.hd_rate_date_tbl,
p_lines.hd_rate_tbl,
p_lines.hd_pcard_id_tbl,
-- txn table columns
p_lines.order_type_lookup_code_tbl,
p_lines.purchase_basis_tbl,
p_lines.matching_basis_tbl,
p_lines.unordered_flag_tbl,
p_lines.cancel_flag_tbl,
p_lines.quantity_committed_tbl,
p_lines.tax_attribute_update_code_tbl,
p_lines.error_flag_tbl,
p_lines.need_to_reject_flag_tbl,
p_lines.create_line_loc_tbl,
p_lines.group_num_tbl,
p_lines.origin_line_num_tbl,
p_lines.match_line_found_tbl,
p_lines.allow_desc_update_flag_tbl,
p_lines.destination_type_code_tbl,
p_lines.dest_organization_id_tbl,
p_lines.deliver_to_loc_id_tbl,
p_lines.rc_enforce_shipto_loc_code_tbl,
p_lines.rc_subst_receipt_flag_tbl,
p_lines.rc_receiving_routing_id_tbl,
p_lines.rc_qty_rcv_tolerance_tbl,
p_lines.rc_qty_rcv_exception_code_tbl,
p_lines.rc_days_early_recpt_tbl,
p_lines.rc_days_late_recpt_tbl,
p_lines.rc_receipt_days_excep_code_tbl,
p_lines.it_list_price_per_unit_tbl,
p_lines.it_market_price_tbl,
p_lines.it_taxable_flag_tbl,
p_lines.it_unit_meas_lookup_code_tbl,
p_lines.it_inspect_req_flag_tbl,
p_lines.it_receipt_req_flag_tbl,
p_lines.it_invoice_tolerance_tbl,
p_lines.it_rcv_tolerance_tbl,
p_lines.it_secondary_uom_code_tbl,
p_lines.it_grade_control_flag_tbl,
p_lines.rate_for_req_fields_tbl,
p_lines.taxable_flag_tbl,
p_lines.org_id_tbl,
p_lines.line_loc_id_tbl,
--CLM specific columns
p_lines.clm_min_total_amount_tbl,
p_lines.clm_max_total_amount_tbl,
p_lines.clm_min_total_quantity_tbl,
p_lines.clm_max_total_quantity_tbl,
p_lines.clm_min_order_amount_tbl,
p_lines.clm_max_order_amount_tbl,
p_lines.clm_min_order_quantity_tbl,
p_lines.clm_max_order_quantity_tbl,
p_lines.clm_total_amount_ordered_tbl,
p_lines.clm_total_quantity_ordered_tbl,
p_lines.clm_period_perf_end_date_tbl,
p_lines.clm_period_perf_start_date_tbl,
p_lines.clm_exercised_flag_tbl, -- Bug 9960752
p_lines.clm_exercised_date_tbl, -- Bug 9960752
--CLM Phase4 Changes
p_lines.clm_exhibit_name_tbl,
p_lines.clm_delivery_event_code_tbl,
p_lines.clm_delivery_period_tbl,
p_lines.clm_promise_period_tbl,
p_lines.clm_pop_duration_tbl,
p_lines.clm_delivery_period_uom_tbl,
p_lines.clm_promise_period_uom_tbl,
p_lines.clm_pop_duration_uom_tbl
FROM po_lines_interface intf_lines,
po_headers_interface intf_headers,
po_headers_draft_all draft_headers
WHERE intf_lines.interface_header_id = intf_headers.interface_header_id
AND intf_headers.draft_id = draft_headers.draft_id
AND intf_headers.po_header_id = draft_headers.po_header_id
AND intf_lines.interface_header_id = p_interface_header_id
ORDER BY intf_lines.line_num,
NVL(intf_lines.shipment_num,0),
intf_lines.unit_price,
intf_lines.interface_line_id;
update_req_pool_fail EXCEPTION;
SELECT PO_PRICE_DIFF_INTERFACE_S.NEXTVAL price_diff_interface_id ,
PD.price_differential_num ,
PD.price_type ,
DECODE ( PD.entity_type , 'REQ LINE' , PD.multiplier , 'PO LINE' , PD.multiplier , 'PRICE BREAK' , PD.min_multiplier , 'BLANKET LINE' , PD.min_multiplier ) multiplier ,
PD.enabled_flag
FROM po_price_differentials PD
WHERE PD.entity_type = p_source_entity_type
AND PD.entity_id = p_source_entity_id
AND NVL(PD.enabled_flag,'N') = 'Y';
SELECT interface_line_id,
need_by_date
FROM po_lines_interface
WHERE interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id
AND line_num = p_line_num
AND shipment_num IS NOT NULL
AND interface_line_id <> p_intf_line_id
ORDER BY shipment_num;
SELECT prl.org_id
INTO l_requesting_ou_id
FROM po_requisition_lines_all prl
WHERE prl.requisition_line_id = p_lines.requisition_line_id_tbl (i);
SELECT order_type_lookup_code,
purchase_basis
INTO x_order_type_lookup_code,
l_purchase_basis
FROM po_line_types
WHERE line_type_id = p_lines.line_type_id_tbl (i);
SELECT po_line_id,
line_type_id,
line_num,
item_id,
item_revision,
unit_meas_lookup_code,
base_unit_price, --
unit_price,
transaction_reason_code,
price_break_lookup_code,
manual_price_change_flag
INTO x_po_line_id,
x_po_line_type_id,
x_line_num,
x_po_item_id,
x_po_item_revision,
x_po_unit_meas_lookup_code,
l_base_unit_price, --
x_po_unit_price,
x_po_transaction_reason_code,
x_price_break_lookup_code,
l_manual_price_change_flag
FROM PO_LINES_draft_all
WHERE PO_HEADER_ID = po_autocreate_params.g_po_header_id
AND LINE_NUM = p_lines.line_num_tbl(i)
AND draft_id =po_autocreate_params.g_draft_id;
SELECT pol.unit_meas_lookup_code,
poh.type_lookup_code
INTO l_ga_uom,
l_from_type_lookup_code
FROM po_lines_all pol,
po_headers_all poh
WHERE pol.po_line_id = p_lines.from_line_id_tbl(i)
AND poh.po_header_id =p_lines.from_header_id_tbl(i)
AND poh.po_header_id =pol.po_header_id;
SELECT NVL(SH.enhanced_pricing_flag,'N')
INTO l_enhanced_pricing_flag
FROM po_doc_style_headers SH
WHERE SH.style_id = p_lines.hd_style_id_tbl(i);
* from the lowest existing level by splitting the select.
*/
/*
Prior to the fix we were getting the values of receipt required
flag and inspection required flag of the item/master org to
default in the autocreated document and were not considering the
values defined at item/destination organization.
Now, we derive the values from the item/destination organization
and if it is not defined at the item/destination organization
level, then we derive the values from the item/master organization.
*/
p_lines.it_list_price_per_unit_tbl(i) :=NULL;
SELECT msi.invoice_close_tolerance,
msi.receive_close_tolerance,
msi.inspection_required_flag,
msi.receipt_required_flag
INTO p_lines.it_invoice_tolerance_tbl(i),
p_lines.it_rcv_tolerance_tbl(i),
p_lines.it_inspect_req_flag_tbl(i),
p_lines.it_receipt_req_flag_tbl(i)
FROM mtl_system_items msi
WHERE msi.inventory_item_id = p_lines.item_id_tbl(i)
AND msi.organization_id = p_lines.dest_organization_id_tbl(i);
SELECT DECODE(x_order_type_lookup_code, 'QUANTITY', msi.list_price_per_unit/NVL(p_lines.hd_rate_tbl(i),1), 1), --
DECODE(x_order_type_lookup_code, 'QUANTITY', msi.market_price /NVL(p_lines.hd_rate_tbl(i),1), 1), --
msi.taxable_flag,
msi.primary_uom_code,
NVL(p_lines.it_inspect_req_flag_tbl(i),msi.inspection_required_flag),
NVL(p_lines.it_receipt_req_flag_tbl(i),msi.receipt_required_flag),
NVL(p_lines.it_invoice_tolerance_tbl(i),msi.invoice_close_tolerance),
NVL(p_lines.it_rcv_tolerance_tbl(i),msi.receive_close_tolerance),
DECODE(msi.tracking_quantity_ind, g_chktype_TRACKING_QTY_IND, msi.secondary_uom_code,NULL),--
NVL(msi.grade_control_flag,'N') --
INTO p_lines.it_list_price_per_unit_tbl(i),
p_lines.it_market_price_tbl(i),
p_lines.it_taxable_flag_tbl(i),
p_lines.it_unit_meas_lookup_code_tbl(i),
p_lines.it_inspect_req_flag_tbl(i),
p_lines.it_receipt_req_flag_tbl(i),
p_lines.it_invoice_tolerance_tbl(i),
p_lines.it_rcv_tolerance_tbl(i),
p_lines.it_secondary_uom_code_tbl(i), --
p_lines.it_grade_control_flag_tbl(i) --
FROM mtl_system_items msi
WHERE msi.inventory_item_id = p_lines.item_id_tbl(i)
AND msi.organization_id = po_autocreate_params.g_sys.master_inv_org_id;
SELECT NVL(p_lines.it_rcv_tolerance_tbl(i),receipt_close),
NVL(p_lines.it_receipt_req_flag_tbl(i),receiving_flag)
INTO p_lines.it_rcv_tolerance_tbl(i),
p_lines.it_receipt_req_flag_tbl(i)
FROM po_line_types_v
WHERE line_type_id = p_lines.line_type_id_tbl(i);
/* Select receipt required flag,inspection required flag
at vendor level before system option level to complete the
default logic
*/
BEGIN
l_progress := '190';
SELECT NVL(p_lines.it_inspect_req_flag_tbl(i), vendor.INSPECTION_REQUIRED_FLAG),
NVL(p_lines.it_receipt_req_flag_tbl(i), vendor.RECEIPT_REQUIRED_FLAG)
INTO p_lines.it_inspect_req_flag_tbl(i),
p_lines.it_receipt_req_flag_tbl(i)
FROM po_vendors vendor
WHERE vendor.vendor_id = p_lines.hd_vendor_id_tbl(i);
/* Select receipt required flag,inspection required flag
receipt close tolerance and insp close tolerance
also from po system parameters if not defined at above level
*/
BEGIN
SELECT NVL(p_lines.it_inspect_req_flag_tbl(i), posp.INSPECTION_REQUIRED_FLAG),
NVL(p_lines.it_receipt_req_flag_tbl(i), posp.RECEIVING_FLAG),
NVL(p_lines.it_invoice_tolerance_tbl(i), posp.INVOICE_CLOSE_TOLERANCE),
NVL(p_lines.it_rcv_tolerance_tbl(i), posp.RECEIVE_CLOSE_TOLERANCE)
INTO p_lines.it_inspect_req_flag_tbl(i),
p_lines.it_receipt_req_flag_tbl(i),
p_lines.it_invoice_tolerance_tbl(i),
p_lines.it_rcv_tolerance_tbl(i)
FROM po_system_parameters_all posp --
WHERE NVL(org_id, -99) = NVL(PO_AUTOCREATE_PARAMS.g_purchasing_ou_id, -99); --
SELECT un_number_id,
hazard_class_id
INTO x_un_number_id,
x_hazard_class_id
FROM mtl_system_items
WHERE inventory_item_id = p_lines.item_id_tbl(i)
AND organization_id = po_autocreate_params.g_sys.master_inv_org_id;
SELECT unit_of_measure
INTO x_unit_of_measure
FROM po_line_types
WHERE line_type_id= p_lines.line_type_id_tbl(i);
SELECT unit_of_measure
INTO x_unit_of_measure
FROM po_line_types
WHERE line_type_id = p_lines.line_type_id_tbl(i);
SELECT po_lines_s.nextval INTO x_po_line_id FROM sys.dual;
p_message => 'Before Inserting into the Price Diff table ' ||
' l_source_entity_type : '||l_source_entity_type ||
' l_source_entity_id : ' || l_source_entity_id );
SELECT type_lookup_code,
global_agreement_flag
INTO l_type_lookup_code,
l_global_agreement_flag
FROM po_headers_all
WHERE po_header_id=p_lines.from_header_id_tbl(i);
SELECT negotiated_by_preparer_flag
INTO l_negotiated_by_preparer_flag
FROM po_lines_all
WHERE po_line_id=p_lines.from_line_id_tbl(i);
SELECT negotiated_by_preparer_flag
INTO l_negotiated_by_preparer_flag
FROM po_requisition_lines_all
WHERE requisition_line_id=p_lines.requisition_line_id_tbl(i);
SELECT negotiated_by_preparer_flag
INTO l_negotiated_by_preparer_flag
FROM po_requisition_lines_all
WHERE requisition_line_id=p_lines.requisition_line_id_tbl(i);
SELECT retainage_rate
INTO l_retainage_rate
FROM po_vendor_sites_all
WHERE vendor_site_id = p_lines.hd_vendor_site_id_tbl(i);
SELECT DECODE(PO_AUTOCREATE_PARAMS.g_document_type,
'RFQ', NULL,
DECODE(PO_AUTOCREATE_PARAMS.g_interface_source_code,
'SOURCING',NULL, ROUND (p_lines.it_list_price_per_unit_tbl(i),NVL(x_ext_precision,15)))) ,
DECODE(PO_AUTOCREATE_PARAMS.g_interface_source_code,'SOURCING',NULL, ROUND(p_lines.it_market_price_tbl(i),NVL(x_ext_precision,15))) ,
DECODE(PO_AUTOCREATE_PARAMS.g_document_type, 'RFQ', 1,'PA',x_quantity, x_quantity) ,
nvl2(g_calculate_tax_flag, 'CREATE', NULL) ,
DECODE(PO_AUTOCREATE_PARAMS.g_document_type, 'RFQ', NULL,p_lines.type_1099_tbl(i)) ,
DECODE(PO_AUTOCREATE_PARAMS.g_interface_source_code,'SOURCING', x_un_number_id,p_lines.un_number_id_tbl(i)) ,
DECODE(PO_AUTOCREATE_PARAMS.g_interface_source_code,'SOURCING', x_hazard_class_id,p_lines.hazard_class_id_tbl(i)) ,
DECODE(p_lines.consigned_flag_tbl(i),'Y',NULL,p_lines.contract_id_tbl(i)) ,
DECODE(PO_AUTOCREATE_PARAMS.g_document_type, 'RFQ', NULL, p_lines.qty_rcv_tolerance_tbl(i)) ,
DECODE(PO_AUTOCREATE_PARAMS.g_document_type, 'RFQ', NULL, p_lines.qty_rcv_exception_code_tbl(i)) ,
DECODE(p_lines.consigned_flag_tbl(i),'Y',NULL,NVL(p_lines.from_header_id_tbl(i),x_quote_header_id)) ,
DECODE(p_lines.consigned_flag_tbl(i),'Y',NULL,NVL(p_lines.from_line_id_tbl(i),x_quote_line_id)) ,
DECODE(p_lines.consigned_flag_tbl(i),'Y',NULL,l_price_break_id) ,
DECODE(PO_AUTOCREATE_PARAMS.g_interface_source_code,'SOURCING',NULL,p_lines.note_to_vendor_tbl(i)) ,
DECODE(p_lines.consigned_flag_tbl(i),'Y',NULL,p_lines.oke_contract_header_id_tbl(i)) ,
DECODE(p_lines.consigned_flag_tbl(i),'Y',NULL,p_lines.oke_contract_version_id_tbl(i)) ,
DECODE ( PO_AUTOCREATE_PARAMS.g_document_type,
'PA' , DECODE ( x_order_type_lookup_code,
'AMOUNT' , NULL, p_lines.quantity_tbl(i), NULL)) ,
DECODE ( PO_AUTOCREATE_PARAMS.g_document_type,
'PA' , DECODE ( x_order_type_lookup_code ,
'QUANTITY', NULL, p_lines.committed_amount_tbl (i)), NULL) ,
DECODE(PO_AUTOCREATE_PARAMS.g_interface_source_code,'SOURCING',p_lines.price_break_lookup_code_tbl(i), NULL)
INTO p_lines.list_price_per_unit_tbl(i) ,
p_lines.market_price_tbl(i) ,
p_lines.quantity_tbl(i) ,
p_lines.tax_attribute_update_code_tbl(i) ,
p_lines.type_1099_tbl(i) ,
p_lines.un_number_id_tbl(i) ,
p_lines.hazard_class_id_tbl(i) ,
p_lines.contract_id_tbl(i) ,
p_lines.qty_rcv_tolerance_tbl(i) ,
p_lines.over_tolerance_err_flag_tbl(i) ,
p_lines.from_header_id_tbl(i) ,
p_lines.from_line_id_tbl(i) ,
p_lines.from_line_location_id_tbl(i) ,
p_lines.note_to_vendor_tbl(i) ,
p_lines.oke_contract_header_id_tbl(i) ,
p_lines.oke_contract_version_id_tbl(i) ,
p_lines.quantity_committed_tbl(i) ,
p_lines.committed_amount_tbl(i) ,
p_lines.price_break_lookup_code_tbl(i)
FROM dual;
p_token => l_progress, p_message => 'Before Insert into Lines Draft '
||' List Price : ' || p_lines.list_price_per_unit_tbl(i)
||' Market Price : ' || p_lines.market_price_tbl(i)
||' Unit Price : ' || p_lines.unit_price_tbl(i));
INSERT
INTO po_lines_draft_all
(
draft_id,
po_line_id,
last_update_date,
last_updated_by,
po_header_id,
line_num,
creation_date,
created_by,
last_update_login,
item_id,
job_id,
category_id,
item_description,
unit_meas_lookup_code,
list_price_per_unit,
market_price,
base_unit_price,
unit_price,
quantity,
amount,
taxable_flag,
type_1099,
negotiated_by_preparer_flag,
closed_code,
item_revision,
un_number_id,
hazard_class_id,
contract_id,
line_type_id,
vendor_product_num,
qty_rcv_tolerance,
over_tolerance_error_flag,
firm_status_lookup_code,
min_release_amount,
price_type_lookup_code,
transaction_reason_code,
from_header_id,
from_line_id,
from_line_location_id,
note_to_vendor,
oke_contract_header_id,
oke_contract_version_id,
secondary_unit_of_measure,
secondary_quantity,
preferred_grade,
auction_header_id,
auction_line_number,
auction_display_number,
bid_number,
bid_line_number,
quantity_committed,
committed_amount,
price_break_lookup_code,
supplier_ref_number,
org_id,
start_date,
expiration_date,
contractor_first_name,
contractor_last_name,
order_type_lookup_code,
purchase_basis,
matching_basis,
retainage_rate,
max_retainage_amount,
progress_payment_rate,
recoupment_rate,
tax_attribute_update_code,
ip_category_id,
supplier_part_auxid,
catalog_name,
line_num_display,
group_line_id,
clm_info_flag,
clm_option_indicator,
clm_option_num,
clm_option_from_date,
clm_option_to_date,
clm_funded_flag,
clm_base_line_num,
contract_type,
cost_constraint,
clm_idc_type,
clm_min_total_amount,
clm_max_total_amount,
clm_min_total_quantity,
clm_max_total_quantity,
clm_min_order_amount,
clm_max_order_amount,
clm_min_order_quantity,
clm_max_order_quantity,
clm_total_amount_ordered,
clm_total_quantity_ordered,
clm_exercised_flag, -- Bug 9960752
clm_exercised_date, -- Bug 9960752
change_status,
--CLM Phase4 Changes
clm_exhibit_name,
clm_delivery_event_code
)
VALUES
(
PO_AUTOCREATE_PARAMS.g_draft_id ,
p_lines.po_line_id_tbl(i) ,
P_lines.last_update_date_tbl(i) ,
p_lines.last_updated_by_tbl(i) ,
p_lines.po_header_id_tbl(i) ,
p_lines.line_num_tbl(i) ,
p_lines.creation_date_tbl(i) ,
p_lines.created_by_tbl(i) ,
p_lines.last_update_login_tbl(i) ,
p_lines.item_id_tbl(i) ,
p_lines.job_id_tbl(i) ,
p_lines.category_id_tbl(i) ,
p_lines.item_desc_tbl(i) ,
p_lines.unit_of_measure_tbl(i) ,
p_lines.list_price_per_unit_tbl(i) ,
p_lines.market_price_tbl(i) ,
p_lines.base_unit_price_tbl(i) ,
p_lines.unit_price_tbl(i) ,
p_lines.quantity_tbl(i) ,
p_lines.amount_tbl(i) ,
p_lines.taxable_flag_tbl(i) ,
p_lines.type_1099_tbl(i) ,
p_lines.negotiated_flag_tbl(i) ,
p_lines.closed_code_tbl(i) ,
p_lines.item_revision_tbl(i) ,
p_lines.un_number_id_tbl(i) ,
p_lines.hazard_class_id_tbl(i) ,
p_lines.contract_id_tbl(i) ,
p_lines.line_type_id_tbl(i) ,
p_lines.vendor_product_num_tbl(i) ,
p_lines.qty_rcv_tolerance_tbl(i) ,
p_lines.over_tolerance_err_flag_tbl(i) ,
p_lines.firm_status_lookup_code_tbl(i) ,
p_lines.min_release_amount_tbl(i) ,
p_lines.price_type_tbl(i) ,
p_lines.transaction_reason_code_tbl(i) ,
p_lines.from_header_id_tbl(i) ,
p_lines.from_line_id_tbl(i) ,
p_lines.from_line_location_id_tbl(i) ,
p_lines.note_to_vendor_tbl(i) ,
p_lines.oke_contract_header_id_tbl(i) ,
p_lines.oke_contract_version_id_tbl(i) ,
p_lines.secondary_unit_of_meas_tbl(i) ,
p_lines.secondary_quantity_tbl(i) ,
p_lines.preferred_grade_tbl(i) ,
p_lines.auction_header_id_tbl(i) ,
p_lines.auction_line_number_tbl(i) ,
p_lines.auction_display_number_tbl(i) ,
p_lines.bid_number_tbl(i) ,
p_lines.bid_line_number_tbl(i) ,
p_lines.quantity_committed_tbl(i) ,
p_lines.committed_amount_tbl(i) ,
p_lines.price_break_lookup_code_tbl(i) ,
p_lines.supplier_ref_number_tbl(i) ,
p_lines.org_id_tbl(i) --PO_AUTOCREATE_PARAMS.g_purchasing_ou_id
,
p_lines.effective_date_tbl(i) ,
p_lines.expiration_date_tbl(i) ,
p_lines.contractor_first_name_tbl(i) ,
p_lines.contractor_last_name_tbl(i) ,
p_lines.order_type_lookup_code_tbl(i) ,
p_lines.purchase_basis_tbl(i) ,
p_lines.matching_basis_tbl(i) ,
p_lines.retainage_rate_tbl(i) ,
p_lines.max_retainage_amount_tbl(i) ,
p_lines.progress_payment_rate_tbl(i) ,
p_lines.recoupment_rate_tbl(i) ,
p_lines.tax_attribute_update_code_tbl(i) ,
p_lines.ip_category_id_tbl(i) ,
p_lines.supplier_part_auxid_tbl(i) ,
p_lines.catalog_name_tbl(i) ,
p_lines.line_num_display_tbl(i) ,
p_lines.group_line_id_tbl(i) ,
p_lines.clm_info_flag_tbl(i) ,
p_lines.clm_option_indicator_tbl(i) ,
p_lines.clm_option_num_tbl(i) ,
p_lines.clm_option_from_date_tbl(i) ,
p_lines.clm_option_to_date_tbl(i) ,
p_lines.clm_funded_flag_tbl(i) ,
p_lines.clm_base_line_num_tbl(i) ,
p_lines.contract_type_tbl(i) ,
p_lines.cost_constraint_tbl(i) ,
p_lines.clm_idc_type_tbl(i) ,
p_lines.clm_min_total_amount_tbl(i) ,
p_lines.clm_max_total_amount_tbl(i) ,
p_lines.clm_min_total_quantity_tbl(i) ,
p_lines.clm_max_total_quantity_tbl(i) ,
p_lines.clm_min_order_amount_tbl(i) ,
p_lines.clm_max_order_amount_tbl(i) ,
p_lines.clm_min_order_quantity_tbl(i) ,
p_lines.clm_max_order_quantity_tbl(i) ,
p_lines.clm_total_amount_ordered_tbl(i) ,
p_lines.clm_total_quantity_ordered_tbl(i) ,
p_lines.clm_exercised_flag_tbl(i) -- Bug 9960752
,
p_lines.clm_exercised_date_tbl(i) -- Bug 9960752
,
'NEW',
--CLM Phase4 Changes
p_lines.clm_exhibit_name_tbl(i),
p_lines.clm_delivery_event_code_tbl(i)
);
) --Enhanced Pricing: Enable pricing call if pricing enhanced for the style selected
--
AND PO_AUTOCREATE_PARAMS.g_interface_source_code <> 'CONSUMPTION_ADVICE' THEN
l_progress := '540';
select pl.quantity
into l_db_quantity
from po_lines_draft_all pl --
where pl.po_line_id = x_po_line_id
AND pl.draft_id=po_autocreate_params.g_draft_id;
select poll.ship_to_location_id,
poll.ship_to_organization_id,
poll.need_by_date
into l_ship_to_loc,
l_ship_to_org,
l_need_by_date
from po_line_locations_draft_all poll
where poll.po_line_id = x_po_line_id
AND poll.draft_id= po_autocreate_params.g_draft_id
and poll.shipment_num = l_min_shipment_num;
UPDATE po_lines_draft_all
SET unit_price= nvl(x_unit_price, unit_price)
,base_unit_price = nvl(l_base_unit_price, base_unit_price)
,from_line_location_id = l_price_break_id
WHERE
draft_id = po_autocreate_params.g_draft_id
AND po_line_id = x_po_line_id;
UPDATE po_line_locations_draft_all
SET price_override = nvl(x_unit_price, price_override)
WHERE po_line_id = x_po_line_id
AND draft_id = po_autocreate_params.g_draft_id;
/** If FSP org and item combination is dual uom control, update the po lines secondary quantity
with the default conversion based on the PO lines quantity **/
-- update secondary quantity/uom to null
IF g_debug_stmt
THEN
PO_DEBUG.debug_stmt( p_log_head => g_log_head||l_api_name
,p_token => l_progress
,p_message => 'Before updating the Lines draft Quantity : '|| x_quantity );
UPDATE po_lines_draft_all plda
SET plda.quantity = Decode(plda.matching_basis,
'QUANTITY', (nvl(plda.quantity,0) + nvl(x_quantity,0)),
plda.quantity)
,plda.amount = Decode(plda.matching_basis,
-- : Added a missing comma to avoid compilation issues
'AMOUNT', (nvl(plda.amount,0) + nvl(p_lines.amount_tbl(i),0)),
plda.amount)
,plda.closed_code ='OPEN'
,plda.closed_date = NULL
,plda.closed_by = NULL
,plda.secondary_quantity = NULL
,plda.secondary_unit_of_measure = NULL
WHERE 1=1
AND plda.draft_id = po_autocreate_params.g_draft_id
AND plda.po_line_id = x_po_line_id
RETURNING quantity INTO l_quantity_temp;
SELECT unit_of_measure
INTO x_secondary_unit_def
FROM mtl_units_of_measure
WHERE uom_code = p_lines.it_secondary_uom_code_tbl(i) ;
UPDATE po_lines_draft_all
SET secondary_quantity = x_secondary_quantity_def,
secondary_unit_of_measure = x_secondary_unit_def
WHERE po_line_id = x_po_line_id
AND draft_id=po_autocreate_params.g_draft_id;
UPDATE po_Lines_draft_all
SET clm_exercised_flag = 'Y',
clm_exercised_date = SYSDATE,
change_status = 'UPDATE'
WHERE po_line_id = x_po_line_id
AND draft_id=po_autocreate_params.g_draft_id
AND clm_option_indicator = 'O'
AND Nvl(clm_exercised_flag,'N')='N';
END IF; --Release check for update
UPDATE po_lines_interface
SET po_line_id = p_lines.po_line_id_tbl(i)
WHERE
interface_line_id = p_lines.intf_line_id_tbl(i);
INSERT
INTO PO_PRICE_DIFF_DRAFT
(
draft_id ,
price_differential_id ,
price_differential_num ,
entity_type ,
entity_id ,
price_type ,
enabled_flag ,
min_multiplier ,
max_multiplier ,
multiplier ,
last_update_date ,
last_updated_by ,
last_update_login ,
creation_date ,
created_by
)
VALUES
(
PO_AUTOCREATE_PARAMS.g_draft_id ,
p_price_diff.intf_price_diff_id_tbl(i) ,
p_price_diff.price_diff_num_tbl(i) ,
p_price_diff.entity_type_tbl(i) ,
p_price_diff.entity_id_tbl(i) ,
p_price_diff.price_type_tbl(i) ,
p_price_diff.enabled_flag_tbl(i) ,
p_price_diff.min_multiplier_tbl(i) ,
p_price_diff.max_multiplier_tbl(i) ,
p_price_diff.multiplier_tbl(i) ,
SYSDATE ,
FND_GLOBAL.user_id ,
FND_GLOBAL.login_id ,
SYSDATE ,
FND_GLOBAL.user_id
);
Sourcing inserts records into these interface tables.
=========================================================================== */
PROCEDURE merge_to_attr_values_draft(p_lines IN OUT NOCOPY PO_AUTOCREATE_TYPES.lines_rec_type)
IS
l_progress VARCHAR2(3) := '000';
UPDATE PO_ATTR_VALUES_INTERFACE
SET po_line_id = p_lines.po_line_id_tbl(i),
req_template_name = TO_CHAR(g_ATTR_VALUES_NULL_ID),
req_template_line_num = TO_CHAR(g_ATTR_VALUES_NULL_ID),
inventory_item_id = NVL(inventory_item_id, g_ATTR_VALUES_NULL_ID)
WHERE po_attr_values_interface.interface_header_id = p_lines.intf_header_id_tbl(i)
AND po_attr_values_interface.interface_line_id = p_lines.intf_line_id_tbl(i);
PO_DEBUG.debug_stmt(p_log_head=>g_log_head||l_api_name,p_token=>l_progress,p_message=>'Number of PO_ATTR_VALUES_INTERFACE rows updated='||SQL% rowcount);
UPDATE PO_ATTR_VALUES_TLP_INTERFACE
SET po_line_id = p_lines.po_line_id_tbl(i),
req_template_name = TO_CHAR(g_ATTR_VALUES_NULL_ID),
req_template_line_num = TO_CHAR(g_ATTR_VALUES_NULL_ID),
inventory_item_id = NVL(inventory_item_id, g_ATTR_VALUES_NULL_ID)
WHERE po_attr_values_tlp_interface.interface_header_id = p_lines.intf_header_id_tbl(i)
AND po_attr_values_tlp_interface.interface_line_id = p_lines.intf_line_id_tbl(i);
p_message=>'Number of PO_ATTR_VALUES_TLP_INTERFACE rows updated='||SQL% rowcount);
INSERT
INTO PO_ATTRIBUTE_VALUES_DRAFT
(
draft_id,
ATTACHMENT_URL,
ATTRIBUTE_VALUES_ID,
AVAILABILITY,
CREATED_BY,
CREATION_DATE,
INVENTORY_ITEM_ID,
IP_CATEGORY_ID,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LEAD_TIME,
MANUFACTURER_PART_NUM,
MANUFACTURER_URL,
NUM_BASE_ATTRIBUTE1,
NUM_BASE_ATTRIBUTE10,
NUM_BASE_ATTRIBUTE100,
NUM_BASE_ATTRIBUTE11,
NUM_BASE_ATTRIBUTE12,
NUM_BASE_ATTRIBUTE13,
NUM_BASE_ATTRIBUTE14,
NUM_BASE_ATTRIBUTE15,
NUM_BASE_ATTRIBUTE16,
NUM_BASE_ATTRIBUTE17,
NUM_BASE_ATTRIBUTE18,
NUM_BASE_ATTRIBUTE19,
NUM_BASE_ATTRIBUTE2,
NUM_BASE_ATTRIBUTE20,
NUM_BASE_ATTRIBUTE21,
NUM_BASE_ATTRIBUTE22,
NUM_BASE_ATTRIBUTE23,
NUM_BASE_ATTRIBUTE24,
NUM_BASE_ATTRIBUTE25,
NUM_BASE_ATTRIBUTE26,
NUM_BASE_ATTRIBUTE27,
NUM_BASE_ATTRIBUTE28,
NUM_BASE_ATTRIBUTE29,
NUM_BASE_ATTRIBUTE3,
NUM_BASE_ATTRIBUTE30,
NUM_BASE_ATTRIBUTE31,
NUM_BASE_ATTRIBUTE32,
NUM_BASE_ATTRIBUTE33,
NUM_BASE_ATTRIBUTE34,
NUM_BASE_ATTRIBUTE35,
NUM_BASE_ATTRIBUTE36,
NUM_BASE_ATTRIBUTE37,
NUM_BASE_ATTRIBUTE38,
NUM_BASE_ATTRIBUTE39,
NUM_BASE_ATTRIBUTE4,
NUM_BASE_ATTRIBUTE40,
NUM_BASE_ATTRIBUTE41,
NUM_BASE_ATTRIBUTE42,
NUM_BASE_ATTRIBUTE43,
NUM_BASE_ATTRIBUTE44,
NUM_BASE_ATTRIBUTE45,
NUM_BASE_ATTRIBUTE46,
NUM_BASE_ATTRIBUTE47,
NUM_BASE_ATTRIBUTE48,
NUM_BASE_ATTRIBUTE49,
NUM_BASE_ATTRIBUTE5,
NUM_BASE_ATTRIBUTE50,
NUM_BASE_ATTRIBUTE51,
NUM_BASE_ATTRIBUTE52,
NUM_BASE_ATTRIBUTE53,
NUM_BASE_ATTRIBUTE54,
NUM_BASE_ATTRIBUTE55,
NUM_BASE_ATTRIBUTE56,
NUM_BASE_ATTRIBUTE57,
NUM_BASE_ATTRIBUTE58,
NUM_BASE_ATTRIBUTE59,
NUM_BASE_ATTRIBUTE6,
NUM_BASE_ATTRIBUTE60,
NUM_BASE_ATTRIBUTE61,
NUM_BASE_ATTRIBUTE62,
NUM_BASE_ATTRIBUTE63,
NUM_BASE_ATTRIBUTE64,
NUM_BASE_ATTRIBUTE65,
NUM_BASE_ATTRIBUTE66,
NUM_BASE_ATTRIBUTE67,
NUM_BASE_ATTRIBUTE68,
NUM_BASE_ATTRIBUTE69,
NUM_BASE_ATTRIBUTE7,
NUM_BASE_ATTRIBUTE70,
NUM_BASE_ATTRIBUTE71,
NUM_BASE_ATTRIBUTE72,
NUM_BASE_ATTRIBUTE73,
NUM_BASE_ATTRIBUTE74,
NUM_BASE_ATTRIBUTE75,
NUM_BASE_ATTRIBUTE76,
NUM_BASE_ATTRIBUTE77,
NUM_BASE_ATTRIBUTE78,
NUM_BASE_ATTRIBUTE79,
NUM_BASE_ATTRIBUTE8,
NUM_BASE_ATTRIBUTE80,
NUM_BASE_ATTRIBUTE81,
NUM_BASE_ATTRIBUTE82,
NUM_BASE_ATTRIBUTE83,
NUM_BASE_ATTRIBUTE84,
NUM_BASE_ATTRIBUTE85,
NUM_BASE_ATTRIBUTE86,
NUM_BASE_ATTRIBUTE87,
NUM_BASE_ATTRIBUTE88,
NUM_BASE_ATTRIBUTE89,
NUM_BASE_ATTRIBUTE9,
NUM_BASE_ATTRIBUTE90,
NUM_BASE_ATTRIBUTE91,
NUM_BASE_ATTRIBUTE92,
NUM_BASE_ATTRIBUTE93,
NUM_BASE_ATTRIBUTE94,
NUM_BASE_ATTRIBUTE95,
NUM_BASE_ATTRIBUTE96,
NUM_BASE_ATTRIBUTE97,
NUM_BASE_ATTRIBUTE98,
NUM_BASE_ATTRIBUTE99,
NUM_CAT_ATTRIBUTE1,
NUM_CAT_ATTRIBUTE10,
NUM_CAT_ATTRIBUTE11,
NUM_CAT_ATTRIBUTE12,
NUM_CAT_ATTRIBUTE13,
NUM_CAT_ATTRIBUTE14,
NUM_CAT_ATTRIBUTE15,
NUM_CAT_ATTRIBUTE16,
NUM_CAT_ATTRIBUTE17,
NUM_CAT_ATTRIBUTE18,
NUM_CAT_ATTRIBUTE19,
NUM_CAT_ATTRIBUTE2,
NUM_CAT_ATTRIBUTE20,
NUM_CAT_ATTRIBUTE21,
NUM_CAT_ATTRIBUTE22,
NUM_CAT_ATTRIBUTE23,
NUM_CAT_ATTRIBUTE24,
NUM_CAT_ATTRIBUTE25,
NUM_CAT_ATTRIBUTE26,
NUM_CAT_ATTRIBUTE27,
NUM_CAT_ATTRIBUTE28,
NUM_CAT_ATTRIBUTE29,
NUM_CAT_ATTRIBUTE3,
NUM_CAT_ATTRIBUTE30,
NUM_CAT_ATTRIBUTE31,
NUM_CAT_ATTRIBUTE32,
NUM_CAT_ATTRIBUTE33,
NUM_CAT_ATTRIBUTE34,
NUM_CAT_ATTRIBUTE35,
NUM_CAT_ATTRIBUTE36,
NUM_CAT_ATTRIBUTE37,
NUM_CAT_ATTRIBUTE38,
NUM_CAT_ATTRIBUTE39,
NUM_CAT_ATTRIBUTE4,
NUM_CAT_ATTRIBUTE40,
NUM_CAT_ATTRIBUTE41,
NUM_CAT_ATTRIBUTE42,
NUM_CAT_ATTRIBUTE43,
NUM_CAT_ATTRIBUTE44,
NUM_CAT_ATTRIBUTE45,
NUM_CAT_ATTRIBUTE46,
NUM_CAT_ATTRIBUTE47,
NUM_CAT_ATTRIBUTE48,
NUM_CAT_ATTRIBUTE49,
NUM_CAT_ATTRIBUTE5,
NUM_CAT_ATTRIBUTE50,
NUM_CAT_ATTRIBUTE6,
NUM_CAT_ATTRIBUTE7,
NUM_CAT_ATTRIBUTE8,
NUM_CAT_ATTRIBUTE9,
ORG_ID,
PICTURE,
PO_LINE_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
REQ_TEMPLATE_LINE_NUM,
REQ_TEMPLATE_NAME,
SUPPLIER_URL,
TEXT_BASE_ATTRIBUTE1,
TEXT_BASE_ATTRIBUTE10,
TEXT_BASE_ATTRIBUTE100,
TEXT_BASE_ATTRIBUTE11,
TEXT_BASE_ATTRIBUTE12,
TEXT_BASE_ATTRIBUTE13,
TEXT_BASE_ATTRIBUTE14,
TEXT_BASE_ATTRIBUTE15,
TEXT_BASE_ATTRIBUTE16,
TEXT_BASE_ATTRIBUTE17,
TEXT_BASE_ATTRIBUTE18,
TEXT_BASE_ATTRIBUTE19,
TEXT_BASE_ATTRIBUTE2,
TEXT_BASE_ATTRIBUTE20,
TEXT_BASE_ATTRIBUTE21,
TEXT_BASE_ATTRIBUTE22,
TEXT_BASE_ATTRIBUTE23,
TEXT_BASE_ATTRIBUTE24,
TEXT_BASE_ATTRIBUTE25,
TEXT_BASE_ATTRIBUTE26,
TEXT_BASE_ATTRIBUTE27,
TEXT_BASE_ATTRIBUTE28,
TEXT_BASE_ATTRIBUTE29,
TEXT_BASE_ATTRIBUTE3,
TEXT_BASE_ATTRIBUTE30,
TEXT_BASE_ATTRIBUTE31,
TEXT_BASE_ATTRIBUTE32,
TEXT_BASE_ATTRIBUTE33,
TEXT_BASE_ATTRIBUTE34,
TEXT_BASE_ATTRIBUTE35,
TEXT_BASE_ATTRIBUTE36,
TEXT_BASE_ATTRIBUTE37,
TEXT_BASE_ATTRIBUTE38,
TEXT_BASE_ATTRIBUTE39,
TEXT_BASE_ATTRIBUTE4,
TEXT_BASE_ATTRIBUTE40,
TEXT_BASE_ATTRIBUTE41,
TEXT_BASE_ATTRIBUTE42,
TEXT_BASE_ATTRIBUTE43,
TEXT_BASE_ATTRIBUTE44,
TEXT_BASE_ATTRIBUTE45,
TEXT_BASE_ATTRIBUTE46,
TEXT_BASE_ATTRIBUTE47,
TEXT_BASE_ATTRIBUTE48,
TEXT_BASE_ATTRIBUTE49,
TEXT_BASE_ATTRIBUTE5,
TEXT_BASE_ATTRIBUTE50,
TEXT_BASE_ATTRIBUTE51,
TEXT_BASE_ATTRIBUTE52,
TEXT_BASE_ATTRIBUTE53,
TEXT_BASE_ATTRIBUTE54,
TEXT_BASE_ATTRIBUTE55,
TEXT_BASE_ATTRIBUTE56,
TEXT_BASE_ATTRIBUTE57,
TEXT_BASE_ATTRIBUTE58,
TEXT_BASE_ATTRIBUTE59,
TEXT_BASE_ATTRIBUTE6,
TEXT_BASE_ATTRIBUTE60,
TEXT_BASE_ATTRIBUTE61,
TEXT_BASE_ATTRIBUTE62,
TEXT_BASE_ATTRIBUTE63,
TEXT_BASE_ATTRIBUTE64,
TEXT_BASE_ATTRIBUTE65,
TEXT_BASE_ATTRIBUTE66,
TEXT_BASE_ATTRIBUTE67,
TEXT_BASE_ATTRIBUTE68,
TEXT_BASE_ATTRIBUTE69,
TEXT_BASE_ATTRIBUTE7,
TEXT_BASE_ATTRIBUTE70,
TEXT_BASE_ATTRIBUTE71,
TEXT_BASE_ATTRIBUTE72,
TEXT_BASE_ATTRIBUTE73,
TEXT_BASE_ATTRIBUTE74,
TEXT_BASE_ATTRIBUTE75,
TEXT_BASE_ATTRIBUTE76,
TEXT_BASE_ATTRIBUTE77,
TEXT_BASE_ATTRIBUTE78,
TEXT_BASE_ATTRIBUTE79,
TEXT_BASE_ATTRIBUTE8,
TEXT_BASE_ATTRIBUTE80,
TEXT_BASE_ATTRIBUTE81,
TEXT_BASE_ATTRIBUTE82,
TEXT_BASE_ATTRIBUTE83,
TEXT_BASE_ATTRIBUTE84,
TEXT_BASE_ATTRIBUTE85,
TEXT_BASE_ATTRIBUTE86,
TEXT_BASE_ATTRIBUTE87,
TEXT_BASE_ATTRIBUTE88,
TEXT_BASE_ATTRIBUTE89,
TEXT_BASE_ATTRIBUTE9,
TEXT_BASE_ATTRIBUTE90,
TEXT_BASE_ATTRIBUTE91,
TEXT_BASE_ATTRIBUTE92,
TEXT_BASE_ATTRIBUTE93,
TEXT_BASE_ATTRIBUTE94,
TEXT_BASE_ATTRIBUTE95,
TEXT_BASE_ATTRIBUTE96,
TEXT_BASE_ATTRIBUTE97,
TEXT_BASE_ATTRIBUTE98,
TEXT_BASE_ATTRIBUTE99,
TEXT_CAT_ATTRIBUTE1,
TEXT_CAT_ATTRIBUTE10,
TEXT_CAT_ATTRIBUTE11,
TEXT_CAT_ATTRIBUTE12,
TEXT_CAT_ATTRIBUTE13,
TEXT_CAT_ATTRIBUTE14,
TEXT_CAT_ATTRIBUTE15,
TEXT_CAT_ATTRIBUTE16,
TEXT_CAT_ATTRIBUTE17,
TEXT_CAT_ATTRIBUTE18,
TEXT_CAT_ATTRIBUTE19,
TEXT_CAT_ATTRIBUTE2,
TEXT_CAT_ATTRIBUTE20,
TEXT_CAT_ATTRIBUTE21,
TEXT_CAT_ATTRIBUTE22,
TEXT_CAT_ATTRIBUTE23,
TEXT_CAT_ATTRIBUTE24,
TEXT_CAT_ATTRIBUTE25,
TEXT_CAT_ATTRIBUTE26,
TEXT_CAT_ATTRIBUTE27,
TEXT_CAT_ATTRIBUTE28,
TEXT_CAT_ATTRIBUTE29,
TEXT_CAT_ATTRIBUTE3,
TEXT_CAT_ATTRIBUTE30,
TEXT_CAT_ATTRIBUTE31,
TEXT_CAT_ATTRIBUTE32,
TEXT_CAT_ATTRIBUTE33,
TEXT_CAT_ATTRIBUTE34,
TEXT_CAT_ATTRIBUTE35,
TEXT_CAT_ATTRIBUTE36,
TEXT_CAT_ATTRIBUTE37,
TEXT_CAT_ATTRIBUTE38,
TEXT_CAT_ATTRIBUTE39,
TEXT_CAT_ATTRIBUTE4,
TEXT_CAT_ATTRIBUTE40,
TEXT_CAT_ATTRIBUTE41,
TEXT_CAT_ATTRIBUTE42,
TEXT_CAT_ATTRIBUTE43,
TEXT_CAT_ATTRIBUTE44,
TEXT_CAT_ATTRIBUTE45,
TEXT_CAT_ATTRIBUTE46,
TEXT_CAT_ATTRIBUTE47,
TEXT_CAT_ATTRIBUTE48,
TEXT_CAT_ATTRIBUTE49,
TEXT_CAT_ATTRIBUTE5,
TEXT_CAT_ATTRIBUTE50,
TEXT_CAT_ATTRIBUTE6,
TEXT_CAT_ATTRIBUTE7,
TEXT_CAT_ATTRIBUTE8,
TEXT_CAT_ATTRIBUTE9,
THUMBNAIL_IMAGE,
UNSPSC,
LAST_UPDATED_PROGRAM
)
SELECT PO_AUTOCREATE_PARAMS.g_draft_id,
ATTACHMENT_URL,
PO_ATTRIBUTE_VALUES_S.nextval,
AVAILABILITY,
CREATED_BY,
CREATION_DATE,
INVENTORY_ITEM_ID,
IP_CATEGORY_ID,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LEAD_TIME,
MANUFACTURER_PART_NUM,
MANUFACTURER_URL,
NUM_BASE_ATTRIBUTE1,
NUM_BASE_ATTRIBUTE10,
NUM_BASE_ATTRIBUTE100,
NUM_BASE_ATTRIBUTE11,
NUM_BASE_ATTRIBUTE12,
NUM_BASE_ATTRIBUTE13,
NUM_BASE_ATTRIBUTE14,
NUM_BASE_ATTRIBUTE15,
NUM_BASE_ATTRIBUTE16,
NUM_BASE_ATTRIBUTE17,
NUM_BASE_ATTRIBUTE18,
NUM_BASE_ATTRIBUTE19,
NUM_BASE_ATTRIBUTE2,
NUM_BASE_ATTRIBUTE20,
NUM_BASE_ATTRIBUTE21,
NUM_BASE_ATTRIBUTE22,
NUM_BASE_ATTRIBUTE23,
NUM_BASE_ATTRIBUTE24,
NUM_BASE_ATTRIBUTE25,
NUM_BASE_ATTRIBUTE26,
NUM_BASE_ATTRIBUTE27,
NUM_BASE_ATTRIBUTE28,
NUM_BASE_ATTRIBUTE29,
NUM_BASE_ATTRIBUTE3,
NUM_BASE_ATTRIBUTE30,
NUM_BASE_ATTRIBUTE31,
NUM_BASE_ATTRIBUTE32,
NUM_BASE_ATTRIBUTE33,
NUM_BASE_ATTRIBUTE34,
NUM_BASE_ATTRIBUTE35,
NUM_BASE_ATTRIBUTE36,
NUM_BASE_ATTRIBUTE37,
NUM_BASE_ATTRIBUTE38,
NUM_BASE_ATTRIBUTE39,
NUM_BASE_ATTRIBUTE4,
NUM_BASE_ATTRIBUTE40,
NUM_BASE_ATTRIBUTE41,
NUM_BASE_ATTRIBUTE42,
NUM_BASE_ATTRIBUTE43,
NUM_BASE_ATTRIBUTE44,
NUM_BASE_ATTRIBUTE45,
NUM_BASE_ATTRIBUTE46,
NUM_BASE_ATTRIBUTE47,
NUM_BASE_ATTRIBUTE48,
NUM_BASE_ATTRIBUTE49,
NUM_BASE_ATTRIBUTE5,
NUM_BASE_ATTRIBUTE50,
NUM_BASE_ATTRIBUTE51,
NUM_BASE_ATTRIBUTE52,
NUM_BASE_ATTRIBUTE53,
NUM_BASE_ATTRIBUTE54,
NUM_BASE_ATTRIBUTE55,
NUM_BASE_ATTRIBUTE56,
NUM_BASE_ATTRIBUTE57,
NUM_BASE_ATTRIBUTE58,
NUM_BASE_ATTRIBUTE59,
NUM_BASE_ATTRIBUTE6,
NUM_BASE_ATTRIBUTE60,
NUM_BASE_ATTRIBUTE61,
NUM_BASE_ATTRIBUTE62,
NUM_BASE_ATTRIBUTE63,
NUM_BASE_ATTRIBUTE64,
NUM_BASE_ATTRIBUTE65,
NUM_BASE_ATTRIBUTE66,
NUM_BASE_ATTRIBUTE67,
NUM_BASE_ATTRIBUTE68,
NUM_BASE_ATTRIBUTE69,
NUM_BASE_ATTRIBUTE7,
NUM_BASE_ATTRIBUTE70,
NUM_BASE_ATTRIBUTE71,
NUM_BASE_ATTRIBUTE72,
NUM_BASE_ATTRIBUTE73,
NUM_BASE_ATTRIBUTE74,
NUM_BASE_ATTRIBUTE75,
NUM_BASE_ATTRIBUTE76,
NUM_BASE_ATTRIBUTE77,
NUM_BASE_ATTRIBUTE78,
NUM_BASE_ATTRIBUTE79,
NUM_BASE_ATTRIBUTE8,
NUM_BASE_ATTRIBUTE80,
NUM_BASE_ATTRIBUTE81,
NUM_BASE_ATTRIBUTE82,
NUM_BASE_ATTRIBUTE83,
NUM_BASE_ATTRIBUTE84,
NUM_BASE_ATTRIBUTE85,
NUM_BASE_ATTRIBUTE86,
NUM_BASE_ATTRIBUTE87,
NUM_BASE_ATTRIBUTE88,
NUM_BASE_ATTRIBUTE89,
NUM_BASE_ATTRIBUTE9,
NUM_BASE_ATTRIBUTE90,
NUM_BASE_ATTRIBUTE91,
NUM_BASE_ATTRIBUTE92,
NUM_BASE_ATTRIBUTE93,
NUM_BASE_ATTRIBUTE94,
NUM_BASE_ATTRIBUTE95,
NUM_BASE_ATTRIBUTE96,
NUM_BASE_ATTRIBUTE97,
NUM_BASE_ATTRIBUTE98,
NUM_BASE_ATTRIBUTE99,
NUM_CAT_ATTRIBUTE1,
NUM_CAT_ATTRIBUTE10,
NUM_CAT_ATTRIBUTE11,
NUM_CAT_ATTRIBUTE12,
NUM_CAT_ATTRIBUTE13,
NUM_CAT_ATTRIBUTE14,
NUM_CAT_ATTRIBUTE15,
NUM_CAT_ATTRIBUTE16,
NUM_CAT_ATTRIBUTE17,
NUM_CAT_ATTRIBUTE18,
NUM_CAT_ATTRIBUTE19,
NUM_CAT_ATTRIBUTE2,
NUM_CAT_ATTRIBUTE20,
NUM_CAT_ATTRIBUTE21,
NUM_CAT_ATTRIBUTE22,
NUM_CAT_ATTRIBUTE23,
NUM_CAT_ATTRIBUTE24,
NUM_CAT_ATTRIBUTE25,
NUM_CAT_ATTRIBUTE26,
NUM_CAT_ATTRIBUTE27,
NUM_CAT_ATTRIBUTE28,
NUM_CAT_ATTRIBUTE29,
NUM_CAT_ATTRIBUTE3,
NUM_CAT_ATTRIBUTE30,
NUM_CAT_ATTRIBUTE31,
NUM_CAT_ATTRIBUTE32,
NUM_CAT_ATTRIBUTE33,
NUM_CAT_ATTRIBUTE34,
NUM_CAT_ATTRIBUTE35,
NUM_CAT_ATTRIBUTE36,
NUM_CAT_ATTRIBUTE37,
NUM_CAT_ATTRIBUTE38,
NUM_CAT_ATTRIBUTE39,
NUM_CAT_ATTRIBUTE4,
NUM_CAT_ATTRIBUTE40,
NUM_CAT_ATTRIBUTE41,
NUM_CAT_ATTRIBUTE42,
NUM_CAT_ATTRIBUTE43,
NUM_CAT_ATTRIBUTE44,
NUM_CAT_ATTRIBUTE45,
NUM_CAT_ATTRIBUTE46,
NUM_CAT_ATTRIBUTE47,
NUM_CAT_ATTRIBUTE48,
NUM_CAT_ATTRIBUTE49,
NUM_CAT_ATTRIBUTE5,
NUM_CAT_ATTRIBUTE50,
NUM_CAT_ATTRIBUTE6,
NUM_CAT_ATTRIBUTE7,
NUM_CAT_ATTRIBUTE8,
NUM_CAT_ATTRIBUTE9,
ORG_ID,
PICTURE,
PO_LINE_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
REQ_TEMPLATE_LINE_NUM,
REQ_TEMPLATE_NAME,
SUPPLIER_URL,
TEXT_BASE_ATTRIBUTE1,
TEXT_BASE_ATTRIBUTE10,
TEXT_BASE_ATTRIBUTE100,
TEXT_BASE_ATTRIBUTE11,
TEXT_BASE_ATTRIBUTE12,
TEXT_BASE_ATTRIBUTE13,
TEXT_BASE_ATTRIBUTE14,
TEXT_BASE_ATTRIBUTE15,
TEXT_BASE_ATTRIBUTE16,
TEXT_BASE_ATTRIBUTE17,
TEXT_BASE_ATTRIBUTE18,
TEXT_BASE_ATTRIBUTE19,
TEXT_BASE_ATTRIBUTE2,
TEXT_BASE_ATTRIBUTE20,
TEXT_BASE_ATTRIBUTE21,
TEXT_BASE_ATTRIBUTE22,
TEXT_BASE_ATTRIBUTE23,
TEXT_BASE_ATTRIBUTE24,
TEXT_BASE_ATTRIBUTE25,
TEXT_BASE_ATTRIBUTE26,
TEXT_BASE_ATTRIBUTE27,
TEXT_BASE_ATTRIBUTE28,
TEXT_BASE_ATTRIBUTE29,
TEXT_BASE_ATTRIBUTE3,
TEXT_BASE_ATTRIBUTE30,
TEXT_BASE_ATTRIBUTE31,
TEXT_BASE_ATTRIBUTE32,
TEXT_BASE_ATTRIBUTE33,
TEXT_BASE_ATTRIBUTE34,
TEXT_BASE_ATTRIBUTE35,
TEXT_BASE_ATTRIBUTE36,
TEXT_BASE_ATTRIBUTE37,
TEXT_BASE_ATTRIBUTE38,
TEXT_BASE_ATTRIBUTE39,
TEXT_BASE_ATTRIBUTE4,
TEXT_BASE_ATTRIBUTE40,
TEXT_BASE_ATTRIBUTE41,
TEXT_BASE_ATTRIBUTE42,
TEXT_BASE_ATTRIBUTE43,
TEXT_BASE_ATTRIBUTE44,
TEXT_BASE_ATTRIBUTE45,
TEXT_BASE_ATTRIBUTE46,
TEXT_BASE_ATTRIBUTE47,
TEXT_BASE_ATTRIBUTE48,
TEXT_BASE_ATTRIBUTE49,
TEXT_BASE_ATTRIBUTE5,
TEXT_BASE_ATTRIBUTE50,
TEXT_BASE_ATTRIBUTE51,
TEXT_BASE_ATTRIBUTE52,
TEXT_BASE_ATTRIBUTE53,
TEXT_BASE_ATTRIBUTE54,
TEXT_BASE_ATTRIBUTE55,
TEXT_BASE_ATTRIBUTE56,
TEXT_BASE_ATTRIBUTE57,
TEXT_BASE_ATTRIBUTE58,
TEXT_BASE_ATTRIBUTE59,
TEXT_BASE_ATTRIBUTE6,
TEXT_BASE_ATTRIBUTE60,
TEXT_BASE_ATTRIBUTE61,
TEXT_BASE_ATTRIBUTE62,
TEXT_BASE_ATTRIBUTE63,
TEXT_BASE_ATTRIBUTE64,
TEXT_BASE_ATTRIBUTE65,
TEXT_BASE_ATTRIBUTE66,
TEXT_BASE_ATTRIBUTE67,
TEXT_BASE_ATTRIBUTE68,
TEXT_BASE_ATTRIBUTE69,
TEXT_BASE_ATTRIBUTE7,
TEXT_BASE_ATTRIBUTE70,
TEXT_BASE_ATTRIBUTE71,
TEXT_BASE_ATTRIBUTE72,
TEXT_BASE_ATTRIBUTE73,
TEXT_BASE_ATTRIBUTE74,
TEXT_BASE_ATTRIBUTE75,
TEXT_BASE_ATTRIBUTE76,
TEXT_BASE_ATTRIBUTE77,
TEXT_BASE_ATTRIBUTE78,
TEXT_BASE_ATTRIBUTE79,
TEXT_BASE_ATTRIBUTE8,
TEXT_BASE_ATTRIBUTE80,
TEXT_BASE_ATTRIBUTE81,
TEXT_BASE_ATTRIBUTE82,
TEXT_BASE_ATTRIBUTE83,
TEXT_BASE_ATTRIBUTE84,
TEXT_BASE_ATTRIBUTE85,
TEXT_BASE_ATTRIBUTE86,
TEXT_BASE_ATTRIBUTE87,
TEXT_BASE_ATTRIBUTE88,
TEXT_BASE_ATTRIBUTE89,
TEXT_BASE_ATTRIBUTE9,
TEXT_BASE_ATTRIBUTE90,
TEXT_BASE_ATTRIBUTE91,
TEXT_BASE_ATTRIBUTE92,
TEXT_BASE_ATTRIBUTE93,
TEXT_BASE_ATTRIBUTE94,
TEXT_BASE_ATTRIBUTE95,
TEXT_BASE_ATTRIBUTE96,
TEXT_BASE_ATTRIBUTE97,
TEXT_BASE_ATTRIBUTE98,
TEXT_BASE_ATTRIBUTE99,
TEXT_CAT_ATTRIBUTE1,
TEXT_CAT_ATTRIBUTE10,
TEXT_CAT_ATTRIBUTE11,
TEXT_CAT_ATTRIBUTE12,
TEXT_CAT_ATTRIBUTE13,
TEXT_CAT_ATTRIBUTE14,
TEXT_CAT_ATTRIBUTE15,
TEXT_CAT_ATTRIBUTE16,
TEXT_CAT_ATTRIBUTE17,
TEXT_CAT_ATTRIBUTE18,
TEXT_CAT_ATTRIBUTE19,
TEXT_CAT_ATTRIBUTE2,
TEXT_CAT_ATTRIBUTE20,
TEXT_CAT_ATTRIBUTE21,
TEXT_CAT_ATTRIBUTE22,
TEXT_CAT_ATTRIBUTE23,
TEXT_CAT_ATTRIBUTE24,
TEXT_CAT_ATTRIBUTE25,
TEXT_CAT_ATTRIBUTE26,
TEXT_CAT_ATTRIBUTE27,
TEXT_CAT_ATTRIBUTE28,
TEXT_CAT_ATTRIBUTE29,
TEXT_CAT_ATTRIBUTE3,
TEXT_CAT_ATTRIBUTE30,
TEXT_CAT_ATTRIBUTE31,
TEXT_CAT_ATTRIBUTE32,
TEXT_CAT_ATTRIBUTE33,
TEXT_CAT_ATTRIBUTE34,
TEXT_CAT_ATTRIBUTE35,
TEXT_CAT_ATTRIBUTE36,
TEXT_CAT_ATTRIBUTE37,
TEXT_CAT_ATTRIBUTE38,
TEXT_CAT_ATTRIBUTE39,
TEXT_CAT_ATTRIBUTE4,
TEXT_CAT_ATTRIBUTE40,
TEXT_CAT_ATTRIBUTE41,
TEXT_CAT_ATTRIBUTE42,
TEXT_CAT_ATTRIBUTE43,
TEXT_CAT_ATTRIBUTE44,
TEXT_CAT_ATTRIBUTE45,
TEXT_CAT_ATTRIBUTE46,
TEXT_CAT_ATTRIBUTE47,
TEXT_CAT_ATTRIBUTE48,
TEXT_CAT_ATTRIBUTE49,
TEXT_CAT_ATTRIBUTE5,
TEXT_CAT_ATTRIBUTE50,
TEXT_CAT_ATTRIBUTE6,
TEXT_CAT_ATTRIBUTE7,
TEXT_CAT_ATTRIBUTE8,
TEXT_CAT_ATTRIBUTE9,
THUMBNAIL_IMAGE,
UNSPSC,
'AUTOCREATE_BACKEND_FOR_SOURCING'
FROM PO_ATTR_VALUES_INTERFACE
WHERE interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id;
PO_DEBUG.debug_stmt(p_log_head=>g_log_head||l_api_name ,p_token=>l_progress ,p_message=>'Number of rows inserted in PO_ATTRIBUTE_VALUES table='||SQL%ROWCOUNT );
INSERT
INTO PO_ATTRIBUTE_VALUES_TLP_DRAFT
(
draft_id,
ALIAS,
ATTRIBUTE_VALUES_TLP_ID,
COMMENTS,
CREATED_BY,
CREATION_DATE,
DESCRIPTION,
INVENTORY_ITEM_ID,
IP_CATEGORY_ID,
LANGUAGE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LONG_DESCRIPTION,
MANUFACTURER,
ORG_ID,
PO_LINE_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
REQ_TEMPLATE_LINE_NUM,
REQ_TEMPLATE_NAME,
TL_TEXT_BASE_ATTRIBUTE1,
TL_TEXT_BASE_ATTRIBUTE10,
TL_TEXT_BASE_ATTRIBUTE100,
TL_TEXT_BASE_ATTRIBUTE11,
TL_TEXT_BASE_ATTRIBUTE12,
TL_TEXT_BASE_ATTRIBUTE13,
TL_TEXT_BASE_ATTRIBUTE14,
TL_TEXT_BASE_ATTRIBUTE15,
TL_TEXT_BASE_ATTRIBUTE16,
TL_TEXT_BASE_ATTRIBUTE17,
TL_TEXT_BASE_ATTRIBUTE18,
TL_TEXT_BASE_ATTRIBUTE19,
TL_TEXT_BASE_ATTRIBUTE2,
TL_TEXT_BASE_ATTRIBUTE20,
TL_TEXT_BASE_ATTRIBUTE21,
TL_TEXT_BASE_ATTRIBUTE22,
TL_TEXT_BASE_ATTRIBUTE23,
TL_TEXT_BASE_ATTRIBUTE24,
TL_TEXT_BASE_ATTRIBUTE25,
TL_TEXT_BASE_ATTRIBUTE26,
TL_TEXT_BASE_ATTRIBUTE27,
TL_TEXT_BASE_ATTRIBUTE28,
TL_TEXT_BASE_ATTRIBUTE29,
TL_TEXT_BASE_ATTRIBUTE3,
TL_TEXT_BASE_ATTRIBUTE30,
TL_TEXT_BASE_ATTRIBUTE31,
TL_TEXT_BASE_ATTRIBUTE32,
TL_TEXT_BASE_ATTRIBUTE33,
TL_TEXT_BASE_ATTRIBUTE34,
TL_TEXT_BASE_ATTRIBUTE35,
TL_TEXT_BASE_ATTRIBUTE36,
TL_TEXT_BASE_ATTRIBUTE37,
TL_TEXT_BASE_ATTRIBUTE38,
TL_TEXT_BASE_ATTRIBUTE39,
TL_TEXT_BASE_ATTRIBUTE4,
TL_TEXT_BASE_ATTRIBUTE40,
TL_TEXT_BASE_ATTRIBUTE41,
TL_TEXT_BASE_ATTRIBUTE42,
TL_TEXT_BASE_ATTRIBUTE43,
TL_TEXT_BASE_ATTRIBUTE44,
TL_TEXT_BASE_ATTRIBUTE45,
TL_TEXT_BASE_ATTRIBUTE46,
TL_TEXT_BASE_ATTRIBUTE47,
TL_TEXT_BASE_ATTRIBUTE48,
TL_TEXT_BASE_ATTRIBUTE49,
TL_TEXT_BASE_ATTRIBUTE5,
TL_TEXT_BASE_ATTRIBUTE50,
TL_TEXT_BASE_ATTRIBUTE51,
TL_TEXT_BASE_ATTRIBUTE52,
TL_TEXT_BASE_ATTRIBUTE53,
TL_TEXT_BASE_ATTRIBUTE54,
TL_TEXT_BASE_ATTRIBUTE55,
TL_TEXT_BASE_ATTRIBUTE56,
TL_TEXT_BASE_ATTRIBUTE57,
TL_TEXT_BASE_ATTRIBUTE58,
TL_TEXT_BASE_ATTRIBUTE59,
TL_TEXT_BASE_ATTRIBUTE6,
TL_TEXT_BASE_ATTRIBUTE60,
TL_TEXT_BASE_ATTRIBUTE61,
TL_TEXT_BASE_ATTRIBUTE62,
TL_TEXT_BASE_ATTRIBUTE63,
TL_TEXT_BASE_ATTRIBUTE64,
TL_TEXT_BASE_ATTRIBUTE65,
TL_TEXT_BASE_ATTRIBUTE66,
TL_TEXT_BASE_ATTRIBUTE67,
TL_TEXT_BASE_ATTRIBUTE68,
TL_TEXT_BASE_ATTRIBUTE69,
TL_TEXT_BASE_ATTRIBUTE7,
TL_TEXT_BASE_ATTRIBUTE70,
TL_TEXT_BASE_ATTRIBUTE71,
TL_TEXT_BASE_ATTRIBUTE72,
TL_TEXT_BASE_ATTRIBUTE73,
TL_TEXT_BASE_ATTRIBUTE74,
TL_TEXT_BASE_ATTRIBUTE75,
TL_TEXT_BASE_ATTRIBUTE76,
TL_TEXT_BASE_ATTRIBUTE77,
TL_TEXT_BASE_ATTRIBUTE78,
TL_TEXT_BASE_ATTRIBUTE79,
TL_TEXT_BASE_ATTRIBUTE8,
TL_TEXT_BASE_ATTRIBUTE80,
TL_TEXT_BASE_ATTRIBUTE81,
TL_TEXT_BASE_ATTRIBUTE82,
TL_TEXT_BASE_ATTRIBUTE83,
TL_TEXT_BASE_ATTRIBUTE84,
TL_TEXT_BASE_ATTRIBUTE85,
TL_TEXT_BASE_ATTRIBUTE86,
TL_TEXT_BASE_ATTRIBUTE87,
TL_TEXT_BASE_ATTRIBUTE88,
TL_TEXT_BASE_ATTRIBUTE89,
TL_TEXT_BASE_ATTRIBUTE9,
TL_TEXT_BASE_ATTRIBUTE90,
TL_TEXT_BASE_ATTRIBUTE91,
TL_TEXT_BASE_ATTRIBUTE92,
TL_TEXT_BASE_ATTRIBUTE93,
TL_TEXT_BASE_ATTRIBUTE94,
TL_TEXT_BASE_ATTRIBUTE95,
TL_TEXT_BASE_ATTRIBUTE96,
TL_TEXT_BASE_ATTRIBUTE97,
TL_TEXT_BASE_ATTRIBUTE98,
TL_TEXT_BASE_ATTRIBUTE99,
TL_TEXT_CAT_ATTRIBUTE1,
TL_TEXT_CAT_ATTRIBUTE10,
TL_TEXT_CAT_ATTRIBUTE11,
TL_TEXT_CAT_ATTRIBUTE12,
TL_TEXT_CAT_ATTRIBUTE13,
TL_TEXT_CAT_ATTRIBUTE14,
TL_TEXT_CAT_ATTRIBUTE15,
TL_TEXT_CAT_ATTRIBUTE16,
TL_TEXT_CAT_ATTRIBUTE17,
TL_TEXT_CAT_ATTRIBUTE18,
TL_TEXT_CAT_ATTRIBUTE19,
TL_TEXT_CAT_ATTRIBUTE2,
TL_TEXT_CAT_ATTRIBUTE20,
TL_TEXT_CAT_ATTRIBUTE21,
TL_TEXT_CAT_ATTRIBUTE22,
TL_TEXT_CAT_ATTRIBUTE23,
TL_TEXT_CAT_ATTRIBUTE24,
TL_TEXT_CAT_ATTRIBUTE25,
TL_TEXT_CAT_ATTRIBUTE26,
TL_TEXT_CAT_ATTRIBUTE27,
TL_TEXT_CAT_ATTRIBUTE28,
TL_TEXT_CAT_ATTRIBUTE29,
TL_TEXT_CAT_ATTRIBUTE3,
TL_TEXT_CAT_ATTRIBUTE30,
TL_TEXT_CAT_ATTRIBUTE31,
TL_TEXT_CAT_ATTRIBUTE32,
TL_TEXT_CAT_ATTRIBUTE33,
TL_TEXT_CAT_ATTRIBUTE34,
TL_TEXT_CAT_ATTRIBUTE35,
TL_TEXT_CAT_ATTRIBUTE36,
TL_TEXT_CAT_ATTRIBUTE37,
TL_TEXT_CAT_ATTRIBUTE38,
TL_TEXT_CAT_ATTRIBUTE39,
TL_TEXT_CAT_ATTRIBUTE4,
TL_TEXT_CAT_ATTRIBUTE40,
TL_TEXT_CAT_ATTRIBUTE41,
TL_TEXT_CAT_ATTRIBUTE42,
TL_TEXT_CAT_ATTRIBUTE43,
TL_TEXT_CAT_ATTRIBUTE44,
TL_TEXT_CAT_ATTRIBUTE45,
TL_TEXT_CAT_ATTRIBUTE46,
TL_TEXT_CAT_ATTRIBUTE47,
TL_TEXT_CAT_ATTRIBUTE48,
TL_TEXT_CAT_ATTRIBUTE49,
TL_TEXT_CAT_ATTRIBUTE5,
TL_TEXT_CAT_ATTRIBUTE50,
TL_TEXT_CAT_ATTRIBUTE6,
TL_TEXT_CAT_ATTRIBUTE7,
TL_TEXT_CAT_ATTRIBUTE8,
TL_TEXT_CAT_ATTRIBUTE9,
LAST_UPDATED_PROGRAM
)
SELECT PO_AUTOCREATE_PARAMS.g_draft_id,
ALIAS,
PO_ATTRIBUTE_VALUES_TLP_S.nextval,
COMMENTS,
CREATED_BY,
CREATION_DATE,
DESCRIPTION,
INVENTORY_ITEM_ID,
IP_CATEGORY_ID,
LANGUAGE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LONG_DESCRIPTION,
MANUFACTURER,
ORG_ID,
PO_LINE_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
REQ_TEMPLATE_LINE_NUM,
REQ_TEMPLATE_NAME,
TL_TEXT_BASE_ATTRIBUTE1,
TL_TEXT_BASE_ATTRIBUTE10,
TL_TEXT_BASE_ATTRIBUTE100,
TL_TEXT_BASE_ATTRIBUTE11,
TL_TEXT_BASE_ATTRIBUTE12,
TL_TEXT_BASE_ATTRIBUTE13,
TL_TEXT_BASE_ATTRIBUTE14,
TL_TEXT_BASE_ATTRIBUTE15,
TL_TEXT_BASE_ATTRIBUTE16,
TL_TEXT_BASE_ATTRIBUTE17,
TL_TEXT_BASE_ATTRIBUTE18,
TL_TEXT_BASE_ATTRIBUTE19,
TL_TEXT_BASE_ATTRIBUTE2,
TL_TEXT_BASE_ATTRIBUTE20,
TL_TEXT_BASE_ATTRIBUTE21,
TL_TEXT_BASE_ATTRIBUTE22,
TL_TEXT_BASE_ATTRIBUTE23,
TL_TEXT_BASE_ATTRIBUTE24,
TL_TEXT_BASE_ATTRIBUTE25,
TL_TEXT_BASE_ATTRIBUTE26,
TL_TEXT_BASE_ATTRIBUTE27,
TL_TEXT_BASE_ATTRIBUTE28,
TL_TEXT_BASE_ATTRIBUTE29,
TL_TEXT_BASE_ATTRIBUTE3,
TL_TEXT_BASE_ATTRIBUTE30,
TL_TEXT_BASE_ATTRIBUTE31,
TL_TEXT_BASE_ATTRIBUTE32,
TL_TEXT_BASE_ATTRIBUTE33,
TL_TEXT_BASE_ATTRIBUTE34,
TL_TEXT_BASE_ATTRIBUTE35,
TL_TEXT_BASE_ATTRIBUTE36,
TL_TEXT_BASE_ATTRIBUTE37,
TL_TEXT_BASE_ATTRIBUTE38,
TL_TEXT_BASE_ATTRIBUTE39,
TL_TEXT_BASE_ATTRIBUTE4,
TL_TEXT_BASE_ATTRIBUTE40,
TL_TEXT_BASE_ATTRIBUTE41,
TL_TEXT_BASE_ATTRIBUTE42,
TL_TEXT_BASE_ATTRIBUTE43,
TL_TEXT_BASE_ATTRIBUTE44,
TL_TEXT_BASE_ATTRIBUTE45,
TL_TEXT_BASE_ATTRIBUTE46,
TL_TEXT_BASE_ATTRIBUTE47,
TL_TEXT_BASE_ATTRIBUTE48,
TL_TEXT_BASE_ATTRIBUTE49,
TL_TEXT_BASE_ATTRIBUTE5,
TL_TEXT_BASE_ATTRIBUTE50,
TL_TEXT_BASE_ATTRIBUTE51,
TL_TEXT_BASE_ATTRIBUTE52,
TL_TEXT_BASE_ATTRIBUTE53,
TL_TEXT_BASE_ATTRIBUTE54,
TL_TEXT_BASE_ATTRIBUTE55,
TL_TEXT_BASE_ATTRIBUTE56,
TL_TEXT_BASE_ATTRIBUTE57,
TL_TEXT_BASE_ATTRIBUTE58,
TL_TEXT_BASE_ATTRIBUTE59,
TL_TEXT_BASE_ATTRIBUTE6,
TL_TEXT_BASE_ATTRIBUTE60,
TL_TEXT_BASE_ATTRIBUTE61,
TL_TEXT_BASE_ATTRIBUTE62,
TL_TEXT_BASE_ATTRIBUTE63,
TL_TEXT_BASE_ATTRIBUTE64,
TL_TEXT_BASE_ATTRIBUTE65,
TL_TEXT_BASE_ATTRIBUTE66,
TL_TEXT_BASE_ATTRIBUTE67,
TL_TEXT_BASE_ATTRIBUTE68,
TL_TEXT_BASE_ATTRIBUTE69,
TL_TEXT_BASE_ATTRIBUTE7,
TL_TEXT_BASE_ATTRIBUTE70,
TL_TEXT_BASE_ATTRIBUTE71,
TL_TEXT_BASE_ATTRIBUTE72,
TL_TEXT_BASE_ATTRIBUTE73,
TL_TEXT_BASE_ATTRIBUTE74,
TL_TEXT_BASE_ATTRIBUTE75,
TL_TEXT_BASE_ATTRIBUTE76,
TL_TEXT_BASE_ATTRIBUTE77,
TL_TEXT_BASE_ATTRIBUTE78,
TL_TEXT_BASE_ATTRIBUTE79,
TL_TEXT_BASE_ATTRIBUTE8,
TL_TEXT_BASE_ATTRIBUTE80,
TL_TEXT_BASE_ATTRIBUTE81,
TL_TEXT_BASE_ATTRIBUTE82,
TL_TEXT_BASE_ATTRIBUTE83,
TL_TEXT_BASE_ATTRIBUTE84,
TL_TEXT_BASE_ATTRIBUTE85,
TL_TEXT_BASE_ATTRIBUTE86,
TL_TEXT_BASE_ATTRIBUTE87,
TL_TEXT_BASE_ATTRIBUTE88,
TL_TEXT_BASE_ATTRIBUTE89,
TL_TEXT_BASE_ATTRIBUTE9,
TL_TEXT_BASE_ATTRIBUTE90,
TL_TEXT_BASE_ATTRIBUTE91,
TL_TEXT_BASE_ATTRIBUTE92,
TL_TEXT_BASE_ATTRIBUTE93,
TL_TEXT_BASE_ATTRIBUTE94,
TL_TEXT_BASE_ATTRIBUTE95,
TL_TEXT_BASE_ATTRIBUTE96,
TL_TEXT_BASE_ATTRIBUTE97,
TL_TEXT_BASE_ATTRIBUTE98,
TL_TEXT_BASE_ATTRIBUTE99,
TL_TEXT_CAT_ATTRIBUTE1,
TL_TEXT_CAT_ATTRIBUTE10,
TL_TEXT_CAT_ATTRIBUTE11,
TL_TEXT_CAT_ATTRIBUTE12,
TL_TEXT_CAT_ATTRIBUTE13,
TL_TEXT_CAT_ATTRIBUTE14,
TL_TEXT_CAT_ATTRIBUTE15,
TL_TEXT_CAT_ATTRIBUTE16,
TL_TEXT_CAT_ATTRIBUTE17,
TL_TEXT_CAT_ATTRIBUTE18,
TL_TEXT_CAT_ATTRIBUTE19,
TL_TEXT_CAT_ATTRIBUTE2,
TL_TEXT_CAT_ATTRIBUTE20,
TL_TEXT_CAT_ATTRIBUTE21,
TL_TEXT_CAT_ATTRIBUTE22,
TL_TEXT_CAT_ATTRIBUTE23,
TL_TEXT_CAT_ATTRIBUTE24,
TL_TEXT_CAT_ATTRIBUTE25,
TL_TEXT_CAT_ATTRIBUTE26,
TL_TEXT_CAT_ATTRIBUTE27,
TL_TEXT_CAT_ATTRIBUTE28,
TL_TEXT_CAT_ATTRIBUTE29,
TL_TEXT_CAT_ATTRIBUTE3,
TL_TEXT_CAT_ATTRIBUTE30,
TL_TEXT_CAT_ATTRIBUTE31,
TL_TEXT_CAT_ATTRIBUTE32,
TL_TEXT_CAT_ATTRIBUTE33,
TL_TEXT_CAT_ATTRIBUTE34,
TL_TEXT_CAT_ATTRIBUTE35,
TL_TEXT_CAT_ATTRIBUTE36,
TL_TEXT_CAT_ATTRIBUTE37,
TL_TEXT_CAT_ATTRIBUTE38,
TL_TEXT_CAT_ATTRIBUTE39,
TL_TEXT_CAT_ATTRIBUTE4,
TL_TEXT_CAT_ATTRIBUTE40,
TL_TEXT_CAT_ATTRIBUTE41,
TL_TEXT_CAT_ATTRIBUTE42,
TL_TEXT_CAT_ATTRIBUTE43,
TL_TEXT_CAT_ATTRIBUTE44,
TL_TEXT_CAT_ATTRIBUTE45,
TL_TEXT_CAT_ATTRIBUTE46,
TL_TEXT_CAT_ATTRIBUTE47,
TL_TEXT_CAT_ATTRIBUTE48,
TL_TEXT_CAT_ATTRIBUTE49,
TL_TEXT_CAT_ATTRIBUTE5,
TL_TEXT_CAT_ATTRIBUTE50,
TL_TEXT_CAT_ATTRIBUTE6,
TL_TEXT_CAT_ATTRIBUTE7,
TL_TEXT_CAT_ATTRIBUTE8,
TL_TEXT_CAT_ATTRIBUTE9,
'AUTOCREATE_BACKEND_FOR_SOURCING'
FROM PO_ATTR_VALUES_TLP_INTERFACE
WHERE interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id;
PO_DEBUG.debug_stmt(p_log_head=>g_log_head||l_api_name ,p_token=>l_progress ,p_message=>'Number of rows inserted in PO_ATTRIBUTE_VALUES_TLP table='||SQL%rowcount );
SELECT PRL.currency_code,
NVL(PRL.rate,1)
INTO l_req_currency_code,
l_req_rate
FROM po_requisition_lines_all PRL
WHERE PRL.requisition_line_id = p_requisition_line_id;
SELECT PRL.currency_code ,
GSB.currency_code ,
PRL.unit_price ,
NVL(PRL.currency_unit_price, PRL.unit_price) ,
PRL.amount ,
NVL(PRL.currency_amount, PRL.amount)
INTO l_req_currency_code ,
l_req_ou_currency_code ,
l_req_unit_price ,
l_req_currency_unit_price ,
l_req_amount ,
l_req_currency_amount
FROM po_requisition_lines_all PRL,
financials_system_params_all FSP,
gl_sets_of_books GSB
WHERE PRL.requisition_line_id = p_requisition_line_id
AND NVL(PRL.org_id, -99) = NVL(FSP.org_id, -99)
AND FSP.set_of_books_id = GSB.set_of_books_id;
SELECT PLI.auction_header_id INTO l_auction_header_id
FROM po_lines_interface pli,pon_award_allocations paa
WHERE PLI.interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id
AND pli.auction_header_id = paa.auction_header_id
AND paa.is_linked_pr_line_yn = 'Y'
AND ROWNUM <2;
INSERT INTO po_distributions_interface
(interface_header_id,
interface_line_id,
interface_distribution_id,
distribution_num,
charge_account_id,
set_of_books_id,
rate,
rate_date,
req_distribution_id,
deliver_to_location_id,
deliver_to_person_id,
encumbered_flag,
destination_type_code,
destination_organization_id,
destination_subinventory,
budget_account_id,
accrual_account_id,
variance_account_id,
wip_entity_id,
wip_line_id,
wip_repetitive_schedule_id,
wip_operation_seq_num,
wip_resource_seq_num,
bom_resource_id,
prevent_encumbrance_flag,
project_id,
task_id,
end_item_unit_number,
expenditure_type,
project_accounting_context,
destination_context,
expenditure_organization_id,
expenditure_item_date,
tax_recovery_override_flag,
recovery_rate,
recoverable_tax,
nonrecoverable_tax,
award_id,
oke_contract_line_id,
oke_contract_deliverable_id,
group_line_id,
funded_value,
quantity_ordered,
amount_ordered,
quantity_funded,
amount_funded
)
SELECT phi.interface_header_id,
PLI.interface_line_id,
po_distributions_interface_s.NEXTVAL,
ROWNUM ,
prd.code_combination_id,
prd.set_of_books_id,
phi.rate,
phi.rate_date,
prd.distribution_id,
prd.deliver_to_location_id,
prl.to_person_id,
'N',
prd.destination_type_code,
prd.destination_organization_id,
prd.destination_subinventory,
prd.budget_account_id,
prd.accrual_account_id,
prd.variance_account_id,
prd.wip_entity_id,
prd.wip_line_id,
prd.wip_repetitive_schedule_id,
prd.wip_operation_seq_num,
prd.wip_resource_seq_num,
prd.bom_resource_id,
prd.prevent_encumbrance_flag,
prd.project_id,
prd.task_id,
prd.end_item_unit_number,
prd.expenditure_type,
prd.project_accounting_context,
prd.destination_context,
prd.expenditure_organization_id,
prd.expenditure_item_date,
prd.tax_recovery_override_flag,
prd.recovery_rate,
prd.recoverable_tax,
prd.nonrecoverable_tax,
prd.award_id,
decode(pli.consigned_flag,'Y',null, prd.oke_contract_line_id),
decode(pli.consigned_flag,'Y',null, prd.oke_contract_deliverable_id),
prd.info_line_id,
paa.allocated_funds * (prd.funds_remaining/ req_dist_amt.tot_funds_remaining),
Decode(plb.matching_basis, 'QUANTITY',(paa.allocated_funds * (prd.funds_remaining/ req_dist_amt.tot_funds_remaining))/ pli.unit_price,
null), --quantity_ordered,
Decode(plb.matching_basis, 'AMOUNT',(paa.allocated_funds * (prd.funds_remaining/ req_dist_amt.tot_funds_remaining)),
NULL ), --amount_ordered,
Decode(plb.matching_basis, 'QUANTITY',(paa.allocated_funds * (prd.funds_remaining/ req_dist_amt.tot_funds_remaining))/ pli.unit_price,
null),--quantity_funded,
Decode(plb.matching_basis, 'AMOUNT',(paa.allocated_funds * (prd.funds_remaining/ req_dist_amt.tot_funds_remaining)),
NULL ) --amount_funded
FROM po_lines_interface pli,
po_headers_interface phi,
pon_award_allocations paa,
po_clmreq_dist_details_v prd,
(SELECT requisition_line_id, Sum(prd.funds_remaining) AS tot_funds_remaining
FROM po_clmreq_dist_details_v prd
GROUP BY prd.requisition_line_id) req_dist_amt,
po_requisition_lines_all prl,
po_line_types_b plb
WHERE phi.interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id
AND pli.interface_header_id = phi.interface_header_id
AND Nvl(pli.orig_from_req_flag, 'N') = 'S'
AND pli.bid_number = paa.bid_number
AND pli.bid_line_number = paa.bid_line_number
AND paa.orig_req_line_id = prd.requisition_line_id
AND prl.requisition_line_id = paa.orig_req_line_id
AND Nvl(paa.is_linked_pr_line_yn, 'N') = 'Y'
AND pli.line_type_id = plb.line_type_id
AND req_dist_amt.requisition_line_id = paa.orig_req_line_id
AND prd.funds_remaining > 0;
PO_DEBUG.debug_stmt(l_log_head,l_progress,'lines inserted into distributions interface'||l_count);