[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT bom_item_type
FROM msc_system_items
WHERE sr_instance_id = p_instance
AND organization_id = p_sr_org_pk
AND inventory_item_id = p_sr_inventory_item_pk
AND plan_id = -1;
* a component is selected and its parent is an option class. When this
* occurs, the option class's nearest model needs to be found. Therefore,
* the components grandparents which are the assemblies parents are
* placed in a stack for further inspection.
*
* Model A
* |
* |--- Option Class A
* | |
* | |--- Option Class A'
* | |
* | |--- Component A
* |
* Model B
* |
* |--- Option Class A
* | |
* | |--- Option Class A'
* | |
* | |--- Component A
* Model C
* |
* |--- Option Class A'
* |
* |--- Component A
*
*
* In this case, Component A needs to find Model A, B, C. Option Class A' is an
* option class, but is used in several places. The procedure will search
* the components using Depth First Search (DFS) and append possible parents
* to the argument parameter.
*
*
* Parameters : 1. p_parents - vector containing all of the assemblies.
* 2. p_instance - source location
* 3. p_sr_org_pk - Organization source primary key.
* 4. p_asmb_ascp_pk - The assembly whose parents we are looking for.
* 5. p_planning_factor - Planning factor from this assembly
* 6. p_disable_date - Disable date for assembly and its component
*/
PROCEDURE GET_ALL_PARENTS (
p_parents IN OUT NOCOPY PARENTS,
p_instance IN VARCHAR2,
p_sr_org_pk IN VARCHAR2,
p_asmb_ascp_pk IN VARCHAR2,
p_planning_factor IN NUMBER,
p_quantity_per IN NUMBER,
p_disable_date IN DATE)
IS
endPos NUMBER := p_parents.last;
SELECT using_assembly_id,
planning_factor,
decode(mbc.usage_quantity/decode(mbc.usage_quantity,
null,1,
0,1,
abs(mbc.usage_quantity)),
1,
(mbc.usage_quantity * mbc.Component_Yield_Factor),
(mbc.usage_quantity / mbc.Component_Yield_Factor))
* msd_dem_common_utilities.uom_conv(msi.sr_instance_id, msi.uom_code,msi.sr_inventory_item_id) usage_quantity,
mbc.disable_date disable_date
FROM msc_system_items msi,
msc_bom_components mbc
WHERE msi.plan_id = -1
AND msi.sr_instance_id = p_instance
AND msi.organization_id = p_sr_org_pk
AND msi.inventory_item_id = p_asmb_ascp_pk
AND mbc.plan_id = -1
AND mbc.sr_instance_id = p_instance
AND mbc.organization_id = msi.organization_id
AND mbc.inventory_item_id = msi.inventory_item_id
AND ( mbc.optional_component = 1
OR msi.ato_forecast_control IN (C_CONSUME ,C_CONSUME_DERIVE));
numInsert NUMBER := 1;
/* Create the Collections needed for Bulk Insert */
C_INSTANCE VARCHAR2LIST := VARCHAR2LIST (C_INIT_VARCHAR2);
SELECT DISTINCT
mb.sr_instance_id,
mb.organization_id,
mbc.using_assembly_id,
ascp_comp.sr_inventory_item_id,
mbc.effectivity_date,
mbc.disable_date,
decode(mbc.usage_quantity/decode(mbc.usage_quantity,
null,1,
0,1,
abs(mbc.usage_quantity)),
1,
(mbc.usage_quantity * mbc.Component_Yield_Factor),
(mbc.usage_quantity / mbc.Component_Yield_Factor))
* msd_dem_common_utilities.uom_conv(ascp_comp.sr_instance_id, ascp_comp.uom_code, ascp_comp.inventory_item_id) usage_quantity,
mbc.planning_factor,
mb.bill_sequence_id,
mbc.optional_component
FROM msc_boms mb,
msc_bom_components mbc,
msc_system_items assemble,
msc_system_items ascp_comp
WHERE mb.plan_id = -1
AND mb.sr_instance_id = p_sr_instance_id
AND decode (p_master_org, null, mb.organization_id, p_master_org) = mb.organization_id
AND mb.alternate_bom_designator is null
AND mbc.bill_sequence_id = mb.bill_sequence_id
AND mbc.plan_id = mb.plan_id
AND mbc.sr_instance_id = mb.sr_instance_id
AND mbc.organization_id = mb.organization_id
AND assemble.sr_instance_id = mbc.sr_instance_id
AND assemble.plan_id = mbc.plan_id
AND assemble.inventory_item_id = mbc.using_assembly_id
AND assemble.organization_id = mbc.organization_id
AND ( assemble.mrp_planning_code <> 6 -- Exclude non plan ATO, but include PTO
OR
( assemble.mrp_planning_code = 6
AND assemble.pick_components_flag = 'Y'))
AND assemble.ato_forecast_control <> 3
AND ( assemble.bom_item_type <> 4 -- exclude Standard bom, but include Kit
OR
( assemble.bom_item_type = 4
AND assemble.pick_components_flag = 'Y'))
AND ascp_comp.plan_id = mbc.plan_id
AND ascp_comp.inventory_item_id = mbc.inventory_item_id
AND ascp_comp.organization_id = mbc.organization_id
AND ascp_comp.sr_instance_id = mbc.sr_instance_id
AND ascp_comp.ato_forecast_control = C_CONSUME_DERIVE
AND ascp_comp.bom_item_type in (C_MODEL,C_STANDARD)
AND ( ascp_comp.mrp_planning_code <> C_NO_PLANNING
OR ( ascp_comp.mrp_planning_code = C_NO_PLANNING
AND ascp_comp.pick_components_flag = 'Y')); -- Support PTO as component
SELECT sr_inventory_item_id
FROM msc_system_items
WHERE sr_instance_id = p_instance
AND plan_id = -1
AND organization_id = p_org_id
AND inventory_item_id = p_item_id;
SELECT sr_instance_id,
sr_organization_id,
sr_assembly_item_id,
sr_component_item_id,
min(effectivity_date) effectivity_date,
max(nvl(disable_date, to_date('01-01-4000', 'DD-MM-YYYY'))) disable_date,
sum(quantity_per) quantity_per,
(sum(quantity_per) * 100)/(decode (sum (decode (planning_factor, 0, null, quantity_per * 100 /planning_factor)),
0,1,null,1,
sum (decode (planning_factor, 0, null, quantity_per * 100 /planning_factor)))) planning_factor,
min(optional_flag) optional_flag
FROM
msd_dem_bom_components
GROUP BY
sr_instance_id,
sr_organization_id,
sr_assembly_item_id,
sr_component_item_id
HAVING count(*) > 1;
msd_dem_common_utilities.log_debug ('Select Valid Components from MSC');
mo_parents.delete;
oc_parents.delete(compLastIndex);
oc_parents.delete(compLastIndex);
oc_parents.delete(compLastIndex);
IF (numInsert > C_INSTANCE.LAST)
THEN
C_INSTANCE.extend;
IF (numInsert > C_INSTANCE.LAST)
THEN
C_INSTANCE.extend;
C_INSTANCE(numInsert) := L_INSTANCE(j);
C_ORG_SR_PKS(numInsert) := L_ORG_SR_PKS(j);
C_ASSEMBLY_SR_PKS(numInsert) := x_sr_level_pk;
C_COMPONENT_SR_PKS(numInsert) := L_COMPONENT_SR_PKS(j);
C_EFFECTIVE_DATES(numInsert) := L_EFFECTIVE_DATES(j);
C_DISABLE_DATES(numInsert) := mo_parents(i).disable_date;
C_QUANTITY_PER(numInsert) := mo_parents(i).quantity_per;
C_PLANNING_FACTOR(numInsert) := mo_parents(i).planning_factor;
C_OPTIONAL_FLAG(numInsert) := L_OPTIONAL_FLAG(j);
numInsert := numInsert + 1;
IF (numInsert > C_INSTANCE.LAST)
THEN
C_INSTANCE.extend;
C_INSTANCE(numInsert) := L_INSTANCE(j);
C_ORG_SR_PKS(numInsert) := L_ORG_SR_PKS(j);
C_ASSEMBLY_SR_PKS(numInsert) := x_sr_level_pk;
C_COMPONENT_SR_PKS(numInsert) := L_COMPONENT_SR_PKS(j);
C_EFFECTIVE_DATES(numInsert) := L_EFFECTIVE_DATES(j);
C_DISABLE_DATES(numInsert) := L_DISABLE_DATES(j);
C_QUANTITY_PER(numInsert) := L_QUANTITY_PER(j);
C_PLANNING_FACTOR(numInsert) := L_PLANNING_FACTOR(j);
C_OPTIONAL_FLAG(numInsert) := L_OPTIONAL_FLAG(j);
numInsert := numInsert + 1;
msd_dem_common_utilities.log_debug ('There is no data to insert - ' || to_char(C_INSTANCE.LAST));
INSERT INTO MSD_DEM_BOM_COMPONENTS (
sr_instance_id,
sr_organization_id,
sr_assembly_item_id,
sr_component_item_id,
effectivity_date,
disable_date,
quantity_per,
planning_factor,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
optional_flag)
VALUES (
C_INSTANCE(k),
C_ORG_SR_PKS(k),
C_ASSEMBLY_SR_PKS(k),
C_COMPONENT_SR_PKS(k),
C_EFFECTIVE_DATES(k),
C_DISABLE_DATES(k),
C_QUANTITY_PER(k),
C_PLANNING_FACTOR(k),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
C_OPTIONAL_FLAG(k) );
msd_dem_common_utilities.log_debug ('The number of rows inserted is : ' || to_char(numInsert - 1));
DELETE FROM MSD_DEM_BOM_COMPONENTS
WHERE sr_instance_id = c_token.sr_instance_id
AND sr_organization_id = c_token.sr_organization_id
AND sr_assembly_item_id = c_token.sr_assembly_item_id
AND sr_component_item_id = c_token.sr_component_item_id;
msd_dem_common_utilities.log_debug ('Number of rows deleted : ' || to_char(SQL%ROWCOUNT));
INSERT INTO MSD_DEM_BOM_COMPONENTS (
sr_instance_id,
sr_organization_id,
sr_assembly_item_id,
sr_component_item_id,
effectivity_date,
disable_date,
quantity_per,
planning_factor,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
optional_flag)
VALUES (
c_token.sr_instance_id,
c_token.sr_organization_id,
c_token.sr_assembly_item_id,
c_token.sr_component_item_id,
c_token.effectivity_date,
decode (c_token.disable_date, to_date('01-01-4000', 'DD-MM-YYYY'), null, c_token.disable_date),
c_token.quantity_per,
c_token.planning_factor,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
c_token.optional_flag);
msd_dem_common_utilities.log_debug ('Number of rows inserted : ' || to_char(SQL%ROWCOUNT));