[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_select_cols VARCHAR2(1000) := 'C_EXT_ATTR39';
SELECT attr.database_column
FROM ego_attrs_v attr
WHERE attr.attr_group_name = x_addr_group_name
AND attr.attr_group_type = 'PO_REQ_HEADER_EXT_ATTRS'
AND attr.display_code <> 'H'
AND read_only_flag = 'N';
sql_stmt1:='SELECT REQUISITION_HEADER_ID,
SEGMENT1,
nvl(CANCEL_FLAG,''N'') CANCEL_FLAG,
nvl(CLOSED_CODE,''OPEN'') CLOSED_CODE,
DESCRIPTION,
NOTE_TO_AUTHORIZER,
USSGL_TRANSACTION_CODE,
PCARD_ID,
REVISION_NUM,
CLM_MIPR_TYPE,
CLM_MIPR_PREPARED_DATE,
CLM_MIPR_REF_NUM
FROM po_requisition_headers_all
WHERE conformed_header_id='||p_confirmed_id||
' AND revision_num='||(p_revision_num -1);
sql_stmt2:='SELECT REQUISITION_HEADER_ID,
SEGMENT1,
nvl(CANCEL_FLAG,''N'') CANCEL_FLAG,
nvl(CLOSED_CODE,''OPEN'') CLOSED_CODE,
DESCRIPTION,
NOTE_TO_AUTHORIZER,
USSGL_TRANSACTION_CODE,
PCARD_ID,
REVISION_NUM,
CLM_MIPR_TYPE,
CLM_MIPR_PREPARED_DATE,
CLM_MIPR_REF_NUM
FROM po_requisition_headers_all
WHERE conformed_header_id='||p_confirmed_id||
' AND revision_num='||p_revision_num;
sql_stmt1:= 'SELECT REQUISITION_LINE_ID,LINE_NUM, LINE_NUM_DISPLAY,nvl(prl.CANCEL_FLAG,''N'') CANCEL_FLAG, nvl(prl.CLOSED_CODE,''OPEN'') CLOSED_CODE,ITEM_ID,ITEM_REVISION,
ITEM_DESCRIPTION, CATEGORY_ID, UNIT_MEAS_LOOKUP_CODE, UNIT_PRICE,QUANTITY,DELIVER_TO_LOCATION_ID,TO_PERSON_ID,
SUGGESTED_BUYER_ID,to_char(NEED_BY_DATE,''DD-MON-YYYY HH24:MI:SS'') NEED_BY_DATE,JUSTIFICATION,NOTE_TO_AGENT,NOTE_TO_RECEIVER,CURRENCY_CODE,RATE_TYPE,to_char(RATE_DATE,''DD-MON-YYYY HH24:MI:SS'') RATE_DATE,
RATE,CURRENCY_UNIT_PRICE,SUGGESTED_VENDOR_NAME,SUGGESTED_VENDOR_LOCATION,SUGGESTED_VENDOR_CONTACT,SUGGESTED_VENDOR_PHONE,
SUGGESTED_VENDOR_PRODUCT_CODE,HAZARD_CLASS_ID,DESTINATION_TYPE_CODE,DESTINATION_ORGANIZATION_ID, DESTINATION_SUBINVENTORY,
VENDOR_ID,VENDOR_SITE_ID, VENDOR_CONTACT_ID,prl.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE, MANUFACTURER_NAME,
MANUFACTURER_PART_NUMBER, REQUESTER_EMAIL,REQUESTER_FAX,REQUESTER_PHONE, UNSPSC_CODE, SUPPLIER_DUNS,AMOUNT,
CURRENCY_AMOUNT,JOB_LONG_DESCRIPTION,SUGGESTED_VENDOR_CONTACT_FAX,SUGGESTED_VENDOR_CONTACT_EMAIL,CANDIDATE_FIRST_NAME,
CANDIDATE_LAST_NAME,to_char(ASSIGNMENT_START_DATE,''DD-MON-YYYY HH24:MI:SS'') ASSIGNMENT_START_DATE,CONTRACT_TYPE,COST_CONSTRAINT,
to_char(CLM_PERIOD_PERF_START_DATE,''DD-MON-YYYY HH24:MI:SS'') CLM_PERIOD_PERF_START_DATE,to_char(CLM_PERIOD_PERF_END_DATE,''DD-MON-YYYY HH24:MI:SS'') CLM_PERIOD_PERF_END_DATE,
nvl(CLM_OPTION_EXERCISED,''N'') CLM_OPTION_EXERCISED, CLM_OPTION_FROM_DATE, CLM_OPTION_TO_DATE, FUND_SOURCE_NOT_KNOWN,CLM_OPTION_INDICATOR , CLM_BASE_LINE_NUM , CLM_MIPR_OBLIGATION_TYPE
FROM po_requisition_lines_all prl,
po_requisition_headers_all prh
WHERE prh.conformed_header_id='||p_confirmed_id||
'AND prh.requisition_header_id=prl.requisition_header_id
AND prh.revision_num='||(p_revision_num -1)||
'ORDER BY LINE_NUM';
sql_stmt2:='SELECT REQUISITION_LINE_ID,LINE_NUM, LINE_NUM_DISPLAY,nvl(prl.CANCEL_FLAG,''N'') CANCEL_FLAG, nvl(prl.CLOSED_CODE,''OPEN'') CLOSED_CODE,ITEM_ID,ITEM_REVISION,
ITEM_DESCRIPTION, CATEGORY_ID, UNIT_MEAS_LOOKUP_CODE, UNIT_PRICE,QUANTITY,DELIVER_TO_LOCATION_ID,TO_PERSON_ID,
SUGGESTED_BUYER_ID,to_char(NEED_BY_DATE,''DD-MON-YYYY HH24:MI:SS'') NEED_BY_DATE,JUSTIFICATION,NOTE_TO_AGENT,NOTE_TO_RECEIVER,CURRENCY_CODE,RATE_TYPE,to_char(RATE_DATE,''DD-MON-YYYY HH24:MI:SS'') RATE_DATE,
RATE,CURRENCY_UNIT_PRICE,SUGGESTED_VENDOR_NAME,SUGGESTED_VENDOR_LOCATION,SUGGESTED_VENDOR_CONTACT,SUGGESTED_VENDOR_PHONE,
SUGGESTED_VENDOR_PRODUCT_CODE,HAZARD_CLASS_ID,DESTINATION_TYPE_CODE,DESTINATION_ORGANIZATION_ID, DESTINATION_SUBINVENTORY,
VENDOR_ID,VENDOR_SITE_ID, VENDOR_CONTACT_ID,prl.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE, MANUFACTURER_NAME,
MANUFACTURER_PART_NUMBER, REQUESTER_EMAIL,REQUESTER_FAX,REQUESTER_PHONE, UNSPSC_CODE, SUPPLIER_DUNS,AMOUNT,
CURRENCY_AMOUNT,JOB_LONG_DESCRIPTION,SUGGESTED_VENDOR_CONTACT_FAX,SUGGESTED_VENDOR_CONTACT_EMAIL,CANDIDATE_FIRST_NAME,
CANDIDATE_LAST_NAME,to_char(ASSIGNMENT_START_DATE,''DD-MON-YYYY HH24:MI:SS'') ASSIGNMENT_START_DATE,CONTRACT_TYPE,COST_CONSTRAINT,
to_char(CLM_PERIOD_PERF_START_DATE,''DD-MON-YYYY HH24:MI:SS'') CLM_PERIOD_PERF_START_DATE,to_char(CLM_PERIOD_PERF_END_DATE,''DD-MON-YYYY HH24:MI:SS'') CLM_PERIOD_PERF_END_DATE,
nvl(CLM_OPTION_EXERCISED,''N'') CLM_OPTION_EXERCISED, CLM_OPTION_FROM_DATE, CLM_OPTION_TO_DATE, FUND_SOURCE_NOT_KNOWN,CLM_OPTION_INDICATOR , CLM_BASE_LINE_NUM , CLM_MIPR_OBLIGATION_TYPE
FROM po_requisition_lines_all prl,
po_requisition_headers_all prh
WHERE prh.conformed_header_id='||p_confirmed_id||
'AND prh.requisition_header_id=prl.requisition_header_id
AND prh.revision_num='||p_revision_num||
'ORDER BY LINE_NUM';
sql_stmt1:='SELECT DISTRIBUTION_ID,
prl.LINE_NUM LINE_NUM,
prl.LINE_NUM_DISPLAY LINE_NUM_DISPLAY,
DISTRIBUTION_NUM,
nvl(prl.CANCEL_FLAG,''N'') LINE_CANCEL_FLAG,
prl.ITEM_ID LINE_ITEM_ID,
CODE_COMBINATION_ID,
REQ_LINE_QUANTITY,
to_char(GL_ENCUMBERED_DATE,''DD-MON-YYYY HH24:MI:SS'') GL_ENCUMBERED_DATE,
prd.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
PROJECT_ID,
TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ORGANIZATION_ID,
to_char(EXPENDITURE_ITEM_DATE,''DD-MON-YYYY HH24:MI:SS'') EXPENDITURE_ITEM_DATE,
nvl(RECOVERABLE_TAX,0) RECOVERABLE_TAX,
nvl(NONRECOVERABLE_TAX,0) NONRECOVERABLE_TAX,
nvl(RECOVERY_RATE,0) RECOVERY_RATE,
REQ_LINE_AMOUNT,
REQ_LINE_CURRENCY_AMOUNT,
PARTIAL_FUNDED_FLAG,
FUNDED_VALUE,
QUANTITY_FUNDED,
AMOUNT_FUNDED,
ACRN
FROM po_req_distributions_all prd,
po_requisition_lines_all prl,
po_requisition_headers_all prh
WHERE prh.conformed_header_id='||p_confirmed_id||
'AND prh.requisition_header_id=prl.requisition_header_id
AND prl.requisition_line_id=nvl(prd.info_line_id,prd.requisition_line_id)
AND revision_num='||(p_revision_num -1)||
'ORDER BY nvl(prd.info_line_id,prd.requisition_line_id),DISTRIBUTION_NUM' ;
sql_stmt2:='SELECT DISTRIBUTION_ID,
prl.LINE_NUM LINE_NUM,
prl.LINE_NUM_DISPLAY LINE_NUM_DISPLAY,
DISTRIBUTION_NUM,
nvl(prl.CANCEL_FLAG,''N'') LINE_CANCEL_FLAG,
prl.ITEM_ID LINE_ITEM_ID,
CODE_COMBINATION_ID,
REQ_LINE_QUANTITY,
to_char(GL_ENCUMBERED_DATE,''DD-MON-YYYY HH24:MI:SS'') GL_ENCUMBERED_DATE,
prd.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
PROJECT_ID,
TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ORGANIZATION_ID,
to_char(EXPENDITURE_ITEM_DATE,''DD-MON-YYYY HH24:MI:SS'') EXPENDITURE_ITEM_DATE,
nvl(RECOVERABLE_TAX,0) RECOVERABLE_TAX,
nvl(NONRECOVERABLE_TAX,0) NONRECOVERABLE_TAX,
nvl(RECOVERY_RATE,0) RECOVERY_RATE,
REQ_LINE_AMOUNT,
REQ_LINE_CURRENCY_AMOUNT,
PARTIAL_FUNDED_FLAG,
FUNDED_VALUE,
QUANTITY_FUNDED,
AMOUNT_FUNDED,
ACRN
FROM po_req_distributions_all prd,
po_requisition_lines_all prl,
po_requisition_headers_all prh
WHERE prh.conformed_header_id='||p_confirmed_id||
'AND prh.requisition_header_id=prl.requisition_header_id
AND prl.requisition_line_id=nvl(prd.info_line_id,prd.requisition_line_id)
AND revision_num='||p_revision_num||
'ORDER BY nvl(prd.info_line_id,prd.requisition_line_id),DISTRIBUTION_NUM';
SELECT u.attribute_group_id, ag.attr_group_name
INTO g_address_attr_group_id, g_address_group_name
FROM po_uda_ag_template_usages u,
po_requisition_headers_all h,
ego_attr_groups_v ag
WHERE h.uda_template_id = u.template_id
AND h.requisition_header_id = p_confirmed_id
AND ag.attr_group_type = 'PO_REQ_HEADER_EXT_ATTRS'
AND ag.attr_group_id = u.attribute_group_id
AND u.attribute_category = 'ADDRESS' ;
l_select_cols := l_select_cols ||' , '|| rec.database_column;
sql_stmt1:= 'SELECT '|| l_select_cols ||'
FROM po_req_headers_ext_b
WHERE requisition_header_id = (SELECT prh.requisition_header_id
FROM po_requisition_headers_all prh
WHERE prh.conformed_header_id= '||p_confirmed_id||
'AND prh.revision_num='||(p_revision_num-1)||')
and attr_group_id = '|| g_address_attr_group_id ||'
ORDER BY C_EXT_ATTR39';
sql_stmt2:= 'SELECT '|| l_select_cols ||'
FROM po_req_headers_ext_b
WHERE requisition_header_id = (SELECT prh.requisition_header_id
FROM po_requisition_headers_all prh
WHERE prh.conformed_header_id= '||p_confirmed_id||
'AND prh.revision_num='||(p_revision_num)||')
and attr_group_id = '|| g_address_attr_group_id ||'
ORDER BY C_EXT_ATTR39';
SELECT database_view_name,
template_id
INTO x_database_view_name,
x_template_id
FROM po_uda_ag_templates
WHERE template_id = (SELECT prh.uda_template_id
FROM po_requisition_headers_all prh
WHERE prh.conformed_header_id= p_confirmed_id
AND prh.revision_num=p_revision_num);
select prh.org_id into g_org_id from po_requisition_headers_all prh WHERE prh.conformed_header_id= p_confirmed_id
AND prh.revision_num=p_revision_num ;
sql_stmt1:='SELECT temp.*
FROM '||x_database_view_name||' temp
WHERE temp.requisition_header_id IN (SELECT prh.requisition_header_id
FROM po_requisition_headers_all prh
WHERE prh.conformed_header_id= '||p_confirmed_id||
'AND prh.revision_num='||(p_revision_num-1)||')';
sql_stmt2:='SELECT temp.*
FROM '||x_database_view_name||' temp
WHERE temp.requisition_header_id IN (SELECT prh.requisition_header_id
FROM po_requisition_headers_all prh
WHERE prh.conformed_header_id= '||p_confirmed_id||
'AND prh.revision_num='||p_revision_num||')';
SELECT database_view_name
INTO x_database_view_name
FROM po_uda_ag_templates
WHERE template_id = (SELECT Max(prl.uda_template_id)
FROM po_requisition_lines_all prl,
po_requisition_headers_all prh
WHERE prh.conformed_header_id= p_confirmed_id
AND prh.revision_num=p_revision_num
AND prh.requisition_header_id=prl.requisition_header_id);
sql_stmt1:='SELECT prla.line_num, prla.LINE_NUM_DISPLAY,
temp.*
FROM '||x_database_view_name||' temp,
po_requisition_lines_all prla
WHERE temp.requisition_line_id IN (SELECT prl.requisition_line_id
FROM po_requisition_lines_all prl,
po_requisition_headers_all prh
WHERE prh.conformed_header_id= '||p_confirmed_id||
'AND prh.revision_num='||(p_revision_num-1)||
'AND prh.requisition_header_id=prl.requisition_header_id)
AND prla.requisition_line_id = temp.requisition_line_id';
sql_stmt2:='SELECT prla.line_num, prla.LINE_NUM_DISPLAY,
temp.*
FROM '||x_database_view_name||' temp,
po_requisition_lines_all prla
WHERE temp.requisition_line_id IN (SELECT prl.requisition_line_id
FROM po_requisition_lines_all prl,
po_requisition_headers_all prh
WHERE prh.conformed_header_id= '||p_confirmed_id||
'AND prh.revision_num='||p_revision_num||
'AND prh.requisition_header_id=prl.requisition_header_id)
AND prla.requisition_line_id = temp.requisition_line_id';
SELECT ags.ATTR_GROUP_NAME
INTO x_doc_number_attr_group_name
FROM PO_UDA_AG_TEMPLATE_USAGES usages, ego_attr_groups_v ags
WHERE usages.template_id = x_template_id
AND usages.ATTRIBUTE1='Base Document'
AND usages.ATTRIBUTE2='NA'
-- bug 14248325 Attribute3 needs to be added here as there may be records that have org_id as well as DEFAULT values in Attribute3 column
AND usages.ATTRIBUTE3=To_Char(g_org_id)
AND usages.ATTRIBUTE_CATEGORY='DOCUMENT_NUMBERING'
AND usages.attribute_group_id = ags.attr_group_id;
SELECT ags.ATTR_GROUP_NAME
INTO x_doc_number_attr_group_name
FROM PO_UDA_AG_TEMPLATE_USAGES usages, ego_attr_groups_v ags
WHERE usages.template_id = x_template_id
AND usages.ATTRIBUTE1='Base Document'
AND usages.ATTRIBUTE2='NA'
AND usages.ATTRIBUTE3='DEFAULT'
AND usages.ATTRIBUTE_CATEGORY='DOCUMENT_NUMBERING'
AND usages.attribute_group_id = ags.attr_group_id;
SELECT displayed_field
INTO x_address_type
FROM po_lookup_codes
WHERE lookup_code = x_lookup_code
AND lookup_type='PR_UDA_ADDRESS_TYPES';
SELECT attr_display_name
INTO x_attr_display_name
FROM ego_attrs_v
WHERE attr_group_type = 'PO_REQ_HEADER_EXT_ATTRS'
AND attr_group_name = g_address_group_name
AND database_column = x_database_column;
insert_changes(p_sequence_num,p_confirmed_id);
l_log_msg := v_progress||': After inserting into temp tabl';
SELECT icx_po_history_details_s.nextval
INTO p_sequence_num
FROM DUAL;
SELECT Max(revision_num)
INTO v_revision_counter
FROM po_requisition_headers_all
WHERE conformed_header_id=p_confirmed_id;
po_num_tab.delete(1,po_num_tab.Count);
line_num_tab.delete(1,line_num_tab.Count);
distribution_num_tab.delete(1,distribution_num_tab.Count);
level_altered_tab.delete(1,level_altered_tab.Count);
field_altered_tab.delete(1,field_altered_tab.Count);
changes_from_tab.delete(1,changes_from_tab.Count);
changes_to_tab.delete(1,changes_to_tab.Count);
line_num_disp_tab.delete(1,line_num_disp_tab.Count);
* Delete records from the temp table, ICX_PO_REVISIONS_TEMP, where
* all the records for differences are stored.
*
* ARGUMENTS
* sequenceNumber Sequence number to identify the comparison
* results for a specific record in
* ICX_PO_REVISIONS_TEMP table.
*
* NOTES
*
* HISTORY
* 15 -07-2009 bisdas Created
********************************************************************/
PROCEDURE purge(sequenceNumber IN NUMBER)
AS
v_progress VARCHAR2(3);
DELETE FROM icx_po_revisions_temp
WHERE line_seq = sequenceNumber;
* insert_changes
*
* PURPOSE
* Insert the comparison result into the temp table.
*
* ARGUMENTS
* p_line_seq Sequence number to identify the comparison
* results for a specific record.
* p_confirmed_id Confirmed Id.
*
*
* HISTORY
* 13-07-2009 bisdas Created
*
********************************************************************/
PROCEDURE insert_changes(p_line_seq NUMBER,p_confirmed_id NUMBER)
AS
v_progress VARCHAR2(3);
l_procedure_name VARCHAR2(100) := 'insert_changes';
INSERT INTO
icx_po_revisions_temp(
header_id,
line_seq,
creation_date,
po_num,
revision_num,
line_num,
distribution_num,
level_altered,
item_id,
field_altered,
changes_from,
changes_to,
line_num_display
)
VALUES
(
p_confirmed_id,
p_line_seq,
SYSDATE,
po_num_tab(i),
revision_num_tab(i),
line_num_tab(i),
distribution_num_tab(i),
level_altered_tab(i),
item_id_tab(i),
field_altered_tab(i),
changes_from_tab(i),
changes_to_tab(i),
line_num_disp_tab(i)
);
l_log_msg := v_progress||' : Exception at procedure insert_changes: '|| SQLERRM;
END insert_changes;
SELECT msi.concatenated_segments
INTO id
FROM mtl_system_items_kfv msi,
financials_system_parameters fsp
WHERE p_id = msi.inventory_item_id (+)
AND fsp.inventory_organization_id = NVL(msi.organization_id,fsp.inventory_organization_id );
SELECT vendor_name
INTO id
FROM po_vendors
WHERE vendor_id = p_id;
SELECT vendor_site_code
INTO id
FROM po_vendor_sites_all
WHERE vendor_site_id = p_id;
SELECT description
INTO id
FROM mtl_categories_tl
WHERE category_id=p_id
AND source_lang=USERENV('LANG');
SELECT name
INTO id
FROM pa_projects_all
WHERE project_id=p_id;
SELECT task_number
INTO id
FROM pa_tasks_expend_v
WHERE task_id=p_id;
SELECT line_num_display INTO id FROM po_requisition_lines_all WHERE requisition_line_id=p_id;
* Insert a line in the ICX_PO_REVISIONS_TEMP table indicating that
* there are no differences between the compared records.
*
* ARGUMENTS
* p_line_seq Sequence number to identify the comparison
* results for a specific record.
*
* NOTES
* Refer to bug#549414 for more details.
*
* This is used specifically to handle AK functionality. AK is
* expecting a row in table with the PK. The initial table in this
* case is actually a procedure, so AK fails. The procedure checks.
* If there are no differences, insert a dummy row in the table that
* say something like 'No differences.'
*
* HISTORY
* 13-07-09 Bisdas Created
********************************************************************/
PROCEDURE verify_no_differences( p_line_seq IN NUMBER,p_revision_num NUMBER, amend_num VARCHAR2) AS
records_exist number;
SELECT COUNT(*)
INTO records_exist
FROM icx_po_revisions_temp
WHERE line_seq = p_line_seq;
INSERT INTO
icx_po_revisions_temp(
header_id,
line_seq,
creation_date,
po_num,
revision_num,
line_num,
distribution_num,
level_altered,
field_altered,
changes_from,
changes_to,
line_num_display
)
VALUES
(
-999,
p_line_seq,
SYSDATE,
amend_num,
p_revision_num,
NULL,
NULL,
'HEADER',
fnd_message.get_String('ICX','ICX_NO_DIFFERENCE'),
NULL,
NULL,
NULL
);
sql_stmt1:='SELECT REQUISITION_HEADER_ID,
SEGMENT1,
nvl(CANCEL_FLAG,''N'') CANCEL_FLAG,
nvl(CLOSED_CODE,''OPEN'') CLOSED_CODE,
DESCRIPTION,
NOTE_TO_AUTHORIZER,
USSGL_TRANSACTION_CODE,
PCARD_ID,
REVISION_NUM,
AMENDMENT_STATUS,
AMENDMENT_TYPE
FROM po_requisition_headers_all
WHERE conformed_header_id='||p_confirmed_id||
' AND revision_num='||(p_revision_num -1);
sql_stmt2:='SELECT REQUISITION_HEADER_ID,
SEGMENT1,
nvl(CANCEL_FLAG,''N'') CANCEL_FLAG,
nvl(CLOSED_CODE,''OPEN'') CLOSED_CODE,
DESCRIPTION,
NOTE_TO_AUTHORIZER,
USSGL_TRANSACTION_CODE,
PCARD_ID,
REVISION_NUM,
AMENDMENT_STATUS,
AMENDMENT_TYPE
FROM po_requisition_headers_all
WHERE conformed_header_id='||p_confirmed_id||
' AND revision_num='||p_revision_num;
sql_stmt1:= 'SELECT REQUISITION_LINE_ID,LINE_NUM, LINE_NUM_DISPLAY,nvl(prl.CANCEL_FLAG,''N'') CANCEL_FLAG, nvl(prl.CLOSED_CODE,''OPEN'') CLOSED_CODE,ITEM_ID,ITEM_REVISION,
ITEM_DESCRIPTION, CATEGORY_ID, UNIT_MEAS_LOOKUP_CODE, UNIT_PRICE,QUANTITY,DELIVER_TO_LOCATION_ID,TO_PERSON_ID,
SUGGESTED_BUYER_ID,to_char(NEED_BY_DATE,''DD-MON-YYYY HH24:MI:SS'') NEED_BY_DATE,JUSTIFICATION,NOTE_TO_AGENT,NOTE_TO_RECEIVER,CURRENCY_CODE,RATE_TYPE,to_char(RATE_DATE,''DD-MON-YYYY HH24:MI:SS'') RATE_DATE,
RATE,CURRENCY_UNIT_PRICE,SUGGESTED_VENDOR_NAME,SUGGESTED_VENDOR_LOCATION,SUGGESTED_VENDOR_CONTACT,SUGGESTED_VENDOR_PHONE,
SUGGESTED_VENDOR_PRODUCT_CODE,HAZARD_CLASS_ID,DESTINATION_TYPE_CODE,DESTINATION_ORGANIZATION_ID, DESTINATION_SUBINVENTORY,
VENDOR_ID,VENDOR_SITE_ID, VENDOR_CONTACT_ID,prl.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE, MANUFACTURER_NAME,
MANUFACTURER_PART_NUMBER, REQUESTER_EMAIL,REQUESTER_FAX,REQUESTER_PHONE, UNSPSC_CODE, SUPPLIER_DUNS,AMOUNT,
CURRENCY_AMOUNT,JOB_LONG_DESCRIPTION,SUGGESTED_VENDOR_CONTACT_FAX,SUGGESTED_VENDOR_CONTACT_EMAIL,CANDIDATE_FIRST_NAME,
CANDIDATE_LAST_NAME,to_char(ASSIGNMENT_START_DATE,''DD-MON-YYYY HH24:MI:SS'') ASSIGNMENT_START_DATE,prl.AMENDMENT_STATUS,prl.AMENDMENT_TYPE,CONTRACT_TYPE,COST_CONSTRAINT,
to_char(CLM_PERIOD_PERF_START_DATE,''DD-MON-YYYY HH24:MI:SS'') CLM_PERIOD_PERF_START_DATE,to_char(CLM_PERIOD_PERF_END_DATE,''DD-MON-YYYY HH24:MI:SS'') CLM_PERIOD_PERF_END_DATE,
nvl(CLM_OPTION_EXERCISED,''N'') CLM_OPTION_EXERCISED, CLM_OPTION_FROM_DATE, CLM_OPTION_TO_DATE, FUND_SOURCE_NOT_KNOWN,CLM_OPTION_INDICATOR,CLM_BASE_LINE_NUM
FROM po_requisition_lines_all prl,
po_requisition_headers_all prh
WHERE prh.conformed_header_id='||p_confirmed_id||
'AND prh.requisition_header_id=prl.requisition_header_id
AND prh.revision_num='||(p_revision_num -1)||
'ORDER BY LINE_NUM';
sql_stmt2:='SELECT REQUISITION_LINE_ID,LINE_NUM, LINE_NUM_DISPLAY,nvl(prl.CANCEL_FLAG,''N'') CANCEL_FLAG, nvl(prl.CLOSED_CODE,''OPEN'') CLOSED_CODE,ITEM_ID,ITEM_REVISION,
ITEM_DESCRIPTION, CATEGORY_ID, UNIT_MEAS_LOOKUP_CODE, UNIT_PRICE,QUANTITY,DELIVER_TO_LOCATION_ID,TO_PERSON_ID,
SUGGESTED_BUYER_ID,to_char(NEED_BY_DATE,''DD-MON-YYYY HH24:MI:SS'') NEED_BY_DATE,JUSTIFICATION,NOTE_TO_AGENT,NOTE_TO_RECEIVER,CURRENCY_CODE,RATE_TYPE,to_char(RATE_DATE,''DD-MON-YYYY HH24:MI:SS'') RATE_DATE,
RATE,CURRENCY_UNIT_PRICE,SUGGESTED_VENDOR_NAME,SUGGESTED_VENDOR_LOCATION,SUGGESTED_VENDOR_CONTACT,SUGGESTED_VENDOR_PHONE,
SUGGESTED_VENDOR_PRODUCT_CODE,HAZARD_CLASS_ID,DESTINATION_TYPE_CODE,DESTINATION_ORGANIZATION_ID, DESTINATION_SUBINVENTORY,
VENDOR_ID,VENDOR_SITE_ID, VENDOR_CONTACT_ID,prl.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE, MANUFACTURER_NAME,
MANUFACTURER_PART_NUMBER, REQUESTER_EMAIL,REQUESTER_FAX,REQUESTER_PHONE, UNSPSC_CODE, SUPPLIER_DUNS,AMOUNT,
CURRENCY_AMOUNT,JOB_LONG_DESCRIPTION,SUGGESTED_VENDOR_CONTACT_FAX,SUGGESTED_VENDOR_CONTACT_EMAIL,CANDIDATE_FIRST_NAME,
CANDIDATE_LAST_NAME,to_char(ASSIGNMENT_START_DATE,''DD-MON-YYYY HH24:MI:SS'') ASSIGNMENT_START_DATE,prl.AMENDMENT_STATUS,prl.AMENDMENT_TYPE,CONTRACT_TYPE,COST_CONSTRAINT,
to_char(CLM_PERIOD_PERF_START_DATE,''DD-MON-YYYY HH24:MI:SS'') CLM_PERIOD_PERF_START_DATE,to_char(CLM_PERIOD_PERF_END_DATE,''DD-MON-YYYY HH24:MI:SS'') CLM_PERIOD_PERF_END_DATE,
nvl(CLM_OPTION_EXERCISED,''N'') CLM_OPTION_EXERCISED, CLM_OPTION_FROM_DATE, CLM_OPTION_TO_DATE, FUND_SOURCE_NOT_KNOWN,CLM_OPTION_INDICATOR,CLM_BASE_LINE_NUM
FROM po_requisition_lines_all prl,
po_requisition_headers_all prh
WHERE prh.conformed_header_id='||p_confirmed_id||
'AND prh.requisition_header_id=prl.requisition_header_id
AND prh.revision_num='||p_revision_num||
'ORDER BY LINE_NUM';
sql_stmt1:='SELECT DISTRIBUTION_ID,
prl.LINE_NUM LINE_NUM,
prl.LINE_NUM_DISPLAY LINE_NUM_DISPLAY,
DISTRIBUTION_NUM,
nvl(prl.CANCEL_FLAG,''N'') LINE_CANCEL_FLAG,
prl.ITEM_ID LINE_ITEM_ID,
CODE_COMBINATION_ID,
REQ_LINE_QUANTITY,
to_char(GL_ENCUMBERED_DATE,''DD-MON-YYYY HH24:MI:SS'') GL_ENCUMBERED_DATE,
prd.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
PROJECT_ID,
TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ORGANIZATION_ID,
to_char(EXPENDITURE_ITEM_DATE,''DD-MON-YYYY HH24:MI:SS'') EXPENDITURE_ITEM_DATE,
nvl(RECOVERABLE_TAX,0) RECOVERABLE_TAX,
nvl(NONRECOVERABLE_TAX,0) NONRECOVERABLE_TAX,
nvl(RECOVERY_RATE,0) RECOVERY_RATE,
REQ_LINE_AMOUNT,
REQ_LINE_CURRENCY_AMOUNT,
prd.AMENDMENT_STATUS,
prd.AMENDMENT_TYPE,
PARTIAL_FUNDED_FLAG,
FUNDED_VALUE,
QUANTITY_FUNDED,
AMOUNT_FUNDED
FROM po_req_distributions_all prd,
po_requisition_lines_all prl,
po_requisition_headers_all prh
WHERE prh.conformed_header_id='||p_confirmed_id||
'AND prh.requisition_header_id=prl.requisition_header_id
AND prl.requisition_line_id=nvl(prd.info_line_id,prd.requisition_line_id)
AND revision_num='||(p_revision_num -1)||
'ORDER BY nvl(prd.info_line_id,prd.requisition_line_id),DISTRIBUTION_NUM' ;
sql_stmt2:='SELECT DISTRIBUTION_ID,
prl.LINE_NUM LINE_NUM,
prl.LINE_NUM_DISPLAY LINE_NUM_DISPLAY,
DISTRIBUTION_NUM,
nvl(prl.CANCEL_FLAG,''N'') LINE_CANCEL_FLAG,
prl.ITEM_ID LINE_ITEM_ID,
CODE_COMBINATION_ID,
REQ_LINE_QUANTITY,
to_char(GL_ENCUMBERED_DATE,''DD-MON-YYYY HH24:MI:SS'') GL_ENCUMBERED_DATE,
prd.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
PROJECT_ID,
TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ORGANIZATION_ID,
to_char(EXPENDITURE_ITEM_DATE,''DD-MON-YYYY HH24:MI:SS'') EXPENDITURE_ITEM_DATE,
nvl(RECOVERABLE_TAX,0) RECOVERABLE_TAX,
nvl(NONRECOVERABLE_TAX,0) NONRECOVERABLE_TAX,
nvl(RECOVERY_RATE,0) RECOVERY_RATE,
REQ_LINE_AMOUNT,
REQ_LINE_CURRENCY_AMOUNT,
prd.AMENDMENT_STATUS,
prd.AMENDMENT_TYPE,
PARTIAL_FUNDED_FLAG,
FUNDED_VALUE,
QUANTITY_FUNDED,
AMOUNT_FUNDED
FROM po_req_distributions_all prd,
po_requisition_lines_all prl,
po_requisition_headers_all prh
WHERE prh.conformed_header_id='||p_confirmed_id||
'AND prh.requisition_header_id=prl.requisition_header_id
AND prl.requisition_line_id=nvl(prd.info_line_id,prd.requisition_line_id)
AND revision_num='||p_revision_num||
'ORDER BY nvl(prd.info_line_id,prd.requisition_line_id),DISTRIBUTION_NUM';
UPDATE po_requisition_headers_all
SET amendment_status = 'N',
amendment_type = 'CHANGED'
WHERE requisition_header_id=header_id;
UPDATE po_requisition_headers_all
SET amendment_type=old_amend_type,
amendment_status = 'P'
WHERE requisition_header_id=header_id
AND amendment_status NOT IN ('A','R');
UPDATE po_requisition_lines_all
SET amendment_status = 'N',
amendment_type = 'CHANGED'
WHERE requisition_line_id=line_id;
UPDATE po_requisition_lines_all
SET amendment_status = 'N',
amendment_type = 'ADDED'
WHERE requisition_line_id=line_id;
UPDATE po_requisition_lines_all
SET amendment_type=old_amend_type,
amendment_status = 'P'
WHERE requisition_line_id=line_id
AND amendment_status NOT IN ('A','R');
UPDATE po_req_distributions_all
SET amendment_status = 'N',
amendment_type = 'CHANGED'
WHERE distribution_id=dist_id;
UPDATE po_requisition_lines_all
SET amendment_status = 'N',
amendment_type = 'CHANGED'
WHERE requisition_line_id=(SELECT Nvl(info_line_id,requisition_line_id)
FROM po_req_distributions_all
WHERE distribution_id=dist_id);
UPDATE po_req_distributions_all
SET amendment_status = 'N',
amendment_type = 'ADDED'
WHERE distribution_id=dist_id;
SELECT amendment_type,amendment_status
INTO l_amendment_type, l_amendment_status
FROM po_requisition_lines_all
WHERE requisition_line_id=(SELECT Nvl(info_line_id,requisition_line_id)
FROM po_req_distributions_all
WHERE distribution_id=dist_id);
UPDATE po_requisition_lines_all
SET amendment_status = new_amendment_status,
amendment_type = new_amendment_type
WHERE requisition_line_id=(SELECT Nvl(info_line_id,requisition_line_id)
FROM po_req_distributions_all
WHERE distribution_id=dist_id);
UPDATE po_req_distributions_all
SET amendment_type=old_amend_type,
amendment_status = 'P'
WHERE distribution_id=dist_id
AND amendment_status NOT IN ('A','R');
* update_amendment
*
* PURPOSE
* This is the wrapper for set_amendment_status to update the amendment_status
* and amendment_type.
*
* ARGUMENTS
* p_confirmed_id
* p_revision_num
*
* NOTES
*
*
* HISTORY
* 19-07-09 Bisdas Created
********************************************************************/
PROCEDURE update_amendment(p_confirmed_id NUMBER, p_revision_num NUMBER)
AS
amend_num VARCHAR2(100);
l_procedure_name VARCHAR2(100) := 'update_amendment';
l_log_msg := v_progress||' : Update completes';
l_log_msg := v_progress||' : Exception at procedure update_amendment : '|| SQLERRM;
END update_amendment;
SELECT Max(Nvl(revision_num,0))+1 FROM po_requisition_headers_all
WHERE conformed_header_id = l_header_id;
SELECT LPad(x_revision_num ,3,'0') INTO l_val FROM dual;
SELECT Count(*) INTO l_Cnt
FROM po_requisition_headers_all
WHERE conformed_header_id = l_req_header_id
AND AUTHORIZATION_STATUS NOT IN ('APPROVED','REJECTED','RETURNED','CANCELLED');
USING (SELECT *
FROM po_requisition_headers_all
WHERE conformed_header_id = '||p_confirmed_id||'
AND revision_num = '||p_revision_num||') source
ON (target.requisition_header_id = '||p_confirmed_id||'
AND target.revision_num IS NULL
AND target.requisition_header_id = source.conformed_header_id)
WHEN MATCHED
THEN
UPDATE
SET target.CLOSED_CODE = source.CLOSED_CODE,
target.DESCRIPTION = source.DESCRIPTION,
target.NOTE_TO_AUTHORIZER = source.NOTE_TO_AUTHORIZER,
target.CANCEL_FLAG = source.CANCEL_FLAG,
target.USSGL_TRANSACTION_CODE = source.USSGL_TRANSACTION_CODE,
target.PCARD_ID = source.PCARD_ID,
target.AMENDMENT_TYPE = source.AMENDMENT_TYPE,
target.AMENDMENT_STATUS = source.AMENDMENT_STATUS,
target.AMENDMENT_REASON = source.AMENDMENT_REASON';
SELECT requisition_header_id
INTO req_header_id
FROM po_requisition_headers_all
WHERE conformed_header_id = p_confirmed_id
AND revision_num = p_revision_num;
fnd_attached_documents2_pkg.delete_attachments('REQ_HEADERS',
''||p_confirmed_id,
'',
'',
'',
'',
'Y',
'');
SELECT po_req_distributions_s.NEXTVAL
INTO x_new_dist_id
FROM dual;
INSERT INTO po_req_distributions_all
(distribution_id,
last_update_date,
last_updated_by,
requisition_line_id,
set_of_books_id,
code_combination_id,
req_line_quantity,
last_update_login,
creation_date,
created_by,
encumbered_flag,
gl_encumbered_date,
gl_encumbered_period_name,
gl_cancelled_date,
failed_funds_lookup_code,
encumbered_amount,
budget_account_id,
accrual_account_id,
variance_account_id,
prevent_encumbrance_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
ussgl_transaction_code,
government_context,
request_id,
program_application_id,
program_id,
program_update_date,
project_id,
task_id,
expenditure_type,
project_accounting_context,
expenditure_organization_id,
gl_closed_date,
source_req_distribution_id,
distribution_num,
project_related_flag,
expenditure_item_date,
org_id,
allocation_type,
allocation_value,
award_id,
end_item_unit_number,
recoverable_tax,
nonrecoverable_tax,
recovery_rate,
tax_recovery_override_flag,
oke_contract_line_id,
oke_contract_deliverable_id,
req_line_amount,
req_line_currency_amount,
req_award_id,
event_id,
amendment_type,
amendment_status,
info_line_id,
partial_funded_flag,
funded_value,
quantity_funded,
amount_funded,
change_in_funded_value, -- <>
clm_misc_loa,
clm_defence_funding,
clm_fms_case_number,
clm_agency_acct_identifier,
acrn)
SELECT
x_new_dist_id,
source.last_update_date,
source.last_updated_by,
p_to_req_line_id,
source.set_of_books_id,
source.code_combination_id,
source.req_line_quantity,
source.last_update_login,
source.creation_date,
source.created_by,
source.encumbered_flag,
source.gl_encumbered_date,
source.gl_encumbered_period_name,
source.gl_cancelled_date,
source.failed_funds_lookup_code,
source.encumbered_amount,
source.budget_account_id,
source.accrual_account_id,
source.variance_account_id,
source.prevent_encumbrance_flag,
source.attribute_category,
source.attribute1,
source.attribute2,
source.attribute3,
source.attribute4,
source.attribute5,
source.attribute6,
source.attribute7,
source.attribute8,
source.attribute9,
source.attribute10,
source.attribute11,
source.attribute12,
source.attribute13,
source.attribute14,
source.attribute15,
source.ussgl_transaction_code,
source.government_context,
source.request_id,
source.program_application_id,
source.program_id,
source.program_update_date,
source.project_id,
source.task_id,
source.expenditure_type,
source.project_accounting_context,
source.expenditure_organization_id,
source.gl_closed_date,
source.source_req_distribution_id,
source.distribution_num,
source.project_related_flag,
source.expenditure_item_date,
source.org_id,
source.allocation_type,
source.allocation_value,
source.award_id,
source.end_item_unit_number,
source.recoverable_tax,
source.nonrecoverable_tax,
source.recovery_rate,
source.tax_recovery_override_flag,
source.oke_contract_line_id,
source.oke_contract_deliverable_id,
source.req_line_amount,
source.req_line_currency_amount,
source.req_award_id,
source.event_id,
source.amendment_type,
source.amendment_status,
p_info_line_id,
source.partial_funded_flag,
source.funded_value,
source.quantity_funded,
source.amount_funded,
source.change_in_funded_value, -- <>
source.clm_misc_loa,
source.clm_defence_funding,
source.clm_fms_case_number,
source.clm_agency_acct_identifier,
source.acrn
FROM po_req_distributions_all source
WHERE distribution_id = p_from_dist_id;
PROCEDURE update_amd_dist_on_conf_copy (p_from_dist_id IN NUMBER,
p_to_dist_id IN NUMBER)
IS
l_procedure_name VARCHAR2(100) := 'update_amd_dist_on_conf_copy';
USING (SELECT * FROM po_req_distributions_all
WHERE distribution_id = p_from_dist_id) source
ON ( target.distribution_id = p_to_dist_id
AND source.CONFORMED_DIST_ID = target.distribution_id
)
WHEN MATCHED
THEN
UPDATE
SET target.CODE_COMBINATION_ID = source.CODE_COMBINATION_ID,
target.REQ_LINE_QUANTITY = source.REQ_LINE_QUANTITY,
target.GL_ENCUMBERED_DATE = source.GL_ENCUMBERED_DATE,
target.USSGL_TRANSACTION_CODE = source.USSGL_TRANSACTION_CODE,
target.PROJECT_ID = source.PROJECT_ID,
target.TASK_ID = source.TASK_ID,
target.EXPENDITURE_TYPE = source.EXPENDITURE_TYPE,
target.EXPENDITURE_ORGANIZATION_ID = source.EXPENDITURE_ORGANIZATION_ID,
target.EXPENDITURE_ITEM_DATE = source.EXPENDITURE_ITEM_DATE,
target.RECOVERABLE_TAX = source.RECOVERABLE_TAX,
target.NONRECOVERABLE_TAX = source.NONRECOVERABLE_TAX,
target.RECOVERY_RATE = source.RECOVERY_RATE,
target.REQ_LINE_AMOUNT = source.REQ_LINE_AMOUNT,
target.REQ_LINE_CURRENCY_AMOUNT = source.REQ_LINE_CURRENCY_AMOUNT,
target.AMENDMENT_TYPE = source.AMENDMENT_TYPE,
target.AMENDMENT_STATUS = source.AMENDMENT_STATUS,
target.ALLOCATION_VALUE = source.ALLOCATION_VALUE,
-- <>
target.partial_funded_flag = source.partial_funded_flag,
target.funded_value = source.funded_value,
target.quantity_funded = source.quantity_funded,
target.amount_funded = source.amount_funded,
target.change_in_funded_value = Nvl(source.change_in_funded_value,0) ;
l_log_msg := ' : Exception at update_amd_dist_on_conf_copy: '|| SQLERRM;
END update_amd_dist_on_conf_copy;
SELECT distribution_id, requisition_line_id, conformed_dist_id, info_line_id
BULK COLLECT INTO l_distribution_ids, l_req_line_id, l_conformed_dist_ids, l_info_line_id
FROM po_req_distributions_all
WHERE requisition_line_id IN
(SELECT requisition_line_id
FROM po_requisition_lines_all prla,
po_requisition_headers_all prha
WHERE prha.conformed_header_id = p_confirmed_id
AND prha.revision_num = p_revision_num
AND prha.requisition_header_id = prla.requisition_header_id);
update_amd_dist_on_conf_copy(l_distribution_ids(i), l_conformed_dist_ids(i));
PROCEDURE update_amd_line_on_conf_copy (p_from_line_id IN NUMBER,
p_to_line_id IN NUMBER,
p_clm_base_line_num IN NUMBER DEFAULT NULL)
IS
l_procedure_name VARCHAR2(100) := 'update_amd_line_on_conf_copy';
(SELECT * FROM po_requisition_lines_all
WHERE requisition_line_id = p_from_line_id) source
ON ( target.requisition_line_id = p_to_line_id
AND source.conformed_line_id = target.requisition_line_id
)
WHEN MATCHED
THEN
UPDATE
SET target.DELIVER_TO_LOCATION_ID = source.DELIVER_TO_LOCATION_ID,
target.TO_PERSON_ID = source.TO_PERSON_ID,
target.ITEM_DESCRIPTION = source.ITEM_DESCRIPTION,
target.CATEGORY_ID = source.CATEGORY_ID,
target.UNIT_MEAS_LOOKUP_CODE = source.UNIT_MEAS_LOOKUP_CODE,
target.UNIT_PRICE = source.UNIT_PRICE,
target.QUANTITY = source.QUANTITY,
target.ITEM_ID = source.ITEM_ID,
target.ITEM_REVISION = source.ITEM_REVISION,
target.SUGGESTED_BUYER_ID = source.SUGGESTED_BUYER_ID,
target.NEED_BY_DATE = source.NEED_BY_DATE,
target.JUSTIFICATION = source.JUSTIFICATION,
target.NOTE_TO_AGENT = source.NOTE_TO_AGENT,
target.NOTE_TO_RECEIVER = source.NOTE_TO_RECEIVER,
target.CURRENCY_CODE = source.CURRENCY_CODE,
target.RATE_TYPE = source.RATE_TYPE,
target.RATE_DATE = source.RATE_DATE,
target.RATE = source.RATE,
target.CURRENCY_UNIT_PRICE = source.CURRENCY_UNIT_PRICE,
target.SUGGESTED_VENDOR_NAME = source.SUGGESTED_VENDOR_NAME,
target.SUGGESTED_VENDOR_LOCATION = source.SUGGESTED_VENDOR_LOCATION,
target.SUGGESTED_VENDOR_CONTACT = source.SUGGESTED_VENDOR_CONTACT,
target.SUGGESTED_VENDOR_PHONE = source.SUGGESTED_VENDOR_PHONE,
target.SUGGESTED_VENDOR_PRODUCT_CODE = source.SUGGESTED_VENDOR_PRODUCT_CODE,
target.HAZARD_CLASS_ID = source.HAZARD_CLASS_ID,
target.CANCEL_FLAG = source.CANCEL_FLAG,
target.DESTINATION_TYPE_CODE = source.DESTINATION_TYPE_CODE,
target.DESTINATION_ORGANIZATION_ID = source.DESTINATION_ORGANIZATION_ID,
target.DESTINATION_SUBINVENTORY = source.DESTINATION_SUBINVENTORY,
target.CLOSED_CODE = source.CLOSED_CODE,
target.VENDOR_ID = source.VENDOR_ID,
target.VENDOR_SITE_ID = source.VENDOR_SITE_ID,
target.VENDOR_CONTACT_ID = source.VENDOR_CONTACT_ID,
target.USSGL_TRANSACTION_CODE = source.USSGL_TRANSACTION_CODE,
target.MANUFACTURER_NAME = source.MANUFACTURER_NAME,
target.MANUFACTURER_PART_NUMBER = source.MANUFACTURER_PART_NUMBER,
target.REQUESTER_EMAIL = source.REQUESTER_EMAIL,
target.REQUESTER_FAX = source.REQUESTER_FAX,
target.REQUESTER_PHONE = source.REQUESTER_PHONE,
target.UNSPSC_CODE = source.UNSPSC_CODE,
target.SUPPLIER_DUNS = source.SUPPLIER_DUNS,
target.AMOUNT = source.AMOUNT,
target.CURRENCY_AMOUNT = source.CURRENCY_AMOUNT,
target.JOB_LONG_DESCRIPTION = source.JOB_LONG_DESCRIPTION,
target.SUGGESTED_VENDOR_CONTACT_FAX = source.SUGGESTED_VENDOR_CONTACT_FAX,
target.SUGGESTED_VENDOR_CONTACT_EMAIL = source.SUGGESTED_VENDOR_CONTACT_EMAIL,
target.CANDIDATE_FIRST_NAME = source.CANDIDATE_FIRST_NAME,
target.CANDIDATE_LAST_NAME = source.CANDIDATE_LAST_NAME,
target.ASSIGNMENT_START_DATE = source.ASSIGNMENT_START_DATE,
target.AMENDMENT_TYPE = source.AMENDMENT_TYPE,
target.AMENDMENT_STATUS = source.AMENDMENT_STATUS,
target.CONTRACT_TYPE = source.CONTRACT_TYPE,
target.COST_CONSTRAINT = source.COST_CONSTRAINT,
target.CLM_PERIOD_PERF_START_DATE = source.CLM_PERIOD_PERF_START_DATE,
target.CLM_PERIOD_PERF_END_DATE = source.CLM_PERIOD_PERF_END_DATE,
target.CLM_OPTION_EXERCISED = source.CLM_OPTION_EXERCISED,
target.CLM_OPTION_FROM_DATE = source.CLM_OPTION_FROM_DATE,
target.CLM_OPTION_TO_DATE = source.CLM_OPTION_TO_DATE,
target.CLM_FUNDED_FLAG = source.CLM_FUNDED_FLAG,
target.CLM_OPTION_INDICATOR = source.CLM_OPTION_INDICATOR,
target.FUND_SOURCE_NOT_KNOWN = source.FUND_SOURCE_NOT_KNOWN,
target.clm_base_line_num = p_clm_base_line_num,
target.clm_option_num = source.clm_option_num,
target.CLM_MIPR_OBLIGATION_TYPE = source.CLM_MIPR_OBLIGATION_TYPE;
l_log_msg := ' : Exception at update_amd_line_on_conf_copy : '|| SQLERRM;
END update_amd_line_on_conf_copy;
SELECT po_requisition_lines_s.NEXTVAL
INTO x_new_line_id
FROM dual;
INSERT INTO po_requisition_lines_all
(requisition_line_id,
requisition_header_id,
line_num,
line_type_id,
category_id,
item_description,
unit_meas_lookup_code,
unit_price,
quantity,
deliver_to_location_id,
to_person_id,
last_update_date,
last_updated_by,
source_type_code,
last_update_login,
creation_date,
created_by,
item_id,
item_revision,
quantity_delivered,
suggested_buyer_id,
encumbered_flag,
rfq_required_flag,
need_by_date,
line_location_id,
modified_by_agent_flag,
parent_req_line_id,
justification,
note_to_agent,
note_to_receiver,
purchasing_agent_id,
document_type_code,
blanket_po_header_id,
blanket_po_line_num,
currency_code,
rate_type,
rate_date,
rate,
currency_unit_price,
suggested_vendor_name,
suggested_vendor_location,
suggested_vendor_contact,
suggested_vendor_phone,
suggested_vendor_product_code,
un_number_id,
hazard_class_id,
must_use_sugg_vendor_flag,
reference_num,
on_rfq_flag,
urgent_flag,
cancel_flag,
source_organization_id,
source_subinventory,
destination_type_code,
destination_organization_id,
destination_subinventory,
quantity_cancelled,
cancel_date,
cancel_reason,
closed_code,
agent_return_note,
changed_after_research_flag,
vendor_id,
vendor_site_id,
vendor_contact_id,
research_agent_id,
on_line_flag,
wip_entity_id,
wip_line_id,
wip_repetitive_schedule_id,
wip_operation_seq_num,
wip_resource_seq_num,
attribute_category,
destination_context,
inventory_source_context,
vendor_source_context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
bom_resource_id,
request_id,
program_application_id,
program_id,
program_update_date,
ussgl_transaction_code,
government_context,
closed_reason,
closed_date,
transaction_reason_code,
quantity_received,
source_req_line_id,
org_id,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
global_attribute_category,
kanban_card_id,
catalog_type,
catalog_source,
manufacturer_id,
manufacturer_name,
manufacturer_part_number,
requester_email,
requester_fax,
requester_phone,
unspsc_code ,
other_category_code,
supplier_duns,
tax_status_indicator,
pcard_flag,
new_supplier_flag,
auto_receive_flag,
tax_user_override_flag,
tax_code_id,
note_to_vendor,
oke_contract_version_id,
oke_contract_header_id,
item_source_id,
supplier_ref_number,
secondary_unit_of_measure,
secondary_quantity,
preferred_grade,
secondary_quantity_received,
secondary_quantity_cancelled,
vmi_flag,
auction_header_id,
auction_display_number,
auction_line_number,
reqs_in_pool_flag,
bid_number,
bid_line_number,
noncat_template_id,
suggested_vendor_contact_fax,
suggested_vendor_contact_email,
amount,
currency_amount,
labor_req_line_id,
job_id,
job_long_description,
contractor_status,
contact_information,
suggested_supplier_flag,
candidate_screening_reqd_flag,
assignment_end_date,
overtime_allowed_flag,
contractor_requisition_flag,
drop_ship_flag ,
candidate_first_name,
candidate_last_name,
assignment_start_date,
order_type_lookup_code,
purchase_basis,
matching_basis ,
negotiated_by_preparer_flag,
ship_method,
estimated_pickup_date,
supplier_notified_for_cancel,
base_unit_price,
at_sourcing_flag,
tax_attribute_update_code,
tax_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,
uda_template_id,
amendment_type,
amendment_status,
cost_constraint,
contract_type,
clm_period_perf_end_date,
clm_period_perf_start_date,
clm_extended_item_description,
clm_option_exercised,
is_line_num_display_modified,
fund_source_not_known,
clm_mipr_obligation_type )
SELECT
x_new_line_id,
p_to_req_hdr_id,
source.line_num,
source.line_type_id,
source.category_id,
source.item_description,
source.unit_meas_lookup_code,
source.unit_price,
source.quantity,
source.deliver_to_location_id,
source.to_person_id,
source.last_update_date,
source.last_updated_by,
source.source_type_code,
source.last_update_login,
source.creation_date,
source.created_by,
source.item_id,
source.item_revision,
source.quantity_delivered,
source.suggested_buyer_id,
source.encumbered_flag,
source.rfq_required_flag,
source.need_by_date,
source.line_location_id,
source.modified_by_agent_flag,
source.parent_req_line_id,
source.justification,
source.note_to_agent,
source.note_to_receiver,
source.purchasing_agent_id,
source.document_type_code,
source.blanket_po_header_id,
source.blanket_po_line_num,
source.currency_code,
source.rate_type,
source.rate_date,
source.rate,
source.currency_unit_price,
source.suggested_vendor_name,
source.suggested_vendor_location,
source.suggested_vendor_contact,
source.suggested_vendor_phone,
source.suggested_vendor_product_code,
source.un_number_id,
source.hazard_class_id,
source.must_use_sugg_vendor_flag,
source.reference_num,
source.on_rfq_flag,
source.urgent_flag,
source.cancel_flag,
source.source_organization_id,
source.source_subinventory,
source.destination_type_code,
source.destination_organization_id,
source.destination_subinventory,
source.quantity_cancelled,
source.cancel_date,
source.cancel_reason,
source.closed_code,
source.agent_return_note,
source.changed_after_research_flag,
source.vendor_id,
source.vendor_site_id,
source.vendor_contact_id,
source.research_agent_id,
source.on_line_flag,
source.wip_entity_id,
source.wip_line_id,
source.wip_repetitive_schedule_id,
source.wip_operation_seq_num,
source.wip_resource_seq_num,
source.attribute_category,
source.destination_context,
source.inventory_source_context,
source.vendor_source_context,
source.attribute1,
source.attribute2,
source.attribute3,
source.attribute4,
source.attribute5,
source.attribute6,
source.attribute7,
source.attribute8,
source.attribute9,
source.attribute10,
source.attribute11,
source.attribute12,
source.attribute13,
source.attribute14,
source.attribute15,
source.bom_resource_id,
source.request_id,
source.program_application_id,
source.program_id,
source.program_update_date,
source.ussgl_transaction_code,
source.government_context,
source.closed_reason,
source.closed_date,
source.transaction_reason_code,
source.quantity_received,
source.source_req_line_id,
source.org_id,
source.global_attribute1,
source.global_attribute2,
source.global_attribute3,
source.global_attribute4,
source.global_attribute5,
source.global_attribute6,
source.global_attribute7,
source.global_attribute8,
source.global_attribute9,
source.global_attribute10,
source.global_attribute11,
source.global_attribute12,
source.global_attribute13,
source.global_attribute14,
source.global_attribute15,
source.global_attribute16,
source.global_attribute17,
source.global_attribute18,
source.global_attribute19,
source.global_attribute20,
source.global_attribute_category,
source.kanban_card_id,
source.catalog_type,
source.catalog_source,
source.manufacturer_id,
source.manufacturer_name,
source.manufacturer_part_number,
source.requester_email,
source.requester_fax,
source.requester_phone,
source.unspsc_code ,
source.other_category_code,
source.supplier_duns,
source.tax_status_indicator,
source.pcard_flag,
source.new_supplier_flag,
source.auto_receive_flag,
source.tax_user_override_flag,
source.tax_code_id,
source.note_to_vendor,
source.oke_contract_version_id,
source.oke_contract_header_id,
source.item_source_id,
source.supplier_ref_number,
source.secondary_unit_of_measure,
source.secondary_quantity,
source.preferred_grade,
source.secondary_quantity_received,
source.secondary_quantity_cancelled,
source.vmi_flag,
source.auction_header_id,
source.auction_display_number,
source.auction_line_number,
'Y',
source.bid_number,
source.bid_line_number,
source.noncat_template_id,
source.suggested_vendor_contact_fax,
source.suggested_vendor_contact_email,
source.amount,
source.currency_amount,
source.labor_req_line_id,
source.job_id,
source.job_long_description,
source.contractor_status,
source.contact_information,
source.suggested_supplier_flag,
source.candidate_screening_reqd_flag,
source.assignment_end_date,
source.overtime_allowed_flag,
source.contractor_requisition_flag,
source.drop_ship_flag ,
source.candidate_first_name,
source.candidate_last_name,
source.assignment_start_date,
source.order_type_lookup_code,
source.purchase_basis,
source.matching_basis ,
source.negotiated_by_preparer_flag,
source.ship_method,
source.estimated_pickup_date,
source.supplier_notified_for_cancel,
source.base_unit_price,
source.at_sourcing_flag,
source.tax_attribute_update_code,
source.tax_name,
source.line_num_display,
p_group_line_id,
source.clm_info_flag,
source.clm_option_indicator,
source.clm_option_num,
source.clm_option_from_date,
source.clm_option_to_date,
source.clm_funded_flag,
p_clm_base_line_num,
source.uda_template_id,
source.amendment_type,
source.amendment_status,
source.cost_constraint,
source.contract_type,
source.clm_period_perf_end_date,
source.clm_period_perf_start_date,
source.clm_extended_item_description,
source.clm_option_exercised,
source.is_line_num_display_modified,
source.fund_source_not_known,
source.clm_mipr_obligation_type
FROM po_requisition_lines_all source
WHERE source.requisition_line_id = p_from_line_id;
PROCEDURE update_oneTimeL_and_Atchmnt(p_from_line_id IN NUMBER,
p_to_line_id IN NUMBER,
p_confirmed_id IN NUMBER)
IS
v_progress VARCHAR2(10);
l_procedure_name VARCHAR2(100) := 'update_oneTimeL_and_Atchmnt';
DELETE FROM por_item_attribute_values
WHERE requisition_line_id = p_to_line_id;
INSERT INTO por_item_attribute_values(
item_type,
requisition_header_id,
requisition_line_id,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
SELECT piav.item_type,
p_confirmed_id,
p_to_line_id,
piav.attribute1,
piav.attribute2,
piav.attribute3,
piav.attribute4,
piav.attribute5,
piav.attribute6,
piav.attribute7,
piav.attribute8,
piav.attribute9,
piav.attribute10,
piav.attribute11,
piav.attribute12,
piav.attribute13,
piav.attribute14,
piav.attribute15,
piav.org_id,
piav.created_by,
piav.creation_date,
piav.last_updated_by,
piav.last_update_date,
piav.last_update_login
FROM por_item_attribute_values piav
WHERE piav.requisition_line_id = p_from_line_id;
fnd_attached_documents2_pkg.delete_attachments('REQ_LINES',
''||p_to_line_id,
'',
'',
'',
'',
'Y',
'');
l_log_msg := ' : Exception at update_oneTimeL_and_Atchmnt : '|| SQLERRM;
END update_oneTimeL_and_Atchmnt;
SELECT requisition_header_id
INTO l_req_header_id
FROM po_requisition_headers_all
WHERE conformed_header_id = p_confirmed_id
AND revision_num = p_revision_num;
SELECT requisition_line_id , conformed_line_id
BULK COLLECT INTO l_line_ids , l_conformed_line_ids
FROM po_requisition_lines_all
WHERE requisition_header_id = l_req_header_id
AND group_line_id IS NULL
AND clm_base_line_num IS NULL;
update_amd_line_on_conf_copy(l_line_ids(i),l_conformed_line_ids(i));
update_oneTimeL_and_Atchmnt(l_line_ids(i),l_conformed_line_ids(i),p_confirmed_id);
update_oneTimeL_and_Atchmnt(l_line_ids(i),l_new_line_id,p_confirmed_id);
l_line_ids.DELETE;
l_conformed_line_ids.DELETE;
SELECT requisition_line_id , conformed_line_id, clm_base_line_num
BULK COLLECT INTO l_line_ids , l_conformed_line_ids, l_clm_base_line_num
FROM po_requisition_lines_all
WHERE requisition_header_id = l_req_header_id
AND group_line_id IS NULL
AND clm_base_line_num IS NOT NULL;
update_amd_line_on_conf_copy(l_line_ids(i),l_conformed_line_ids(i), line_map(l_clm_base_line_num(i)));
update_oneTimeL_and_Atchmnt(l_line_ids(i),l_conformed_line_ids(i),p_confirmed_id);
update_oneTimeL_and_Atchmnt(l_line_ids(i),l_new_line_id,p_confirmed_id);
l_line_ids.DELETE;
l_conformed_line_ids.DELETE;
l_clm_base_line_num.DELETE;
SELECT requisition_line_id , conformed_line_id, group_line_id
BULK COLLECT INTO l_line_ids , l_conformed_line_ids, l_group_line_id
FROM po_requisition_lines_all
WHERE requisition_header_id = l_req_header_id
AND group_line_id IS NOT NULL
AND clm_base_line_num IS NULL;
update_amd_line_on_conf_copy(l_line_ids(i),l_conformed_line_ids(i));
update_oneTimeL_and_Atchmnt(l_line_ids(i),l_conformed_line_ids(i),p_confirmed_id);
update_oneTimeL_and_Atchmnt(l_line_ids(i),l_new_line_id,p_confirmed_id);
l_line_ids.DELETE;
l_conformed_line_ids.DELETE;
l_group_line_id.DELETE;
SELECT requisition_line_id , conformed_line_id, group_line_id, clm_base_line_num
BULK COLLECT INTO l_line_ids , l_conformed_line_ids, l_group_line_id, l_clm_base_line_num
FROM po_requisition_lines_all
WHERE requisition_header_id = l_req_header_id
AND group_line_id IS NOT NULL
AND clm_base_line_num IS NOT NULL;
update_amd_line_on_conf_copy(l_line_ids(i),l_conformed_line_ids(i),line_map(l_clm_base_line_num(i)));
update_oneTimeL_and_Atchmnt(l_line_ids(i),l_conformed_line_ids(i),p_confirmed_id);
update_oneTimeL_and_Atchmnt(l_line_ids(i),l_new_line_id,p_confirmed_id);
SELECT Max(sequence_num)
INTO max_seq
FROM po_action_history
WHERE object_id = (SELECT requisition_header_id
FROM po_requisition_headers_all
WHERE conformed_header_id = p_confirmed_id
AND revision_num =p_revision_num)
AND object_type_code = 'REQUISITION';
INSERT INTO po_action_history(
object_id,
object_type_code,
object_sub_type_code,
sequence_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
action_code,
action_date,
employee_id,
approval_path_id,
note,
object_revision_num,
offline_code,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
program_date,
approval_group_id)
SELECT p_confirmed_id,
object_type_code,
object_sub_type_code,
max_seq+sequence_num+1,
last_update_date,
last_updated_by,
creation_date,
created_by,
Decode(action_code,'SUBMIT','SUBMIT CHANGE',action_code),
action_date,
employee_id,
approval_path_id,
note,
p_revision_num,
offline_code,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
program_date,
approval_group_id
FROM po_action_history
WHERE object_id = (SELECT requisition_header_id
FROM po_requisition_headers_all
WHERE conformed_header_id = p_confirmed_id
AND revision_num =p_revision_num)
AND object_type_code = 'REQUISITION';
SELECT po_requisition_headers_s.NEXTVAL
INTO x_to_req_header_id
FROM dual;
INSERT INTO po_requisition_headers_all (
requisition_header_id,
preparer_id,
last_update_date,
last_updated_by,
segment1,
summary_flag,
enabled_flag,
segment2,
segment3,
segment4,
segment5,
start_date_active,
end_date_active,
last_update_login,
creation_date,
created_by,
description,
authorization_status,
note_to_authorizer,
type_lookup_code,
transferred_to_oe_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
on_line_flag,
preliminary_research_flag,
research_complete_flag,
preparer_finished_flag,
preparer_finished_date,
agent_return_flag,
agent_return_note,
cancel_flag,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
ussgl_transaction_code,
government_context,
request_id,
program_application_id,
program_id,
program_update_date,
interface_source_code,
interface_source_line_id,
closed_code,
org_id,
wf_item_type,
wf_item_key,
emergency_po_num,
pcard_id,
apps_source_code,
cbc_accounting_date,
change_pending_flag,
active_shopping_cart_flag,
contractor_status,
contractor_requisition_flag,
supplier_notified_flag,
emergency_po_org_id,
approved_date,
tax_attribute_update_code,
first_approver_id,
first_position_id,
uda_template_id,
conformed_header_id,
revision_num,
federal_flag,
amendment_type,
amendment_status,
clm_issuing_office,
clm_cotr_office,
clm_cotr_contact,
clm_priority_code,
suggested_award_no,
clm_assist_office,
clm_mipr_prepared_date,
clm_mipr_ref_num,
clm_mipr_type,
clm_req_office,
clm_mipr_acknowledged_flag,
clm_assist_contact,
clm_req_contact)
SELECT
x_to_req_header_id,
preparer_id,
last_update_date,
last_updated_by,
decode(p_to_revision_num, 0, segment1, segment1 || '-' || lpad(p_to_revision_num,3,'0')),
summary_flag,
enabled_flag,
segment2,
segment3,
segment4,
segment5,
start_date_active,
end_date_active,
last_update_login,
creation_date,
created_by,
description,
authorization_status,
note_to_authorizer,
type_lookup_code,
transferred_to_oe_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
on_line_flag,
preliminary_research_flag,
research_complete_flag,
preparer_finished_flag,
preparer_finished_date,
agent_return_flag,
agent_return_note,
cancel_flag,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
ussgl_transaction_code,
government_context,
request_id,
program_application_id,
program_id,
program_update_date,
interface_source_code,
interface_source_line_id,
closed_code,
org_id,
wf_item_type,
wf_item_key,
emergency_po_num,
pcard_id,
apps_source_code,
cbc_accounting_date,
change_pending_flag,
active_shopping_cart_flag,
contractor_status,
contractor_requisition_flag,
supplier_notified_flag,
emergency_po_org_id,
approved_date,
tax_attribute_update_code,
first_approver_id,
first_position_id,
uda_template_id,
p_from_req_header_id,
p_to_revision_num,
federal_flag,
amendment_type,
amendment_status,
clm_issuing_office,
clm_cotr_office,
clm_cotr_contact,
clm_priority_code,
suggested_award_no,
clm_assist_office,
clm_mipr_prepared_date,
clm_mipr_ref_num,
clm_mipr_type,
clm_req_office,
clm_mipr_acknowledged_flag,
clm_assist_contact,
clm_req_contact
FROM po_requisition_headers_all
WHERE requisition_header_id = p_from_req_header_id;
l_log_msg := v_progress||' : After insert into po_requisition_headers_all';
SELECT requisition_line_id
FROM po_requisition_lines_all
WHERE requisition_header_id = p_from_req_header_id;
SELECT po_requisition_lines_s.NEXTVAL
INTO to_req_line_id
FROM dual;
INSERT INTO po_requisition_lines_all (
requisition_line_id,
requisition_header_id,
line_num,
line_type_id,
category_id,
item_description,
unit_meas_lookup_code,
unit_price,
quantity,
deliver_to_location_id,
to_person_id,
last_update_date,
last_updated_by,
source_type_code,
last_update_login,
creation_date,
created_by,
item_id,
item_revision,
quantity_delivered,
suggested_buyer_id,
encumbered_flag,
rfq_required_flag,
need_by_date,
line_location_id,
modified_by_agent_flag,
parent_req_line_id,
justification,
note_to_agent,
note_to_receiver,
purchasing_agent_id,
document_type_code,
blanket_po_header_id,
blanket_po_line_num,
currency_code,
rate_type,
rate_date,
rate,
currency_unit_price,
suggested_vendor_name,
suggested_vendor_location,
suggested_vendor_contact,
suggested_vendor_phone,
suggested_vendor_product_code,
un_number_id,
hazard_class_id,
must_use_sugg_vendor_flag,
reference_num,
on_rfq_flag,
urgent_flag,
cancel_flag,
source_organization_id,
source_subinventory,
destination_type_code,
destination_organization_id,
destination_subinventory,
quantity_cancelled,
cancel_date,
cancel_reason,
closed_code,
agent_return_note,
changed_after_research_flag,
vendor_id,
vendor_site_id,
vendor_contact_id,
research_agent_id,
on_line_flag,
wip_entity_id,
wip_line_id,
wip_repetitive_schedule_id,
wip_operation_seq_num,
wip_resource_seq_num,
attribute_category,
destination_context,
inventory_source_context,
vendor_source_context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
bom_resource_id,
request_id,
program_application_id,
program_id,
program_update_date,
ussgl_transaction_code,
government_context,
closed_reason,
closed_date,
transaction_reason_code,
quantity_received,
source_req_line_id,
org_id,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
global_attribute_category,
kanban_card_id,
catalog_type,
catalog_source,
manufacturer_id,
manufacturer_name,
manufacturer_part_number,
requester_email,
requester_fax,
requester_phone,
unspsc_code ,
other_category_code,
supplier_duns,
tax_status_indicator,
pcard_flag,
new_supplier_flag,
auto_receive_flag,
tax_user_override_flag,
tax_code_id,
note_to_vendor,
oke_contract_version_id,
oke_contract_header_id,
item_source_id,
supplier_ref_number,
secondary_unit_of_measure,
secondary_quantity,
preferred_grade,
secondary_quantity_received,
secondary_quantity_cancelled,
vmi_flag,
auction_header_id,
auction_display_number,
auction_line_number,
reqs_in_pool_flag,
bid_number,
bid_line_number,
noncat_template_id,
suggested_vendor_contact_fax,
suggested_vendor_contact_email,
amount,
currency_amount,
labor_req_line_id,
job_id,
job_long_description,
contractor_status,
contact_information,
suggested_supplier_flag,
candidate_screening_reqd_flag,
assignment_end_date,
overtime_allowed_flag,
contractor_requisition_flag,
drop_ship_flag ,
candidate_first_name,
candidate_last_name,
assignment_start_date,
order_type_lookup_code,
purchase_basis,
matching_basis ,
negotiated_by_preparer_flag,
ship_method,
estimated_pickup_date,
supplier_notified_for_cancel,
base_unit_price,
at_sourcing_flag,
tax_attribute_update_code,
tax_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,
conformed_line_id,
amendment_type,
amendment_status,
cost_constraint,
contract_type,
clm_period_perf_end_date,
clm_period_perf_start_date,
clm_option_exercised,
uda_template_id,
fund_source_not_known,
clm_mipr_obligation_type
)
SELECT to_req_line_id,
p_to_req_header_id,
prl.line_num,
prl.line_type_id,
prl.category_id,
prl.item_description,
prl.unit_meas_lookup_code,
prl.unit_price,
prl.quantity,
prl.deliver_to_location_id,
prl.to_person_id,
prl.last_update_date,
prl.last_updated_by,
prl.source_type_code,
prl.last_update_login,
prl.creation_date,
prl.created_by,
prl.item_id,
prl.item_revision,
prl.quantity_delivered,
prl.suggested_buyer_id,
prl.encumbered_flag,
prl.rfq_required_flag,
prl.need_by_date,
prl.line_location_id,
prl.modified_by_agent_flag,
prl.parent_req_line_id,
prl.justification,
prl.note_to_agent,
prl.note_to_receiver,
prl.purchasing_agent_id,
prl.document_type_code,
prl.blanket_po_header_id,
prl.blanket_po_line_num,
prl.currency_code,
prl.rate_type,
prl.rate_date,
prl.rate,
prl.currency_unit_price,
prl.suggested_vendor_name,
prl.suggested_vendor_location,
prl.suggested_vendor_contact,
prl.suggested_vendor_phone,
prl.suggested_vendor_product_code,
prl.un_number_id,
prl.hazard_class_id,
prl.must_use_sugg_vendor_flag,
prl.reference_num,
prl.on_rfq_flag,
prl.urgent_flag,
prl.cancel_flag,
prl.source_organization_id,
prl.source_subinventory,
prl.destination_type_code,
prl.destination_organization_id,
prl.destination_subinventory,
prl.quantity_cancelled,
prl.cancel_date,
prl.cancel_reason,
prl.closed_code,
prl.agent_return_note,
prl.changed_after_research_flag,
prl.vendor_id,
prl.vendor_site_id,
prl.vendor_contact_id,
prl.research_agent_id,
prl.on_line_flag,
prl.wip_entity_id,
prl.wip_line_id,
prl.wip_repetitive_schedule_id,
prl.wip_operation_seq_num,
prl.wip_resource_seq_num,
prl.attribute_category,
prl.destination_context,
prl.inventory_source_context,
prl.vendor_source_context,
prl.attribute1,
prl.attribute2,
prl.attribute3,
prl.attribute4,
prl.attribute5,
prl.attribute6,
prl.attribute7,
prl.attribute8,
prl.attribute9,
prl.attribute10,
prl.attribute11,
prl.attribute12,
prl.attribute13,
prl.attribute14,
prl.attribute15,
prl.bom_resource_id,
prl.request_id,
prl.program_application_id,
prl.program_id,
prl.program_update_date,
prl.ussgl_transaction_code,
prl.government_context,
prl.closed_reason,
prl.closed_date,
prl.transaction_reason_code,
prl.quantity_received,
prl.source_req_line_id,
prl.org_id,
prl.global_attribute1,
prl.global_attribute2,
prl.global_attribute3,
prl.global_attribute4,
prl.global_attribute5,
prl.global_attribute6,
prl.global_attribute7,
prl.global_attribute8,
prl.global_attribute9,
prl.global_attribute10,
prl.global_attribute11,
prl.global_attribute12,
prl.global_attribute13,
prl.global_attribute14,
prl.global_attribute15,
prl.global_attribute16,
prl.global_attribute17,
prl.global_attribute18,
prl.global_attribute19,
prl.global_attribute20,
prl.global_attribute_category,
prl.kanban_card_id,
prl.catalog_type,
prl.catalog_source,
prl.manufacturer_id,
prl.manufacturer_name,
prl.manufacturer_part_number,
prl.requester_email,
prl.requester_fax,
prl.requester_phone,
prl.unspsc_code ,
prl.other_category_code,
prl.supplier_duns,
prl.tax_status_indicator,
prl.pcard_flag,
prl.new_supplier_flag,
prl.auto_receive_flag,
prl.tax_user_override_flag,
prl.tax_code_id,
prl.note_to_vendor,
prl.oke_contract_version_id,
prl.oke_contract_header_id,
prl.item_source_id,
prl.supplier_ref_number,
prl.secondary_unit_of_measure,
prl.secondary_quantity,
prl.preferred_grade,
prl.secondary_quantity_received,
prl.secondary_quantity_cancelled,
prl.vmi_flag,
prl.auction_header_id,
prl.auction_display_number,
prl.auction_line_number,
null,
prl.bid_number,
prl.bid_line_number,
prl.noncat_template_id,
prl.suggested_vendor_contact_fax,
prl.suggested_vendor_contact_email,
prl.amount,
prl.currency_amount,
prl.labor_req_line_id,
prl.job_id,
prl.job_long_description,
prl.contractor_status,
prl.contact_information,
prl.suggested_supplier_flag,
prl.candidate_screening_reqd_flag,
prl.assignment_end_date,
prl.overtime_allowed_flag,
prl.contractor_requisition_flag,
prl.drop_ship_flag ,
prl.candidate_first_name,
prl.candidate_last_name,
prl.assignment_start_date,
prl.order_type_lookup_code,
prl.purchase_basis,
prl.matching_basis ,
prl.negotiated_by_preparer_flag,
prl.ship_method,
prl.estimated_pickup_date,
prl.supplier_notified_for_cancel,
prl.base_unit_price,
prl.at_sourcing_flag,
prl.tax_attribute_update_code,
prl.tax_name,
prl.line_num_display,
prl.group_line_id,
prl.clm_info_flag,
prl.clm_option_indicator,
prl.clm_option_num,
prl.clm_option_from_date,
prl.clm_option_to_date,
prl.clm_funded_flag,
prl.clm_base_line_num,
from_req_line_id,
prl.amendment_type,
prl.amendment_status,
prl.cost_constraint,
prl.contract_type,
prl.clm_period_perf_end_date,
prl.clm_period_perf_start_date,
prl.clm_option_exercised,
prl.uda_template_id,
prl.fund_source_not_known,
clm_mipr_obligation_type
FROM po_requisition_lines_all prl
WHERE prl.requisition_line_id = from_req_line_id;
l_log_msg := v_progress||' : After inserting line: '||to_req_line_id;
INSERT INTO por_item_attribute_values (
item_type,
requisition_header_id,
requisition_line_id,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT item_type,
p_to_req_header_id,
to_req_line_id,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
FROM por_item_attribute_values
WHERE requisition_header_id = p_from_req_header_id
AND requisition_line_id = from_req_line_id
AND item_type = 'AD_HOC_LOCATION';
l_log_msg := v_progress||' : After inserting distributions for line: '||to_req_line_id;
l_log_msg := v_progress||' : After insert into po_requisition_lines_all and po_req_distributions_all';
INSERT INTO po_req_distributions_all (
distribution_id,
last_update_date,
last_updated_by,
requisition_line_id,
set_of_books_id,
code_combination_id,
req_line_quantity,
last_update_login,
creation_date,
created_by,
encumbered_flag,
gl_encumbered_date,
gl_encumbered_period_name,
gl_cancelled_date,
failed_funds_lookup_code,
encumbered_amount,
budget_account_id,
accrual_account_id,
variance_account_id,
prevent_encumbrance_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
ussgl_transaction_code,
government_context,
request_id,
program_application_id,
program_id,
program_update_date,
project_id,
task_id,
expenditure_type,
project_accounting_context,
expenditure_organization_id,
gl_closed_date,
source_req_distribution_id,
distribution_num,
project_related_flag,
expenditure_item_date,
org_id,
allocation_type,
allocation_value,
award_id,
end_item_unit_number,
recoverable_tax,
nonrecoverable_tax,
recovery_rate,
tax_recovery_override_flag,
oke_contract_line_id,
oke_contract_deliverable_id,
req_line_amount,
req_line_currency_amount,
req_award_id,
event_id,
-- line_num_display,
conformed_dist_id,
amendment_type,
amendment_status,
partial_funded_flag,
funded_value,
quantity_funded,
amount_funded,
change_in_funded_value, --<>
clm_misc_loa,
clm_defence_funding,
clm_fms_case_number,
clm_agency_acct_identifier,
info_line_id,
acrn
)
SELECT po_req_distributions_s.NEXTVAL,
last_update_date,
last_updated_by,
p_to_req_line_id,
set_of_books_id,
code_combination_id,
req_line_quantity,
last_update_login,
creation_date,
created_by,
encumbered_flag,
gl_encumbered_date,
gl_encumbered_period_name,
gl_cancelled_date,
failed_funds_lookup_code,
encumbered_amount,
budget_account_id,
accrual_account_id,
variance_account_id,
prevent_encumbrance_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
ussgl_transaction_code,
government_context,
request_id,
program_application_id,
program_id,
program_update_date,
project_id,
task_id,
expenditure_type,
project_accounting_context,
expenditure_organization_id,
gl_closed_date,
source_req_distribution_id,
distribution_num,
project_related_flag,
expenditure_item_date,
org_id,
allocation_type,
allocation_value,
award_id,
end_item_unit_number,
recoverable_tax,
nonrecoverable_tax,
recovery_rate,
tax_recovery_override_flag,
oke_contract_line_id,
oke_contract_deliverable_id,
req_line_amount,
req_line_currency_amount,
req_award_id,
event_id,
-- line_num_display,
distribution_id,
amendment_type,
amendment_status,
partial_funded_flag,
funded_value,
quantity_funded,
amount_funded,
change_in_funded_value, -- <>
clm_misc_loa,
clm_defence_funding,
clm_fms_case_number,
clm_agency_acct_identifier,
info_line_id,
acrn
FROM po_req_distributions_all
WHERE requisition_line_id = p_from_req_line_id;
l_log_msg := v_progress||' : After inserting distribution';
UPDATE po_requisition_lines_all SET amendment_status = 'A'
WHERE conformed_line_id IS NULL AND
group_line_id IS NULL AND
requisition_header_id = l_requisition_header_id;
UPDATE po_requisition_lines_all prla SET prla.amendment_status = 'A'
WHERE prla.requisition_header_id = l_requisition_header_id
AND EXISTS (SELECT 1 FROM po_requisition_lines_all prla1
WHERE prla1.requisition_line_id = prla.group_line_id
AND prla1.conformed_line_id IS NULL
AND prla1.amendment_status = 'A');
UPDATE po_req_distributions_all SET amendment_status = 'A'
WHERE conformed_dist_id IS NULL AND
requisition_line_id IN
(SELECT requisition_line_id FROM po_requisition_lines_all
WHERE conformed_line_id IS NULL AND
requisition_header_id = l_requisition_header_id);
UPDATE po_requisition_lines_all prla SET prla.amendment_status = 'A'
WHERE prla.requisition_header_id = l_requisition_header_id
AND EXISTS (SELECT 1 FROM po_requisition_lines_all prla1
WHERE prla1.requisition_line_id = prla.conformed_line_id
AND prla1.line_location_id IS NULL
AND prla1.auction_header_id is NULL
AND nvl(prla1.clm_info_flag,'N') = 'N')
AND prla.group_line_id IS NULL;
UPDATE po_requisition_lines_all prla SET prla.amendment_status = 'A'
WHERE prla.requisition_header_id = l_requisition_header_id
AND prla.group_line_id IS NOT NULL
AND EXISTS (SELECT 1 FROM po_requisition_lines_all prla1,po_requisition_lines_all prla2
WHERE prla1.requisition_line_id = prla.group_line_id
AND prla2.requisition_line_id = prla1.conformed_line_id
AND prla2.line_location_id IS NULL
AND prla2.auction_header_id is NULL
AND nvl(prla2.clm_info_flag,'N') = 'N');
UPDATE po_req_distributions_all prda SET prda.amendment_status = 'A'
WHERE prda.requisition_line_id IN
(SELECT requisition_line_id FROM po_requisition_lines_all prla
WHERE prla.requisition_header_id = l_requisition_header_id
AND EXISTS (SELECT 1 FROM po_requisition_lines_all prla1
WHERE prla1.requisition_line_id = prla.conformed_line_id
AND prla1.line_location_id IS NULL
AND prla1.auction_header_id is NULL));
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
SELECT Min(awdrefs.agent_id)
INTO l_buyer_id
FROM po_requisition_lines_all prl,
PO_CLMREQ_LINE_AWD_REFS_V awdrefs
-- po_line_locations_all pll,
-- po_headers_all pha
WHERE prl.requisition_header_id = (SELECT conformed_header_id
FROM po_requisition_headers_all
WHERE requisition_header_id = l_document_id)
AND awdrefs.requisition_line_id = prl.requisition_line_id
AND awdrefs.requisition_header_id=prl.requisition_header_id
--AND prl.line_location_id = pll.line_location_id
--AND pll.po_header_id = pha.po_header_id
AND prl.AMENDMENT_TYPE = 'CHANGED'
AND prl.AMENDMENT_STATUS IN ('N','P')
AND awdrefs.agent_id > Nvl(l_current_buyer_id,0);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
SELECT Min(pah.trading_partner_contact_id)
INTO l_sourcing_owner_id
FROM pon_auction_headers_all pah,
po_requisition_lines_all prl
WHERE prl.requisition_header_id = (SELECT conformed_header_id
FROM po_requisition_headers_all
WHERE requisition_header_id = l_document_id)
AND pah.auction_header_id = prl.auction_header_id
AND prl.AMENDMENT_TYPE = 'CHANGED'
AND prl.AMENDMENT_STATUS IN ('N','P')
AND pah.trading_partner_contact_id > Nvl(l_current_sourcing_owner_id,0);
SELECT user_name
INTO l_ourcing_owner_name
FROM fnd_user
WHERE person_party_id = l_sourcing_owner_id
AND nvl(end_date, sysdate+1) > sysdate
AND rownum=1;
SELECT NVL(SUM(ROUND(DECODE(order_type_lookup_code, 'RATE', amount, 'FIXED PRICE', amount, quantity * unit_price),l_precision)) ,0)
FROM po_requisition_lines
WHERE requisition_header_id = p_doc_id
AND NVL(cancel_flag,'N') = 'N'
AND NVL(modified_by_agent_flag, 'N') = 'N';
SELECT NVL(SUM(nonrecoverable_tax), 0)
FROM po_requisition_lines rl,
po_req_distributions_all rd --
WHERE rl.requisition_header_id = p_doc_id
AND rd.requisition_line_id = rl.requisition_line_id
AND NVL(rl.cancel_flag,'N') = 'N'
AND NVL(rl.modified_by_agent_flag, 'N') = 'N';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
SELECT requisition_header_id
INTO l_old_document_id
FROM po_requisition_headers_all
WHERE conformed_header_id = l_conformed_header_id
AND revision_num = l_revision_num -1;
SELECT line_location_id
INTO l_line_location_id
FROM po_requisition_lines_all
WHERE REQUISITION_line_ID = reqLineId;
select 'Y' into isApprovedPOLine from PO_CLMREQ_LINE_AWD_REFS_V WHERE requisition_line_id= reqLineId
AND nvl(authorization_status,'INCOMPLETE') ='APPROVED' AND ROWNUM=1;
SELECT Nvl(pra.authorization_status, 'INCOMPLETE')
INTO l_authorization_status
FROM po_releases_all pra,po_line_locations_all plla
WHERE plla.line_location_id = l_line_location_id
AND pra.po_release_id =plla.po_release_id ;
SELECT pha.authorization_status
INTO l_authorization_status
FROM po_headers_all pha,
po_line_locations_all plla
WHERE
pha.po_header_id = plla.po_header_id
AND plla.line_location_id = l_line_location_id;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
select max(nvl(revision_num,0))
into l_revision_num
from po_requisition_headers_all
where conformed_header_id = p_from_req_header_id;
update po_requisition_headers_all
set amendment_reason = p_cancel_Reason
where requisition_header_id = l_to_req_header_id;
update po_requisition_lines_all
set amendment_type='CANCELLED',
amendment_status='A',
cancel_flag='Y'
where requisition_header_id = p_req_header_id
and conformed_line_id = p_line_id_list(i);
SELECT nvl(max(distribution_num), 0)
INTO x_distribution_num
FROM po_distributions_all --
WHERE line_location_id = x_line_location_id;
SELECT order_type_lookup_code
INTO x_order_type_lookup_code
FROM po_line_types
WHERE line_type_id =(SELECT line_type_id
FROM po_requisition_lines_all
WHERE requisition_line_id = x_requisition_line_id);
SELECT PRH.org_id
INTO x_requesting_ou_id
FROM po_requisition_headers_all PRH,
po_Requisition_lines_all PRL
WHERE PRH.requisition_header_id=PRL.requisition_header_id
AND PRL.requisition_line_id = x_requisition_line_id;
SELECT nvl(fsp.purch_encumbrance_flag,'N'),
nvl(fsp.req_encumbrance_flag,'N'),
sob.set_of_books_id,
psp.receiving_flag,
psp.expense_accrual_code
INTO x_po_encumbrance_flag,
x_req_encumbrance_flag,
x_sob_id,
x_receiving_flag,
x_expense_accrual_code
FROM financials_system_parameters fsp,
gl_sets_of_books sob,
po_system_parameters psp,
rcv_parameters rcv
WHERE fsp.set_of_books_id = sob.set_of_books_id
AND rcv.organization_id (+) = fsp.inventory_organization_id;
SELECT PHA.currency_code,
PHA.pcard_id,
PHA.org_id,
PHA.po_header_id,
PHA.rate_type,
PHA.rate_date,
PLL.transaction_flow_header_id
INTO x_po_currency_code,
x_pcard_id,
g_purchasing_ou_id,
x_po_header_id,
x_po_rate_type,
x_po_rate_date,
x_transaction_flow_header_id
FROM po_line_locations_all PLL,
po_headers_all PHA
WHERE PLL.po_header_id = PHA.po_header_id
AND PLL.line_location_id = x_line_location_id;
SELECT KANBAN_CARD_ID
INTO x_kanban_card_id
FROM po_requisition_lines_all pol --
WHERE pol.REQUISITION_LINE_ID = x_requisition_line_id;
SELECT application_id
INTO x_po_appl_id
FROM fnd_application
WHERE application_short_name = 'PO';
SELECT application_id
INTO x_gl_appl_id
FROM fnd_application
WHERE application_short_name = 'SQLGL';
SELECT PS1.period_name
INTO x_period_name
FROM GL_PERIOD_STATUSES PS1
, GL_PERIOD_STATUSES PS2
, GL_SETS_OF_BOOKS GSOB
WHERE PS1.application_id = x_gl_appl_id
AND PS1.set_of_books_id = x_sob_id
AND PS1.adjustment_period_flag = 'N'
AND trunc(sysdate) BETWEEN trunc(PS1.start_date)
AND trunc(PS1.end_date)
AND ps1.period_year <= gsob.latest_encumbrance_year
AND gsob.set_of_books_id = x_sob_id
AND PS1.period_name = PS2.period_name
AND PS2.application_id = x_po_appl_id
AND PS2.adjustment_period_flag = 'N'
AND PS2.set_of_books_id = x_sob_id;
SELECT pol.unit_meas_lookup_code
INTO x_po_uom
FROM po_lines_all pol , --
po_line_locations_all pll --
WHERE pol.po_line_id = pll.po_line_id
AND pll.line_location_id = x_line_location_id;
SELECT unit_meas_lookup_code,
item_id
INTO x_req_uom,
x_item_id
FROM po_requisition_lines_all prl
WHERE prl.requisition_line_id = x_requisition_line_id;
/* before inserting into the distributions table get the conversion rate to convert
into the BPA uom if the uom's on the req and BPA are different .
This conversion is done only if the Convert UOM profile option is set to Yes. */
if x_req_uom <> x_po_uom then
x_conversion_rate := po_uom_s.po_uom_convert(x_req_uom,
x_po_uom,
x_item_id);
select nvl(drop_ship_flag,'N') into l_drop_ship_flag
from po_line_locations_all where
line_location_id=x_line_location_id;
SELECT PRL.currency_code,
PRL.rate,
PRL.quantity,
PRL.unit_price,
PRL.base_unit_price,
PRL.amount,
PRL.deliver_to_location_id,
PRL.to_person_id,
PRL.destination_type_code,
PRL.destination_organization_id,
PRL.destination_subinventory,
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,
PRL.destination_context,
PRL.vendor_id
INTO x_req_currency_code,
x_req_rate,
x_req_quantity,
x_req_unit_price,
x_req_base_unit_price,
x_req_amount,
x_deliver_to_location_id,
x_deliver_to_person_id,
x_destination_type_code,
x_destination_organization_id,
x_destination_subinventory,
x_wip_entity_id,
x_wip_line_id,
x_wip_repetitive_schedule_id,
x_wip_operation_seq_num,
x_wip_resource_seq_num,
x_bom_resource_id,
x_destination_context,
x_vendor_id
FROM po_requisition_lines_all PRL
WHERE PRL.requisition_line_id = x_requisition_line_id;
SELECT msi.inspection_required_flag
INTO x_item_inspection_req_flag
FROM mtl_system_items msi
WHERE msi.inventory_item_id = x_item_id
AND msi.organization_id = x_destination_organization_id;
SELECT v.receipt_required_flag
INTO x_vendor_receipt_required_flag
FROM po_vendors v
WHERE v.vendor_id = x_vendor_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 = x_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 = g_purchasing_ou_id; --
select default_rate_type
into l_rate_type
from po_system_parameters_all psp
where psp.org_id = g_purchasing_ou_id; --
INSERT INTO po_distributions_all --
(po_distribution_id,
last_update_date,
last_updated_by,
po_header_id,
creation_date,
created_by,
last_update_login,
po_line_id,
line_location_id,
po_release_id,
req_distribution_id,
set_of_books_id,
code_combination_id,
deliver_to_location_id,
deliver_to_person_id,
quantity_ordered,
quantity_delivered,
quantity_billed,
quantity_cancelled,
amount_ordered, --
amount_delivered, --
amount_cancelled, --
amount_billed, --
rate_date,
rate,
accrued_flag,
encumbered_flag,
gl_encumbered_date,
gl_encumbered_period_name,
distribution_num,
destination_type_code,
destination_organization_id,
destination_subinventory,
budget_account_id,
accrual_account_id,
variance_account_id,
--< Shared Proc FPJ Start >
dest_charge_account_id,
dest_variance_account_id,
--< Shared Proc FPJ End >
wip_entity_id,
wip_line_id,
wip_repetitive_schedule_id,
wip_operation_seq_num,
wip_resource_seq_num,
bom_resource_id,
prevent_encumbrance_flag,
project_id,
task_id,
end_item_unit_number,
expenditure_type,
project_accounting_context,
destination_context,
expenditure_organization_id,
expenditure_item_date,
accrue_on_receipt_flag,
kanban_card_id,
tax_recovery_override_flag, --
recovery_rate,
award_id,
--togeorge 09/27/2000
--added oke columns
oke_contract_line_id,
oke_contract_deliverable_id,
org_id, --
distribution_type, --
tax_attribute_update_code, --
interface_distribution_ref --
-- <>
,partial_funded_flag,
funded_value,
quantity_funded,
amount_funded,
change_in_funded_value,
-- <>
acrn
)
SELECT po_distributions_s.NEXTVAL, --
sysdate,
FND_GLOBAL.USER_ID,
x_po_header_id,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
(SELECT po_line_id
FROM po_line_locations_all
WHERE line_location_id = x_line_location_id),
x_line_location_id,
nvl(x_po_release_id,''),
x_requisition_dist_id,
nvl(x_sob_id, prd.set_of_books_id), --
prd.code_combination_id,
x_deliver_to_location_id,
decode(l_drop_ship_flag,'Y',NULL,x_deliver_to_person_id),
decode(
x_order_type_lookup_code,
'QUANTITY',
round((req_line_quantity * x_conversion_rate), 15),
'AMOUNT',
round(
(prd.req_line_quantity
* x_conversion_rate
/ x_rate), --
nvl(x_ext_precision, 15)
),
NULL
),
--
0,
0,
0,
--
decode ( x_order_type_lookup_code -- amount_ordered
, 'RATE' ,round ( ( req_line_quantity
* l_uom_conversion_rate
/ l_currency_conversion_rate)
, x_precision )
, 'FIXED PRICE',round ( ( req_line_quantity
/ l_currency_conversion_rate)
, x_precision )
,NULL
),
0, -- amount_delivered
0, -- amount_cancelled
0, -- amount_billed
--
x_req_rate_date,
x_req_rate,
x_accrued_flag,
'N'
--
-- If Req encumbrance is on and the profile option requests
-- that the Req's GL date be used, use the Req's GL date.
-- Otherwise, if PO enc is on, use SYSDATE.
-- if PO enc is not on, use NULL.
-- gl_encumbered_date =
, NVL( DECODE( x_req_encumbrance_flag
, 'Y', DECODE( x_gl_date_option
, 'REQ GL DATE', prd.gl_encumbered_date
, NULL
)
, NULL
)
, DECODE( x_po_encumbrance_flag
, 'Y', TRUNC(SYSDATE)
, NULL
)
)
-- gl_encumbered_period_name =
, NVL( DECODE( x_req_encumbrance_flag
, 'Y', DECODE(x_gl_date_option
, 'REQ GL DATE', prd.gl_encumbered_period_name
, NULL
)
, NULL
)
, DECODE( x_po_encumbrance_flag
, 'Y', x_period_name
, NULL
)
)
, x_distribution_num +1, --
x_destination_type_code,
x_destination_organization_id,
x_destination_subinventory,
prd.budget_account_id,
prd.accrual_account_id,
prd.variance_account_id,
--< Shared Proc FPJ Start >
-- Copy the receiving accounts from the interface table to
-- the PO table.
null, --pdi.dest_charge_account_id,
null, --pdi.dest_variance_account_id,
--< Shared Proc FPJ End >
x_wip_entity_id,
x_wip_line_id,
x_wip_repetitive_schedule_id,
x_wip_operation_seq_num,
x_wip_resource_seq_num,
x_bom_resource_id
--
-- prevent_encumbrance_flag =
, DECODE( x_destination_type_code
, 'SHOP FLOOR', 'Y'
, 'N'
)
, prd.project_id,
prd.task_id,
prd.end_item_unit_number,
prd.expenditure_type,
prd.project_accounting_context,
x_destination_context,
prd.expenditure_organization_id,
prd.expenditure_item_date,
decode(x_transaction_flow_header_id, NULL, --
DECODE(x_destination_type_code,
'EXPENSE',
decode(nvl(x_item_inspection_req_flag,
nvl(x_vendor_receipt_required_flag,
nvl(x_receiving_flag,'N'))),
'N', 'N',
decode(x_expense_accrual_code,
'PERIOD END', 'N', 'Y')),
'INVENTORY', 'Y',
'SHOP FLOOR', 'Y'),
'Y'), --
x_kanban_card_id,
prd.tax_recovery_override_flag, --
decode(prd.tax_recovery_override_flag, 'Y', prd.recovery_rate, null), --
prd.award_id, -- OGM_0.0 changes..
--togeorge 09/27/2000
--added oke columns
prd.oke_contract_line_id,
prd.oke_contract_deliverable_id,
g_purchasing_ou_id, --
(SELECT shipment_type
FROM po_line_locations_all
WHERE line_location_id = x_line_location_id), --poll.shipment_type, --
nvl2(g_calculate_tax_flag, 'CREATE', null), --
null --pdi.interface_distribution_ref --
-- <>
,prd.partial_funded_flag,
prd.funded_value,
prd.quantity_funded,
prd.amount_funded,
prd.change_in_funded_value,
-- <>
prd.acrn
FROM po_req_distributions_all prd
WHERE prd.distribution_id = x_requisition_dist_id;
SELECT 1
FROM po_requisition_lines_all prl,
po_req_distributions_all prd
WHERE prl.requisition_line_id = req_line_id
AND prl.requisition_line_id = Nvl(prd.info_line_id,prd.requisition_line_id)
AND
( (prl.amendment_type = 'ADDED' AND
prl.clm_info_flag='Y' AND
prl.amendment_status IN ('N','P')
)
OR
(prl.amendment_type = 'CHANGED' AND
prl.amendment_status IN ('N','P') AND
prd.amendment_type IN ('ADDED') AND
prd.amendment_status IN ('N','P')
)
);
SELECT clin.auction_header_id
INTO auction_id
FROM po_requisition_lines_all clin,
po_requisition_lines_all slin
WHERE slin.requisition_line_id = x_req_line_id
AND slin.group_line_id = clin.requisition_line_id
AND slin.amendment_type IN ('ADDED')
AND slin.amendment_status IN ('N')
AND slin.group_line_id IS NOT NULL ;
SELECT icx_po_history_details_s.nextval
INTO p_sequence_num
FROM DUAL;
SELECT getLatestAmendmentNum(prh.requisition_header_id)
INTO x_amendment_num
FROM po_requisition_headers_all prh
WHERE prh.requisition_header_id = (SELECT requisition_header_id
FROM po_requisition_lines_all
WHERE requisition_line_id = p_conformed_req_line_id);
SELECT Max(requisition_line_id)
INTO x_line_id
FROM po_requisition_lines_all
WHERE conformed_line_id = p_conformed_req_line_id
AND amendment_type = 'CHANGED'
AND amendment_status IN ('A','R');
SELECT Min(requisition_line_id)
INTO x_line_id
FROM po_requisition_lines_all
WHERE conformed_line_id = p_conformed_req_line_id;
SELECT line_num,line_num_display
INTO l_line_num,l_line_num_disp
FROM po_requisition_lines_all
WHERE requisition_line_id = p_conformed_req_line_id;
SELECT revision_num INTO l_revision_num
FROM po_requisition_headers_all
WHERE segment1=x_amendment_num;
INSERT INTO icx_po_revisions_temp(
header_id,
line_seq,
creation_date,
po_num,
revision_num,
line_num,
distribution_num,
level_altered,
item_id,
field_altered,
changes_from,
changes_to,
line_num_display
)
VALUES
(
p_conformed_req_line_id,
p_sequence_num,
SYSDATE,
x_amendment_num,
l_revision_num,
l_line_num,
NULL,
'LINES',
NULL,
'New',
NULL,
NULL,
l_line_num_disp
);
SELECT Count(*) INTO l_cnt
FROM po_req_distributions_all
WHERE info_line_id = p_conformed_req_line_id;
INSERT INTO icx_po_revisions_temp(
header_id,
line_seq,
creation_date,
po_num,
revision_num,
line_num,
distribution_num,
level_altered,
item_id,
field_altered,
changes_from,
changes_to,
line_num_display
)
VALUES
(
p_conformed_req_line_id,
p_sequence_num,
SYSDATE,
x_amendment_num,
l_revision_num,
l_line_num,
i,
'DIST',
NULL,
'New',
NULL,
NULL,
l_line_num_disp
);
po_num_tab.delete(1,po_num_tab.Count);
line_num_tab.delete(1,line_num_tab.Count);
distribution_num_tab.delete(1,distribution_num_tab.Count);
level_altered_tab.delete(1,level_altered_tab.Count);
field_altered_tab.delete(1,field_altered_tab.Count);
changes_from_tab.delete(1,changes_from_tab.Count);
changes_to_tab.delete(1,changes_to_tab.Count);
line_num_disp_tab.delete(1,line_num_disp_tab.Count);
sql_stmt1 := 'SELECT REQUISITION_LINE_ID,LINE_NUM, LINE_NUM_DISPLAY,nvl(prl.CANCEL_FLAG,''N'') CANCEL_FLAG, nvl(prl.CLOSED_CODE,''OPEN'') CLOSED_CODE,ITEM_ID,ITEM_REVISION,
ITEM_DESCRIPTION, CATEGORY_ID, UNIT_MEAS_LOOKUP_CODE, UNIT_PRICE,QUANTITY,DELIVER_TO_LOCATION_ID,TO_PERSON_ID,
SUGGESTED_BUYER_ID,to_char(NEED_BY_DATE,''DD-MON-YYYY HH24:MI:SS'') NEED_BY_DATE,JUSTIFICATION,NOTE_TO_AGENT,NOTE_TO_RECEIVER,CURRENCY_CODE,RATE_TYPE,to_char(RATE_DATE,''DD-MON-YYYY HH24:MI:SS'') RATE_DATE,
RATE,CURRENCY_UNIT_PRICE,SUGGESTED_VENDOR_NAME,SUGGESTED_VENDOR_LOCATION,SUGGESTED_VENDOR_CONTACT,SUGGESTED_VENDOR_PHONE,
SUGGESTED_VENDOR_PRODUCT_CODE,HAZARD_CLASS_ID,DESTINATION_TYPE_CODE,DESTINATION_ORGANIZATION_ID, DESTINATION_SUBINVENTORY,
VENDOR_ID,VENDOR_SITE_ID, VENDOR_CONTACT_ID,prl.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE, MANUFACTURER_NAME,
MANUFACTURER_PART_NUMBER, REQUESTER_EMAIL,REQUESTER_FAX,REQUESTER_PHONE, UNSPSC_CODE, SUPPLIER_DUNS,AMOUNT,
CURRENCY_AMOUNT,JOB_LONG_DESCRIPTION,SUGGESTED_VENDOR_CONTACT_FAX,SUGGESTED_VENDOR_CONTACT_EMAIL,CANDIDATE_FIRST_NAME,
CANDIDATE_LAST_NAME,to_char(ASSIGNMENT_START_DATE,''DD-MON-YYYY HH24:MI:SS'') ASSIGNMENT_START_DATE,CONTRACT_TYPE,COST_CONSTRAINT,
to_char(CLM_PERIOD_PERF_START_DATE,''DD-MON-YYYY HH24:MI:SS'') CLM_PERIOD_PERF_START_DATE,to_char(CLM_PERIOD_PERF_END_DATE,''DD-MON-YYYY HH24:MI:SS'') CLM_PERIOD_PERF_END_DATE,
nvl(CLM_OPTION_EXERCISED,''N'') CLM_OPTION_EXERCISED, CLM_OPTION_FROM_DATE, CLM_OPTION_TO_DATE,CLM_OPTION_INDICATOR , CLM_BASE_LINE_NUM , CLM_MIPR_OBLIGATION_TYPE
FROM po_requisition_lines_all prl
WHERE prl.requisition_line_id='||p_to_req_line_id||
'ORDER BY LINE_NUM';
sql_stmt2 := 'SELECT REQUISITION_LINE_ID,LINE_NUM, LINE_NUM_DISPLAY,nvl(prl.CANCEL_FLAG,''N'') CANCEL_FLAG, nvl(prl.CLOSED_CODE,''OPEN'') CLOSED_CODE,ITEM_ID,ITEM_REVISION,
ITEM_DESCRIPTION, CATEGORY_ID, UNIT_MEAS_LOOKUP_CODE, UNIT_PRICE,QUANTITY,DELIVER_TO_LOCATION_ID,TO_PERSON_ID,
SUGGESTED_BUYER_ID,to_char(NEED_BY_DATE,''DD-MON-YYYY HH24:MI:SS'') NEED_BY_DATE,JUSTIFICATION,NOTE_TO_AGENT,NOTE_TO_RECEIVER,CURRENCY_CODE,RATE_TYPE,to_char(RATE_DATE,''DD-MON-YYYY HH24:MI:SS'') RATE_DATE,
RATE,CURRENCY_UNIT_PRICE,SUGGESTED_VENDOR_NAME,SUGGESTED_VENDOR_LOCATION,SUGGESTED_VENDOR_CONTACT,SUGGESTED_VENDOR_PHONE,
SUGGESTED_VENDOR_PRODUCT_CODE,HAZARD_CLASS_ID,DESTINATION_TYPE_CODE,DESTINATION_ORGANIZATION_ID, DESTINATION_SUBINVENTORY,
VENDOR_ID,VENDOR_SITE_ID, VENDOR_CONTACT_ID,prl.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE, MANUFACTURER_NAME,
MANUFACTURER_PART_NUMBER, REQUESTER_EMAIL,REQUESTER_FAX,REQUESTER_PHONE, UNSPSC_CODE, SUPPLIER_DUNS,AMOUNT,
CURRENCY_AMOUNT,JOB_LONG_DESCRIPTION,SUGGESTED_VENDOR_CONTACT_FAX,SUGGESTED_VENDOR_CONTACT_EMAIL,CANDIDATE_FIRST_NAME,
CANDIDATE_LAST_NAME,to_char(ASSIGNMENT_START_DATE,''DD-MON-YYYY HH24:MI:SS'') ASSIGNMENT_START_DATE,CONTRACT_TYPE,COST_CONSTRAINT,
to_char(CLM_PERIOD_PERF_START_DATE,''DD-MON-YYYY HH24:MI:SS'') CLM_PERIOD_PERF_START_DATE,to_char(CLM_PERIOD_PERF_END_DATE,''DD-MON-YYYY HH24:MI:SS'') CLM_PERIOD_PERF_END_DATE,
nvl(CLM_OPTION_EXERCISED,''N'') CLM_OPTION_EXERCISED, CLM_OPTION_FROM_DATE, CLM_OPTION_TO_DATE, CLM_OPTION_INDICATOR, CLM_BASE_LINE_NUM , CLM_MIPR_OBLIGATION_TYPE
FROM po_requisition_lines_all prl
WHERE prl.requisition_line_id='||p_from_req_line_id||
'ORDER BY LINE_NUM';
sql_stmt1:='SELECT DISTRIBUTION_ID,
prl.LINE_NUM LINE_NUM,
prl.LINE_NUM_DISPLAY LINE_NUM_DISPLAY,
DISTRIBUTION_NUM,
nvl(prl.CANCEL_FLAG,''N'') LINE_CANCEL_FLAG,
prl.ITEM_ID LINE_ITEM_ID,
CODE_COMBINATION_ID,
REQ_LINE_QUANTITY,
to_char(GL_ENCUMBERED_DATE,''DD-MON-YYYY HH24:MI:SS'') GL_ENCUMBERED_DATE,
prd.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
PROJECT_ID,
TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ORGANIZATION_ID,
to_char(EXPENDITURE_ITEM_DATE,''DD-MON-YYYY HH24:MI:SS'') EXPENDITURE_ITEM_DATE,
nvl(RECOVERABLE_TAX,0) RECOVERABLE_TAX,
nvl(NONRECOVERABLE_TAX,0) NONRECOVERABLE_TAX,
nvl(RECOVERY_RATE,0) RECOVERY_RATE,
REQ_LINE_AMOUNT,
REQ_LINE_CURRENCY_AMOUNT,
PARTIAL_FUNDED_FLAG,
FUNDED_VALUE,
QUANTITY_FUNDED,
AMOUNT_FUNDED
FROM po_req_distributions_all prd,
po_requisition_lines_all prl
WHERE prl.requisition_line_id ='||p_to_req_line_id||
'AND prl.requisition_line_id=nvl(prd.info_line_id,prd.requisition_line_id)
ORDER BY nvl(prd.info_line_id,prd.requisition_line_id),DISTRIBUTION_NUM';
sql_stmt2:='SELECT DISTRIBUTION_ID,
prl.LINE_NUM LINE_NUM,
prl.LINE_NUM_DISPLAY LINE_NUM_DISPLAY,
DISTRIBUTION_NUM,
nvl(prl.CANCEL_FLAG,''N'') LINE_CANCEL_FLAG,
prl.ITEM_ID LINE_ITEM_ID,
CODE_COMBINATION_ID,
REQ_LINE_QUANTITY,
to_char(GL_ENCUMBERED_DATE,''DD-MON-YYYY HH24:MI:SS'') GL_ENCUMBERED_DATE,
prd.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
PROJECT_ID,
TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ORGANIZATION_ID,
to_char(EXPENDITURE_ITEM_DATE,''DD-MON-YYYY HH24:MI:SS'') EXPENDITURE_ITEM_DATE,
nvl(RECOVERABLE_TAX,0) RECOVERABLE_TAX,
nvl(NONRECOVERABLE_TAX,0) NONRECOVERABLE_TAX,
nvl(RECOVERY_RATE,0) RECOVERY_RATE,
REQ_LINE_AMOUNT,
REQ_LINE_CURRENCY_AMOUNT,
PARTIAL_FUNDED_FLAG,
FUNDED_VALUE,
QUANTITY_FUNDED,
AMOUNT_FUNDED
FROM po_req_distributions_all prd,
po_requisition_lines_all prl
WHERE prl.requisition_line_id ='||p_from_req_line_id||
'AND prl.requisition_line_id=nvl(prd.info_line_id,prd.requisition_line_id)
ORDER BY nvl(prd.info_line_id,prd.requisition_line_id),DISTRIBUTION_NUM';
insert_changes(p_sequence_num,p_from_req_line_id);
l_log_msg := v_progress||' : After cinserting into the temp table';
* Update amendment status of the implemented lines( Accepted or Rejected)
*
* ARGUMENTS
* selected_lines IN variable of type REQ_LINE_TABLE_TYPE
* stores requisition_line_id's of the selected lines
*
* reason IN Accept or Reject reason
* implement_mode IN Accept or Reject
* caller IN Specifies source document (possible values: BUYER or SOURCING_BUYER )
*
* HISTORY
* 05-10-09 Bisdas Created
**************************************************************************************************/
PROCEDURE implement_amendment_changes(selected_lines IN po_tbl_number,
reason IN VARCHAR2,
implement_mode IN VARCHAR2,
caller IN VARCHAR2,
dest_doc_id IN varchar2)
IS
--Debugging purpose
v_progress VARCHAR2(3);
FOR i IN 1..selected_lines.count
LOOP
/* Add the newly added distribution to PO */
IF caller = 'BUYER' THEN
/* Auto addition of newly added distribution */
implement_dist_auto(selected_lines(i));
implement_slin_auto(selected_lines(i),dest_doc_id);
/* Update amendment status of the implemented lines/ distributions
selected_lines is the list of line_id from conformed copy. When
stamping the response, stamp it on the lastest amendment copy also */
UPDATE po_requisition_lines_all
SET amendment_status = 'A',
amendment_response_reason = reason
WHERE requisition_line_id = selected_lines(i)
AND amendment_status IN ('N','P');
select prh.requisition_header_id
into l_latest_amendment_hdr_id
from po_requisition_headers_all prh,
po_requisition_lines_all prl
where prh.revision_num = (select max(prh2.revision_num)
from po_requisition_headers_all prh2,
po_requisition_lines_all prl2
where prh2.conformed_header_id = prl2.requisition_header_id
and prl2.requisition_line_id = selected_lines(i) )
and prh.conformed_header_id = prl.requisition_header_id
and prl.requisition_line_id = selected_lines(i);
select prl2.requisition_line_id, prl2.line_num_display
into l_amendment_line_id, l_line_num_display
from po_requisition_lines_all prl1,
po_requisition_lines_all prl2
where prl1.requisition_line_id = selected_lines(i)
and prl2.requisition_header_id = l_latest_amendment_hdr_id
and prl1.line_num_display = prl2.line_num_display;
UPDATE po_requisition_lines_all prl
SET amendment_status = 'A',
amendment_response_reason = reason
WHERE requisition_line_id = l_amendment_line_id
and amendment_status in ('N','P');
UPDATE po_req_distributions_all
SET amendment_status = 'A'
WHERE requisition_line_id = selected_lines(i)
AND amendment_status IN ('N','P');
update po_req_distributions_all
set amendment_status = 'A'
where requisition_line_id = l_amendment_line_id
and amendment_status in ('N', 'P');
FOR i IN 1..selected_lines.count
LOOP
/* Update amendment status of the implemented lines/ distributions */
UPDATE po_requisition_lines_all
SET amendment_status = 'R',
amendment_response_reason = reason
WHERE requisition_line_id = selected_lines(i)
AND amendment_status IN ('N','P');
select prh.requisition_header_id
into l_latest_amendment_hdr_id
from po_requisition_headers_all prh,
po_requisition_lines_all prl
where prh.revision_num = (select max(prh2.revision_num)
from po_requisition_headers_all prh2,
po_requisition_lines_all prl2
where prh2.conformed_header_id = prl2.requisition_header_id
and prl2.requisition_line_id = selected_lines(i) )
and prh.conformed_header_id = prl.requisition_header_id
and prl.requisition_line_id = selected_lines(i);
select prl2.requisition_line_id, prl2.line_num_display
into l_amendment_line_id, l_line_num_display
from po_requisition_lines_all prl1,
po_requisition_lines_all prl2
where prl1.requisition_line_id = selected_lines(i)
and prl2.requisition_header_id = l_latest_amendment_hdr_id
and prl1.line_num_display = prl2.line_num_display;
UPDATE po_requisition_lines_all prl
SET amendment_status = 'R',
amendment_response_reason = reason
WHERE requisition_line_id = l_amendment_line_id
and amendment_status in ('N','P');
UPDATE po_req_distributions_all
SET amendment_status = 'R'
WHERE requisition_line_id = selected_lines(i)
AND amendment_status IN ('N','P');
UPDATE po_req_distributions_all
SET amendment_status = 'R'
WHERE requisition_line_id = l_amendment_line_id
AND amendment_status IN ('N','P');
select po_line_id
into l_po_line_id
from po_requisition_lines_all
where requisition_line_id = p_conformed_line_id;
select count(*)
into l_cnt
from po_requisition_lines_all
where requisition_line_id = p_conformed_line_id
and auction_header_id is not null;
select nvl(CLM_OPTION_EXERCISED,'N')
into l_option_exercised
from po_requisition_lines_all
where requisition_line_id = p_conformed_line_id;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
update_amendment(l_conformed_id,l_revision_num);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
SELECT segment1 INTO l_amendment_num
FROM
(SELECT prha.segment1,rownum rnum
FROM po_requisition_headers_all prha
WHERE conformed_header_id = p_req_header_id
AND authorization_status = 'APPROVED'
ORDER BY revision_num DESC)
WHERE RNUM =1;
SELECT requisition_header_id INTO l_header_id
FROM
(SELECT prha.requisition_header_id,rownum rnum
FROM po_requisition_headers_all prha
WHERE conformed_header_id = p_req_header_id
AND authorization_status = 'APPROVED'
ORDER BY revision_num DESC)
WHERE RNUM =1;
SELECT draft_id
INTO p_draft_id
FROM po_drafts DFT
WHERE DFT.document_id = p_req_header_id
AND DFT.status in ('DRAFT')
AND DFT.creation_date = (SELECT Max(creation_date)
FROM po_drafts
WHERE document_id = p_req_header_id
AND status in ('DRAFT'));
SELECT Max(distribution_id)
INTO l_dist_id
FROM po_req_distributions_all
WHERE ( conformed_dist_id = p_distribution_id AND amendment_status = 'A')
---Bug#14332568 : Added below condition to get the funded value for requistion line
-- which is newly added after requisition1405has been autocreated.
OR (distribution_id = p_distribution_id
AND amendment_type = 'ADDED'
AND amendment_type IS NOT NULL );
SELECT prda.funded_value INTO L_FUNDED_VALUE
FROM po_req_distributions_all prda,
po_requisition_lines_all prla,
po_requisition_headers_all prha
WHERE
prda.conformed_dist_id = p_distribution_id
AND prda.requisition_line_id = prla.requisition_line_id
AND prla.requisition_header_id = prha.requisition_header_id
AND prha.revision_num=0;
SELECT funded_value INTO L_FUNDED_VALUE
FROM po_req_distributions_all WHERE
distribution_id = l_dist_id;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'is Amenement Approval '||l_amendment_approval);
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'Conformed Header Id '||l_conformed_header_id);
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'Revision Num '||l_revision_num);
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'Document Id '||l_document_id);
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'Document Type '||l_document_type);
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'Approver EmpId '||l_employee_id);
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'Funds Override '||l_override_funds);
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'Calling process_cancel_amendments before mergeReserveAutonomous ');
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'Calling MergeReserveAutonomous. ');
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'MergeReserveAutonomous - Return Status: '|| l_return_status );
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'MergeReserveAutonomous - Po Return Code: '|| l_po_return_code );
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'MergeReserve Completed Successfully.');
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'MergeReserveAutonomous: Start');
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'Savepoint: Clm_MergeReserve_Autonomous_SP');
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'Calling Auto Accept for Amendment. Requisition Header Id: '||p_conformed_header_id);
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'Auto Accept for Amendment Successful.');
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'Calling Merge Amendment with ConfirmedId: '||p_conformed_header_id||' Revision Num: '||p_revision_num);
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'Merge Amendment Successful');
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'Requisition Encumbrance is On');
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'Calling do Reserve with doctype: '||p_doc_type||' - sub: '||p_doc_subtype||'- document id: '||l_document_id||' - Ovrride: '||p_override_funds||'- Emp: '||p_employee_id||'.');
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'Do Reserve Completed with Return Status: '||l_return_status);
po_wf_debug_pkg.Insert_debug(itemtype,itemkey,'Create Req Supply failed for Confirmed Header Id : '||p_conformed_header_id);
SELECT attr_display_name
INTO x_attr_disp_name
FROM ego_attrs_v
WHERE p_node_name = attr_group_name||PO_UDA_TEMPLATES_UTIL.g_delimiter||attr_name
AND ATTR_GROUP_TYPE = 'PO_UDA_DUMMY_EXT_ATTRS'
AND application_id = 201;
SELECT attr_name,
attr_group_type,
attr_group_name,
attr_id,
application_id
INTO l_attr_name,
l_attr_group_Type,
l_attr_grp_name,
l_attr_id,
l_application_id
FROM ego_attrs_v
WHERE p_node_name = attr_group_name||PO_UDA_TEMPLATES_UTIL.g_delimiter||attr_name
AND ATTR_GROUP_TYPE = 'PO_UDA_DUMMY_EXT_ATTRS'
AND application_id = 201;
SELECT requisition_header_id
INTO l_pk1_col_val
FROM PO_REQUISITION_HEADERS_ALL
WHERE segment1 = amend_num;
SELECT requisition_header_id
INTO l_req_header_id
FROM PO_REQUISITION_HEADERS_ALL
WHERE segment1 = amend_num;
SELECT requisition_line_id
INTO l_pk1_col_val
FROM PO_REQUISITION_LINES_ALL
WHERE requisition_header_id = l_req_header_id
AND line_num = line_num;
SELECT attr.attr_id,
attr.attr_name,
attr.application_id
INTO l_attr_id,
l_attr_name,
l_application_id
FROM ego_attrs_v attr
WHERE attr.attr_group_name = 'addresses'
AND attr.attr_group_type = 'PO_REQ_HEADER_EXT_ATTRS'
AND attr.database_column = p_node_name
AND attr.display_code <> 'H'
AND application_id = 201;
SELECT requisition_header_id
INTO l_pk1_col_val
FROM PO_REQUISITION_HEADERS_ALL
WHERE segment1 = amend_num;
SELECT 1 INTO l_num
FROM po_requisition_lines_all
WHERE requisition_line_id = l_clin_line_id
AND line_location_id IS NOT NULL;
SELECT 1 INTO l_num
FROM po_requisition_lines_all
WHERE group_line_id = l_clin_line_id
AND line_location_id IS NOT NULL
AND ROWNUM=1;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
UPDATE po_requisition_headers_all h1 SET (h1.authorization_status,h1.approved_date) =
(SELECT h2.authorization_status,h2.approved_date
FROM po_requisition_headers_all h2
WHERE h2.requisition_header_id = l_req_header_id)
WHERE requisition_header_id = l_conf_hdr_id;
po_req_lines_sv.update_reqs_in_pool_flag(NULL,l_conf_hdr_id,x_return_status);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
SELECT REQUISITION_LINE_ID,CONFORMED_LINE_ID
FROM po_requisition_lines_all
WHERE requisition_header_id = x_req_header_id
AND AMENDMENT_TYPE = 'CANCELLED'
AND AMENDMENT_STATUS = 'N';
SELECT AMENDMENT_REASON, PREPARER_ID, ORG_ID
INTO x_amendment_reason, x_preparer_id, x_org_id
FROM PO_REQUISITION_HEADERS_ALL
WHERE requisition_header_id = p_req_header_id;
SELECT po_line_id, line_location_id
INTO x_po_line_id, x_line_loc_id
FROM po_requisition_lines_all
WHERE requisition_line_id = x_conf_line_id;
UPDATE po_requisition_lines_all set cancel_flag = 'I'
WHERE requisition_line_id = x_conf_line_id and nvl(cancel_flag,'N') = 'N';
UPDATE po_requisition_lines_all set amendment_response_reason = 'Cancel Funds failed. Contact Purchasing Administrator if necessary.',
amendment_status = 'R',
amendment_type = 'CANCELLED'
WHERE requisition_line_id IN (x_conf_line_id, x_req_line_id);
update po_requisition_lines_all set cancel_flag = NULL
where requisition_line_id = x_conf_line_id and nvl(cancel_flag,'N') = 'I';
UPDATE po_requisition_lines_all set amendment_response_reason = 'Cancel Funds completed with warnings.',
amendment_type = 'CANCELLED'
WHERE requisition_line_id in (x_req_line_id, x_conf_line_id);
UPDATE po_requisition_lines_all set amendment_response_reason = 'Cancel Funds completed with errors.',
amendment_status = 'R',
amendment_type = 'CANCELLED'
WHERE requisition_line_id in (x_req_line_id, x_conf_line_id);
update po_requisition_lines_all set cancel_flag = NULL
where requisition_line_id = x_conf_line_id and nvl(cancel_flag,'N') = 'I';
po_reqs_control_sv.update_reqs_status
(p_conf_hdr_id,
x_conf_line_id,
x_preparer_id,
'REQUISITION',
'PURCHASE',
'CANCEL',
x_amendment_reason,
SYSDATE,
l_req_encumbrance_on,
po_core_s.get_product_install_status('OE'),
X_req_control_error_rc);
UPDATE po_requisition_lines_all SET amendment_status = 'A',
amendment_type = 'CANCELLED'
WHERE requisition_line_id in (x_req_line_id, x_conf_line_id);
UPDATE po_requisition_lines_all SET amendment_status = 'R',
amendment_type = 'CANCELLED'
WHERE requisition_line_id in (x_req_line_id, x_conf_line_id);
SELECT Count(*)
INTO l_po_cnt
FROM po_clmreq_line_awd_refs_v
WHERE requisition_header_id = l_req_header_id
AND Nvl(authorization_status,'INCOMPLETE') NOT IN ('APPROVED','REJECTED','CANCELLED');
Select 'Y' into x_return_status from PO_REQUISITION_HEADERS_ALL where requisition_header_id=p_req_header_id and federal_flag='Y';