[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_blanket
( p_doc_rec IN doc_row_type,
x_process_code OUT NOCOPY VARCHAR2
);
PROCEDURE update_quotation
( p_doc_rec IN doc_row_type
);
PROCEDURE update_standard_po
( p_doc_rec IN doc_row_type,
x_process_code OUT NOCOPY VARCHAR2
);
PROCEDURE update_document_status
( p_doc_rec IN doc_row_type,
p_auth_status IN VARCHAR2,
p_status_lookup_code IN VARCHAR2
);
SELECT pui.interface_line_id
FROM po_headers_interface phi,
po_uda_interface pui
WHERE phi.interface_header_id = p_interface_header_id
AND phi.interface_header_id = pui.interface_reference_id
AND pui.attr_group_type = 'PO_HEADER_EXT_ATTRS'
UNION
SELECT pui.interface_line_id
FROM po_headers_interface phi,
po_lines_interface pli,
po_uda_interface pui
WHERE phi.interface_header_id = p_interface_header_id
AND pli.interface_header_id = phi.interface_header_id
AND pli.interface_line_id = pui.interface_reference_id
AND pui.attr_group_type = 'PO_LINE_EXT_ATTRS'
UNION
SELECT pui.interface_line_id
FROM po_headers_interface phi,
po_lines_interface pli,
po_line_locations_interface pll,
po_uda_interface pui
WHERE phi.interface_header_id = p_interface_header_id
AND pli.interface_header_id = phi.interface_header_id
AND pli.interface_line_id = pll.interface_line_id
AND pll.interface_line_location_id = pui.interface_reference_id
AND pui.attr_group_type = 'PO_SHIPMENTS_EXT_ATTRS';
UPDATE PO_UDA_INTERFACE
SET pk1_value = Decode(attr_group_type, 'PO_HEADER_EXT_ATTRS', (SELECT po_header_id
FROM po_headers_interface
WHERE interface_header_id = interface_reference_id ),
'PO_LINE_EXT_ATTRS' , (SELECT PO_LINE_ID
FROM po_lines_interface
WHERE interface_line_id = interface_reference_id),
'PO_SHIPMENTS_EXT_ATTRS' , (SELECT LINE_LOCATION_ID
FROM PO_LINE_LOCATIONS_INTERFACE
WHERE interface_line_location_id = interface_reference_id)
),
pk2_value = -1
WHERE interface_line_id = l_uda_lines_tbl(i);
PO_DRAFTS_PVT.update_draft_status
( p_draft_id => l_doc_rec.draft_id,
p_new_status => PO_DRAFTS_PVT.g_STATUS_DRAFT
);
ELSIF (l_doc_rec.action = PO_PDOI_CONSTANTS.g_ACTION_UPDATE) THEN
d_position := 100;
update_blanket(p_doc_rec => l_doc_rec,
x_process_code => x_process_code); -- bug 7277317
ELSIF (l_doc_rec.action = PO_PDOI_CONSTANTS.g_ACTION_UPDATE) THEN
d_position := 130;
update_quotation(p_doc_rec => l_doc_rec);
ELSIF (l_doc_rec.action = PO_PDOI_CONSTANTS.g_ACTION_UPDATE) THEN
d_position := 150;
update_standard_po(p_doc_rec => l_doc_rec,
x_process_code => x_process_code); -- bug 7277317
/* bug 8490582 Purchase documents are created even submission check fails. Therefore Interface should be update as ACCEPTED
not as INCOMPLETE */
l_process_code := PO_PDOI_CONSTANTS.g_PROCESS_CODE_ACCEPTED;
UPDATE po_headers_interface
SET process_code = l_process_code
WHERE interface_header_id = l_doc_rec.interface_header_id;
PO_DRAFTS_PVT.update_draft_status
( p_draft_id => l_doc_rec.draft_id,
p_new_status => PO_DRAFTS_PVT.g_STATUS_DRAFT
);
DELETE FROM po_lines_draft_all
WHERE draft_id = p_draft_id
AND change_accepted_flag = PO_DRAFTS_PVT.g_chg_accepted_flag_NOTIFY
RETURNING po_line_id
BULK COLLECT
INTO l_po_line_id_tbl;
DELETE FROM po_line_locations_draft_all
WHERE draft_id = p_draft_id
AND po_line_id = l_po_line_id_tbl(i);
DELETE FROM po_attribute_values_draft
WHERE draft_id = p_draft_id
AND po_line_id = l_po_line_id_tbl(i);
DELETE FROM po_attribute_values_tlp_draft
WHERE draft_id = p_draft_id
AND po_line_id = l_po_line_id_tbl(i);
DELETE FROM po_price_diff_draft
WHERE draft_id = p_draft_id
AND entity_id = l_po_line_id_tbl(i)
AND entity_type = 'BLANKET LINE';
DELETE FROM po_price_diff_draft PPDD
WHERE draft_id = p_draft_id
AND entity_type = 'PRICE BREAK'
AND EXISTS (SELECT 1
FROM po_line_locations_draft_all PLLD
WHERE PLLD.draft_id = p_draft_id
AND PLLD.po_line_id = l_po_line_id_tbl(i)
AND PLLD.line_location_id = PPDD.entity_id
UNION ALL
SELECT 1
FROM po_line_locations_all PLLA
WHERE PLLA.po_line_id = l_po_line_id_tbl(i)
AND PLLA.line_location_id = PPDD.entity_id);
l_dft_to_delete_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
SELECT PHI.interface_header_id,
PHI.action,
PHI.draft_id,
PHI.po_header_id
BULK COLLECT
INTO l_intf_header_id_tbl,
l_action_tbl,
l_dft_id_tbl,
l_po_header_id_tbl
FROM po_headers_interface PHI
WHERE processing_id = -PO_PDOI_PARAMS.g_processing_id
AND processing_round_num = PO_PDOI_PARAMS.g_current_round_num;
IF ( l_action_tbl(i) <> PO_PDOI_CONSTANTS.g_action_UPDATE ) THEN
d_position := 20;
l_dft_to_delete_tbl.EXTEND;
l_dft_to_delete_tbl(l_dft_to_delete_tbl.COUNT) := l_dft_id_tbl(i);
l_dft_to_delete_tbl.EXTEND;
l_dft_to_delete_tbl(l_dft_to_delete_tbl.COUNT) := l_dft_id_tbl(i);
l_dft_to_delete_tbl.EXTEND;
l_dft_to_delete_tbl(l_dft_to_delete_tbl.COUNT) :=
l_dft_exist_chg_check_tbl(i);
FORALL i IN 1..l_dft_to_delete_tbl.COUNT
DELETE po_drafts
WHERE draft_id = l_dft_to_delete_tbl(i);
IF ( p_doc_rec.action <> PO_PDOI_CONSTANTS.g_ACTION_UPDATE ) THEN
-- If action <> update, we need to tell user that the document created
-- has 0 line, and thus the failure
PO_PDOI_ERR_UTL.add_fatal_error
( p_interface_header_id => p_doc_rec.interface_header_id,
p_error_message_name => 'PO_PDOI_INVALID_NUM_OF_LINES',
p_table_name => 'PO_HEADERS_INTERFACE',
p_column_name => 'PO_HEADER_ID',
p_column_value => p_doc_rec.po_header_id,
p_token1_name => 'COLUMN_NAME',
p_token1_value => 'PO_HEADER_ID'
);
And if submission check got fails the interface table process code has to be updated as ACCEPTED
not as INCOMPLETE as the records has been considered for processing and purchase document got created
Thus reverting the fix done in the bug 7706598*/
/*x_process_code := NULL ;
update_document_status
( p_doc_rec => p_doc_rec,
p_auth_status => l_new_auth_status,
p_status_lookup_code => NULL
);
PROCEDURE update_blanket
( p_doc_rec IN doc_row_type,
x_process_code OUT NOCOPY VARCHAR2
) IS
d_api_name CONSTANT VARCHAR2(30) := 'update_blanket';
UPDATE po_headers_interface
SET process_code = 'NOTIFIED'
WHERE interface_header_id = p_doc_rec.interface_header_id;
p_any_line_updated => l_doc_info.has_lines_updated,
p_buyer_id => PO_PDOI_PARAMS.g_request.buyer_id,
p_agent_id => p_doc_rec.agent_id,
p_vendor_id => p_doc_rec.vendor_id,
p_vendor_name => p_doc_rec.vendor_name
);
And if submission check got fails the interface table process code has to be updated as ACCEPTED
not as INCOMPLETE as the records has been considered for processing and purchase document got created
Thus reverting the fix done in the bug 7706598*/
/* x_process_code := NULL ;
update_document_status
( p_doc_rec => p_doc_rec,
p_auth_status => l_new_auth_status,
p_status_lookup_code => NULL
);
END update_blanket;
update_document_status
( p_doc_rec => p_doc_rec,
p_auth_status => NULL,
p_status_lookup_code => 'A'
);
PROCEDURE update_quotation
( p_doc_rec IN doc_row_type
) IS
d_api_name CONSTANT VARCHAR2(30) := 'update_quotation';
UPDATE po_headers_interface
SET process_code = 'NOTIFIED'
WHERE interface_header_id = p_doc_rec.interface_header_id;
p_any_line_updated => l_doc_info.has_lines_updated,
p_buyer_id => PO_PDOI_PARAMS.g_request.buyer_id,
p_agent_id => p_doc_rec.agent_id,
p_vendor_id => p_doc_rec.vendor_id,
p_vendor_name => p_doc_rec.vendor_name
);
END update_quotation;
SELECT ORG_ID
INTO l_org_id
FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID = p_doc_rec.PHI_PO_HEADER_ID;
SELECT po_distribution_id, code_combination_id, ACRN
,CLM_MISC_LOA,''
BULK COLLECT INTO l_distribution_id_tbl,
l_charge_acc_tbl, l_ACRN_tbl
,L_LOA_TBL,L_CHANGE_STAT_TBL
FROM po_distributions_all
WHERE po_header_id = p_doc_rec.PHI_PO_HEADER_ID;
UPDATE po_distributions_all
SET ACRN = l_ACRN_tbl(i)
WHERE po_distribution_id = l_distribution_id_tbl(i);
SELECT interface_header_id
INTO l_int_header_id
FROM po_headers_interface
WHERE po_header_id = p_doc_rec.PHI_PO_HEADER_ID;
SELECT po_distribution_id, partial_funded_flag, funded_value
BULK COLLECT INTO l_distribution_id_tbl, l_partial_funded_flag_tbl, l_funded_value_tbl
FROM po_distributions_all
WHERE po_header_id = p_doc_rec.PHI_PO_HEADER_ID;
UPDATE po_distributions_all
SET funded_value = l_amount_ordered,
change_in_funded_value = l_amount_ordered
WHERE po_distribution_id = l_distribution_id_tbl(i)
AND budget_account_id IS NOT NULL;
PO_LOG.stmt(d_module, d_position, 'Updated the Encumbrance amount for this Distribution to ',l_amount_ordered);
SELECT interface_header_id
INTO l_int_header_id
FROM po_headers_interface
WHERE po_header_id = p_doc_rec.PHI_PO_HEADER_ID;
update_document_status
( p_doc_rec => p_doc_rec,
p_auth_status => 'INCOMPLETE',
p_status_lookup_code => NULL
);
And if submission check got fails the interface table process code has to be updated as ACCEPTED
not as INCOMPLETE as the records has been considered for processing and purchase document got created
Thus reverting the fix done in the bug 7706598*/
/* x_process_code := NULL ;
update_document_status
( p_doc_rec => p_doc_rec,
p_auth_status => 'PRE-APPROVED',
p_status_lookup_code => NULL
);
update_document_status
( p_doc_rec => p_doc_rec,
p_auth_status => l_new_auth_status,
p_status_lookup_code => NULL
);
PROCEDURE update_standard_po
( p_doc_rec IN doc_row_type,
x_process_code OUT NOCOPY VARCHAR2
) IS
d_api_name CONSTANT VARCHAR2(30) := 'update_standard_po';
l_update_auth_status VARCHAR2(1);
SELECT ORG_ID
INTO l_org_id
FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID = p_doc_rec.PHI_PO_HEADER_ID;
SELECT po_distribution_id, code_combination_id, ACRN,
CLM_MISC_LOA ,''
BULK COLLECT INTO l_distribution_id_tbl,
l_charge_acc_tbl, l_ACRN_tbl
,L_LOA_TBL,L_CHANGE_STAT_TBL
FROM po_distributions_all
WHERE po_header_id = p_doc_rec.PHI_PO_HEADER_ID;
UPDATE po_distributions_all
SET ACRN = l_ACRN_tbl(i)
WHERE po_distribution_id = l_distribution_id_tbl(i);
SELECT Max(interface_header_id)
INTO l_int_header_id
FROM po_headers_interface
WHERE po_header_id = p_doc_rec.PHI_PO_HEADER_ID;
And if submission check got fails the interface table process code has to be updated as ACCEPTED
not as INCOMPLETE as the records has been considered for processing and purchase document got created
Thus reverting the fix done in the bug 7706598*/
/* x_process_code := NULL ;
l_update_auth_status := FND_API.G_TRUE;
l_update_auth_status := FND_API.G_FALSE;
IF (l_update_auth_status = FND_API.G_TRUE) THEN
d_position := 80;
update_document_status
( p_doc_rec => p_doc_rec,
p_auth_status => l_new_auth_status,
p_status_lookup_code => NULL
);
END update_standard_po;
UPDATE po_headers_all
SET start_date = NVL(start_date, l_expiration_date),
end_date = l_expiration_date,
last_updated_by = FND_GLOBAL.user_id,
last_update_date = SYSDATE
WHERE po_header_id = p_doc_rec.orig_po_header_id;
/* bug 12584165 : During PDOI creation we need to update the
clm_document_number with the value in segment1. */
UPDATE po_headers_draft_all
SET segment1 = l_document_num,
clm_document_number = l_document_num --bug 12584165
WHERE po_header_id = p_doc_rec.po_header_id;
SELECT PLD.po_line_id,
PLD.item_id,
PLD.category_id,
PLI.interface_line_id,
PLI.sourcing_rule_name,
PLI.effective_date,
PLI.expiration_date
BULK COLLECT
INTO x_lines.po_line_id_tbl,
x_lines.item_id_tbl,
x_lines.category_id_tbl,
x_lines.interface_line_id_tbl,
x_lines.sourcing_rule_name_tbl,
x_lines.effective_date_tbl,
x_lines.expiration_date_tbl
FROM po_headers_interface PHI,
po_lines_interface PLI,
po_lines_draft_all PLD
WHERE PHI.interface_header_id = p_doc_rec.interface_header_id
AND PHI.interface_header_id = PLI.interface_header_id
AND PHI.draft_id = PLD.draft_id
AND PLI.po_line_id = PLD.po_line_id
AND NVL(PLI.price_break_flag, 'N') <> 'Y'
AND PLD.item_id IS NOT NULL
AND PLD.order_type_lookup_code = 'QUANTITY'
AND NOT EXISTS
(SELECT 1
FROM po_lines_all PLA
WHERE PLD.po_line_id = PLA.po_line_id);
SELECT vendor_id,
vendor_site_id
INTO l_vendor_id,
l_vendor_site_id
FROM po_headers_all
WHERE po_header_id = p_doc_rec.po_header_id;
p_create_update_code => 'CREATE_UPDATE',
p_interface_error_code => 'PO_DOCS_OPEN_INTERFACE',
x_header_processable_flag => l_header_processable_flag
);
l_delete_processed_draft VARCHAR2(1);
l_delete_processed_draft := FND_API.G_TRUE;
l_delete_processed_draft := FND_API.G_FALSE;
p_delete_processed_draft => l_delete_processed_draft,
p_acceptance_action => NULL,
x_return_status => l_return_status
);
PROCEDURE update_document_status
( p_doc_rec IN doc_row_type,
p_auth_status IN VARCHAR2,
p_status_lookup_code IN VARCHAR2
) IS
d_api_name CONSTANT VARCHAR2(30) := 'update_document_status';
/*Bug 8490582 The following code cause the status to update wrongly and document not visible on form/page so commenting the line
ELSIF (p_auth_status = 'REQUIRES_REAPPROVAL') THEN */
ELSIF (p_auth_status = 'REQUIRES REAPPROVAL') THEN
l_approved_flag := 'R';
UPDATE po_headers_all
SET authorization_status = p_auth_status,
approved_flag = l_approved_flag,
approved_date = DECODE(l_approved_date,
FND_API.G_MISS_DATE, approved_date,
l_approved_date),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE po_header_id = p_doc_rec.po_header_id;
PO_LOG.stmt(d_module, d_position, 'Update shipment approval status');
UPDATE po_line_locations_all
SET approved_flag = 'Y',
approved_date = l_approved_date,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE po_header_id = p_doc_rec.po_header_id
AND shipment_type IN ('STANDARD', 'PRICE BREAK')
AND NVL(approved_flag, 'N') <> 'Y';
PO_LOG.stmt(d_module, d_position, 'Update action history');
PO_FORWARD_SV1.update_action_history
( x_object_id => p_doc_rec.po_header_id,
x_object_type_code => PO_PDOI_PARAMS.g_request.document_type,
x_old_employee_id => p_doc_rec.agent_id,
x_action_code => 'APPROVE',
x_note => NULL,
x_user_id => FND_GLOBAL.user_id,
x_login_id => FND_GLOBAL.login_id
);
END update_document_status;
IF (p_doc_rec.action = PO_PDOI_CONSTANTS.g_ACTION_UPDATE AND
PO_PDOI_PARAMS.g_sys.is_federal_instance = 'Y') THEN
l_validate_document := PO_DOCUMENT_FUNDS_PVT.g_parameter_YES;
p_doc_rec.action <> PO_PDOI_CONSTANTS.g_ACTION_UPDATE AND
p_doc_rec.encumbrance_required_flag = 'Y' AND
p_new_auth_status = 'APPROVED') THEN
l_need_to_encumber := FND_API.G_TRUE;
update po_headers_all
set SUPPLIER_NOTIF_METHOD = nvl(l_default_method,'NONE'),
EMAIL_ADDRESS = l_email_address,
FAX = l_fax_number
where po_header_id = p_doc_rec.po_header_id;
SELECT emp.person_id
INTO l_current_employee_id
FROM fnd_user fu,
per_workforce_current_x emp,
po_agents poa
WHERE fu.user_id = fnd_global.user_id
AND fu.employee_id = emp.person_id (+)
AND emp.person_id = poa.agent_id (+)
AND SYSDATE BETWEEN Nvl(poa.start_date_active (+), SYSDATE - 1) AND
Nvl(poa.end_date_active (+), SYSDATE + 1);
UPDATE po_headers_all
SET AME_TRANSACTION_TYPE = ( SELECT AME_TRANSACTION_TYPE FROM po_doc_style_headers
WHERE style_id = p_doc_rec.style_id
),
AME_APPROVAL_ID = PO_AME_APPROVALS_S.NEXTVAL
WHERE po_header_id = p_doc_rec.po_header_id;
UpdateSourcingRule => PO_PDOI_PARAMS.g_request.create_sourcing_rules_flag
);
PO_DELREC_PVT.create_update_delrec
( p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_action => 'APPROVE',
p_doc_type => 'PO',
p_doc_subtype => 'STANDARD',
p_doc_id => p_doc_rec.po_header_id,
p_line_id => NULL,
p_line_location_id => NULL
);
FOR i IN (SELECT MESSAGE_NAME, TEXT_LINE, MESSAGE_TYPE FROM po_online_report_text_gt WHERE online_report_id = x_online_report_id)
LOOP
--For Bug 13042689
--Incrementing the error count ignoring the warnings.
IF NVL(i.MESSAGE_TYPE,'E') = 'E' THEN
x_count := x_count + 1;
/* BUG 8490582 Added the else condition to check for update action. If an incomplete purchase document
comes for updation then authorization status should be in incomplete not in requires re-approval. */
IF (x_count >0) then /* Bug 10300319 following logic shd executed only if any error reported */
IF ( p_doc_rec.action = PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL) THEN
update_document_status
( p_doc_rec => p_doc_rec,
p_auth_status => 'INCOMPLETE',
p_status_lookup_code => NULL
);
IF ( p_doc_rec.action = PO_PDOI_CONSTANTS.g_ACTION_UPDATE) and
(nvl(p_doc_rec.ORIG_AUTH_STATUS,'INCOMPLETE') in ('INCOMPLETE','REJECTED')) THEN --bug 8597275 ,10300319
update_document_status
( p_doc_rec => p_doc_rec,
p_auth_status => nvl(p_doc_rec.ORIG_AUTH_STATUS,'INCOMPLETE'), --bug 8597275 ,10300319
p_status_lookup_code => NULL
);
update_document_status
( p_doc_rec => p_doc_rec,
p_auth_status => 'REQUIRES REAPPROVAL',
p_status_lookup_code => NULL
);
SELECT Nvl(style_id,1),type_lookup_code INTO l_style_id, l_doc_type
FROM po_headers_draft_all
WHERE po_header_id = p_po_header_id
AND Nvl(draft_id,-1) = p_draft_id;
UPDATE po_headers_draft_all SET
uda_template_id = l_template_id
WHERE po_header_id = p_po_header_id
AND Nvl(draft_id,-1) = p_draft_id;
UPDATE po_lines_draft_all SET
uda_template_id = l_line_template_id
WHERE po_header_id = p_po_header_id
AND Nvl(draft_id,-1) = p_draft_id;
UPDATE po_line_locations_draft_all SET
uda_template_id = l_shipment_template_id
WHERE po_header_id = p_po_header_id
AND Nvl(draft_id,-1) = p_draft_id;
SELECT pk1_value, pk2_value, attr_group_type
INTO l_pk1_value, l_pk2_value, l_attr_group_type
FROM po_uda_interface
WHERE Nvl(interface_line_id,-1) = p_uda_lines_tbl(i);
SELECT po_line_id, line_num_display
FROM po_lines_all
WHERE po_header_id = p_po_header_id
AND group_line_id IS NULL
ORDER BY line_num;
SELECT po_line_id, line_num_display
FROM po_lines_all
WHERE po_header_id = p_po_header_id
AND group_line_id = p_group_line_id
AND NVL (clm_info_flag, 'N') = 'Y'
ORDER BY line_num;
SELECT po_line_id, line_num_display
FROM po_lines_all
WHERE po_header_id = p_po_header_id
AND group_line_id = p_group_line_id
AND NVL (clm_info_flag, 'N') = 'N'
ORDER BY line_num;
SELECT Nvl(Max(line_num_display),'0000')
INTO l_clin_num
FROM po_lines_all
WHERE po_header_id = p_po_header_id
AND group_line_id IS NULL;
UPDATE po_lines_all
SET line_num_display = l_new_clin_num
WHERE po_line_id = clin_rec.po_line_id
AND line_num_display IS NULL;
UPDATE po_lines_all
SET line_num_display = l_info_slin_num
WHERE po_line_id = info_slin_rec.po_line_id;
UPDATE po_lines_all
SET line_num_display = l_priced_slin_num
WHERE po_line_id = priced_slin_rec.po_line_id;
SELECT po_line_id
BULK COLLECT INTO l_po_line_id_tbl
FROM po_lines_all
WHERE po_header_id = p_po_header_id
AND order_type_lookup_code = 'AMOUNT';
SELECT 'Y' INTO is_pricing_valid
FROM dual
WHERE EXISTS (
SELECT 1
FROM po_lines_all pol,
po_lines_all_ext_b ple,
po_uda_ag_template_usages ptu
WHERE pol.po_line_id = l_po_line_id_tbl(i)
AND ple.po_line_id = pol.po_line_id
--AND ple.draft_id = p_draft_id
AND ple.attr_group_id = ptu.attribute_group_id
AND ptu.attribute_category = 'PRICING'
AND ptu.attribute2 = pol.contract_type
AND ptu.attribute1 = pol.clm_idc_type
AND ptu.template_id = ple.uda_template_id
AND Nvl(ple.n_ext_attr20, 0) =
Decode(pol.matching_basis, 'QUANTITY',
(Nvl(pol.quantity, 0) * Nvl(pol.unit_price, 0)),
'AMOUNT', Nvl(pol.amount, 0))
AND pol.order_type_lookup_code = 'AMOUNT'
AND ple.pk1_value IS NULL
);
SELECT ORG_ID
INTO l_org_id
FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID = p_po_header_id;
INSERT INTO po_clm_cars( car_id,
po_header_id,
po_draft_id,
report_type,
car_status,
car_number,
piid,
reporting_method,
exemption_reason,
modification_segment,
agency_id,
prepared_user,
last_update_date,
last_updated_by,
creation_date,
created_by
)
VALUES ( po_clm_cars_s.NEXTVAL,
p_po_header_id,
-1,
po_clm_car_util.get_report_type(p_po_header_id),
'NOT_REPORTED',
po_clm_car_util.get_car_number(p_po_header_id, -1),
po_clm_car_util.get_piid(p_po_header_id, -1),
'EXEMPT',
'CLASSIFIED',
po_clm_car_util.get_modification_segment(p_po_header_id, -1),
po_clm_car_util.get_agency_id(l_org_id),
po_clm_car_util.get_prepared_user,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id
);
SELECT uda_template_id INTO l_template_id
FROM po_headers_all
WHERE po_header_id = p_doc_rec.po_header_id
AND Nvl(draft_id,-1) = -1;
SELECT Count(attr_group_id)
INTO l_count
FROM po_headers_all_ext_b
WHERE po_header_id = p_doc_rec.po_header_id
AND Nvl(draft_id,-1) = -1
AND UDA_TEMPLATE_ID = l_template_id
AND ATTR_GROUP_ID = ( SELECT ATTRIBUTE_GROUP_ID FROM po_uda_ag_template_usages
WHERE template_id = l_template_id
AND attribute_category = 'DOCUMENT_NUMBERING'
AND ATTRIBUTE1 = 'Base Document'
AND ATTRIBUTE2 = l_source_org_owned
AND ATTRIBUTE3 = 'DEFAULT'
);
UPDATE po_headers_all SET
CLM_DOCUMENT_NUMBER = l_doc_number,
SEGMENT1 = (SELECT REPLACE(l_doc_number, l_delimiter, '')
FROM po_headers_all_ext_b
WHERE po_header_id = p_doc_rec.po_header_id
AND Nvl(draft_id,-1) = -1
AND UDA_TEMPLATE_ID = l_template_id
AND ATTR_GROUP_ID = ( SELECT ATTRIBUTE_GROUP_ID FROM po_uda_ag_template_usages
WHERE template_id = l_template_id
AND attribute_category = 'DOCUMENT_NUMBERING'
AND ATTRIBUTE1 = 'Base Document'
AND ATTRIBUTE2 = l_source_org_owned
AND ATTRIBUTE3 = 'DEFAULT'
)
)
WHERE po_header_id = p_doc_rec.po_header_id
AND Nvl(draft_id,-1) = -1;