[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
function update_count(id IN NUMBER) return varchar2
IS
BEGIN
g_updated_ids.extend();
g_updated_ids(g_updated_ids.count) := id;
function get_updated_ids return JTF_NUMBER_TABLE
IS
BEGIN
return g_updated_ids;
END get_updated_ids;
procedure update_rma_ro_line_id(id IN NUMBER)
IS
BEGIN
if not g_rma_ro_line_ids.exists(id) then
g_rma_ro_line_ids(id) := 1;
procedure update_ship_ro_line_id(id IN NUMBER)
IS
BEGIN
if not g_ship_ro_line_ids.exists(id) then
g_ship_ro_line_ids(id) := 1;
procedure update_int_order_header_id(id IN NUMBER)
IS
BEGIN
if not g_int_order_header_ids.exists(id) then
g_int_order_header_ids(id) := 1;
/* Called from : Called from Update API */
/* */
/* STANDARD PARAMETERS */
/* In Parameters : */
/* p_message Required Debug message that needs to be logged */
/* p_mod_name Required Module name */
/* p_severity_level Required Severity level */
/* Output Parameters: */
/* NON-STANDARD PARAMETERS */
/* In Parameters */
/* Out parameters */
/* Change Hist : */
/* 09/20/03 vlakaman Initial Creation. */
/*-------------------------------------------------------------------------------------*/
Procedure DEBUG
(p_message in varchar2,
p_mod_name in varchar2,
p_severity_level in number
) IS
-- Variables used in FND Log
l_stat_level number := FND_LOG.LEVEL_STATEMENT;
SELECT name
from cs_incident_statuses
where trunc(sysdate) between nvl(start_date_active, sysdate - 1)
and nvl(end_date_active, sysdate + 1)
and incident_status_id = c_incident_status_id;
select ml.meaning status_name
from mfg_lookups ml
where ml.lookup_type = 'WIP_JOB_STATUS'
and ml.lookup_code = c_job_status_id
and enabled_flag = 'Y'
and trunc(sysdate) between nvl(start_date_active, sysdate - 1)
and nvl(end_date_active, sysdate + 1);
select name
from jtf_task_statuses_vl
where task_status_id = c_task_status_id and
trunc(sysdate) between nvl(start_date_active, sysdate - 1)
and nvl(end_date_active, sysdate + 1);
select meaning
from fnd_lookup_values
where lookup_type = 'AUTHORIZATION STATUS'
AND LOOKUP_CODE = NVL(c_auth_status_code,'INCOMPLETE')
AND LANGUAGE = USERENV('LANG')
and enabled_flag = 'Y'
and trunc(sysdate) between nvl(start_date_active, sysdate - 1)
and nvl(end_date_active, sysdate + 1);
select
lkp.meaning status_name
from
oe_lookups lkp
where
lkp.lookup_type = 'LINE_FLOW_STATUS'
and lkp.lookup_code = c_om_line_status_code
and enabled_flag = 'Y'
and trunc(sysdate) between nvl(start_date_active, sysdate - 1)
and nvl(end_date_active, sysdate + 1);
select meaning
from wsh_lookups
where lookup_type = 'PICK_STATUS' and
lookup_code = c_status_code and
enabled_flag = 'Y' and
trunc(sysdate) between nvl(start_date_active, sysdate - 1)
and nvl(end_date_active, sysdate + 1);
l_updated_keys JTF_NUMBER_TABLE;
SELECT prev_status_id, status_id, repair_line_id, document_header_id
FROM CSD_SO_ORCHESTRATION
WHERE orchestration_id = c_orchestration_id;
g_updated_ids := JTF_NUMBER_TABLE();
select
xref.repair_line_id,
wdj.wip_entity_id document_header_id,
null document_line_id,
we.wip_entity_name document_header_name,
null document_line_name,
'JOB' document_type,
wdj.status_type status_id,
null status_code,
we.organization_id org_id
from
csd_repairs dra,
wip_discrete_jobs wdj,
wip_entities we,
ORG_ORGANIZATION_DEFINITIONS orgd,
csd_repair_job_xref xref
where
xref.wip_entity_id = wdj.wip_entity_id and
xref.wip_entity_id = we.wip_entity_id and
xref.repair_line_id = dra.repair_line_id and
orgd.organization_id = we.organization_id and
we.wip_entity_id = decode(p_wip_entity_id,
fnd_api.g_miss_num, we.wip_entity_id,
p_wip_entity_id) and
dra.repair_line_id = decode(p_repair_line_id,
fnd_api.g_miss_num, dra.repair_line_id,
p_repair_line_id) and
nvl(orgd.operating_unit, fnd_api.g_miss_num) =
decode(p_operating_unit_id,
fnd_api.g_miss_num, nvl(orgd.operating_unit, fnd_api.g_miss_num),
p_operating_unit_id) and
we.organization_id =
decode(p_inventory_org_id,
fnd_api.g_miss_num, we.organization_id,
p_inventory_org_id) and
nvl(dra.resource_id, fnd_api.g_miss_num) =
decode(p_repair_owner_id,
fnd_api.g_miss_num, nvl(dra.resource_id, fnd_api.g_miss_num),
p_repair_owner_id) and
dra.flow_status_id =
decode(p_ro_status_id,
fnd_api.g_miss_num, dra.flow_status_id,
p_ro_status_id) and
dra.repair_type_id =
decode(p_repair_type_id,
fnd_api.g_miss_num, dra.repair_type_id,
p_repair_type_id) and
nvl(dra.ro_priority_code, fnd_api.g_miss_char) =
decode(p_ro_priority_code,
fnd_api.g_miss_char, nvl(dra.ro_priority_code, fnd_api.g_miss_char),
p_ro_priority_code) and
dra.creation_date >=
decode(p_ro_age,
fnd_api.g_miss_num, dra.creation_date,
sysdate - p_ro_age) and
nvl(dra.promise_date, G_MAX_DATE) <=
decode(p_ro_due_date_age,
fnd_api.g_miss_num, nvl(dra.promise_date, G_MAX_DATE),
sysdate + p_ro_due_date_age)) OLTP
ON (soo.repair_line_id = OLTP.repair_line_id and
soo.document_header_id = OLTP.document_header_id and
soo.document_type = 'JOB')
WHEN MATCHED THEN
UPDATE SET
soo.prev_status_id = (CASE update_count(soo.orchestration_id) when 'A' then soo.status_id end),
soo.status_id = OLTP.status_id,
soo.object_version_number = soo.object_version_number + 1,
soo.last_update_date = sysdate,
soo.last_updated_by = l_user_id,
soo.last_update_login = l_login_id
WHEN NOT MATCHED THEN
INSERT (ORCHESTRATION_ID,
REPAIR_LINE_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
DOCUMENT_HEADER_ID,
DOCUMENT_LINE_ID,
DOCUMENT_HEADER_NAME,
DOCUMENT_LINE_NAME,
DOCUMENT_TYPE,
STATUS_ID,
STATUS_CODE,
PREV_STATUS_ID,
PREV_STATUS_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PARAMN1)
VALUES
(csd_so_orchestration_s1.nextval,
OLTP.repair_line_id,
1,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_login_id,
OLTP.document_header_id,
null, -- document_line_id
OLTP.document_header_name,
null, -- document_line_name
'JOB',
OLTP.status_id,
null, --status_code
null, -- prev_status_id
null, -- prev_status_code
null, -- attribute_category
null, -- attribute1
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
OLTP.org_id); --paramn1
l_updated_keys := get_updated_ids();
FOR i IN 1..l_updated_keys.COUNT
LOOP
OPEN c_get_defaulting_info(l_updated_keys(i));
l_updated_keys JTF_NUMBER_TABLE;
SELECT prev_status_id, status_id, repair_line_id
FROM CSD_SO_ORCHESTRATION
WHERE orchestration_id = c_orchestration_id;
g_updated_ids := JTF_NUMBER_TABLE();
select
dra.repair_line_id,
dra.incident_id document_header_id,
null document_line_id,
inc.incident_number document_header_name,
null document_line_name,
'SR' document_type,
inc.incident_status_id status_id,
null status_code
from
csd_repairs dra,
ORG_ORGANIZATION_DEFINITIONS orgd,
cs_incidents_all_b inc
where
dra.incident_id = inc.incident_id and
orgd.organization_id(+) = inc.inv_organization_id and
inc.incident_id = decode(p_incident_id,
fnd_api.g_miss_num, inc.incident_id,
p_incident_id) and
dra.repair_line_id = decode(p_repair_line_id,
fnd_api.g_miss_num, dra.repair_line_id,
p_repair_line_id) and
nvl(orgd.operating_unit, fnd_api.g_miss_num) =
decode(p_operating_unit_id,
fnd_api.g_miss_num, nvl(orgd.operating_unit, fnd_api.g_miss_num),
p_operating_unit_id) and
nvl(inc.inv_organization_id, fnd_api.g_miss_num) =
decode(p_inventory_org_id,
fnd_api.g_miss_num, nvl(inc.inv_organization_id, fnd_api.g_miss_num),
p_inventory_org_id) and
nvl(dra.resource_id, fnd_api.g_miss_num) =
decode(p_repair_owner_id,
fnd_api.g_miss_num, nvl(dra.resource_id, fnd_api.g_miss_num),
p_repair_owner_id) and
dra.flow_status_id =
decode(p_ro_status_id,
fnd_api.g_miss_num, dra.flow_status_id,
p_ro_status_id) and
dra.repair_type_id =
decode(p_repair_type_id,
fnd_api.g_miss_num, dra.repair_type_id,
p_repair_type_id) and
nvl(dra.ro_priority_code, fnd_api.g_miss_char) =
decode(p_ro_priority_code,
fnd_api.g_miss_char, nvl(dra.ro_priority_code, fnd_api.g_miss_char),
p_ro_priority_code) and
dra.creation_date >=
decode(p_ro_age,
fnd_api.g_miss_num, dra.creation_date,
sysdate - p_ro_age) and
nvl(dra.promise_date, G_MAX_DATE) <=
decode(p_ro_due_date_age,
fnd_api.g_miss_num, nvl(dra.promise_date, G_MAX_DATE),
sysdate + p_ro_due_date_age)) OLTP
ON (soo.repair_line_id = OLTP.repair_line_id and
soo.document_header_id = OLTP.document_header_id and
soo.document_type = 'SR')
WHEN MATCHED THEN
UPDATE SET
soo.prev_status_id = (CASE update_count(soo.orchestration_id) when 'A' then soo.status_id end),
soo.status_id = OLTP.status_id,
soo.object_version_number = soo.object_version_number + 1,
soo.last_update_date = sysdate,
soo.last_updated_by = l_user_id,
soo.last_update_login = l_login_id
WHEN NOT MATCHED THEN
INSERT (ORCHESTRATION_ID,
REPAIR_LINE_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
DOCUMENT_HEADER_ID,
DOCUMENT_LINE_ID,
DOCUMENT_HEADER_NAME,
DOCUMENT_LINE_NAME,
DOCUMENT_TYPE,
STATUS_ID,
STATUS_CODE,
PREV_STATUS_ID,
PREV_STATUS_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PARAMN1)
VALUES
(csd_so_orchestration_s1.nextval,
OLTP.repair_line_id,
1,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_login_id,
OLTP.document_header_id,
null, -- document_line_id
OLTP.document_header_name,
null, -- document_line_name
'SR',
OLTP.status_id,
null, -- status_code
null, -- prev_status_id
null, -- prev_status_code
null, -- attribute_category
null, -- attribute1
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null); -- paramn1
l_updated_keys := get_updated_ids();
FOR i IN 1..l_updated_keys.COUNT
LOOP
OPEN c_get_defaulting_info(l_updated_keys(i));
l_updated_keys JTF_NUMBER_TABLE;
SELECT prev_status_id, status_id, repair_line_id
FROM CSD_SO_ORCHESTRATION
WHERE orchestration_id = c_orchestration_id;
g_updated_ids := JTF_NUMBER_TABLE();
select
dra.repair_line_id,
tsk.task_id document_header_id,
null document_line_id,
tsk.task_number document_header_name,
null document_line_name,
'SR_TASK' document_type,
tsk.task_status_id status_id,
null status_code
from cs_sr_tasks_v tsk,
cs_incidents_all_b inc,
ORG_ORGANIZATION_DEFINITIONS orgd,
csd_repairs dra
where
tsk.source_object_type_code = 'SR' and
dra.incident_id = inc.incident_id and
dra.incident_id = tsk.source_object_id and
orgd.organization_id(+) = inc.inv_organization_id and
tsk.task_id = decode(p_task_id,
fnd_api.g_miss_num, tsk.task_id,
p_task_id) and
dra.repair_line_id = decode(p_repair_line_id,
fnd_api.g_miss_num, dra.repair_line_id,
p_repair_line_id) and
nvl(orgd.operating_unit, fnd_api.g_miss_num) =
decode(p_operating_unit_id,
fnd_api.g_miss_num, nvl(orgd.operating_unit, fnd_api.g_miss_num),
p_operating_unit_id) and
nvl(inc.inv_organization_id, fnd_api.g_miss_num) =
decode(p_inventory_org_id,
fnd_api.g_miss_num, nvl(inc.inv_organization_id, fnd_api.g_miss_num),
p_inventory_org_id) and
nvl(dra.resource_id, fnd_api.g_miss_num) =
decode(p_repair_owner_id,
fnd_api.g_miss_num, nvl(dra.resource_id, fnd_api.g_miss_num),
p_repair_owner_id) and
dra.flow_status_id =
decode(p_ro_status_id,
fnd_api.g_miss_num, dra.flow_status_id,
p_ro_status_id) and
dra.repair_type_id =
decode(p_repair_type_id,
fnd_api.g_miss_num, dra.repair_type_id,
p_repair_type_id) and
nvl(dra.ro_priority_code, fnd_api.g_miss_char) =
decode(p_ro_priority_code,
fnd_api.g_miss_char, nvl(dra.ro_priority_code, fnd_api.g_miss_char),
p_ro_priority_code) and
dra.creation_date >=
decode(p_ro_age,
fnd_api.g_miss_num, dra.creation_date,
sysdate - p_ro_age) and
nvl(dra.promise_date, G_MAX_DATE) <=
decode(p_ro_due_date_age,
fnd_api.g_miss_num, nvl(dra.promise_date, G_MAX_DATE),
sysdate + p_ro_due_date_age)) OLTP
ON (soo.repair_line_id = OLTP.repair_line_id and
soo.document_header_id = OLTP.document_header_id and
-- nvl(soo.document_line_id, fnd_api.g_miss_num) =
-- nvl(OLTP.document_line_id, fnd_api.g_miss_num) and
soo.document_type = 'SR_TASK')
WHEN MATCHED THEN
UPDATE SET
soo.prev_status_id = (CASE update_count(soo.orchestration_id) when 'A' then soo.status_id end),
soo.status_id = OLTP.status_id,
soo.object_version_number = soo.object_version_number + 1,
soo.last_update_date = sysdate,
soo.last_updated_by = l_user_id,
soo.last_update_login = l_login_id
WHEN NOT MATCHED THEN
INSERT (ORCHESTRATION_ID,
REPAIR_LINE_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
DOCUMENT_HEADER_ID,
DOCUMENT_LINE_ID,
DOCUMENT_HEADER_NAME,
DOCUMENT_LINE_NAME,
DOCUMENT_TYPE,
STATUS_ID,
STATUS_CODE,
PREV_STATUS_ID,
PREV_STATUS_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PARAMN1)
VALUES
(csd_so_orchestration_s1.nextval,
OLTP.repair_line_id,
1,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_login_id,
OLTP.document_header_id,
null, -- document_line_id
OLTP.document_header_name,
null, -- document_line_name
'SR_TASK',
OLTP.status_id,
null, -- status_code
null, -- prev_status_id
null, -- prev_status_code
null, -- attribute_category
null, -- attribute1
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null); -- paramn1
l_updated_keys := get_updated_ids();
FOR i IN 1..l_updated_keys.COUNT
LOOP
OPEN c_get_defaulting_info(l_updated_keys(i));
l_updated_keys JTF_NUMBER_TABLE;
SELECT prev_status_code, status_code, repair_line_id, document_header_id
FROM CSD_SO_ORCHESTRATION
WHERE orchestration_id = c_orchestration_id;
SELECT wip_entity_id
FROM po_requisition_lines_all
WHERE requisition_header_id = c_requisition_header_id;
g_updated_ids := JTF_NUMBER_TABLE();
select distinct
xref.repair_line_id,
porh.requisition_header_id document_header_id,
null document_line_id,
porh.segment1 document_header_name,
null document_line_name,
'PR' document_type,
null status_id,
porh.authorization_status status_code
from
csd_repairs dra,
wip_entities we,
po_requisition_headers_all porh,
csd_repair_job_xref xref,
po_requisition_lines_all porl
where
dra.repair_line_id = xref.repair_line_id and
porh.type_lookup_code = 'PURCHASE' and
we.wip_entity_id = xref.wip_entity_id and
porl.wip_entity_id = xref.wip_entity_id and
porh.requisition_header_id = porl.requisition_header_id and
porh.requisition_header_id = decode(p_req_header_id,
fnd_api.g_miss_num, porh.requisition_header_id,
p_req_header_id) and
dra.repair_line_id = decode(p_repair_line_id,
fnd_api.g_miss_num, dra.repair_line_id,
p_repair_line_id) and
nvl(porh.org_id, fnd_api.g_miss_num) =
decode(p_operating_unit_id,
fnd_api.g_miss_num, nvl(porh.org_id, fnd_api.g_miss_num),
p_operating_unit_id) and
we.organization_id =
decode(p_inventory_org_id,
fnd_api.g_miss_num, we.organization_id,
p_inventory_org_id) and
nvl(dra.resource_id, fnd_api.g_miss_num) =
decode(p_repair_owner_id,
fnd_api.g_miss_num, nvl(dra.resource_id, fnd_api.g_miss_num),
p_repair_owner_id) and
dra.flow_status_id =
decode(p_ro_status_id,
fnd_api.g_miss_num, dra.flow_status_id,
p_ro_status_id) and
dra.repair_type_id =
decode(p_repair_type_id,
fnd_api.g_miss_num, dra.repair_type_id,
p_repair_type_id) and
nvl(dra.ro_priority_code, fnd_api.g_miss_char) =
decode(p_ro_priority_code,
fnd_api.g_miss_char, nvl(dra.ro_priority_code, fnd_api.g_miss_char),
p_ro_priority_code) and
dra.creation_date >=
decode(p_ro_age,
fnd_api.g_miss_num, dra.creation_date,
sysdate - p_ro_age) and
nvl(dra.promise_date, G_MAX_DATE) <=
decode(p_ro_due_date_age,
fnd_api.g_miss_num, nvl(dra.promise_date, G_MAX_DATE),
sysdate + p_ro_due_date_age)) OLTP
ON (soo.repair_line_id = OLTP.repair_line_id and
soo.document_header_id = OLTP.document_header_id and
-- nvl(soo.document_line_id, fnd_api.g_miss_num) =
-- nvl(OLTP.document_line_id, fnd_api.g_miss_num) and
soo.document_type = 'PR')
WHEN MATCHED THEN
UPDATE SET
soo.prev_status_code = (CASE update_count(soo.orchestration_id) when 'A' then soo.status_code end),
soo.status_code = OLTP.status_code,
soo.object_version_number = soo.object_version_number + 1,
soo.last_update_date = sysdate,
soo.last_updated_by = l_user_id,
soo.last_update_login = l_login_id
WHEN NOT MATCHED THEN
INSERT (ORCHESTRATION_ID,
REPAIR_LINE_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
DOCUMENT_HEADER_ID,
DOCUMENT_LINE_ID,
DOCUMENT_HEADER_NAME,
DOCUMENT_LINE_NAME,
DOCUMENT_TYPE,
STATUS_ID,
STATUS_CODE,
PREV_STATUS_ID,
PREV_STATUS_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PARAMN1)
VALUES
(csd_so_orchestration_s1.nextval,
OLTP.repair_line_id,
1,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_login_id,
OLTP.document_header_id,
null, -- document_line_id
OLTP.document_header_name,
null, -- document_line_name
'PR',
null, -- status_od
OLTP.status_code,
null, -- prev_status_id
null, -- prev_status_code
null, -- attribute_category
null, -- attribute1
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null); -- paramn1
l_updated_keys := get_updated_ids();
FOR i IN 1..l_updated_keys.COUNT
LOOP
OPEN c_get_defaulting_info(l_updated_keys(i));
l_updated_keys JTF_NUMBER_TABLE;
SELECT prev_status_code, status_code, repair_line_id, document_header_id
FROM CSD_SO_ORCHESTRATION
WHERE orchestration_id = c_orchestration_id;
SELECT wip_entity_id
FROM po_requisition_lines_all
WHERE requisition_header_id = c_requisition_header_id;
SELECT distinct
order_header_id
FROM csd_product_transactions
WHERE req_header_id = c_requisition_header_id and
repair_line_id = c_repair_line_id;
g_updated_ids := JTF_NUMBER_TABLE();
select distinct
dra.repair_line_id,
porh.requisition_header_id document_header_id,
null document_line_id,
porh.segment1 document_header_name,
null document_line_name,
'IR' document_type,
null status_id,
porh.authorization_status status_code
from
csd_repairs dra,
po_requisition_headers_all porh,
csd_product_transactions dpt
where
dra.repair_line_id = dpt.repair_line_id and
dpt.action_type is null and
dpt.req_header_id = porh.requisition_header_id and
porh.requisition_header_id = decode(p_req_header_id,
fnd_api.g_miss_num, porh.requisition_header_id,
p_req_header_id) and
dra.repair_line_id = decode(p_repair_line_id,
fnd_api.g_miss_num, dra.repair_line_id,
p_repair_line_id) and
nvl(dpt.rcv_into_ou, fnd_api.g_miss_num) = -- operating unit
decode(p_operating_unit_id,
fnd_api.g_miss_num, nvl(dpt.rcv_into_ou, fnd_api.g_miss_num),
p_operating_unit_id) and
nvl(dpt.rcv_into_org, fnd_api.g_miss_num) = -- inventory org
decode(p_inventory_org_id,
fnd_api.g_miss_num, nvl(dpt.rcv_into_org, fnd_api.g_miss_num),
p_inventory_org_id) and
nvl(dra.resource_id, fnd_api.g_miss_num) =
decode(p_repair_owner_id,
fnd_api.g_miss_num, nvl(dra.resource_id, fnd_api.g_miss_num),
p_repair_owner_id) and
dra.flow_status_id =
decode(p_ro_status_id,
fnd_api.g_miss_num, dra.flow_status_id,
p_ro_status_id) and
dra.repair_type_id =
decode(p_repair_type_id,
fnd_api.g_miss_num, dra.repair_type_id,
p_repair_type_id) and
nvl(dra.ro_priority_code, fnd_api.g_miss_char) =
decode(p_ro_priority_code,
fnd_api.g_miss_char, nvl(dra.ro_priority_code, fnd_api.g_miss_char),
p_ro_priority_code) and
dra.creation_date >=
decode(p_ro_age,
fnd_api.g_miss_num, dra.creation_date,
sysdate - p_ro_age) and
nvl(dra.promise_date, G_MAX_DATE) <=
decode(p_ro_due_date_age,
fnd_api.g_miss_num, nvl(dra.promise_date, G_MAX_DATE),
sysdate + p_ro_due_date_age)) OLTP
ON (soo.repair_line_id = OLTP.repair_line_id and
soo.document_header_id = OLTP.document_header_id and
-- nvl(soo.document_line_id, fnd_api.g_miss_num) =
-- nvl(OLTP.document_line_id, fnd_api.g_miss_num) and
soo.document_type = 'IR')
WHEN MATCHED THEN
UPDATE SET
soo.prev_status_code = (CASE update_count(soo.orchestration_id) when 'A' then soo.status_code end),
soo.status_code = OLTP.status_code,
soo.object_version_number = soo.object_version_number + 1,
soo.last_update_date = sysdate,
soo.last_updated_by = l_user_id,
soo.last_update_login = l_login_id
WHEN NOT MATCHED THEN
INSERT (ORCHESTRATION_ID,
REPAIR_LINE_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
DOCUMENT_HEADER_ID,
DOCUMENT_LINE_ID,
DOCUMENT_HEADER_NAME,
DOCUMENT_LINE_NAME,
DOCUMENT_TYPE,
STATUS_ID,
STATUS_CODE,
PREV_STATUS_ID,
PREV_STATUS_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PARAMN1)
VALUES
(csd_so_orchestration_s1.nextval,
OLTP.repair_line_id,
1,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_login_id,
OLTP.document_header_id,
null, -- document_line_id
OLTP.document_header_name,
null, -- document_line_name
'IR',
null, -- status_od
OLTP.status_code,
null, -- prev_status_id
null, -- prev_status_code
null, -- attribute_category
null, -- attribute1
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null); -- paramn1
l_updated_keys := get_updated_ids();
FOR i IN 1..l_updated_keys.COUNT
LOOP
OPEN c_get_defaulting_info(l_updated_keys(i));
update_int_order_header_id(l_order_header_id);
l_updated_keys JTF_NUMBER_TABLE;
SELECT prev_status_code, status_code, repair_line_id, document_header_id
FROM CSD_SO_ORCHESTRATION
WHERE orchestration_id = c_orchestration_id;
g_updated_ids := JTF_NUMBER_TABLE();
select distinct
dra.repair_line_id,
oel.header_id document_header_id,
oel.line_id document_line_id,
oeh.order_number document_header_name,
oel.line_number document_line_name,
'INTSO' document_type,
null status_id,
lv.lookup_code status_code
from oe_order_lines_all oel,
oe_order_headers_all oeh,
csd_product_transactions dpt,
csd_repairs dra,
fnd_lookup_values lv
where oeh.header_id = oel.header_id
and dpt.order_line_id = oel.line_id
and dpt.repair_line_id = dra.repair_line_id
and dpt.action_type is null
and lv.lookup_type = 'LINE_FLOW_STATUS'
and lv.meaning = OE_LINE_STATUS_PUB.get_line_status(p_line_id => oel.line_id,
p_flow_status_code => oel.flow_status_code)
and
oel.line_id = decode(p_order_line_id,
fnd_api.g_miss_num, oel.line_id,
p_order_line_id) and
dra.repair_line_id = decode(p_repair_line_id,
fnd_api.g_miss_num, dra.repair_line_id,
p_repair_line_id) and
nvl(dpt.ship_from_ou, fnd_api.g_miss_num) = -- operating unit
decode(p_operating_unit_id,
fnd_api.g_miss_num, nvl(dpt.ship_from_ou, fnd_api.g_miss_num),
p_operating_unit_id) and
nvl(dpt.ship_from_org, fnd_api.g_miss_num) = -- inventory org
decode(p_inventory_org_id,
fnd_api.g_miss_num, nvl(dpt.ship_from_org, fnd_api.g_miss_num),
p_inventory_org_id) and
nvl(dra.resource_id, fnd_api.g_miss_num) =
decode(p_repair_owner_id,
fnd_api.g_miss_num, nvl(dra.resource_id, fnd_api.g_miss_num),
p_repair_owner_id) and
dra.flow_status_id =
decode(p_ro_status_id,
fnd_api.g_miss_num, dra.flow_status_id,
p_ro_status_id) and
dra.repair_type_id =
decode(p_repair_type_id,
fnd_api.g_miss_num, dra.repair_type_id,
p_repair_type_id) and
nvl(dra.ro_priority_code, fnd_api.g_miss_char) =
decode(p_ro_priority_code,
fnd_api.g_miss_char, nvl(dra.ro_priority_code, fnd_api.g_miss_char),
p_ro_priority_code) and
dra.creation_date >=
decode(p_ro_age,
fnd_api.g_miss_num, dra.creation_date,
sysdate - p_ro_age) and
nvl(dra.promise_date, G_MAX_DATE) <=
decode(p_ro_due_date_age,
fnd_api.g_miss_num, nvl(dra.promise_date, G_MAX_DATE),
sysdate + p_ro_due_date_age)) OLTP
ON (soo.repair_line_id = OLTP.repair_line_id and
soo.document_line_id = OLTP.document_line_id and
soo.document_type = 'INTSO')
WHEN MATCHED THEN
UPDATE SET
soo.prev_status_code = (CASE update_count(soo.orchestration_id) when 'A' then soo.status_code end),
soo.status_code = OLTP.status_code,
soo.object_version_number = soo.object_version_number + 1,
soo.last_update_date = sysdate,
soo.last_updated_by = l_user_id,
soo.last_update_login = l_login_id
WHEN NOT MATCHED THEN
INSERT (ORCHESTRATION_ID,
REPAIR_LINE_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
DOCUMENT_HEADER_ID,
DOCUMENT_LINE_ID,
DOCUMENT_HEADER_NAME,
DOCUMENT_LINE_NAME,
DOCUMENT_TYPE,
STATUS_ID,
STATUS_CODE,
PREV_STATUS_ID,
PREV_STATUS_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PARAMN1)
VALUES
(csd_so_orchestration_s1.nextval,
OLTP.repair_line_id,
1,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_login_id,
OLTP.document_header_id,
OLTP.document_line_id,
OLTP.document_header_name,
OLTP.document_line_name,
'INTSO',
null, -- status_id
OLTP.status_code,
null, -- prev_status_id
null, -- prev_status_code
null, -- attribute_category
null, -- attribute1
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null); -- paramn1
l_updated_keys := get_updated_ids();
FOR i IN 1..l_updated_keys.COUNT
LOOP
OPEN c_get_defaulting_info(l_updated_keys(i));
update_int_order_header_id(l_order_header_id);
l_updated_keys JTF_NUMBER_TABLE;
SELECT prev_status_code, status_code, repair_line_id
FROM CSD_SO_ORCHESTRATION
WHERE orchestration_id = c_orchestration_id;
g_updated_ids := JTF_NUMBER_TABLE();
select
dra.repair_line_id,
oel.header_id document_header_id,
oel.line_id document_line_id,
oeh.order_number document_header_name,
oel.line_number document_line_name,
'RMA' document_type,
null status_id,
lv.lookup_code status_code
from oe_order_lines_all oel,
oe_order_headers_all oeh,
csd_product_transactions dpt,
cs_estimate_details edt,
csd_repairs dra,
fnd_lookup_values lv
where oeh.header_id = oel.header_id
and dpt.estimate_detail_id = edt.estimate_detail_id
and edt.order_line_id = oel.line_id
and dpt.repair_line_id = dra.repair_line_id
and dpt.action_type = 'RMA'
and lv.lookup_type = 'LINE_FLOW_STATUS'
and lv.meaning = OE_LINE_STATUS_PUB.get_line_status(p_line_id => oel.line_id,
p_flow_status_code => oel.flow_status_code)
and
oel.line_id = decode(p_order_line_id,
fnd_api.g_miss_num, oel.line_id,
p_order_line_id) and
dra.repair_line_id = decode(p_repair_line_id,
fnd_api.g_miss_num, dra.repair_line_id,
p_repair_line_id) and
nvl(oel.org_id, fnd_api.g_miss_num) = -- operating unit
decode(p_operating_unit_id,
fnd_api.g_miss_num, nvl(oel.org_id, fnd_api.g_miss_num),
p_operating_unit_id) and
nvl(edt.transaction_inventory_org, fnd_api.g_miss_num) = -- inventory org
decode(p_inventory_org_id,
fnd_api.g_miss_num, nvl(edt.transaction_inventory_org, fnd_api.g_miss_num),
p_inventory_org_id) and
nvl(dra.resource_id, fnd_api.g_miss_num) =
decode(p_repair_owner_id,
fnd_api.g_miss_num, nvl(dra.resource_id, fnd_api.g_miss_num),
p_repair_owner_id) and
dra.flow_status_id =
decode(p_ro_status_id,
fnd_api.g_miss_num, dra.flow_status_id,
p_ro_status_id) and
dra.repair_type_id =
decode(p_repair_type_id,
fnd_api.g_miss_num, dra.repair_type_id,
p_repair_type_id) and
nvl(dra.ro_priority_code, fnd_api.g_miss_char) =
decode(p_ro_priority_code,
fnd_api.g_miss_char, nvl(dra.ro_priority_code, fnd_api.g_miss_char),
p_ro_priority_code) and
dra.creation_date >=
decode(p_ro_age,
fnd_api.g_miss_num, dra.creation_date,
sysdate - p_ro_age) and
nvl(dra.promise_date, G_MAX_DATE) <=
decode(p_ro_due_date_age,
fnd_api.g_miss_num, nvl(dra.promise_date, G_MAX_DATE),
sysdate + p_ro_due_date_age)) OLTP
ON (soo.repair_line_id = OLTP.repair_line_id and
soo.document_line_id = OLTP.document_line_id and
soo.document_type = 'RMA')
WHEN MATCHED THEN
UPDATE SET
soo.prev_status_code = (CASE update_count(soo.orchestration_id) when 'A' then soo.status_code end),
soo.status_code = OLTP.status_code,
soo.object_version_number = soo.object_version_number + 1,
soo.last_update_date = sysdate,
soo.last_updated_by = l_user_id,
soo.last_update_login = l_login_id
WHEN NOT MATCHED THEN
INSERT (ORCHESTRATION_ID,
REPAIR_LINE_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
DOCUMENT_HEADER_ID,
DOCUMENT_LINE_ID,
DOCUMENT_HEADER_NAME,
DOCUMENT_LINE_NAME,
DOCUMENT_TYPE,
STATUS_ID,
STATUS_CODE,
PREV_STATUS_ID,
PREV_STATUS_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PARAMN1)
VALUES
(csd_so_orchestration_s1.nextval,
OLTP.repair_line_id,
1,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_login_id,
OLTP.document_header_id,
OLTP.document_line_id,
OLTP.document_header_name,
OLTP.document_line_name,
'RMA',
null, -- status_id
OLTP.status_code,
null, -- prev_status_id
null, -- prev_status_code
null, -- attribute_category
null, -- attribute1
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null); -- paramn1
l_updated_keys := get_updated_ids();
FOR i IN 1..l_updated_keys.COUNT
LOOP
OPEN c_get_defaulting_info(l_updated_keys(i));
update_rma_ro_line_id(l_repair_line_id);
l_updated_keys JTF_NUMBER_TABLE;
SELECT prev_status_code, status_code, repair_line_id
FROM CSD_SO_ORCHESTRATION
WHERE orchestration_id = c_orchestration_id;
g_updated_ids := JTF_NUMBER_TABLE();
select
dra.repair_line_id,
oel.header_id document_header_id,
oel.line_id document_line_id,
oeh.order_number document_header_name,
oel.line_number document_line_name,
'RMA_TP' document_type,
null status_id,
lv.lookup_code status_code
from oe_order_lines_all oel,
oe_order_headers_all oeh,
csd_product_transactions dpt,
cs_estimate_details edt,
csd_repairs dra,
fnd_lookup_values lv
where oeh.header_id = oel.header_id
and dpt.estimate_detail_id = edt.estimate_detail_id
and edt.order_line_id = oel.line_id
and dpt.repair_line_id = dra.repair_line_id
and dpt.action_type = 'RMA_THIRD_PTY'
and lv.lookup_type = 'LINE_FLOW_STATUS'
and lv.meaning = OE_LINE_STATUS_PUB.get_line_status(p_line_id => oel.line_id,
p_flow_status_code => oel.flow_status_code)
and
oel.line_id = decode(p_order_line_id,
fnd_api.g_miss_num, oel.line_id,
p_order_line_id) and
dra.repair_line_id = decode(p_repair_line_id,
fnd_api.g_miss_num, dra.repair_line_id,
p_repair_line_id) and
nvl(oel.org_id, fnd_api.g_miss_num) = -- operating unit
decode(p_operating_unit_id,
fnd_api.g_miss_num, nvl(oel.org_id, fnd_api.g_miss_num),
p_operating_unit_id) and
nvl(edt.transaction_inventory_org, fnd_api.g_miss_num) = -- inventory org
decode(p_inventory_org_id,
fnd_api.g_miss_num, nvl(edt.transaction_inventory_org, fnd_api.g_miss_num),
p_inventory_org_id) and
nvl(dra.resource_id, fnd_api.g_miss_num) =
decode(p_repair_owner_id,
fnd_api.g_miss_num, nvl(dra.resource_id, fnd_api.g_miss_num),
p_repair_owner_id) and
dra.flow_status_id =
decode(p_ro_status_id,
fnd_api.g_miss_num, dra.flow_status_id,
p_ro_status_id) and
dra.repair_type_id =
decode(p_repair_type_id,
fnd_api.g_miss_num, dra.repair_type_id,
p_repair_type_id) and
nvl(dra.ro_priority_code, fnd_api.g_miss_char) =
decode(p_ro_priority_code,
fnd_api.g_miss_char, nvl(dra.ro_priority_code, fnd_api.g_miss_char),
p_ro_priority_code) and
dra.creation_date >=
decode(p_ro_age,
fnd_api.g_miss_num, dra.creation_date,
sysdate - p_ro_age) and
nvl(dra.promise_date, G_MAX_DATE) <=
decode(p_ro_due_date_age,
fnd_api.g_miss_num, nvl(dra.promise_date, G_MAX_DATE),
sysdate + p_ro_due_date_age)) OLTP
ON (soo.repair_line_id = OLTP.repair_line_id and
-- soo.document_header_id = OLTP.document_header_id and
soo.document_line_id = OLTP.document_line_id and
soo.document_type = 'RMA_TP')
WHEN MATCHED THEN
UPDATE SET
soo.prev_status_code = (CASE update_count(soo.orchestration_id) when 'A' then soo.status_code end),
soo.status_code = OLTP.status_code,
soo.object_version_number = soo.object_version_number + 1,
soo.last_update_date = sysdate,
soo.last_updated_by = l_user_id,
soo.last_update_login = l_login_id
WHEN NOT MATCHED THEN
INSERT (ORCHESTRATION_ID,
REPAIR_LINE_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
DOCUMENT_HEADER_ID,
DOCUMENT_LINE_ID,
DOCUMENT_HEADER_NAME,
DOCUMENT_LINE_NAME,
DOCUMENT_TYPE,
STATUS_ID,
STATUS_CODE,
PREV_STATUS_ID,
PREV_STATUS_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PARAMN1)
VALUES
(csd_so_orchestration_s1.nextval,
OLTP.repair_line_id,
1,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_login_id,
OLTP.document_header_id,
OLTP.document_line_id,
OLTP.document_header_name,
OLTP.document_line_name,
'RMA_TP',
null, -- status_id
OLTP.status_code,
null, -- prev_status_id
null, -- prev_status_code
null, -- attribute_category
null, -- attribute1
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null); -- paramn1
l_updated_keys := get_updated_ids();
FOR i IN 1..l_updated_keys.COUNT
LOOP
OPEN c_get_defaulting_info(l_updated_keys(i));
update_rma_ro_line_id(l_repair_line_id);
l_updated_keys JTF_NUMBER_TABLE;
SELECT prev_status_code, status_code, repair_line_id
FROM CSD_SO_ORCHESTRATION
WHERE orchestration_id = c_orchestration_id;
g_updated_ids := JTF_NUMBER_TABLE();
select
dra.repair_line_id,
oel.header_id document_header_id,
oel.line_id document_line_id,
oeh.order_number document_header_name,
oel.line_number document_line_name,
'SHIP' document_type,
null status_id,
lv.lookup_code status_code
from oe_order_lines_all oel,
oe_order_headers_all oeh,
csd_product_transactions dpt,
cs_estimate_details edt,
csd_repairs dra,
fnd_lookup_values lv
where oeh.header_id = oel.header_id
and dpt.estimate_detail_id = edt.estimate_detail_id
and edt.order_line_id = oel.line_id
and dpt.repair_line_id = dra.repair_line_id
and dpt.action_type = 'SHIP'
and lv.lookup_type = 'LINE_FLOW_STATUS'
and lv.meaning = OE_LINE_STATUS_PUB.get_line_status(p_line_id => oel.line_id,
p_flow_status_code => oel.flow_status_code)
and
oel.line_id = decode(p_order_line_id,
fnd_api.g_miss_num, oel.line_id,
p_order_line_id) and
dra.repair_line_id = decode(p_repair_line_id,
fnd_api.g_miss_num, dra.repair_line_id,
p_repair_line_id) and
nvl(oel.org_id, fnd_api.g_miss_num) = -- operating unit
decode(p_operating_unit_id,
fnd_api.g_miss_num, nvl(oel.org_id, fnd_api.g_miss_num),
p_operating_unit_id) and
nvl(edt.transaction_inventory_org, fnd_api.g_miss_num) = -- inventory org
decode(p_inventory_org_id,
fnd_api.g_miss_num, nvl(edt.transaction_inventory_org, fnd_api.g_miss_num),
p_inventory_org_id) and
nvl(dra.resource_id, fnd_api.g_miss_num) =
decode(p_repair_owner_id,
fnd_api.g_miss_num, nvl(dra.resource_id, fnd_api.g_miss_num),
p_repair_owner_id) and
dra.flow_status_id =
decode(p_ro_status_id,
fnd_api.g_miss_num, dra.flow_status_id,
p_ro_status_id) and
dra.repair_type_id =
decode(p_repair_type_id,
fnd_api.g_miss_num, dra.repair_type_id,
p_repair_type_id) and
nvl(dra.ro_priority_code, fnd_api.g_miss_char) =
decode(p_ro_priority_code,
fnd_api.g_miss_char, nvl(dra.ro_priority_code, fnd_api.g_miss_char),
p_ro_priority_code) and
dra.creation_date >=
decode(p_ro_age,
fnd_api.g_miss_num, dra.creation_date,
sysdate - p_ro_age) and
nvl(dra.promise_date, G_MAX_DATE) <=
decode(p_ro_due_date_age,
fnd_api.g_miss_num, nvl(dra.promise_date, G_MAX_DATE),
sysdate + p_ro_due_date_age)) OLTP
ON (soo.repair_line_id = OLTP.repair_line_id and
-- soo.document_header_id = OLTP.document_header_id and
soo.document_line_id = OLTP.document_line_id and
soo.document_type = 'SHIP')
WHEN MATCHED THEN
UPDATE SET
soo.prev_status_code = (CASE update_count(soo.orchestration_id) when 'A' then soo.status_code end),
soo.status_code = OLTP.status_code,
soo.object_version_number = soo.object_version_number + 1,
soo.last_update_date = sysdate,
soo.last_updated_by = l_user_id,
soo.last_update_login = l_login_id
WHEN NOT MATCHED THEN
INSERT (ORCHESTRATION_ID,
REPAIR_LINE_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
DOCUMENT_HEADER_ID,
DOCUMENT_LINE_ID,
DOCUMENT_HEADER_NAME,
DOCUMENT_LINE_NAME,
DOCUMENT_TYPE,
STATUS_ID,
STATUS_CODE,
PREV_STATUS_ID,
PREV_STATUS_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PARAMN1)
VALUES
(csd_so_orchestration_s1.nextval,
OLTP.repair_line_id,
1,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_login_id,
OLTP.document_header_id,
OLTP.document_line_id,
OLTP.document_header_name,
OLTP.document_line_name,
'SHIP',
null, -- status_id
OLTP.status_code,
null, -- prev_status_id
null, -- prev_status_code
null, -- attribute_category
null, -- attribute1
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null); -- paramn1
l_updated_keys := get_updated_ids();
FOR i IN 1..l_updated_keys.COUNT
LOOP
OPEN c_get_defaulting_info(l_updated_keys(i));
update_ship_ro_line_id(l_repair_line_id);
l_updated_keys JTF_NUMBER_TABLE;
SELECT prev_status_code, status_code, repair_line_id
FROM CSD_SO_ORCHESTRATION
WHERE orchestration_id = c_orchestration_id;
g_updated_ids := JTF_NUMBER_TABLE();
select
dra.repair_line_id,
oel.header_id document_header_id,
oel.line_id document_line_id,
oeh.order_number document_header_name,
oel.line_number document_line_name,
'SHIP_TP' document_type,
null status_id,
lv.lookup_code status_code
from oe_order_lines_all oel,
oe_order_headers_all oeh,
csd_product_transactions dpt,
cs_estimate_details edt,
csd_repairs dra,
fnd_lookup_values lv
where oeh.header_id = oel.header_id
and dpt.estimate_detail_id = edt.estimate_detail_id
and edt.order_line_id = oel.line_id
and dpt.repair_line_id = dra.repair_line_id
and dpt.action_type = 'SHIP_THIRD_PTY'
and lv.lookup_type = 'LINE_FLOW_STATUS'
and lv.meaning = OE_LINE_STATUS_PUB.get_line_status(p_line_id => oel.line_id,
p_flow_status_code => oel.flow_status_code)
and
oel.line_id = decode(p_order_line_id,
fnd_api.g_miss_num, oel.line_id,
p_order_line_id) and
dra.repair_line_id = decode(p_repair_line_id,
fnd_api.g_miss_num, dra.repair_line_id,
p_repair_line_id) and
nvl(oel.org_id, fnd_api.g_miss_num) = -- operating unit
decode(p_operating_unit_id,
fnd_api.g_miss_num, nvl(oel.org_id, fnd_api.g_miss_num),
p_operating_unit_id) and
nvl(edt.transaction_inventory_org, fnd_api.g_miss_num) = -- inventory org
decode(p_inventory_org_id,
fnd_api.g_miss_num, nvl(edt.transaction_inventory_org, fnd_api.g_miss_num),
p_inventory_org_id) and
nvl(dra.resource_id, fnd_api.g_miss_num) =
decode(p_repair_owner_id,
fnd_api.g_miss_num, nvl(dra.resource_id, fnd_api.g_miss_num),
p_repair_owner_id) and
dra.flow_status_id =
decode(p_ro_status_id,
fnd_api.g_miss_num, dra.flow_status_id,
p_ro_status_id) and
dra.repair_type_id =
decode(p_repair_type_id,
fnd_api.g_miss_num, dra.repair_type_id,
p_repair_type_id) and
nvl(dra.ro_priority_code, fnd_api.g_miss_char) =
decode(p_ro_priority_code,
fnd_api.g_miss_char, nvl(dra.ro_priority_code, fnd_api.g_miss_char),
p_ro_priority_code) and
dra.creation_date >=
decode(p_ro_age,
fnd_api.g_miss_num, dra.creation_date,
sysdate - p_ro_age) and
nvl(dra.promise_date, G_MAX_DATE) <=
decode(p_ro_due_date_age,
fnd_api.g_miss_num, nvl(dra.promise_date, G_MAX_DATE),
sysdate + p_ro_due_date_age)) OLTP
ON (soo.repair_line_id = OLTP.repair_line_id and
-- soo.document_header_id = OLTP.document_header_id and
soo.document_line_id = OLTP.document_line_id and
soo.document_type = 'SHIP_TP')
WHEN MATCHED THEN
UPDATE SET
soo.prev_status_code = (CASE update_count(soo.orchestration_id) when 'A' then soo.status_code end),
soo.status_code = OLTP.status_code,
soo.object_version_number = soo.object_version_number + 1,
soo.last_update_date = sysdate,
soo.last_updated_by = l_user_id,
soo.last_update_login = l_login_id
WHEN NOT MATCHED THEN
INSERT (ORCHESTRATION_ID,
REPAIR_LINE_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
DOCUMENT_HEADER_ID,
DOCUMENT_LINE_ID,
DOCUMENT_HEADER_NAME,
DOCUMENT_LINE_NAME,
DOCUMENT_TYPE,
STATUS_ID,
STATUS_CODE,
PREV_STATUS_ID,
PREV_STATUS_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PARAMN1)
VALUES
(csd_so_orchestration_s1.nextval,
OLTP.repair_line_id,
1,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_login_id,
OLTP.document_header_id,
OLTP.document_line_id,
OLTP.document_header_name,
OLTP.document_line_name,
'SHIP_TP',
null, -- status_id
OLTP.status_code,
null, -- prev_status_id
null, -- prev_status_code
null, -- attribute_category
null, -- attribute1
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null); -- paramn1
l_updated_keys := get_updated_ids();
FOR i IN 1..l_updated_keys.COUNT
LOOP
OPEN c_get_defaulting_info(l_updated_keys(i));
update_ship_ro_line_id(l_repair_line_id);
l_updated_keys JTF_NUMBER_TABLE;
SELECT prev_status_code, status_code, repair_line_id
FROM CSD_SO_ORCHESTRATION
WHERE orchestration_id = c_orchestration_id;
g_updated_ids := JTF_NUMBER_TABLE();
select distinct
dra.repair_line_id,
wnd.delivery_id document_header_id,
null document_line_id,
wnd.name document_header_name,
null document_line_name,
'DELIVERY' document_type,
null status_id,
wdd.released_status status_code
from wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd,
oe_order_lines_all oel,
csd_product_transactions dpt,
cs_estimate_details edt,
ORG_ORGANIZATION_DEFINITIONS orgd,
csd_repairs dra
where
oel.line_id = wdd.source_line_id AND
wdd.delivery_detail_id = wda.delivery_detail_id AND
wdd.source_code = 'OE' AND
wnd.delivery_id = wda.delivery_id and
wnd.delivery_type = 'STANDARD'
and dpt.estimate_detail_id = edt.estimate_detail_id
and edt.order_line_id = oel.line_id
and dpt.repair_line_id = dra.repair_line_id
and orgd.organization_id(+) = wnd.organization_id
and
wnd.delivery_id = decode(p_delivery_id,
fnd_api.g_miss_num, wnd.delivery_id,
p_delivery_id) and
dra.repair_line_id = decode(p_repair_line_id,
fnd_api.g_miss_num, dra.repair_line_id,
p_repair_line_id) and
nvl(orgd.operating_unit, fnd_api.g_miss_num) =
decode(p_operating_unit_id,
fnd_api.g_miss_num, nvl(orgd.operating_unit, fnd_api.g_miss_num),
p_operating_unit_id) and
nvl(wnd.organization_id, fnd_api.g_miss_num) =
decode(p_inventory_org_id,
fnd_api.g_miss_num, nvl(wnd.organization_id, fnd_api.g_miss_num),
p_inventory_org_id) and
nvl(dra.resource_id, fnd_api.g_miss_num) =
decode(p_repair_owner_id,
fnd_api.g_miss_num, nvl(dra.resource_id, fnd_api.g_miss_num),
p_repair_owner_id) and
dra.flow_status_id =
decode(p_ro_status_id,
fnd_api.g_miss_num, dra.flow_status_id,
p_ro_status_id) and
dra.repair_type_id =
decode(p_repair_type_id,
fnd_api.g_miss_num, dra.repair_type_id,
p_repair_type_id) and
nvl(dra.ro_priority_code, fnd_api.g_miss_char) =
decode(p_ro_priority_code,
fnd_api.g_miss_char, nvl(dra.ro_priority_code, fnd_api.g_miss_char),
p_ro_priority_code) and
dra.creation_date >=
decode(p_ro_age,
fnd_api.g_miss_num, dra.creation_date,
sysdate - p_ro_age) and
nvl(dra.promise_date, G_MAX_DATE) <=
decode(p_ro_due_date_age,
fnd_api.g_miss_num, nvl(dra.promise_date, G_MAX_DATE),
sysdate + p_ro_due_date_age)) OLTP
ON (soo.repair_line_id = OLTP.repair_line_id and
soo.document_header_id = OLTP.document_header_id and
-- soo.document_line_id = OLTP.document_line_id and
soo.document_type = 'DELIVERY')
WHEN MATCHED THEN
UPDATE SET
soo.prev_status_code = (CASE update_count(soo.orchestration_id) when 'A' then soo.status_code end),
soo.status_code = OLTP.status_code,
soo.object_version_number = soo.object_version_number + 1,
soo.last_update_date = sysdate,
soo.last_updated_by = l_user_id,
soo.last_update_login = l_login_id
WHEN NOT MATCHED THEN
INSERT (ORCHESTRATION_ID,
REPAIR_LINE_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
DOCUMENT_HEADER_ID,
DOCUMENT_LINE_ID,
DOCUMENT_HEADER_NAME,
DOCUMENT_LINE_NAME,
DOCUMENT_TYPE,
STATUS_ID,
STATUS_CODE,
PREV_STATUS_ID,
PREV_STATUS_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PARAMN1)
VALUES
(csd_so_orchestration_s1.nextval,
OLTP.repair_line_id,
1,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_login_id,
OLTP.document_header_id,
null,
OLTP.document_header_name,
null,
'DELIVERY',
null, -- status_id
OLTP.status_code,
null, -- prev_status_id
null, -- prev_status_code
null, -- attribute_category
null, -- attribute1
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null); -- paramn1
l_updated_keys := get_updated_ids();
FOR i IN 1..l_updated_keys.COUNT
LOOP
OPEN c_get_defaulting_info(l_updated_keys(i));
update_ship_ro_line_id(l_repair_line_id);
/* description : procedure to load or update */
/* CSD_SO_ORCHESTRATION_B and */
/* CSD_SO_ORCHESTRATION_TL */
/*--------------------------------------------------*/
PROCEDURE Refresh(p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_so_orchestration_key_tbl IN so_orchestration_key_tbl_type,
p_repair_line_id IN NUMBER := FND_API.G_MISS_NUM,
p_document_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_operating_unit_id IN NUMBER := FND_API.G_MISS_NUM,
p_inventory_org_id IN NUMBER := FND_API.G_MISS_NUM,
p_repair_owner_id IN NUMBER := FND_API.G_MISS_NUM,
p_ro_status_id IN NUMBER := FND_API.G_MISS_NUM,
p_repair_type_id IN NUMBER := FND_API.G_MISS_NUM,
p_ro_priority_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ro_age IN NUMBER := FND_API.G_MISS_NUM,
p_ro_due_date_age IN NUMBER := FND_API.G_MISS_NUM)
IS
-- Standard Variables
l_api_name CONSTANT VARCHAR2(30) := 'REFRESH';
SELECT repair_line_id, document_type, document_header_id, document_line_id
FROM csd_so_orchestration
WHERE orchestration_id = c_orchestration_id;
g_rma_ro_line_ids.delete();
g_ship_ro_line_ids.delete();
g_int_order_header_ids.delete();
CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE
(p_api_version => l_api_version,
p_commit => FND_API.G_FALSE,
p_init_msg_list => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_internal_order_flag => 'N',
p_order_header_id => NULL,
p_repair_line_id => l_rma_repair_line_id);
Debug('RECEIPTS_UPDATE failed',l_mod_name,4);
CSD_UPDATE_PROGRAMS_PVT.SHIP_UPDATE
(p_api_version => l_api_version,
p_commit => FND_API.G_FALSE,
p_init_msg_list => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => l_msg_data,
p_internal_order_flag => 'N',
p_order_header_id => NULL,
p_repair_line_id => l_ship_repair_line_id);
Debug('SHIP_UPDATE failed',l_mod_name,4);
CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE
(p_api_version => l_api_version,
p_commit => FND_API.G_FALSE,
p_init_msg_list => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => l_msg_data,
p_internal_order_flag => 'Y',
p_order_header_id => l_int_order_header_id,
p_repair_line_id => NULL);
Debug('RECEIPTS_UPDATE failed for internal sales orders and requisitions',l_mod_name,4);