[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
D_secondary_uom_update CONSTANT VARCHAR2(100) :=
PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'secondary_uom_update');
INSERT INTO PO_VALIDATION_RESULTS_GT
( result_set_id
, entity_type
, entity_id
, column_name
, column_val
, message_name
-- PBWC Message Change Impact: Adding a token
, token1_name
, token1_value
)
SELECT
x_result_set_id
, p_entity_type
, p_entity_id_tbl(i)
, p_column_name
, TO_CHAR(p_date_tbl(i))
, p_message_name
-- PBWC Message Change Impact: Adding a token
, p_token1_name
, to_char(p_token1_value(i))
FROM
DUAL
WHERE NOT EXISTS
( SELECT null
FROM
GL_PERIOD_STATUSES PO_PERIOD
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE
FSP.org_id = p_org_id_tbl(i)
AND PO_PERIOD.set_of_books_id = FSP.set_of_books_id
AND PO_PERIOD.application_id = 201 -- PO
AND PO_PERIOD.adjustment_period_flag = 'N'
AND PO_PERIOD.closing_status IN ('O','F')
AND TRUNC(p_date_tbl(i))
BETWEEN TRUNC(PO_PERIOD.start_date) AND TRUNC(PO_PERIOD.end_date)
)
;
INSERT INTO PO_SESSION_GT SES
( key
, num1
, date1
, date2
)
VALUES
( l_data_key
, p_line_id_tbl(i)
, l_start_date_tbl(i)
, l_expiration_date_tbl(i)
);
SELECT
LINE.po_line_id
, SES.date2
BULK COLLECT INTO
l_line_id_tbl
, l_end_date_tbl
FROM
PO_SESSION_GT SES
, PO_LINES_ALL LINE
, PO_HEADERS_ALL HEADER
WHERE
SES.key = l_data_key
AND LINE.po_line_id = SES.num1
AND HEADER.po_header_id = LINE.po_header_id
AND (l_ignore_start_date_flag = 'Y' OR SES.date1 > LINE.start_date)
AND (l_ignore_expiration_date_flag = 'Y' OR SES.date2 < LINE.expiration_date)
AND HEADER.type_lookup_code = c_STANDARD
AND LINE.order_type_lookup_code = c_RATE
;
INSERT INTO PO_VALIDATION_RESULTS_GT
( result_set_id
, result_type
, entity_type
, entity_id
, column_name
, message_name
)
SELECT
x_result_set_id
, PO_VALIDATIONS.c_result_type_WARNING
, c_ENTITY_TYPE_LINE
, p_line_id_tbl(i)
, p_column_name
, p_message_name
FROM
PO_LINES_ALL PO_LINE
WHERE
PO_LINE.po_line_id = p_line_id_tbl(i)
AND
( l_quantity_flag <> 'Y'
OR
( l_quantity_tbl(i) IS NOT NULL
-- Quantity is being changed from the transaction quantity:
AND l_quantity_tbl(i) <> PO_LINE.quantity
)
)
AND EXISTS
( SELECT NULL
FROM PO_NOTIFICATION_CONTROLS NTF
WHERE
NTF.po_header_id = PO_LINE.po_header_id
AND NTF.notification_condition_code <> c_EXPIRATION
)
;
INSERT INTO PO_SESSION_GT
( key
, index_num1
, index_num2
, num1
, char1 --
)
SELECT
l_data_key
, DIST.line_location_id
, DIST.po_distribution_id
, DIST.distribution_num
, DIST.distribution_type --
FROM
PO_DISTRIBUTIONS_MERGE_V DIST
WHERE
DIST.line_location_id = l_parent_id_tbl(i)
--
--do not consider the PAR from which this is autocreated
AND DIST.draft_id NOT IN (SELECT * FROM TABLE(l_par_draft_id_tbl)) --
-- do not consider the corresponding dist on PAR
AND DIST.po_distribution_id <>Nvl ((SELECT par_distribution_id
FROM po_distributions_draft_all
WHERE po_distribution_id = p_entity_id_tbl(i)
AND draft_id = p_draft_id_tbl(i)),-999);
INSERT INTO PO_SESSION_GT
( key
, index_num1
, index_num2
, num1
, char1 --
)
SELECT
l_data_key
, LINE_LOC.po_line_id
, LINE_LOC.line_location_id
, LINE_LOC.shipment_num
, LINE_LOC.shipment_type --
FROM
PO_LINE_LOCATIONS_MERGE_V LINE_LOC ,
PO_LINES_MERGE_V LINE
WHERE LINE.po_line_id = LINE_LOC.po_line_id
AND LINE_LOC.po_line_id = l_parent_id_tbl(i)
-- : Don't validate shipment_num
-- on advance pay items, since user cannot set, and
-- because deletions do not propagate to the DB before
-- validation, which means the validation would fail often.
AND ( LINE_LOC.payment_type IS NULL
OR LINE_LOC.payment_type <> 'ADVANCE' )
--
--do not consider the PAR from which this is autocreated
AND LINE_LOC.draft_id NOT IN (SELECT * FROM TABLE(l_par_draft_id_tbl)) --
-- do not consider the corresponding shipment on PAR
AND Nvl(LINE.mod_line_id,-999 )<> l_parent_id_tbl(i);
INSERT INTO PO_SESSION_GT
( key
, index_num1
, index_num2
, num1
, char1 --
)
SELECT
l_data_key
, LINE.po_header_id
, LINE.po_line_id
, LINE.line_num
, NULL --
FROM
PO_LINES_MERGE_V LINE
WHERE
LINE.po_header_id = l_parent_id_tbl(i)
--
--do not consider the PAR from which this is autocreated
AND LINE.draft_id NOT IN (SELECT * FROM TABLE(l_par_draft_id_tbl)) --
-- do not consider the corresponding line on PAR
AND Nvl(LINE.mod_line_id, -999) <> p_entity_id_tbl(i) ;
WHEN MATCHED THEN UPDATE SET
SES.index_num1 = p_parent_id_tbl(i),
SES.num1 = p_entity_num_tbl(i),
SES.index_char2 = c_NEW
WHEN NOT MATCHED THEN INSERT
( key
, index_num1
, index_num2
, num1
, index_char2
)
VALUES
( l_data_key
, p_parent_id_tbl(i)
, p_entity_id_tbl(i)
, p_entity_num_tbl(i)
, c_NEW
);
UPDATE PO_SESSION_GT SES
SET SES.char1 = p_entity_type_tbl(i)
WHERE SES.key = l_data_key
AND SES.index_num2 = p_entity_id_tbl(i);
INSERT INTO PO_VALIDATION_RESULTS_GT
( result_set_id
, entity_type
, entity_id
, column_name
, column_val
, message_name
)
SELECT
x_result_set_id
, p_entity_type
, CHILD.index_num2
, l_column_name
, TO_CHAR(CHILD.num1)
, decode(char1,
PO_CONSTANTS_SV.SHIP_TYPE_PRICE_BREAK, PO_MESSAGE_S.PO_PO_ENTER_UNIQUE_PRC_BRK_NUM,
l_message_name)
FROM
PO_SESSION_GT CHILD
WHERE
CHILD.key = l_data_key
AND CHILD.index_char2 = c_NEW
AND EXISTS
( SELECT null
FROM PO_SESSION_GT SIBLING
WHERE
SIBLING.key = l_data_key
AND SIBLING.index_num1 = CHILD.index_num1 -- parent id
AND SIBLING.num1 = CHILD.num1 -- child num
AND SIBLING.index_num2 <> CHILD.index_num2 -- child id
-- : if using filter, check that char1s match
AND ((p_entity_type_tbl IS NULL) OR (SIBLING.char1 = CHILD.char1))
--
)
;
INSERT INTO PO_SESSION_GT
( key
, index_num1
, index_char1
, index_num2
, char1
)
SELECT
l_data_key
, PRICE_DIFF.entity_id
, PRICE_DIFF.entity_type
, PRICE_DIFF.price_differential_id
, DECODE(p_column_name
, c_PRICE_TYPE, PRICE_DIFF.price_type
, TO_CHAR(PRICE_DIFF.price_differential_num)
)
FROM
PO_PRICE_DIFF_MERGE_V PRICE_DIFF
WHERE
PRICE_DIFF.entity_id = l_parent_id_tbl(i)
;
WHEN MATCHED THEN UPDATE SET
SES.index_num1 = p_entity_id_tbl(i)
, SES.index_char1 = p_entity_type_tbl(i)
, SES.char1 = p_unique_value_tbl(i)
, SES.index_char2 = c_NEW
WHEN NOT MATCHED THEN INSERT
( key
, index_num1
, index_char1
, index_num2
, char1
, index_char2
)
VALUES
( l_data_key
, p_entity_id_tbl(i)
, p_entity_type_tbl(i)
, p_price_diff_id_tbl(i)
, p_unique_value_tbl(i)
, c_NEW
);
INSERT INTO PO_VALIDATION_RESULTS_GT
( result_set_id
, entity_type
, entity_id
, column_name
, column_val
, message_name
, token1_name --bug #4956116
, token1_value --bug #4956116
)
SELECT
x_result_set_id
, PO_VALIDATIONS.c_entity_type_PRICE_DIFF
, CHILD.index_num2
, p_column_name
, CHILD.char1
, p_message_name
, p_column_name --bug #4956116
, (select displayed_field from po_lookup_codes where lookup_code = CHILD.char1 and lookup_type = 'PRICE DIFFERENTIALS') --bug #4956116
FROM
PO_SESSION_GT CHILD
WHERE
CHILD.key = l_data_key
AND CHILD.index_char2 = c_NEW
AND EXISTS
( SELECT null
FROM PO_SESSION_GT SIBLING
WHERE
SIBLING.key = l_data_key
AND SIBLING.index_num1 = CHILD.index_num1 -- entity_id
AND SIBLING.index_char1 = CHILD.index_char1 -- entity_type
AND SIBLING.index_num2 <> CHILD.index_num2 -- price_differential_id
AND SIBLING.char1 = CHILD.char1 -- price_differential_num or price_type
)
;
, p_column_val_selector IN VARCHAR2
, p_message_name IN VARCHAR2
, p_validation_id IN NUMBER DEFAULT NULL
, x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
, x_result_type OUT NOCOPY VARCHAR2
)
IS
d_mod CONSTANT VARCHAR2(100) := D_start_date_le_end_date;
PO_LOG.proc_begin(d_mod,'p_column_val_selector',p_column_val_selector);
IF (p_column_val_selector = c_START_DATE) THEN
l_column_val := p_start_date_tbl(i);
ELSIF (p_column_val_selector = c_END_DATE) THEN
l_column_val := p_end_date_tbl(i);
INSERT INTO PO_VALIDATION_RESULTS_GT
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value,
validation_id)
SELECT x_result_set_id,
PO_VALIDATIONS.c_result_type_FAILURE,
p_entity_type,
p_entity_id_tbl(i),
'PO_PDOI_INVALID_PAY_TERMS',
'TERMS_ID',
p_terms_id_tbl(i),
'VALUE',
p_terms_id_tbl(i),
p_validation_id
FROM DUAL
WHERE p_terms_id_tbl(i) IS NOT NULL
AND NOT EXISTS
(SELECT 1
FROM AP_TERMS APT
WHERE p_terms_id_tbl(i) = APT.TERM_ID
AND sysdate BETWEEN
nvl(APT.start_date_active, sysdate - 1) AND
nvl(APT.end_date_active, sysdate + 1)); -- END WHERE, FORALL
SELECT decode(msi.tracking_quantity_ind,'PS',msi.secondary_default_ind,NULL)
INTO l_sec_default_ind
FROM mtl_system_items msi
WHERE msi.organization_id = p_inv_org_id_tbl(i)
AND msi.inventory_item_id = p_item_id_tbl(i);
SELECT decode(msi.tracking_quantity_ind,'PS',msi.secondary_default_ind,NULL)
INTO l_sec_default_ind
FROM mtl_system_items msi
WHERE msi.organization_id = p_inv_org_id_tbl(i)
AND msi.inventory_item_id = p_item_id_tbl(i);
p_create_or_update_item_flag IN VARCHAR2,
x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
x_result_type OUT NOCOPY VARCHAR2)
IS
d_mod CONSTANT VARCHAR2(100) := d_secondary_unit_of_measure;
PO_LOG.proc_begin(d_mod,'p_create_or_update_item_flag',p_create_or_update_item_flag);
IF ((p_create_or_update_item_flag = 'Y' AND p_item_id_tbl(i) IS NULL AND p_item_tbl(i) IS NULL) OR
(p_create_or_update_item_flag = 'N' and p_item_id_tbl(i) IS NULL)) AND
p_secondary_unit_of_meas_tbl(i) IS NOT NULL THEN
IF (p_entity_type = PO_VALIDATIONS.c_entity_type_line) THEN
l_validation_id := PO_VAL_CONSTANTS.c_line_secondary_uom_null;
p_create_or_update_item_flag IN VARCHAR2,
x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
x_result_type OUT NOCOPY VARCHAR2)
IS
d_mod CONSTANT VARCHAR2(100) := d_secondary_quantity;
PO_LOG.proc_begin(d_mod,'p_create_or_update_item_flag',p_create_or_update_item_flag);
IF ((p_create_or_update_item_flag = 'Y' AND p_item_id_tbl(i) IS NULL AND p_item_tbl(i) IS NULL) OR
(p_create_or_update_item_flag = 'N' AND p_item_id_tbl(i) IS NULL )) AND
p_secondary_quantity_tbl(i) IS NOT NULL AND
p_doc_type IN ('STANDARD', 'BLANKET') THEN
IF (p_entity_type = PO_VALIDATIONS.c_entity_type_line) THEN
l_validation_id := PO_VAL_CONSTANTS.c_line_sec_quantity_not_reqd;
PROCEDURE secondary_uom_update(
p_id_tbl IN po_tbl_number,
p_entity_type IN VARCHAR2,
p_secondary_unit_of_meas_tbl IN po_tbl_varchar30,
p_item_id_tbl IN po_tbl_number,
p_organization_id_tbl IN po_tbl_number,
p_create_or_update_item_flag IN VARCHAR2,
x_result_set_id IN OUT NOCOPY NUMBER,
x_result_type OUT NOCOPY VARCHAR2)
IS
d_mod CONSTANT VARCHAR2(100) := d_secondary_uom_update;
PO_LOG.proc_begin(d_mod,'p_create_or_update_item_flag',p_create_or_update_item_flag);
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
token1_name,
token2_name,
token3_name,
token1_value,
token2_value,
token3_value)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
p_entity_type,
p_id_tbl(i),
'PO_PDOI_ITEM_RELATED_INFO',
'SECONDARY_UNIT_OF_MEASURE',
'COLUMN_NAME',
'VALUE',
'ITEM',
'SECONDARY_UNIT_OF_MEASURE',
p_secondary_unit_of_meas_tbl(i),
p_item_id_tbl(i)
FROM DUAL
WHERE p_item_id_tbl(i) IS NOT NULL
AND p_organization_id_tbl(i) IS NOT NULL
AND p_secondary_unit_of_meas_tbl(i) IS NOT NULL
AND EXISTS(
SELECT 1
FROM mtl_system_items msi
WHERE msi.inventory_item_id = p_item_id_tbl(i)
AND msi.organization_id = p_organization_id_tbl(i)
AND p_secondary_unit_of_meas_tbl(i) <> msi.secondary_uom_code);
END secondary_uom_update;
p_create_or_update_item_flag IN VARCHAR2,
p_validation_id IN NUMBER DEFAULT NULL,
x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
x_result_set_id IN OUT NOCOPY NUMBER,
x_result_type OUT NOCOPY VARCHAR2)
IS
d_mod CONSTANT VARCHAR2(100) := d_preferred_grade;
PO_LOG.proc_begin(d_mod,'p_create_or_update_item_flag',p_create_or_update_item_flag);
IF ((p_create_or_update_item_flag = 'Y' AND p_item_id_tbl(i) IS NULL AND p_item_tbl(i) IS NULL) OR
(p_create_or_update_item_flag = 'N' AND p_item_id_tbl(i) IS NULL )) AND
p_preferred_grade_tbl(i) IS NOT NULL THEN
IF (p_validation_id = PO_VAL_CONSTANTS.c_line_preferred_grade) THEN
l_validation_id := PO_VAL_CONSTANTS.c_line_preferred_grade_item;
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
p_entity_type,
p_id_tbl(i),
'PO_ITEM_NOT_GRADE_CTRL',
'PREFERRED_GRADE',
p_preferred_grade_tbl(i),
DECODE(p_validation_id, PO_VAL_CONSTANTS.c_line_preferred_grade,
PO_VAL_CONSTANTS.c_line_preferred_grade_item,
PO_VAL_CONSTANTS.c_loc_preferred_grade_item)
FROM DUAL
WHERE p_preferred_grade_tbl(i) IS NOT NULL
AND p_item_id_tbl(i) IS NOT NULL
AND EXISTS(
SELECT 1
FROM mtl_system_items msi
WHERE msi.inventory_item_id = p_item_id_tbl(i)
AND msi.organization_id = p_organization_id_tbl(i)
AND nvl(msi.grade_control_flag,'N') = 'N');
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
p_entity_type,
p_id_tbl(i),
'PO_INVALID_GRADE_CODE',
'PREFERRED_GRADE',
p_preferred_grade_tbl(i),
DECODE(p_validation_id, PO_VAL_CONSTANTS.c_line_preferred_grade,
PO_VAL_CONSTANTS.c_line_preferred_grade_valid,
PO_VAL_CONSTANTS.c_loc_preferred_grade_valid)
FROM DUAL
WHERE p_preferred_grade_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM mtl_grades_b mgb
WHERE mgb.grade_code = p_preferred_grade_tbl(i) AND
mgb.disable_flag = 'N');
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
p_entity_type,
p_id_tbl(i),
'PO_OPS_ITEM_PROCESS_ORG',
'SHIP_TO_ORGANIZATION_ID',
p_ship_to_organization_id_tbl(i)
FROM DUAL
WHERE p_ship_to_organization_id_tbl(i) IS NOT NULL
AND p_item_id_tbl(i) IS NOT NULL
AND EXISTS(
SELECT 1
FROM mtl_system_items msi,
mtl_parameters mp
WHERE msi.inventory_item_id = p_item_id_tbl(i)
AND msi.organization_id = p_ship_to_organization_id_tbl(i)
AND msi.organization_id = mp.organization_id
AND msi.outside_operation_flag = 'Y'
AND mp.process_enabled_flag = 'Y');
INSERT INTO po_session_gt(key, num1, char1)
SELECT l_key, l_index_tbl(i), uom.unit_of_measure
FROM mtl_system_items msi,
mtl_units_of_measure uom
WHERE msi.inventory_item_id = p_item_id_tbl(i)
AND msi.organization_id = p_organization_id_tbl(i)
AND msi.tracking_quantity_ind = 'PS'
AND msi.secondary_uom_code = uom.uom_code;
DELETE FROM po_session_gt
WHERE key = l_key
RETURNING num1, char1 BULK COLLECT INTO l_index1_tbl, l_result1_tbl;
select REGEXP_SUBSTR(p_par_draft_id ,'[^,]+', 1, LEVEL) l_par_draft_id FROM dual
CONNECT BY regexp_substr(p_par_draft_id, '[^,]+', 1, LEVEL) IS NOT NULL
) LOOP
IF context_cur.l_par_draft_id IS NOT NULL THEN
l_par_draft_id_tbl.extend;