[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* 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_common_utilities.uom_conv(ascp_comp.uom_code,ascp_comp.inventory_item_id) usage_quantity,
mbc.planning_factor,
mb.bill_sequence_id,
mbc.optional_component
FROM msc_bom_components mbc,
msc_boms mb,
msc_system_items assemble,
msc_system_items ascp_comp
WHERE mbc.plan_id = -1
AND mb.plan_id = mbc.plan_id
AND ascp_comp.plan_id = mbc.plan_id
AND mb.organization_id = mbc.organization_id
AND mb.alternate_bom_designator is null
AND mb.bill_sequence_id = mbc.bill_sequence_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 or -- Exclude non plan ATO, but include PTO
(assemble.mrp_planning_code = 6 and assemble.pick_components_flag = 'Y'))
AND assemble.ato_forecast_control <> 3
AND (assemble.bom_item_type <> 4 or -- exclude Standard bom, but include Kit
(assemble.bom_item_type = 4 and assemble.pick_components_flag = 'Y'))
AND mbc.inventory_item_id = ascp_comp.inventory_item_id
AND mbc.organization_id = ascp_comp.organization_id
AND mbc.sr_instance_id = ascp_comp.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'))
AND mbc.sr_instance_id = mb.sr_instance_id; -- 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;
numInsert number := 1;
Delete from msd_bom_components
where instance = p_instance_id;
debug_line('Selecting 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) := L_DISABLE_DATES(j);
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;
debug_line('There is no data to insert.');
INSERT INTO msd_bom_components (
instance,
sr_organization_pk,
sr_assembly_pk,
sr_component_pk,
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) );
debug_line('The number of rows inserted is : ' || numInsert);
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
*/
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) 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_sr_util.uom_conv(msi.uom_code,msi.sr_inventory_item_id) usage_quantity
from msc_bom_components mbc,
msc_system_items msi
where mbc.plan_id = -1
and msi.plan_id = -1
and msi.organization_id = mbc.organization_id
and msi.sr_instance_id = p_instance
and mbc.organization_id = p_sr_org_pk
and mbc.sr_instance_id = p_instance
and mbc.inventory_item_id = msi.inventory_item_id
and msi.inventory_item_id = p_asmb_ascp_pk
and (mbc.optional_component = 1 or msi.ato_forecast_control in (C_CONSUME ,C_CONSUME_DERIVE));