The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Delete records from the temp table, POS_PO_REVISIONS_GT, where
* all the records for differences are stored.
*
* ARGUMENTS
* p_date Purges all records that are older than this date. The
* date defaults to two hours back from the current date.
*
* NOTES
* You need to set-up a concurrent program in Oracle Applications to
* call this program in a specific frequency.
*
* HISTORY
* 10-SEP-97 Rami Haddad Created
* 20-OCT-97 Winston Lang Added errbuf and retcode parameters.
********************************************************************/
PROCEDURE purge(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_date IN DATE DEFAULT SYSDATE - 1/12
) AS
v_progress VARCHAR2(3);
DELETE pos_po_revisions_gt
WHERE creation_date < NVL( p_date, SYSDATE - 1/12 );
SELECT un_number
INTO v_un_number
FROM po_un_numbers
WHERE un_number_id = p_un_number_id;
SELECT icx_util.item_flex_seg( msi.rowid )
INTO v_item_num
FROM
mtl_system_items msi,
financials_system_parameters fsp
WHERE
p_item_id = msi.inventory_item_id (+)
AND fsp.inventory_organization_id = NVL(
msi.organization_id,
fsp.inventory_organization_id );
SELECT hazard_class
INTO v_hazard_class
FROM po_hazard_classes
WHERE hazard_class_id = p_hazard_class_id;
SELECT description
INTO v_ap_terms
FROM ap_terms
WHERE term_id = p_term_id;
SELECT distinct full_name
INTO v_full_name
FROM per_people_f
WHERE person_id = p_agent_id
and TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE ;
select organization_name
into v_org_name
from org_organization_definitions ood
where ood.organization_id = p_org_id;
select HAOTL.NAME
into v_org_name
from HR_ALL_ORGANIZATION_UNITS_TL HAOTL
where HAOTL.ORGANIZATION_ID = p_org_id and
HAOTL.LANGUAGE = USERENV('LANG');
SELECT DECODE(last_name, NULL, NULL, last_name || ',' || first_name)
INTO v_full_name
FROM po_vendor_contacts
WHERE vendor_contact_id = p_vendor_contact_id
AND ROWNUM=1;
SELECT location_code
INTO v_location_code
FROM hr_locations_all
WHERE location_id = p_location_id;
SELECT clm_document_number
INTO v_po_num
FROM po_headers_all
WHERE po_header_id = p_header_id;
SELECT line_num
INTO v_line_num
FROM po_lines_all
WHERE po_line_id = p_line_id;
SELECT description
INTO v_description
FROM po_lookup_codes
WHERE
lookup_type = p_lookup_type
AND lookup_code = p_lookup_code;
SELECT vendor_site_code
INTO v_site_code
FROM po_vendor_sites_all
WHERE vendor_site_id = p_vendor_site_id;
SELECT PRICE_DIFFERENTIAL_DSP
INTO v_pricediff_dsp
FROM PO_PRICE_DIFF_LOOKUPS_V
WHERE PRICE_DIFFERENTIAL_TYPE = p_pricediff_code;
* 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_header_id Unique identifier for PO.
* p_release_id Unique identifier for PO release.
* p_line_id Unique identifier for PO line.
* p_location_id Unique identifier for PO line location.
* p_distribution_id Unique identifier for PO distribution.
* p_item_id Unique identified for line item.
* p_po_num PO number.
* p_line_num PO line number.
* p_location_num PO line location number.
* p_distribution_num PO distribution number.
* p_level_altered Level altered. Possible values are:
*
* Code User-friendly name
* ---- ------------------
* ICX_DISTRIBUTION Distribution
* ICX_HEADER Header
* ICX_LINE Line
* ICX_SHIPMENT Shipment
*
* p_field_altered Field altered. Possible values are:
*
* Code User-friendly name
* ---- ------------------
* ICX_ACCEPTANCE_DUE_DATE Acceptance Due Date
* ICX_ACCEPTANCE_REQUIRED Acceptance Required
* ICX_AMOUNT Amount
* ICX_AMOUNT_AGREED Amount Agreed
* ICX_AMOUNT_DUE_DATE Amount Due Date
* ICX_AMOUNT_LIMIT Amount Limit
* ICX_BILL_TO Bill To
* ICX_BUYER Buyer
* ICX_CANCELLED Cancelled
* ICX_CHARGE_ACCT Charge Account
* ICX_CLOSED_CODE Closed
* ICX_CONFIRMED Confirm
* ICX_CONTRACT_NUMBER Contract Number
* ICX_EFFECTIVE_DATE Effective Date
* ICX_ERROR Error
* ICX_EXPIRATION_DATE Expiration Date
* ICX_FOB FOB
* ICX_FREIGHT_TERMS Freight Terms
* ICX_HAZARD_CLASS Hazard Class
* ICX_ITEM Item
* ICX_ITEM_DESCRIPTION Item Description
* ICX_ITEM_REVISION Item Revision
* ICX_LAST_ACCEPT_DATE Last Acceptance Date
* ICX_LINE_NUMBER Line Number
* ICX_NEED_BY_DATE Need By Date
* ICX_NEW New
* ICX_NOTE_TO_VENDOR Note To Vendor
* ICX_PAYMENT_TERMS Payment Terms
* ICX_PRICE_BREAK Price Break
* ICX_PRICE_TYPE Price Type
* ICX_PROMISED_DATE Promised Date
* ICX_QUANTITY Quantity
* ICX_QUANTITY_AGREED Quantity Agreed
* ICX_RELEASE_DATE Released Date
* ICX_RELEASE_NUMBER Release Number
* ICX_REQUESTOR Requestor
* ICX_SHIP_NUM Shipment Number
* ICX_SHIP_TO Ship To
* ICX_SHIP_VIA Ship Via
* ICX_SOURCE_QT_HEADER Source Quotation Header
* ICX_SOURCE_QT_LINE Source Quotation Line
* ICX_SUPPLIER_CONTACT Supplier Contact
* ICX_SUPPLIER_ITEM_NUM Supplier Item Number
* ICX_TAXABLE_FLAG Taxable
* ICX_UNIT_PRICE Unit Price
* ICX_UN_NUMBER UN Number
* ICX_UOM UOM
*
* p_changes_from Previous value of field altered.
* p_changes_to New value of field altered.
*
* NOTES
* Stamps every line with the current system date. Use that value
* when purging the table, to remove 2-hours old records for example.
*
* Replace IDs that are NULL with -99, to do the sorting correctly.
* When sorting in an ascending order, NULL values are at the last,
* while, to sort these records correctly, they should be the first.
*
* HISTORY
* 08-AUG-97 Nilo Paredes Created
* 22-SEP-97 Rami Haddad Removed prompts look-up in AK.
* Replace NULL with -99 for sorting.
* 22-APR-06 Abhishek Tri Adding new columns for item and job
* instead of calculating value in VO
********************************************************************/
PROCEDURE insert_changes(
p_line_seq IN NUMBER,
p_header_id IN NUMBER,
p_release_id IN NUMBER,
p_line_id IN NUMBER,
p_location_id IN NUMBER,
p_distribution_id IN NUMBER,
p_item_id IN NUMBER,
p_po_num IN VARCHAR2,
p_revision_num IN NUMBER,
p_line_num IN NUMBER,
p_location_num IN NUMBER,
p_distribution_num IN NUMBER,
p_level_altered IN VARCHAR2,
p_field_altered IN VARCHAR2,
p_changes_from IN VARCHAR2,
p_changes_to IN VARCHAR2,
p_enabled_org_name in VARCHAR2 default null,
p_price_diff_num in NUMBER default null,
p_change_from_date IN DATE DEFAULT NULL,
p_change_to_date IN DATE DEFAULT NULL,
p_item in varchar2 default null,
p_job in varchar2 default null
) AS
v_progress VARCHAR2(3);
INSERT INTO
pos_po_revisions_gt(
line_seq,
creation_date,
header_id,
release_id,
line_id,
location_id,
distribution_id,
item_id,
po_num,
revision_num,
line_num,
location_num,
distribution_num,
level_altered,
field_altered,
changes_from,
changes_to,
enabled_org_name,
price_diff_num,
change_from_date,
change_to_date,
item,
job
)
VALUES
(
p_line_seq,
SYSDATE,
p_header_id,
p_release_id,
p_line_id,
p_location_id,
p_distribution_id,
p_item_id,
p_po_num,
p_revision_num,
p_line_num,
p_location_num,
p_distribution_num,
p_level_altered,
p_field_altered,
p_changes_from,
p_changes_to,
p_enabled_org_name,
p_price_diff_num,
p_change_from_date,
p_change_to_date,
p_item,
p_job
);
'PO_COMPARE_REVISIONS.INSERT_CHANGES',
v_progress,
sqlcode );
END insert_changes;
* Insert a line in the POS_PO_REVISIONS_GT 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
* 31-OCT-97 Rami Haddad Created
********************************************************************/
PROCEDURE verify_no_differences( p_line_seq IN NUMBER ) AS
records_exist number;
SELECT COUNT(*)
INTO records_exist
FROM pos_po_revisions_gt
WHERE line_seq = p_line_seq;
insert_changes(
p_line_seq,
-99,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
0, -- -99
NULL,
NULL,
NULL,
'ICX_HEADER',
--fnd_message.get_String('PO', 'POS_NO_DIFFERENCE'),
'ICX_NO_DIFFERENCE',
NULL,
NULL
);
* Constant variables to pass for insert_changes
*/
c_level_altered pos_po_revisions_gt.level_altered%TYPE
:= 'ICX_HEADER';
SELECT *
FROM po_ga_org_assignments_archive
WHERE po_header_id = current_header_id and
revision_num = current_revision_num and
latest_external_flag = 'Y';
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('POS', 'POS_ERROR'),
'ICX_ERROR',
NULL,
NULL
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_string('POS', 'POS_CANCELLED'),
'ICX_CANCELLED',
NULL,
NULL
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('POS', 'POS_BUYER'),
'ICX_BUYER',
get_buyer( p_po_from.agent_id ),
get_buyer( p_po_to.agent_id )
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('POS', 'POS_SUPPLIER_CONTACT'),
'ICX_SUPPLIER_CONTACT',
get_vendor_contact( p_po_from.vendor_contact_id ),
get_vendor_contact( p_po_to.vendor_contact_id )
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('POS', 'POS_CONFIRMED'),
'ICX_CONFIRMED',
p_po_from.confirming_order_flag,
p_po_to.confirming_order_flag
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('POS', 'POS_SHIP_TO'),
'ICX_SHIP_TO',
get_location( p_po_from.ship_to_location_id ),
get_location( p_po_to.ship_to_location_id )
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_BILL_TO'),
'ICX_BILL_TO',
get_location( p_po_from.bill_to_location_id ),
get_location( p_po_to.bill_to_location_id )
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_PAYMENT_TERMS'),
'ICX_PAYMENT_TERMS',
get_ap_terms( p_po_from.terms_id ),
get_ap_terms( p_po_to.terms_id )
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_AMOUNT'),
'ICX_AMOUNT',
v_amount_from,
v_amount_to
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_SHIP_VIA'),
'ICX_SHIP_VIA',
p_po_from.ship_via_lookup_code,
p_po_to.ship_via_lookup_code
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_FOB'),
'ICX_FOB',
get_po_lookup( 'FOB', p_po_from.fob_lookup_code ),
get_po_lookup( 'FOB', p_po_to.fob_lookup_code )
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_FREIGHT_TERMS'),
'ICX_FREIGHT_TERMS',
get_po_lookup(
'FREIGHT TERMS',
p_po_from.freight_terms_lookup_code ),
get_po_lookup(
'FREIGHT TERMS',
p_po_to.freight_terms_lookup_code )
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_NOTE_TO_VENDOR'),
'ICX_NOTE_TO_VENDOR',
p_po_from.note_to_vendor,
p_po_to.note_to_vendor
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_ACCEPTANCE_REQUIRED'),
'ICX_ACCEPTANCE_REQUIRED',
p_po_from.acceptance_required_flag,
p_po_to.acceptance_required_flag
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_ACCEPTANCE_DUE_DATE'),
'ICX_ACCEPTANCE_DUE_DATE',
--start bug 4179194
/* to_char(p_po_from.acceptance_due_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
to_char(p_po_to.acceptance_due_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_AMOUNT_LIMIT'),
'ICX_AMOUNT_LIMIT',
p_po_from.amount_limit,
p_po_to.amount_limit
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_EFFECTIVE_DATE'),
'ICX_START_DATE',
--start bug 4179194
/* to_char(p_po_from.start_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
to_char(p_po_to.start_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_EXPIRATION_DATE'),
'ICX_EXPIRATION_DATE',
--start bug 4179194
/* to_char(p_po_from.end_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
to_char(p_po_to.end_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_AMOUNT_AGREED'),
'ICX_AMOUNT_AGREED',
p_po_from.blanket_total_amount,
p_po_to.blanket_total_amount
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_VENDOR_SITE',
get_vendor_site(p_po_from.vendor_site_id),
get_vendor_site(p_po_to.vendor_site_id)
);
inserting the IF to check if p_po_from.conterms_deliv_upd_date is NULL
if NOT NULL, then only show a link for deliverables
*/
/*
bug 5471267
inserting the IF to check if p_po_from.conterms_articles_upd_date is NOT NULL
if one of conterms_deliv_upd_date OR conterms_articles_upd_date is NOT NULL, then only show a link for deliverables
*/
IF (p_po_from.conterms_deliv_upd_date IS NOT NULL OR p_po_from.conterms_articles_upd_date IS NOT NULL) THEN
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_CONTERMS_DELIV_DATE',
null,
null
);
inserting the IF to check if p_po_from.conterms_articles_upd_date is NULL
if NULL, then show a link to the contreacts document instead
*/
IF (p_po_from.conterms_articles_upd_date IS NULL) THEN
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_CONTERMS_ARTICLES_DATE_NEW',
null,
null
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_CONTERMS_ARTICLES_DATE',
null,
null
);
* Constant variables to pass for insert_changes
*/
c_level_altered pos_po_revisions_gt.level_altered%TYPE
:= 'ICX_HEADER';
SELECT segment1
INTO v_po_num
FROM po_headers_archive_all
WHERE
po_header_id = p_release_to.po_header_id
AND latest_external_flag = 'Y';
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_NEW'),
'ICX_NEW',
NULL,
NULL
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_string('PO', 'POS_CANCELLED'),
'ICX_CANCELLED',
NULL,
NULL
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_BUYER'),
'ICX_BUYER',
get_buyer( p_release_from.agent_id ),
get_buyer( p_release_to.agent_id )
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_AMOUNT'),
'ICX_AMOUNT',
v_amount_from,
v_amount_to
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_ACCEPTANCE_DUE_DATE'),
'ICX_ACCEPTANCE_DUE_DATE',
--start bug 4179194
/* to_char(p_release_from.acceptance_due_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
to_char(p_release_to.acceptance_due_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_ACCEPTANCE_REQUIRED'),
'ICX_ACCEPTANCE_REQUIRED',
p_release_from.acceptance_required_flag,
p_release_to.acceptance_required_flag
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_revision_num,
c_po_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_RELEASE_NUMBER'),
'ICX_RELEASE_NUMBER',
p_release_from.release_num,
p_release_to.release_num
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_RELEASE_DATE'),
'ICX_RELEASE_DATE',
--start bug 4179194
/* to_char(p_release_from.release_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
to_char(p_release_to.release_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
);
SELECT poh.segment1,poh.po_header_id,pol.line_num
INTO c_po_num,c_po_header_id,c_line_num
FROM po_headers_all poh,po_lines_all pol
WHERE
poh.po_header_id = pol.po_header_id and
pol.po_line_id = c_entity_id;
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_PRICE_TYPE',
get_pricediff_type(p_pdiffs_from.price_type),
get_pricediff_type(p_pdiffs_to.price_type),
null,
c_price_diff_num
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_MULTIPLIER',
to_char(p_pdiffs_from.multiplier),
to_char(p_pdiffs_to.multiplier),
null,
c_price_diff_num
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_MIN_MULTIPLIER',
to_char(p_pdiffs_from.min_multiplier),
to_char(p_pdiffs_to.min_multiplier),
null,
c_price_diff_num
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_MAX_MULTIPLIER',
to_char(p_pdiffs_from.max_multiplier),
to_char(p_pdiffs_to.max_multiplier),
null,
c_price_diff_num
);
SELECT segment1,poh.po_header_id,pll.shipment_num,pol.line_num
INTO c_po_num,c_po_header_id,c_location_num,c_line_num
FROM po_headers_all poh,po_line_locations_all pll,po_lines_all pol
WHERE
poh.po_header_id = pll.po_header_id and
pll.line_location_id = c_entity_id and
pol.po_line_id = pll.po_line_id;
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_PRICE_TYPE',
p_pdiffs_from.price_type,
p_pdiffs_to.price_type,
null,
c_price_diff_num
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_MULTIPLIER',
p_pdiffs_from.multiplier,
p_pdiffs_to.multiplier,
null,
c_price_diff_num
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_MIN_MULTIPLIER',
p_pdiffs_from.min_multiplier,
p_pdiffs_to.min_multiplier,
null,
c_price_diff_num
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_MAX_MULTIPLIER',
p_pdiffs_from.max_multiplier,
p_pdiffs_to.max_multiplier,
null,
c_price_diff_num
);
SELECT segment1
INTO c_po_num
FROM po_headers_archive_all
WHERE
po_header_id = p_ga_ass_to.po_header_id
AND latest_external_flag = 'Y';
select ood.organization_name
into c_org_name
from org_organization_definitions ood
where ood.organization_id = c_enabled_org_id;
select HAOTL.NAME
into c_org_name
from HR_ALL_ORGANIZATION_UNITS_TL HAOTL
where HAOTL.ORGANIZATION_ID = c_enabled_org_id and
HAOTL.LANGUAGE = USERENV('LANG');
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_PURCHASING_ORG',
get_organization(p_ga_ass_from.purchasing_org_id),
get_organization(p_ga_ass_to.purchasing_org_id),
c_org_name
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_ENABLED_VENDOR_SITE',
get_vendor_site(p_ga_ass_from.vendor_site_id),
get_vendor_site(p_ga_ass_to.vendor_site_id),
c_org_name
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_ENABLED_FLAG',
p_ga_ass_from.enabled_flag,
p_ga_ass_to.enabled_flag,
c_org_name
);
* Constant variables to pass for insert_changes
*/
c_level_altered pos_po_revisions_gt.level_altered%TYPE
:= 'ICX_LINE';
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_NEW',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_string('PO', 'POS_CANCELLED'),
'ICX_CANCELLED',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_UNIT_PRICE'),
'ICX_UNIT_PRICE',
p_line_from.unit_price,
p_line_to.unit_price,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_LINE_NUMBER'),
'ICX_LINE_NUMBER',
p_line_from.line_num,
p_line_to.line_num,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_ITEM'),
'ICX_ITEM',
-- get_item_number( p_line_from.item_id ),
-- get_item_number( p_line_to.item_id ),
get_item( p_line_from.item_id, p_line_from.org_id ),
get_item( p_line_to.item_id, p_line_to.org_id ),
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_ITEM'),
'ICX_JOB',
get_job( p_line_from.job_id),
get_job( p_line_to.job_id),
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_CATEGORY',
get_shopping_category(p_line_from.ip_category_id),
get_shopping_category(p_line_to.ip_category_id),
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_ITEM_REVISION'),
'ICX_ITEM_REVISION',
p_line_from.item_revision,
p_line_to.item_revision,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_ITEM_DESCRIPTION'),
'ICX_ITEM_DESCRIPTION',
p_line_from.item_description,
p_line_to.item_description,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_QUANTITY'),
'ICX_QUANTITY',
p_line_from.quantity,
p_line_to.quantity,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_UOM'),
'ICX_UOM',
p_line_from.unit_meas_lookup_code,
p_line_to.unit_meas_lookup_code,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_SOURCE_QT_HEADER'),
'ICX_SOURCE_QT_HEADER',
get_source_quotation_header(
p_line_from.from_header_id ),
get_source_quotation_header(
p_line_to.from_header_id ),
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_SOURCE_QT_LINE'),
'ICX_SOURCE_QT_LINE',
get_source_quotation_line(
p_line_from.from_line_id ),
get_source_quotation_line(
p_line_to.from_line_id ),
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_HAZARD_CLASS'),
'ICX_HAZARD_CLASS',
get_hazard_class( p_line_from.hazard_class_id ),
get_hazard_class( p_line_to.hazard_class_id ),
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_CONTRACT_NUMBER'),
'ICX_CONTRACT_NUMBER',
p_line_from.contract_num,
p_line_to.contract_num,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_SUPPLIER_ITEM_NUM'),
'ICX_SUPPLIER_ITEM_NUM',
p_line_from.vendor_product_num,
p_line_to.vendor_product_num,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_NOTE_TO_VENDOR'),
'ICX_NOTE_TO_VENDOR',
p_line_from.note_to_vendor,
p_line_to.note_to_vendor,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_UN_NUMBER'),
'ICX_UN_NUMBER',
get_un_number( p_line_from.un_number_id ),
get_un_number( p_line_to.un_number_id ),
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_PRICE_TYPE'),
'ICX_PRICE_TYPE',
get_po_lookup(
'PRICE TYPE',
p_line_from.price_type_lookup_code ),
get_po_lookup(
'PRICE TYPE',
p_line_to.price_type_lookup_code ),
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_QUANTITY_AGREED'),
'ICX_QUANTITY_AGREED',
p_line_from.quantity_committed,
p_line_to.quantity_committed,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_AMOUNT_AGREED'),
'ICX_AMOUNT_AGREED',
p_line_from.committed_amount,
p_line_to.committed_amount,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_CLOSED_CODE'),
'ICX_CLOSED_CODE',
get_po_lookup( 'DOCUMENT STATE',
p_line_from.committed_amount ),
get_po_lookup( 'DOCUMENT STATE',
p_line_to.committed_amount ),
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_CONTRATOR_FIRST_NAME',
p_line_from.CONTRACTOR_FIRST_NAME,
p_line_to.CONTRACTOR_FIRST_NAME,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_CONTRATOR_LAST_NAME',
p_line_from.CONTRACTOR_LAST_NAME,
p_line_to.CONTRACTOR_LAST_NAME,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_START_DATE',
--start bug 4179194
/* to_char(p_line_from.start_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
to_char(p_line_to.start_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_EXPIRATION_DATE'),
'ICX_EXPIRATION_DATE',
--start bug 4179194
/* to_char(p_line_from.expiration_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
to_char(p_line_to.expiration_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
-- start fix for bug 5388428 - this takes back changes we made for bug 5167605
'ICX_UNIT_PRICE',
-- 'ICX_AMOUNT',
-- end fix
p_line_from.amount,
p_line_to.amount,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'POS_UNIT_PRICE_LIMIT',
p_line_from.not_to_exceed_price,
p_line_to.not_to_exceed_price,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_RETAINAGE_RATE',
p_line_from.RETAINAGE_RATE,
p_line_to.RETAINAGE_RATE,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_MAX_RETAINAGE_AMOUNT',
p_line_from.MAX_RETAINAGE_AMOUNT,
p_line_to.MAX_RETAINAGE_AMOUNT,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_PROGRESS_PAYMENT_RATE',
p_line_from.PROGRESS_PAYMENT_RATE,
p_line_to.PROGRESS_PAYMENT_RATE,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_RECOUPMENT_RATE',
p_line_from.RECOUPMENT_RATE,
p_line_to.RECOUPMENT_RATE,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
/* Doing this comparison at the shipment level and inserting change as change in line
search for ADVAMT to look for code block replacing this */
/*
--start advance amount
IF ( Get_Line_adv_Amount_revision(p_line_from.po_line_id, p_line_from.revision_num ) <>
Get_Line_adv_Amount_revision( p_line_to.po_line_id, p_line_to.revision_num ) THEN
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_ADVANCE_AMOUNT',
Get_Line_adv_Amount_revision(p_line_from.po_line_id, p_line_from.revision_num,
Get_Line_adv_Amount_revision( p_line_to.po_line_id, p_line_to.revision_num),
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
/* Constant variables to pass for insert_changes */
c_level_altered pos_po_revisions_gt.level_altered%TYPE
:= 'ICX_SHIPMENT';
select line_num ,item_id, job_id, org_id
into c_line_num,c_item_id, c_job_id, c_org_id
from po_lines_all
where po_line_id = p_loc_to.po_line_id;
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_NEW'),
'ICX_NEW',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
NULL,
NULL,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
NULL,
NULL,
'ICX_LINE',
'ICX_ADVANCE_AMOUNT',
p_loc_from.amount,
p_loc_to.amount,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_string('PO', 'POS_CANCELLED'),
'ICX_CANCELLED',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_SHIP_NUM'),
'ICX_SHIP_NUM',
p_loc_from.shipment_num,
p_loc_to.shipment_num,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_SHIP_TO'),
'ICX_SHIP_TO',
get_location( p_loc_from.ship_to_location_id ),
get_location( p_loc_to.ship_to_location_id ),
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_QUANTITY'),
'ICX_QUANTITY',
p_loc_from.quantity,
p_loc_to.quantity,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_PROMISED_DATE'),
'ICX_PROMISED_DATE',
null,
null,
null,
null,
p_loc_from.promised_date,
p_loc_to.promised_date,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_NEED_BY_DATE'),
'ICX_NEED_BY_DATE',
null,
null,
null,
null,
p_loc_from.need_by_date,
p_loc_to.need_by_date,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
p_loc_from.revision_num || '-' || p_loc_to.revision_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_LAST_ACCEPT_DATE'),
'ICX_LAST_ACCEPT_DATE',
--start bug 4179194
/*
to_char(p_loc_from.last_accept_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
to_char(p_loc_to.last_accept_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_TAXABLE_FLAG'),
'ICX_TAXABLE_FLAG',
get_po_lookup(
'YES/NO',
p_loc_from.taxable_flag ),
get_po_lookup(
'YES/NO',
p_loc_to.taxable_flag ),
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_PRICE_BREAK'),
'ICX_PRICE_BREAK',
p_loc_from.price_override,
p_loc_to.price_override,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_UNIT_BREAK'),
'ICX_UNIT_PRICE',
p_loc_from.price_override,
p_loc_to.price_override,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_START_EFF_DATE',
--start bug 4179194
/*
to_char(p_loc_from.start_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
to_char(p_loc_to.start_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_END_EFF_DATE',
--start bug 4179194
/* to_char(p_loc_from.end_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
to_char(p_loc_to.end_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
-- start fix for bug 5388428
'ICX_UNIT_PRICE',
-- 'ICX_AMOUNT',
-- end fix
p_loc_from.amount,
p_loc_to.amount,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_OWNER',
get_owner(p_loc_from.work_approver_id),
get_owner(p_loc_to.work_approver_id),
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_PAY_DESCRIPTION',
p_loc_from.description,
p_loc_to.description,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_PAY_TYPE',
v_from_payment_type,
v_to_payment_type,
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
'ICX_VALUE_PERCENT',
Get_ship_val_percent_revision (p_loc_from.line_location_id, p_loc_from.revision_num),
Get_ship_val_percent_revision (p_loc_to.line_location_id, p_loc_to.revision_num),
NULL,
NULL,
NULL,
NULL,
c_item,
c_job
);
* Constant variables to pass for insert_changes
*/
c_level_altered pos_po_revisions_gt.level_altered%TYPE
:= 'ICX_DISTRIBUTION';
select line_num,item_id
into c_line_num,c_item_id
from po_lines_all
where po_line_id = p_dist_to.po_line_id;
select shipment_num
into c_location_num
from po_line_locations_all
where line_location_id = p_dist_to.line_location_id;
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_NEW'),
'ICX_NEW',
NULL,
NULL
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_QUANTITY'),
'ICX_QUANTITY',
p_dist_from.quantity_ordered,
p_dist_to.quantity_ordered
);
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_REQUESTOR'),
'ICX_REQUESTOR',
get_requestor(p_dist_from.deliver_to_person_id),
get_requestor(p_dist_to.deliver_to_person_id)
);
inserting the associated charge account instead of the
ccid by using the function get_charge_account.
*/
IF NVL( p_dist_from.code_combination_id, -99 ) <>
NVL( p_dist_to.code_combination_id, -99 ) THEN
insert_changes(
p_sequence,
c_po_header_id,
c_release_id,
c_line_id,
c_location_id,
c_distribution_id,
c_item_id,
c_po_num,
c_revision_num,
c_line_num,
c_location_num,
c_distribution_num,
c_level_altered,
--fnd_message.get_String('PO', 'POS_CHARGE_ACCT'),
'ICX_CHARGE_ACCT',
get_charge_account(p_dist_from.code_combination_id),
get_charge_account(p_dist_to.code_combination_id)
);
SELECT concatenated_segments
INTO v_charge_account
FROM gl_code_combinations_kfv
WHERE code_combination_id = p_code_combination_id;
SELECT full_name
INTO v_full_name
FROM per_people_f
WHERE person_id = p_agent_id and rownum = 1
order by effective_start_date desc ;
select category_name
INTO v_category_name
from icx_cat_categories_v
where rt_category_id = p_category_id
and language = USERENV('LANG')
and rownum = 1;
select hecv.full_name
INTO v_full_name
from PER_PEOPLE_F --hr_employees_current_v
hecv, fnd_user fu
where fu.user_id = p_work_approver_id and
fu.employee_id = hecv.person_id (+) --employee_id (+)
and rownum = 1
order by hecv.effective_start_date desc ;
SELECT icx_po_history_details_s.nextval
INTO p_nextval
FROM DUAL;
SELECT name
INTO v_job_name
FROM per_jobs
WHERE job_id = p_job_id and rownum = 1;
select msik.concatenated_segments
INTO v_item_name
from mtl_system_items_kfv msik, FINANCIALS_SYSTEM_PARAMS_ALL FSP
where msik.INVENTORY_ITEM_ID = P_ITEM_ID
AND FSP.INVENTORY_ORGANIZATION_ID = NVL(msik.ORGANIZATION_ID,FSP.INVENTORY_ORGANIZATION_ID)
AND FSP.ORG_ID (+)= P_ORG_ID and rownum = 1;
select PLL.amount
from po_line_locations_archive_all PLL
where PLL.po_line_id = p_po_line_id
and PLL.payment_type = 'ADVANCE'
and PLL.shipment_type = 'PREPAYMENT'
and PLL.revision_num = (select max(revision_num) from po_line_locations_archive_all PLL1
where PLL1.line_location_id = PLL.line_location_id
and PLL1.revision_num <= p_revision_num);
select ROUND(DECODE(PLL.matching_basis,
'AMOUNT', (NVL(PLL.amount, 0)/POL.amount)*100,
'QUANTITY', (NVL(PLL.price_override, 0)/POL.unit_price)*100))
from PO_LINE_LOCATIONS_ARCHIVE_ALL PLL,
PO_LINES_ARCHIVE_ALL POL
where PLL.po_line_id = POL.po_line_id
and PLL.line_location_id = p_po_line_location_id
and PLL.payment_type = 'MILESTONE'
and PLL.revision_num = (select max(revision_num) from po_line_locations_archive_all PLL1
where PLL1.line_location_id = PLL.line_location_id
and PLL1.revision_num <= p_revision_num)
and POL.revision_num = (select max(revision_num) from po_lines_archive_all POL1
where POL1.po_line_id = POL.po_line_id
and POL1.revision_num <= p_revision_num);