1 package ENGPKIMP AUTHID CURRENT_USER as
2 /* $Header: ENGEIMPS.pls 120.7 2011/05/11 11:44:30 rambkond ship $ */
3
4 yes constant number(1) := 1;
5 no constant number(1) := 2;
6 Type StringArray is table of varchar2(81) index by binary_integer;
7 Type NameArray is table of varchar2(30) index by binary_integer;
8 Type BooleanArray is table of number(1) index by binary_integer;
9 ----------------------------- Procedure ---------------------------------
10 --
11 -- NAME
12 -- implement_revised_item
13 -- DESCRIPTION
14 -- Implements a revised item defined on an Engineering Change Order.
15 -- REQUIRES
16 -- Revised Item Sequence Id - The unique identifier of the Revised Item.
17 -- Who values - Information about the user enviroment for who columns.
18 -- Trial mode - Yes(1) or No(2). If Yes, then all possible errors are
19 -- returned. If No, aborts upon first error.
20 -- MODIFIES
21 --
22 -- RETURNS
23 -- Update WIP - Yes(1) or No(2). Should work in progress be updated with
24 -- re-exploded bills of material.
25 -- New Item Revision High Date - High Date of Revision to use for
26 -- Update WIP routine.
27 -- Bill Sequence Id - Unique identifier of bill of material for
28 -- this item.
29 -- Error Messages - Names, tokens, values, translates and quantity of
30 -- message dictionary messages.
31 -- NOTES
32 -- This must be called within the C program, enlimp. Enlimp updates work
33 -- in progress with a re-exploded bill of material if update_wip
34 -- is "yes".
35 -- EXAMPLE
36 --
37
38
39 TYPE Rev_op_disable_date_Rec_Type IS RECORD
40 (
41 Revised_Item_Id NUMBER
42 , Operation_seq_id NUMBER
43 , Disable_date DATE
44 );
45 TYPE Rev_Op_Disable_Date_Tbl_Type IS TABLE OF Rev_op_disable_date_Rec_Type
46 INDEX BY BINARY_INTEGER ;
47
48 TYPE Rev_Comp_Disable_Date_Rec_Type IS RECORD
49 (
50 Revised_Item_Id NUMBER
51 , Component_seq_id NUMBER
52 , Disable_date DATE
53 );
54 TYPE Rev_Comp_Disable_Date_Tbl_Type IS TABLE OF Rev_Comp_Disable_Date_Rec_Type
55 INDEX BY BINARY_INTEGER ;
56
57 Procedure implement_revised_item(
58 revised_item in eng_revised_items.revised_item_sequence_id%type,
59 trial_mode in number,
60 max_messages in number, -- size of host arrays
61 userid in number, -- user id
62 reqstid in number, -- concurrent request id
63 appid in number, -- application id
64 progid in number, -- program id
65 loginid in number, -- login id
66 bill_sequence_id OUT NOCOPY eng_revised_items.bill_sequence_id%type,
67 routing_sequence_id OUT NOCOPY eng_revised_items.routing_sequence_id%type,
68 eco_for_production OUT NOCOPY eng_revised_items.eco_for_production%type,
69 revision_high_date OUT NOCOPY mtl_item_revisions.effectivity_date%type,
70 rtg_revision_high_date OUT NOCOPY mtl_rtg_item_revisions.effectivity_date%type,
71 update_wip OUT NOCOPY eng_revised_items.update_wip%type,
72 group_id1 OUT NOCOPY wip_job_schedule_interface.group_id%type,
73 group_id2 OUT NOCOPY wip_job_schedule_interface.group_id%type,
74 wip_job_name1 OUT NOCOPY wip_entities.wip_entity_name%type,
75 wip_job_name2 OUT NOCOPY wip_entities.wip_entity_name%type,
76 wip_job_name2_org_id OUT NOCOPY wip_entities.organization_id%type,
77 message_names OUT NOCOPY NameArray,
78 token1 OUT NOCOPY NameArray,
79 value1 OUT NOCOPY StringArray,
80 translate1 OUT NOCOPY BooleanArray,
81 token2 OUT NOCOPY NameArray,
82 value2 OUT NOCOPY StringArray,
83 translate2 OUT NOCOPY BooleanArray,
84 msg_qty in OUT NOCOPY binary_integer,
85 warnings in OUT NOCOPY number);
86
87 Procedure reverse_standard_bom(
88 revised_item in eng_revised_items.revised_item_sequence_id%type,
89 userid in number,
90 reqstid in number,
91 appid in number,
92 progid in number,
93 loginid in number,
94 bill_sequence_id in eng_revised_items.bill_sequence_id%type,
95 routing_sequence_id in eng_revised_items.routing_sequence_id%type,
96 return_message OUT NOCOPY VARCHAR2,
97 return_status in OUT NOCOPY NUMBER
98 );
99
100 Procedure generate_new_wip_name(
101 p_wip_entity_name IN VARCHAR2
102 ,p_organization_id IN NUMBER
103 ,x_wip_entity_name1 OUT NOCOPY VARCHAR2
104 ,x_wip_entity_name2 OUT NOCOPY VARCHAR2
105 ,x_return_status OUT NOCOPY NUMBER
106 );
107
108 -- Added procedure for bug 4767315
109 /********************************************************************
110 * API Name : implement_eco_wo_revised_item
111 * Parameters IN : p_change_notice
112 * temp_organization_id
113 * Parameters OUT: None
114 * Purpose : used to implement eco for which all revised items are implemented/cancelled and no mandatory tasks are left
115 *********************************************************************/
116
117 PROCEDURE implement_eco_wo_revised_item
118 (
119 p_change_notice in varchar2,
120 temp_organization_id in varchar2
121 );
122
123
124 -- Added procedure for bug 3402607
125 /********************************************************************
126 * API Name : LOG_IMPLEMENT_FAILURE
127 * Parameters IN : p_change_id
128 * p_revised_item_seq_id
129 * Parameters OUT: None
130 * Purpose : Used to update the lifecycle states of the header
134 *********************************************************************/
131 * and create a log in header Action Log if implementation fails.
132 * In case of revised item implementation failure, updates the revised
133 * item status_type
135 PROCEDURE LOG_IMPLEMENT_FAILURE(p_change_id IN NUMBER
136 ,p_revised_item_seq_id IN NUMBER -- Added parameter for bug 3720341
137 );
138
139 -- Code changes for enhancement 6084027 start
140
141 PROCEDURE LOG_IMPLEMENT_FAILURE(p_change_notice IN VARCHAR2
142 ,p_org_id IN NUMBER
143 , p_revised_item_seq_id IN NUMBER
144 );
145 -- Code changes for enhancement 6084027 end
146
147 /*
148 * Added for bug 11895331. This Procedure updates request_id column, to avoid multiple concurrent
149 * requests running on the same revised item, which causes the data corruption.
153 * @param x_return_status Return Status to check Success or Exception.
150 * Autonomous Transaction is required to commit the changes only for this revised item.
151 *
152 * @param p_revised_item_sequence_id Revised Item Sequence ID.
154 * @param p_autonomous_commit Autonomous Commit flag
155 */
156 PROCEDURE Update_BSB_Request_Id_Column ( p_revised_item_sequence_id IN NUMBER,
157 x_return_status OUT NOCOPY NUMBER,
158 p_autonomous_commit IN VARCHAR2
159 );
160
161 end ENGPKIMP;