tag:blogger.com,1999:blog-68182599787130939062024-02-07T11:17:53.607+05:30HomeJithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.comBlogger1725125tag:blogger.com,1999:blog-6818259978713093906.post-28854233236877734752018-01-09T22:54:00.002+05:302018-01-09T22:54:16.710+05:30Query too Find Profile Option Values<div dir="ltr" style="text-align: left;" trbidi="on">
SELECT po.profile_option_name "NAME",<br />
po.USER_PROFILE_OPTION_NAME,<br />
decode(to_char(pov.level_id),<br />
'10001', 'SITE',<br />
'10002', 'APP',<br />
'10003', 'RESP',<br />
'10005', 'SERVER',<br />
'10006', 'ORG',<br />
'10004', 'USER', '???') "LEV",<br />
decode(to_char(pov.level_id),<br />
'10001', '',<br />
'10002', app.application_short_name,<br />
'10003', rsp.responsibility_key,<br />
'10005', svr.node_name,<br />
'10006', org.name,<br />
'10004', usr.user_name,<br />
'???') "CONTEXT",<br />
pov.profile_option_value "VALUE"<br />
FROM FND_PROFILE_OPTIONS_VL po,<br />
FND_PROFILE_OPTION_VALUES pov,<br />
fnd_user usr,<br />
fnd_application app,<br />
fnd_responsibility rsp,<br />
fnd_nodes svr,<br />
hr_operating_units org<br />
WHERE 1=1<br />
and po.USER_PROFILE_OPTION_NAME in( 'ICX: Limit time','ICX: Limit connect', 'ICX:Session Timeout')<br />
AND pov.application_id = po.application_id<br />
AND pov.profile_option_id = po.profile_option_id<br />
AND usr.user_id (+) = pov.level_value<br />
AND rsp.application_id (+) = pov.level_value_application_id<br />
AND rsp.responsibility_id (+) = pov.level_value<br />
AND app.application_id (+) = pov.level_value<br />
AND svr.node_id (+) = pov.level_value<br />
AND org.organization_id (+) = pov.level_value<br />
-- AND decode(to_char(pov.level_id),<br />
-- '10001', '',<br />
-- '10002', app.application_short_name,<br />
-- '10003', rsp.responsibility_key,<br />
-- '10005', svr.node_name,<br />
-- '10006', org.name,<br />
-- '10004', usr.user_name,<br />
-- '???') LIKE '%RCH'<br />
ORDER BY "NAME", pov.level_id, "VALUE";<br />
<div>
<br /></div>
<span id="fullpost">
</span></div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-12083326090213405402018-01-09T01:20:00.001+05:302018-01-09T01:20:07.219+05:30<div dir="ltr" style="text-align: left;" trbidi="on">
<span id="fullpost">
</span><br />
<div>
<span style="font-size: large;">Script to create user accounts and assign the responsibilities from back end. </span> </div>
<div>
<br /></div>
<div>
<div>
DECLARE</div>
<div>
l_record NUMBER := 0;</div>
<div>
l_user_id NUMBER := fnd_global.user_id;</div>
<div>
x_user_id NUMBER := NULL;</div>
<div>
l_password VARCHAR2 (30) := 'welcome1';</div>
<div>
l_appl_name VARCHAR2 (20);</div>
<div>
l_resp_name VARCHAR2 (200);</div>
<div>
l_resp_key VARCHAR2 (200);</div>
<div>
l_resp_desc VARCHAR2 (2000);</div>
<div>
<br /></div>
<div>
CURSOR cur_user_rec</div>
<div>
IS</div>
<div>
SELECT *</div>
<div>
FROM (SELECT DECODE (col,</div>
<div>
1, "1",</div>
<div>
2, "2",</div>
<div>
3, "3",</div>
<div>
4, "4",</div>
<div>
5, "5",</div>
<div>
6, "6",</div>
<div>
7, "7" /* ,</div>
<div>
8, "8",</div>
<div>
9, "9",</div>
<div>
10, "10",</div>
<div>
11, "11",</div>
<div>
12, "12",</div>
<div>
13, "13",</div>
<div>
14, "14" */</div>
<div>
<span style="white-space: pre;"> </span> )</div>
<div>
username</div>
<div>
FROM (SELECT 'JBAVISETTI' AS "1" ,</div>
<div>
'USERNAME2' AS "2", -- replace with required usernames</div>
<div>
'USERNAME3' AS "3",</div>
<div>
'USERNAME4' AS "4",</div>
<div>
'USERNAME5' AS "5",</div>
<div>
'USERNAME6' AS "6",</div>
<div>
'USERNAME7' AS "7" /*,</div>
<div>
'USERNAME8' AS "8",</div>
<div>
'USERNAME9' AS "9",</div>
<div>
'USERNAME10' AS "10" ,</div>
<div>
'USERNAME11' AS "11",</div>
<div>
'USERNAME12' AS "12",</div>
<div>
'USERNAME13' AS "13",</div>
<div>
'USERNAME14' AS "14" */</div>
<div>
FROM DUAL),</div>
<div>
(SELECT ROWNUM AS col</div>
<div>
FROM all_objects</div>
<div>
WHERE ROWNUM <= 20))</div>
<div>
WHERE username IS NOT NULL;</div>
<div>
<br /></div>
<div>
</div>
<div>
CURSOR cur_resp_rec</div>
<div>
IS</div>
<div>
SELECT *</div>
<div>
FROM (SELECT DECODE (col,</div>
<div>
1, "1",</div>
<div>
2, "2",</div>
<div>
3, "3",</div>
<div>
4, "4",</div>
<div>
5, "5",</div>
<div>
6, "6",</div>
<div>
7, "7",</div>
<div>
8, "8",</div>
<div>
9, "9",</div>
<div>
10, "10",</div>
<div>
11, "11",</div>
<div>
12, "12",</div>
<div>
13, "13",</div>
<div>
14, "14" /*,</div>
<div>
15, "15",</div>
<div>
16, "16",</div>
<div>
17, "17",</div>
<div>
18, "18",</div>
<div>
19, "19",</div>
<div>
20, "20",</div>
<div>
21, "21",</div>
<div>
22, "22",</div>
<div>
23, "23",</div>
<div>
24, "24",</div>
<div>
25, "25",</div>
<div>
26, "26",</div>
<div>
27, "27",</div>
<div>
28, "28",</div>
<div>
29, "29",</div>
<div>
30, "30",</div>
<div>
31, "31",</div>
<div>
32, "32",</div>
<div>
33, "33" */</div>
<div>
<span style="white-space: pre;"> </span>)</div>
<div>
respname</div>
<div>
FROM (SELECT 'Application Developer' AS "1",</div>
<div>
'Application Diagnostics' AS "2",</div>
<div>
<span style="white-space: pre;"> </span> 'Cash Management Superuser' AS "3",</div>
<div>
<span style="white-space: pre;"> </span> 'Cost Management - SLA' AS "4",</div>
<div>
'Fixed Assets Manager' AS "5",</div>
<div>
'Fixed Assets Administrator' AS "6",</div>
<div>
'General Ledger Super User' AS "7",</div>
<div>
<span style="white-space: pre;"> </span> 'Payables Manager' AS "8",</div>
<div>
<span style="white-space: pre;"> </span> 'Receivables Inquiry' AS "9",</div>
<div>
<span style="white-space: pre;"> </span> 'Receivables Manager' AS "10",</div>
<div>
<span style="white-space: pre;"> </span> 'Tax Managers' AS "11",</div>
<div>
'Workflow Administrator' AS "12",</div>
<div>
'XML Publisher Administrator' AS "13",</div>
<div>
'System Administrator' AS "14"</div>
<div>
FROM DUAL),</div>
<div>
(SELECT ROWNUM AS col</div>
<div>
FROM all_objects</div>
<div>
WHERE ROWNUM <= 100))</div>
<div>
WHERE respname IS NOT NULL;</div>
<div>
<br /></div>
<div>
CURSOR cur_resp_detail_rec (</div>
<div>
p_resp_name VARCHAR2)</div>
<div>
IS</div>
<div>
SELECT frt.responsibility_name,</div>
<div>
fa.application_short_name,</div>
<div>
fr.RESPONSIBILITY_KEY,</div>
<div>
frt.description,</div>
<div>
frt.responsibility_id,</div>
<div>
frt.application_id</div>
<div>
FROM fnd_responsibility_tl frt,</div>
<div>
fnd_responsibility fr,</div>
<div>
fnd_application fa</div>
<div>
WHERE frt.responsibility_id = fr.responsibility_id</div>
<div>
AND frt.application_id = fa.application_id</div>
<div>
AND UPPER (responsibility_name) = UPPER (p_resp_name);</div>
<div>
BEGIN</div>
<div>
FOR rec_user_cur IN cur_user_rec</div>
<div>
LOOP</div>
<div>
SELECT COUNT (user_id)</div>
<div>
INTO l_record</div>
<div>
FROM fnd_user</div>
<div>
WHERE user_name = UPPER (rec_user_cur.username);</div>
<div>
<br /></div>
<div>
IF l_record = 0</div>
<div>
THEN</div>
<div>
hr_user_acct_internal.create_fnd_user (</div>
<div>
p_user_name => rec_user_cur.username,</div>
<div>
p_password => l_password,</div>
<div>
p_employee_id => NULL,</div>
<div>
p_user_id => x_user_id,</div>
<div>
p_user_start_date => SYSDATE,</div>
<div>
p_email_address => NULL,</div>
<div>
p_description => 'Apps Associates Consultant',</div>
<div>
p_password_date => NULL);</div>
<div>
DBMS_OUTPUT.put_line (x_user_id);</div>
<div>
<br /></div>
<div>
IF x_user_id IS NOT NULL</div>
<div>
THEN</div>
<div>
UPDATE fnd_user</div>
<div>
SET password_lifespan_days = 90</div>
<div>
WHERE user_id = x_user_id;</div>
<div>
<br /></div>
<div>
COMMIT;</div>
<div>
END IF;</div>
<div>
ELSE</div>
<div>
SELECT user_id</div>
<div>
INTO x_user_id</div>
<div>
FROM fnd_user</div>
<div>
WHERE user_name = UPPER (rec_user_cur.username);</div>
<div>
<br /></div>
<div>
DBMS_OUTPUT.put_line (</div>
<div>
'User '</div>
<div>
|| rec_user_cur.username</div>
<div>
|| ' is exists with user id '</div>
<div>
|| x_user_id);</div>
<div>
END IF;</div>
<div>
<br /></div>
<div>
FOR rec_resp_cur IN cur_resp_rec</div>
<div>
LOOP</div>
<div>
FOR rec_resp_detail_cur</div>
<div>
IN cur_resp_detail_rec (rec_resp_cur.respname)</div>
<div>
LOOP</div>
<div>
SELECT COUNT (RESPONSIBILITY_ID)</div>
<div>
INTO l_record</div>
<div>
FROM FND_USER_RESP_GROUPS_ALL</div>
<div>
WHERE USER_ID = x_user_id</div>
<div>
AND RESPONSIBILITY_ID =</div>
<div>
rec_resp_detail_cur.responsibility_id</div>
<div>
AND RESPONSIBILITY_APPLICATION_ID =</div>
<div>
rec_resp_detail_cur.application_id;</div>
<div>
<br /></div>
<div>
IF l_record = 0</div>
<div>
THEN</div>
<div>
BEGIN</div>
<div>
fnd_user_pkg.addresp (</div>
<div>
username => UPPER (rec_user_cur.username),</div>
<div>
resp_app => rec_resp_detail_cur.application_short_name,</div>
<div>
resp_key => rec_resp_detail_cur.RESPONSIBILITY_KEY,</div>
<div>
security_group => 'STANDARD',</div>
<div>
description => rec_resp_detail_cur.description,</div>
<div>
start_date => SYSDATE,</div>
<div>
end_date => NULL);</div>
<div>
COMMIT;</div>
<div>
<br /></div>
<div>
DBMS_OUTPUT.put_line (</div>
<div>
rec_resp_detail_cur.responsibility_name</div>
<div>
|| ' Responsibility Added Successfully for the user '</div>
<div>
|| rec_user_cur.username);</div>
<div>
EXCEPTION</div>
<div>
WHEN OTHERS</div>
<div>
THEN</div>
<div>
DBMS_OUTPUT.put_line (</div>
<div>
rec_resp_detail_cur.responsibility_name</div>
<div>
|| ' Responsibility is not added for the user '</div>
<div>
|| rec_user_cur.username</div>
<div>
|| ' due to'</div>
<div>
|| SQLCODE</div>
<div>
|| SUBSTR (SQLERRM, 1, 100));</div>
<div>
END;</div>
<div>
ELSE</div>
<div>
DBMS_OUTPUT.put_line (</div>
<div>
rec_resp_detail_cur.responsibility_name</div>
<div>
|| ' Responsibility already assigned to the user '</div>
<div>
|| rec_user_cur.username);</div>
<div>
END IF;</div>
<div>
END LOOP;</div>
<div>
END LOOP;</div>
<div>
END LOOP;</div>
<div>
<br /></div>
<div>
COMMIT;</div>
<div>
END;</div>
<div>
/</div>
</div>
</div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-59547478768316005062018-01-09T01:09:00.003+05:302018-01-09T01:09:51.838+05:30<div dir="ltr" style="text-align: left;" trbidi="on">
<span id="fullpost">
</span><br />
<div>
<h1 class="post-title entry-title" itemprop="name headline" style="background-color: white; font-family: Roboto, sans-serif; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 100; line-height: 1.3em; margin: 0px 0px 10px; position: relative; word-wrap: break-word !important;">
<span style="font-size: large;">Script To Remove End Date From Responsibilities Assigned to a user</span></h1>
</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<div>
DECLARE</div>
<div>
p_user_name VARCHAR2(50) := 'RSRIVASTAV';</div>
<div>
p_resp_name VARCHAR2(50) := 'System Administrator';</div>
<div>
v_user_id NUMBER(10) := 0;</div>
<div>
v_responsibility_id NUMBER(10) := 0;</div>
<div>
v_application_id NUMBER(10) := 0;</div>
<div>
BEGIN</div>
<div>
BEGIN</div>
<div>
SELECT</div>
<div>
user_id</div>
<div>
INTO</div>
<div>
v_user_id</div>
<div>
FROM</div>
<div>
fnd_user</div>
<div>
WHERE</div>
<div>
upper(user_name) = upper(p_user_name);</div>
<div>
<br /></div>
<div>
EXCEPTION</div>
<div>
WHEN no_data_found THEN</div>
<div>
dbms_output.put_line('User not found');</div>
<div>
RAISE;</div>
<div>
WHEN OTHERS THEN</div>
<div>
dbms_output.put_line('Error finding User.');</div>
<div>
RAISE;</div>
<div>
END;</div>
<div>
<br /></div>
<div>
BEGIN</div>
<div>
SELECT</div>
<div>
application_id,</div>
<div>
responsibility_id</div>
<div>
INTO</div>
<div>
v_application_id,v_responsibility_id</div>
<div>
FROM</div>
<div>
fnd_responsibility_vl</div>
<div>
WHERE</div>
<div>
upper(responsibility_name) = upper(p_resp_name);</div>
<div>
<br /></div>
<div>
EXCEPTION</div>
<div>
WHEN no_data_found THEN</div>
<div>
dbms_output.put_line('Responsibility not found.');</div>
<div>
RAISE;</div>
<div>
WHEN too_many_rows THEN</div>
<div>
dbms_output.put_line('More than one responsibility found with this name.');</div>
<div>
RAISE;</div>
<div>
WHEN OTHERS THEN</div>
<div>
dbms_output.put_line('Error finding responsibility.');</div>
<div>
RAISE;</div>
<div>
END;</div>
<div>
<br /></div>
<div>
BEGIN</div>
<div>
dbms_output.put_line('Initializing The Application');</div>
<div>
fnd_global.apps_initialize(user_id => v_user_id,resp_id => v_responsibility_id,resp_appl_id => v_application_id);</div>
<div>
<br /></div>
<div>
dbms_output.put_line('Calling FND_USER_RESP_GROUPS_API API To Insert/Update Resp');</div>
<div>
fnd_user_resp_groups_api.update_assignment(user_id => v_user_id,responsibility_id => v_responsibility_id,responsibility_application_id</div>
<div>
=> v_application_id,security_group_id => 0,start_date => SYSDATE,end_date => SYSDATE + 10,description => NULL);</div>
<div>
<br /></div>
<div>
dbms_output.put_line('The End Date has been removed from responsibility');</div>
<div>
COMMIT;</div>
<div>
EXCEPTION</div>
<div>
WHEN OTHERS THEN</div>
<div>
dbms_output.put_line('Error calling the API');</div>
<div>
RAISE;</div>
<div>
END;</div>
<div>
<br /></div>
<div>
END;</div>
</div>
</div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-76465356752915023252014-11-21T11:51:00.002+05:302014-11-21T11:51:51.718+05:30Performance Rating/Review Load<div dir="ltr" style="text-align: left;" trbidi="on">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]--><br />
<!--[if gte mso 9]><xml>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves>false</w:TrackMoves>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>X-NONE</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:DontVertAlignCellWithSp/>
<w:DontBreakConstrainedForcedTables/>
<w:DontVertAlignInTxbx/>
<w:Word11KerningPairs/>
<w:CachedColBalance/>
</w:Compatibility>
<m:mathPr>
<m:mathFont m:val="Cambria Math"/>
<m:brkBin m:val="before"/>
<m:brkBinSub m:val="--"/>
<m:smallFrac m:val="off"/>
<m:dispDef/>
<m:lMargin m:val="0"/>
<m:rMargin m:val="0"/>
<m:defJc m:val="centerGroup"/>
<m:wrapIndent m:val="1440"/>
<m:intLim m:val="subSup"/>
<m:naryLim m:val="undOvr"/>
</m:mathPr></w:WordDocument>
</xml><![endif]--><!--[if gte mso 9]><xml>
<w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
DefSemiHidden="true" DefQFormat="false" DefPriority="99"
LatentStyleCount="267">
<w:LsdException Locked="false" Priority="0" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/>
<w:LsdException Locked="false" Priority="39" Name="toc 1"/>
<w:LsdException Locked="false" Priority="39" Name="toc 2"/>
<w:LsdException Locked="false" Priority="39" Name="toc 3"/>
<w:LsdException Locked="false" Priority="39" Name="toc 4"/>
<w:LsdException Locked="false" Priority="39" Name="toc 5"/>
<w:LsdException Locked="false" Priority="39" Name="toc 6"/>
<w:LsdException Locked="false" Priority="39" Name="toc 7"/>
<w:LsdException Locked="false" Priority="39" Name="toc 8"/>
<w:LsdException Locked="false" Priority="39" Name="toc 9"/>
<w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/>
<w:LsdException Locked="false" Priority="10" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Title"/>
<w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/>
<w:LsdException Locked="false" Priority="11" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/>
<w:LsdException Locked="false" Priority="22" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Strong"/>
<w:LsdException Locked="false" Priority="20" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/>
<w:LsdException Locked="false" Priority="59" SemiHidden="false"
UnhideWhenUsed="false" Name="Table Grid"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/>
<w:LsdException Locked="false" Priority="1" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 1"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 1"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 1"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/>
<w:LsdException Locked="false" Priority="34" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/>
<w:LsdException Locked="false" Priority="29" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Quote"/>
<w:LsdException Locked="false" Priority="30" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 1"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 1"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 2"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 2"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 2"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 2"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 2"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 3"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 3"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 3"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 3"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 3"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 4"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 4"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 4"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 4"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 4"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 5"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 5"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 5"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 5"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 5"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 6"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 6"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 6"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 6"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 6"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/>
<w:LsdException Locked="false" Priority="19" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/>
<w:LsdException Locked="false" Priority="21" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/>
<w:LsdException Locked="false" Priority="31" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/>
<w:LsdException Locked="false" Priority="32" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/>
<w:LsdException Locked="false" Priority="33" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/>
<w:LsdException Locked="false" Priority="37" Name="Bibliography"/>
<w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/>
</w:LatentStyles>
</xml><![endif]--><!--[if gte mso 10]>
<style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;}
</style>
<![endif]-->
<br />
<div align="center" class="MsoNormal" style="text-align: center;">
<b style="mso-bidi-font-weight: normal;"><u><span style="font-size: 17.0pt; line-height: 115%; mso-bidi-font-size: 11.0pt;">Performance Rating Load</span></u></b></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><u>From Application :</u></b>
Performance rating can be entered from application as in below</div>
<div class="MsoListParagraphCxSpFirst" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font: 7.0pt "Times New Roman";">
</span></span></span>Select Appropriate Business Group
Responsibility:<span style="mso-spacerun: yes;"> </span>ex: HR Operations US</div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font: 7.0pt "Times New Roman";">
</span></span></span><b style="mso-bidi-font-weight: normal;">Navigation</b>
: HR Operations US > People > Enter and Maintain > Query for Employee
> Assignments > Others > Performance</div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font: 7.0pt "Times New Roman";">
</span></span></span>Enter, 'Effective Date' and 'Performance
Rating', 'Next Date'</div>
<div class="MsoListParagraphCxSpLast" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font: 7.0pt "Times New Roman";">
</span></span></span>Save</div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><u>Tables effected : </u></b>PER_PERFORMANCE_REVIEWS</div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><u>Loading through
Program or backend:</u> </b>Performance rating can also be entered from backend
or by developing a script and registering as Program for bulk Load</div>
<div class="MsoListParagraphCxSpFirst" style="mso-list: l1 level1 lfo2; text-indent: -.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font: 7.0pt "Times New Roman";">
</span></span></span><b style="mso-bidi-font-weight: normal;">Api : </b>HR_PERF_REVIEW_API.CREATE_PERF_REVIEW</div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l1 level1 lfo2; text-indent: -.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font: 7.0pt "Times New Roman";">
</span></span></span>Required data:<span style="mso-spacerun: yes;">
</span></div>
<div class="MsoListParagraphCxSpMiddle">
Created_by, </div>
<div class="MsoListParagraphCxSpMiddle">
person_id ( can be derived if Employee
number provided)</div>
<div class="MsoListParagraphCxSpMiddle">
full_name (just<span style="mso-spacerun: yes;"> </span>to validate or to confirm the exact employee
provided in csv or for any typo)</div>
<div class="MsoListParagraphCxSpMiddle">
performance_rating<span style="mso-spacerun: yes;"> </span></div>
<div class="MsoListParagraphCxSpMiddle">
Effective_date </div>
<div class="MsoListParagraphCxSpMiddle">
next_review_date</div>
<div class="MsoListParagraphCxSpLast">
Business_Group </div>
<div class="MsoListParagraphCxSpLast">
<br /></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><u>Single Insert :</u>
</b>using the below script done a single insert and the
data get inserted into application and could able to see it ( refer to the
below screenshot)</div>
<div class="MsoNormal">
The below script is just a sample single insert script followed by a a package /procedure with required validations with commit and
Roll back options</div>
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt; line-height: 115%; mso-ansi-language: EN-US; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><br clear="all" style="mso-special-character: line-break; page-break-before: always;" />
</span>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhy0X-PO8-BTUq3JKwFqcEkXJmZH7vQmVhYuB40-lcZmyd5Ha78IMsK9HaYL0ouqzOyamMk83lT3HAhrSQAIbqrxXae1oZKR6XIgv1iNphlitOlmgdnHZqRMMCqFFAxySyOAs-lJxsJri8/s1600/Untitled.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhy0X-PO8-BTUq3JKwFqcEkXJmZH7vQmVhYuB40-lcZmyd5Ha78IMsK9HaYL0ouqzOyamMk83lT3HAhrSQAIbqrxXae1oZKR6XIgv1iNphlitOlmgdnHZqRMMCqFFAxySyOAs-lJxsJri8/s1600/Untitled.png" height="473" width="640" /></a></div>
<div class="MsoNormal">
<span style="mso-no-proof: yes;"><br /></span></div>
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt; line-height: 115%; mso-ansi-language: EN-US; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><br clear="all" style="mso-special-character: line-break; page-break-before: always;" />
</span>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
DECLARE</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>-- Start of Variable declarations, Initialize
Variables with appropriate values to test the script</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>-- Input Variables</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>V_VALIDATE BOOLEAN;</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>-- Output Variables</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>V_PERFORMANCE_REVIEW_ID NUMBER;</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>-- Input Variables</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>V_PERSON_ID NUMBER := 21227; </div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>V_EVENT_ID<span style="mso-spacerun: yes;">
</span>NUMBER;</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>V_REVIEW_DATE DATE<span style="mso-spacerun: yes;"> </span>:= '01-NOV-2014' ;</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>V_PERFORMANCE_RATING VARCHAR2(2000) :=
'XX_4';</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>V_NEXT_PERF_REVIEW_DATE DATE<span style="mso-spacerun: yes;"> </span>:= '01-NOV-2015';</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>-- Output Variables</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>V_OBJECT_VERSION_NUMBER<span style="mso-spacerun: yes;"> </span>NUMBER;</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>V_NEXT_REVIEW_DATE_WARNING BOOLEAN;</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
begin</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>fnd_global.apps_initialize(23248,52441,800 );</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>--<span style="mso-spacerun: yes;">
</span>Calling API HR_PERF_REVIEW_API.CREATE_PERF_REVIEW</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>HR_PERF_REVIEW_API.CREATE_PERF_REVIEW (</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>P_VALIDATE => V_VALIDATE ,</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>P_PERFORMANCE_REVIEW_ID =>
V_PERFORMANCE_REVIEW_ID ,</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>P_PERSON_ID => V_PERSON_ID ,</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>P_EVENT_ID => V_EVENT_ID ,</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>P_REVIEW_DATE => V_REVIEW_DATE ,</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>P_PERFORMANCE_RATING =>
V_PERFORMANCE_RATING ,</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>P_NEXT_PERF_REVIEW_DATE =>
V_NEXT_PERF_REVIEW_DATE</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span>--,P_ATTRIBUTE_CATEGORY<span style="mso-spacerun: yes;"> </span>=> V_ATTRIBUTE_CATEGORY</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>,P_OBJECT_VERSION_NUMBER =>
V_OBJECT_VERSION_NUMBER ,</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>P_NEXT_REVIEW_DATE_WARNING =>
V_NEXT_REVIEW_DATE_WARNING </div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>);</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>dbms_output.put_line('V_PERFORMANCE_REVIEW_ID
: ' || V_PERFORMANCE_REVIEW_ID);</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>dbms_output.put_line('V_OBJECT_VERSION_NUMBER
: ' || V_OBJECT_VERSION_NUMBER);</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
EXCEPTION</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
WHEN OTHERS
THEN</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="mso-spacerun: yes;"> </span>dbms_output.put_line('error : ' || sqlerrm);</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
END;</div>
<br /><br />
<br />
CREATE or replace package Apps.XX_HR_PERFORMANCE_PKG AS<br /> /*<br /> *************************************************************************<br /> ** XX Performance Review Load by Apps Associates<br /> *************************************************************************<br /> FILE_NAME : XX_HR_PERFORMANCE_PKG.pks<br /> File Type : PACKAGE SPECIFICATION<br /> PURPOSE : The Package for the Wrapper Functions/Procedures used to Create Performance Ratings.<br /> Created by : Jithendra <br /> Creation Date : 17-Sep-2014<br /> Updated by :<br /> Updation Date :<br /> VERSION : 1.0<br /> Comments :<br /> ***************************************************************************/ <br /><br />PROCEDURE XX_PERF_REVIEW_LOAD_PROC(<br /> X_ERRBUF OUT VARCHAR2,<br /> X_RETCODE OUT NUMBER,<br /> V_PATH_LOCATION in varchar2,<br /> v_file_name varchar2,<br /> V_REVIEW_DATE in varchar2,<br /> V_NEXT_PERF_REVIEW_DATE in varchar2<br /> );<br />end XX_HR_PERFORMANCE_PKG;<br /><br />/<br /><br />CREATE or replace package body Apps.XX_HR_PERFORMANCE_PKG AS<br /> /*<br /> *************************************************************************<br /> ** XX Performance Review Load by Apps Associates<br /> *************************************************************************<br /> FILE_NAME : XX_HR_PERFORMANCE_PKG.pkb<br /> File Type : PACKAGE body<br /> PURPOSE : The Package for the Wrapper Functions/Procedures used to Create Performance Ratings.<br /> Created by : Jithendra <br /> Creation Date : 17-Sep-2014<br /> Updated by :<br /> Updation Date :<br /> VERSION : 1.0<br /> Comments :<br /> ***************************************************************************/ <br /><br />PROCEDURE XX_PERF_REVIEW_LOAD_PROC (<br /> X_ERRBUF OUT VARCHAR2,<br /> X_RETCODE OUT NUMBER,<br /> V_PATH_LOCATION in varchar2,<br /> v_file_name varchar2, <br /> V_REVIEW_DATE in varchar2,<br /> V_NEXT_PERF_REVIEW_DATE in varchar2<br /> )<br />as<br /> V_FILE_TYPE UTL_FILE.FILE_TYPE;<br /> V_LINE varchar2 (1000);<br /> V_EMPLOYEE_NUMBER number;<br /> V_BUSINESS_GROUP varchar2(200);<br /> V_LAST_NAME varchar2(200);<br /> V_FIRST_NAME varchar2(200);<br /> V_PERF_RATING_NAME varchar2(30);<br /> V_PERF_RATING_CODE VARCHAR2(30);<br /> V_REC_COUNT number := 0;<br /> V_VALIDATE BOOLEAN;<br /> V_PERSON_ID number ;<br /> V_PERFORMANCE_REVIEW_ID NUMBER;<br /> V_OBJECT_VERSION_NUMBER NUMBER;<br /> V_NEXT_REVIEW_DATE_WARNING BOOLEAN;<br /> L_NEXT_REVIEW_DATE date;<br /> V_TOT_count Number :=0;<br /> v_SUC_count Number :=0;<br /> V_FAIL_Count Number :=0;<br />BEGIN<br /><br /> fnd_file.put_line(fnd_file.output,'File Name:' ||V_PATH_LOCATION||'/'||v_file_name);<br /> fnd_file.put_line(fnd_file.output,'Review Date:' ||V_REVIEW_DATE);<br /> fnd_file.put_line(fnd_file.output,'Next Review Date:' ||V_NEXT_PERF_REVIEW_DATE);<br /> <br /> <br /> V_FILE_TYPE := UTL_FILE.FOPEN (v_path_location, v_file_name, 'R');<br /> <br /> if V_NEXT_PERF_REVIEW_DATE = null<br /> then <br /> L_NEXT_REVIEW_DATE := null;<br /> else<br /> L_NEXT_REVIEW_DATE := TO_DATE(V_NEXT_PERF_REVIEW_DATE,'dd-mon-yyyy');<br /> end if;<br /> <br /> IF UTL_FILE.IS_OPEN(V_FILE_TYPE) THEN<br /> LOOP<br />-- fnd_file.put_line(fnd_file.output,'start/repeat :- '||V_REC_COUNT);<br /> BEGIN<br /> <br /> begin<br /> UTL_FILE.GET_LINE(V_FILE_TYPE, V_LINE);<br /> V_TOT_count := V_TOT_count +1;<br /> Exception when no_data_found THEN<br /> fnd_file.put_line(fnd_file.log,'No More Reccords ' ||V_REC_COUNT);<br /> UTL_FILE.FCLOSE(V_FILE_TYPE);<br /> EXIT;<br /> END; <br /> if V_REC_COUNT = 0 then<br /> fnd_file.put_line(fnd_file.log,'skipiping the first line to read values '||V_LINE);<br /> V_REC_COUNT := V_REC_COUNT+1;<br /> <br /> else<br /> <br /> V_REC_COUNT := V_REC_COUNT+1;<br /> <br /> -- fnd_file.put_line(fnd_file.output,'Rec :- '||V_LINE);<br /><br /> v_employee_number := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 1);<br /> --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'v_employee_number : '|| V_EMPLOYEE_NUMBER);<br /><br /> v_business_group := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 2);<br /> --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'v_business_group : '||V_BUSINESS_GROUP);<br /><br /> v_last_name := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 3);<br /> --fnd_file.put_line(fnd_file.output,'v_last_name : '||v_last_name);<br /><br /> v_first_name := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 4);<br /> --fnd_file.put_line(fnd_file.output,'v_first_name : '||v_first_name);<br /> <br /> --V_PERF_RATING_NAME := nvl(trim(BOTH CHR(13) from REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 5)),trim(BOTH CHR(13) from REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 4)));<br /> V_PERF_RATING_NAME := trim(BOTH CHR(13) from REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 5));<br /> <br /> <br /> --fnd_file.put_line(fnd_file.output,'V_PERF_RATING_NAME : '||V_PERF_RATING_NAME);<br /> <br /> select papf.PERSON_ID , DECODE(V_PERF_RATING_NAME ,'1 - Lowest' , 'XX_1',<br /> '2' , 'XX_2',<br /> '3', 'XX_3',<br /> '4' , 'XX_4'<br /> ,'5 - Highest' ,'XX_5')<br /> into V_PERSON_ID, V_PERF_RATING_CODE <br /> from PER_ALL_PEOPLE_F papf, per_business_groups pbg<br /> where EMPLOYEE_NUMBER = V_EMPLOYEE_NUMBER <br /> and sysdate between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE<br /> AND papf.business_group_id=pbg.business_group_id<br /> and pbg.name=v_business_group;<br /> <br /> --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'v_person_id :'||V_PERSON_ID||', V_PERF_RATING_CODE :'||V_PERF_RATING_CODE); <br /> <br /> HR_PERF_REVIEW_API.CREATE_PERF_REVIEW( P_VALIDATE => V_VALIDATE<br /> ,P_PERFORMANCE_REVIEW_ID => V_PERFORMANCE_REVIEW_ID<br /> ,P_PERSON_ID => V_PERSON_ID<br /> ,P_EVENT_ID => null<br /> ,P_REVIEW_DATE => TO_DATE(V_REVIEW_DATE,'dd-mon-yyyy')<br /> ,P_PERFORMANCE_RATING => V_PERF_RATING_CODE<br /> ,P_NEXT_PERF_REVIEW_DATE => L_NEXT_REVIEW_DATE<br /> ,P_OBJECT_VERSION_NUMBER => V_OBJECT_VERSION_NUMBER<br /> ,P_NEXT_REVIEW_DATE_WARNING => V_NEXT_REVIEW_DATE_WARNING<br /> );<br /> <br /> <br /> <br /> -- FND_FILE.PUT_LINE(FND_FILE.log,V_LINE);<br /> --rollback;<br /> v_SUC_count := v_SUC_count +1; <br /> --COMMIT;<br /> end if;<br /> EXCEPTION when OTHERS then <br /> --fnd_file.put_line(fnd_file.log,'V_PERF_RATING_NAME'||V_PERF_RATING_NAME);<br /> --fnd_file.put_line(fnd_file.log,V_LINE);<br /> fnd_file.put_line(fnd_file.log,'Failed - '||V_EMPLOYEE_NUMBER||' with Error :'||sqlerrm);<br /> --EXIT;<br /> --continue;<br /> V_FAIL_Count := V_FAIL_Count +1;<br /> end ;<br /> <br /> END LOOP;<br /> end if;<br />--rollback;<br /> commit;<br /> V_TOT_count:=V_TOT_count-1;<br /> fnd_file.put_line(fnd_file.output,'Total No of records in file : '||V_TOT_count);<br /> fnd_file.put_line(fnd_file.output,'Failed records count: '||V_FAIL_Count);<br /> fnd_file.put_line(fnd_file.output,'Successful records count: '||V_Suc_Count);<br /><br />EXCEPTION when OTHERS then <br /> FND_FILE.PUT_LINE(FND_FILE.log,'Other errors :'||SQLERRM);<br /> UTL_FILE.FCLOSE(V_FILE_TYPE);<br />END XX_PERF_REVIEW_LOAD_PROC;<br />end XX_HR_PERFORMANCE_PKG;<br />
<span id="fullpost">
</span></div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-16183788399179719472014-11-21T11:37:00.005+05:302014-11-21T11:37:57.805+05:30Oracle HRMS API – Update Employee Assignment<div dir="ltr" style="text-align: left;" trbidi="on">
/*<br />To Update: Supervisor, Manager Flag, Bargaining Unit, Labour Union Member Flag, Gre, Time Card, Work Schedule, Normal Hours, Frequency, Time Normal Finish, Time Normal Start, Default Code Combination, Set of Books Id<br /><br />
API -- hr_assignment_api.update_emp_asg<br /><br />To Update: Grade, Location, Job, Payroll, Organization, Employee Category, People Group<br /><br />
API -- hr_assignment_api.update_emp_asg_criteria<br />*/ <br /><br />--Example --<br /><br />DECLARE<br /> -- Local Variables<br /> -- -----------------------<br /> LC_DT_UD_MODE VARCHAR2(100) := NULL;<br /> LN_ASSIGNMENT_ID NUMBER := 9375;<br />
LN_SUPERVISOR_ID NUMBER := NULL;<br /> LN_OBJECT_NUMBER NUMBER := 1;<br /> ln_people_group_id NUMBER := 1;<br /><br /> -- Out Variables for Find Date Track Mode API<br /> -- -----------------------------------------------------------------<br /> lb_correction BOOLEAN;<br /> lb_update BOOLEAN;<br /> lb_update_override BOOLEAN;<br /> lb_update_change_insert BOOLEAN;<br /> <br /> -- Out Variables for Update Employee Assignment API<br /> -- ----------------------------------------------------------------------------<br /> ln_soft_coding_keyflex_id HR_SOFT_CODING_KEYFLEX.SOFT_CODING_KEYFLEX_ID%TYPE;<br /> lc_concatenated_segments VARCHAR2(2000);<br /> LN_COMMENT_ID PER_ALL_ASSIGNMENTS_F.COMMENT_ID%TYPE;<br /> lb_no_managers_warning BOOLEAN;<br /><br /> -- Out Variables for Update Employee Assgment Criteria<br /> -- -------------------------------------------------------------------------------<br /> ln_special_ceiling_step_id PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID%TYPE;<br /> lc_group_name VARCHAR2(30);<br /> ld_effective_start_date PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE%TYPE;<br /> LD_EFFECTIVE_END_DATE PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE%TYPE;<br /> lb_org_now_no_manager_warning BOOLEAN;<br /> lb_other_manager_warning BOOLEAN;<br /> lb_spp_delete_warning BOOLEAN;<br /> lc_entries_changed_warning VARCHAR2(30);<br /> lb_tax_district_changed_warn BOOLEAN;<br /><br /><br /><br />BEGIN<br /> -- Find Date Track Mode<br /> -- --------------------------------<br /> dt_api.find_dt_upd_modes<br /> ( p_effective_date => TO_DATE('12-JUN-2011'),<br /> p_base_table_name => 'PER_ALL_ASSIGNMENTS_F',<br /> p_base_key_column => 'ASSIGNMENT_ID',<br /> p_base_key_value => ln_assignment_id,<br /> -- Output data elements<br /> -- --------------------------------<br /> p_correction => lb_correction,<br /> p_update => lb_update,<br /> p_update_override => lb_update_override,<br /> p_update_change_insert => lb_update_change_insert<br /> );<br /><br /><br /> IF ( lb_update_override = TRUE OR lb_update_change_insert = TRUE )<br /> THEN<br /> -- UPDATE_OVERRIDE<br /> -- ---------------------------------<br /> lc_dt_ud_mode := 'UPDATE_OVERRIDE';<br /> END IF;<br /><br /><br /><br /> IF ( lb_correction = TRUE )<br /> THEN<br /> -- CORRECTION<br /> -- ----------------------<br /> lc_dt_ud_mode := 'CORRECTION';<br /> END IF;<br /><br /><br /><br /> IF ( lb_update = TRUE )<br /> THEN<br /> -- UPDATE<br /> -- --------------<br /> lc_dt_ud_mode := 'UPDATE';<br /> END IF;<br /><br /><br /><br /> -- Update Employee Assignment<br /> -- ---------------------------------------------<br /> hr_assignment_api.update_emp_asg<br /> ( -- Input data elements<br /> -- ------------------------------<br /> p_effective_date => TO_DATE('12-JUN-2011'),<br /> p_datetrack_update_mode => lc_dt_ud_mode,<br /> p_assignment_id => ln_assignment_id,<br /> p_supervisor_id => NULL,<br /> p_change_reason => NULL,<br /> p_manager_flag => 'N',<br /> p_bargaining_unit_code => NULL,<br /> p_labour_union_member_flag => NULL,<br /> p_segment1 => 204,<br /> p_segment3 => 'N',<br /> p_normal_hours => 10,<br /> p_frequency => 'W',<br /> -- Output data elements<br /> -- -------------------------------<br /> p_object_version_number => ln_object_number,<br /> p_soft_coding_keyflex_id => ln_soft_coding_keyflex_id,<br /> p_concatenated_segments => lc_concatenated_segments,<br /> p_comment_id => ln_comment_id,<br /> p_effective_start_date => ld_effective_start_date,<br /> p_effective_end_date => ld_effective_end_date,<br /> p_no_managers_warning => lb_no_managers_warning,<br /> p_other_manager_warning => lb_other_manager_warning<br /> );<br /><br /> -- Find Date Track Mode for Second API <br /> -- ------------------------------------------------------<br /> dt_api.find_dt_upd_modes<br /> ( p_effective_date => TO_DATE('12-JUN-2011'),<br /> p_base_table_name => 'PER_ALL_ASSIGNMENTS_F',<br /> p_base_key_column => 'ASSIGNMENT_ID',<br /> p_base_key_value => ln_assignment_id,<br /> -- Output data elements<br /> -- -------------------------------<br /> p_correction => lb_correction,<br /> p_update => lb_update,<br /> p_update_override => lb_update_override,<br /> p_update_change_insert => lb_update_change_insert<br /> );<br /><br /><br /><br /> IF ( lb_update_override = TRUE OR lb_update_change_insert = TRUE )<br /> THEN<br /> -- UPDATE_OVERRIDE<br /> -- --------------------------------<br /> lc_dt_ud_mode := 'UPDATE_OVERRIDE';<br /> END IF;<br /><br /><br /><br /> IF ( lb_correction = TRUE )<br /> THEN<br /> -- CORRECTION<br /> -- ----------------------<br /> lc_dt_ud_mode := 'CORRECTION';<br /> END IF;<br /><br /><br /><br /> IF ( lb_update = TRUE )<br /> THEN<br /> -- UPDATE<br /> -- --------------<br /> lc_dt_ud_mode := 'UPDATE';<br /> END IF;<br /><br /><br /><br /> -- Update Employee Assgment Criteria<br /> -- -----------------------------------------------------<br /> hr_assignment_api.update_emp_asg_criteria<br /> ( -- Input data elements<br /> -- ------------------------------<br /> p_effective_date => TO_DATE('12-JUN-2011'),<br /> p_datetrack_update_mode => lc_dt_ud_mode,<br /> p_assignment_id => ln_assignment_id,<br /> p_location_id => 204,<br /> p_grade_id => 29,<br /> p_job_id => 16,<br /> p_payroll_id => 52,<br /> p_organization_id => 239,<br /> p_employment_category => 'FR',<br /> -- Output data elements<br /> -- -------------------------------<br /> p_people_group_id => ln_people_group_id,<br /> p_object_version_number => ln_object_number,<br /> p_special_ceiling_step_id => ln_special_ceiling_step_id,<br /> p_group_name => lc_group_name,<br /> p_effective_start_date => ld_effective_start_date,<br /> p_effective_end_date => ld_effective_end_date,<br /> p_org_now_no_manager_warning => lb_org_now_no_manager_warning,<br /> p_other_manager_warning => lb_other_manager_warning,<br /> p_spp_delete_warning => lb_spp_delete_warning,<br /> p_entries_changed_warning => lc_entries_changed_warning,<br /> p_tax_district_changed_warning => lb_tax_district_changed_warn<br /> );<br /><br /> COMMIT;<br /><br />EXCEPTION<br /> WHEN OTHERS THEN<br /> ROLLBACK;<br /> dbms_output.put_line(SQLERRM);<br />END;<br />/<br /><br />
<span id="fullpost">
</span></div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-68761034292026031512014-09-16T18:39:00.001+05:302014-09-16T18:39:47.240+05:30Scheduling the Discoverer reports<div class="WordSection1"> <p class="MsoNormal" align="center" style="text-align:center"><b><u><span style="font-size:16.0pt;line-height:115%">Scheduling the Discoverer reports<o:p></o:p></span></u></b></p> <p class="MsoNormal" style="text-align:justify">Scheduling the Discoverer reports can be done only through Discoverer Plus /Desktop and the scheduled reports can be shared to users so that they can see the results from Application. <o:p></o:p></p> <p class="MsoNormal" style="text-align:justify">As the end users may not have access to Discoverer plus / Desktop, Tech team or DBA's have to schedule the reports and to share to the respective users who are looking for the scheduled results.<o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><b><u>Prerequisites to schedule the discoverer reports:<o:p></o:p></u></b></p> <p class="MsoNormal" style="text-align:justify">user should have Scheduling access to schedule the reports from Discoverer plus/Discoverer Desktop<o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZi78M7cTZ1lZHMrUsKaUNzq110hRpBampm9F_7GC7eJ5k-hyEOVlivmdQ4f3HpJphTVYZkSCR1BaALccTta_i7oKg93I-Ddmzm3hVCTErKZEpJRsPbrpVLC1YDUFjFytnPjszNK4tJzE/s1600/image001-787241.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZi78M7cTZ1lZHMrUsKaUNzq110hRpBampm9F_7GC7eJ5k-hyEOVlivmdQ4f3HpJphTVYZkSCR1BaALccTta_i7oKg93I-Ddmzm3hVCTErKZEpJRsPbrpVLC1YDUFjFytnPjszNK4tJzE/s320/image001-787241.png" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653340056872226" /></a><o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><b><u>Steps to Verify and Enable the required Privileges :<o:p></o:p></u></b></p> <p class="MsoNormal" style="text-align:justify"><b>Step1: </b>Login to Discoverer Administrator as sysadmin<o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><b><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-AP4frhoTflv2R0WCM9FIK3ji4oNxP0yoLPNpeC9a6zcAfmMqMXLTFqj_kMq5Tv1A3IdfATPGXaSjZWqVB3DVuYsB31n7UjMgnwSYwJkr-dUvnbDKZPvh1bM0HX5VGIDCBmjN-8WMIxU/s1600/image002-789522.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-AP4frhoTflv2R0WCM9FIK3ji4oNxP0yoLPNpeC9a6zcAfmMqMXLTFqj_kMq5Tv1A3IdfATPGXaSjZWqVB3DVuYsB31n7UjMgnwSYwJkr-dUvnbDKZPvh1bM0HX5VGIDCBmjN-8WMIxU/s320/image002-789522.png" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653349818816498" /></a><o:p></o:p></b></p> <p class="MsoNormal" style="text-align:justify"><b>Step2: </b>Choose the responsibility "System Administrator"<o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiANAm8LpE4-Sy-adUC7zQLt-l9IpLL_1KfsrvGCgmoamRMQrgHVoq3OwSqSd3U4pY5gxhaRO8i7G_4UqPROt9gXvP3ADyG26pvkV6dPOC5yiWBHU5HafpNOhOoq-MCCraGgVz2vM1k2is/s1600/image003-792348.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiANAm8LpE4-Sy-adUC7zQLt-l9IpLL_1KfsrvGCgmoamRMQrgHVoq3OwSqSd3U4pY5gxhaRO8i7G_4UqPROt9gXvP3ADyG26pvkV6dPOC5yiWBHU5HafpNOhOoq-MCCraGgVz2vM1k2is/s320/image003-792348.png" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653362175834914" /></a><o:p></o:p></p> <p class="MsoNormal" style="text-align:justify">Step3: Open an Existing Business Area "IDCO UK Contract and Receivables"<o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhz_IZhloUyVDUix52b5XJaK1UV6TLYWRkZNglsW5mpr6d49sfrGzN4PSPpHpePH0v4yG2v47wDfqKbd6amOHA4l3HpmpxV9fsgBafzT41kdolLqdMIcYQWK6N9LEbv7Uem3pYnzeuq0N8/s1600/image004-794238.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhz_IZhloUyVDUix52b5XJaK1UV6TLYWRkZNglsW5mpr6d49sfrGzN4PSPpHpePH0v4yG2v47wDfqKbd6amOHA4l3HpmpxV9fsgBafzT41kdolLqdMIcYQWK6N9LEbv7Uem3pYnzeuq0N8/s320/image004-794238.png" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653370146459170" /></a><o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhaApzK_IZJUMqw2hi4VcPxkNh9ushHlb2_5KipPCxTSAE_UBKpbcvrhODlUllALD-zYdWpA8SgFXbD3SwyKC5Ri9EGnKRlTsf2ELSenFlU-BmNlFoaoi3FgfiCW7sj4n8v8aQyP98Z8HE/s1600/image005-797089.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhaApzK_IZJUMqw2hi4VcPxkNh9ushHlb2_5KipPCxTSAE_UBKpbcvrhODlUllALD-zYdWpA8SgFXbD3SwyKC5Ri9EGnKRlTsf2ELSenFlU-BmNlFoaoi3FgfiCW7sj4n8v8aQyP98Z8HE/s320/image005-797089.png" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653384755868258" /></a><o:p></o:p></p> <p class="MsoNormal" style="text-align:justify">Step4 : Go to Tools Menu --> Privileges<o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvp54NV-W0f3aYNL8AaH39Q4X8pTmsv6T059yT40CLba5KvZS5USVGccDeXFMIocj6Qa1lMBLlBaakNfew-RC9fYvhpZ_81QnuOfrI2XrwpN-eDTqdxbNXSboNl9henoB_C3cYT2VXT2A/s1600/image006-799542.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvp54NV-W0f3aYNL8AaH39Q4X8pTmsv6T059yT40CLba5KvZS5USVGccDeXFMIocj6Qa1lMBLlBaakNfew-RC9fYvhpZ_81QnuOfrI2XrwpN-eDTqdxbNXSboNl9henoB_C3cYT2VXT2A/s320/image006-799542.png" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653389635506066" /></a><o:p></o:p></p> <p class="MsoNormal" style="text-align:justify">Step5: Click on Select button on Privileges tab and search for the username "SYSADMIN" and click on "<b>Go"</b><o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgp6HuNhsAxz9LgdkbkODIbWz7qw3KxUHS7-pWNBHtRe-6sqQH0m7Fr__w8Uj-I4cE3znverofWk713QbennJvpsYOkLo6Vgc3xXD3ELT_wMrIhhV5yjwfOk0mifU7aaCX1lf6HqC_eeHc/s1600/image007-702176.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgp6HuNhsAxz9LgdkbkODIbWz7qw3KxUHS7-pWNBHtRe-6sqQH0m7Fr__w8Uj-I4cE3znverofWk713QbennJvpsYOkLo6Vgc3xXD3ELT_wMrIhhV5yjwfOk0mifU7aaCX1lf6HqC_eeHc/s320/image007-702176.png" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653406378220914" /></a><o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRKJDOtfAJ19iM9HswmuBDCIAJWWfKL6OJ2VWRdWCzrhqxMIPTEvUVwu7YEnshXkVt9IqLAeHyoQJX8FA_fZwXkfLRB6hFWij0V5w9SguCFhtnunWFRuXsHsQ_36kOh4MzotEOqg6iEdE/s1600/image008-704721.jpg"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRKJDOtfAJ19iM9HswmuBDCIAJWWfKL6OJ2VWRdWCzrhqxMIPTEvUVwu7YEnshXkVt9IqLAeHyoQJX8FA_fZwXkfLRB6hFWij0V5w9SguCFhtnunWFRuXsHsQ_36kOh4MzotEOqg6iEdE/s320/image008-704721.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653414208041026" /></a><o:p></o:p></p> <p class="MsoNormal" style="text-align:justify">Step6: "Schedule Workbooks" Option on "Desktop and Plus Privilege" should be checked. if this was not checked, please check the check box and click on Apply.<o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZi78M7cTZ1lZHMrUsKaUNzq110hRpBampm9F_7GC7eJ5k-hyEOVlivmdQ4f3HpJphTVYZkSCR1BaALccTta_i7oKg93I-Ddmzm3hVCTErKZEpJRsPbrpVLC1YDUFjFytnPjszNK4tJzE/s1600/image001-787241.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZi78M7cTZ1lZHMrUsKaUNzq110hRpBampm9F_7GC7eJ5k-hyEOVlivmdQ4f3HpJphTVYZkSCR1BaALccTta_i7oKg93I-Ddmzm3hVCTErKZEpJRsPbrpVLC1YDUFjFytnPjszNK4tJzE/s320/image001-787241.png" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653340056872226" /></a><o:p></o:p></p> <p class="MsoNormal" style="text-align:justify">Step7: Now navigate to tab "Scheduled workbooks" on same window.<o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhVZU5z23yGbO7NCBZVd1B_TXKAoRf4fwYLpD04XbzJYgoFOUAwzwO9Z_g2h4_Uv9zgpfH9mcnE1wU-UzEspDK259qub7xO6IdTALbs62-IwQMaRMwDOFGjaJqeEDZTFm1HfNEsdi6OE8/s1600/image009-707502.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhVZU5z23yGbO7NCBZVd1B_TXKAoRf4fwYLpD04XbzJYgoFOUAwzwO9Z_g2h4_Uv9zgpfH9mcnE1wU-UzEspDK259qub7xO6IdTALbs62-IwQMaRMwDOFGjaJqeEDZTFm1HfNEsdi6OE8/s320/image009-707502.png" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653425811855826" /></a><o:p></o:p></p> <p class="MsoNormal" style="text-align:justify">Step8: <o:p></o:p></p> <p class="MsoListParagraphCxSpFirst" style="text-align:justify;text-indent:-.25in;mso-list:l1 level1 lfo1"> <![if !supportLists]><span style="mso-list:Ignore">1.<span style="font:7.0pt "Times New Roman""> </span></span><![endif]>Select the 3rd Radio button "Schedule Only if predicted time Exceeds (HH:MM:SS)"<o:p></o:p></p> <p class="MsoListParagraphCxSpMiddle" style="text-align:justify;text-indent:-.25in;mso-list:l1 level1 lfo1"> <![if !supportLists]><span style="mso-list:Ignore">2.<span style="font:7.0pt "Times New Roman""> </span></span><![endif]>Set any time (ex: 5 mins i.e, 00:05:00) <o:p></o:p></p> <p class="MsoListParagraphCxSpMiddle" style="text-align:justify;text-indent:-.25in;mso-list:l1 level1 lfo1"> <![if !supportLists]><span style="mso-list:Ignore">3.<span style="font:7.0pt "Times New Roman""> </span></span><![endif]>uncheck the "Limit number of scheduled workbooks" and time <o:p></o:p></p> <p class="MsoListParagraphCxSpLast" style="text-align:justify;text-indent:-.25in;mso-list:l1 level1 lfo1"> <![if !supportLists]><span style="mso-list:Ignore">4.<span style="font:7.0pt "Times New Roman""> </span></span><![endif]>the other options can be choose as per requirement.<o:p></o:p></p> <p class="MsoNormal" style="text-align:justify">Click Apply and OK.<o:p></o:p></p> <span style="font-size:11.0pt;line-height:115%;font-family:"Calibri","sans-serif""><br clear="all" style="page-break-before:always"> </span> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal" style="text-align:justify"><b><u>Schedule the Discoverer Reports : <o:p></o:p></u></b></p> <p class="MsoNormal" style="text-align:justify"><b>Step1: </b>Login to discoverer plus as syadmin ( make sure to chose the connect to as "Oracle Applications") and select the responsibility "System Administrator"<o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><b><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7UfFJOQT31BYS3OecMYfdksOsu_Zev186TJFStJInqDfrJI2so9OuZhs0E5OtrVPU_R12zaVgooiDfpgOtgk4JyWsfHnwgHsDzBGGO5sgcD1yqcWimxVFrneYoNL4h2_3E2I-PYBSII4/s1600/image010-710684.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7UfFJOQT31BYS3OecMYfdksOsu_Zev186TJFStJInqDfrJI2so9OuZhs0E5OtrVPU_R12zaVgooiDfpgOtgk4JyWsfHnwgHsDzBGGO5sgcD1yqcWimxVFrneYoNL4h2_3E2I-PYBSII4/s320/image010-710684.png" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653438833136466" /></a><o:p></o:p></b></p> <p class="MsoNormal" style="text-align:justify"><b><o:p> </o:p></b></p> <p class="MsoNormal" style="text-align:justify"><b><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOyIVJS7CFQvHgbKqwEFCQI1pufKuw74tQtJ-n8l6SnNNfeC4bhGH4WlRLdcMCYBgTtghTy2Q6L6XbqA8gu7N66e0K4jMvjbde8hgHxMRmGPNiDTNJ6UmXbgC0Y3NGlmpYJ_QYbd3HOxY/s1600/image011-713230.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOyIVJS7CFQvHgbKqwEFCQI1pufKuw74tQtJ-n8l6SnNNfeC4bhGH4WlRLdcMCYBgTtghTy2Q6L6XbqA8gu7N66e0K4jMvjbde8hgHxMRmGPNiDTNJ6UmXbgC0Y3NGlmpYJ_QYbd3HOxY/s320/image011-713230.png" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653453403629314" /></a><o:p></o:p></b></p> <p class="MsoNormal" style="text-align:justify"><b>Step2 : </b> Navigate to Tools menu --> Manage Schedules <o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><b><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgHoqx2q9ay_Xryh5tlHh-bYJq-Lvtar9F6KIsX9hg5FY4lVK1iyA0x5OQdoL2awS9F8UooYSnEm2ybI-f5pwK5f7IeQ2QY_Q8Z2rVk_bkDy_iBqXE-JpvJxFtCzsoMQ1_1kLuZo6-yXp4/s1600/image012-715493.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgHoqx2q9ay_Xryh5tlHh-bYJq-Lvtar9F6KIsX9hg5FY4lVK1iyA0x5OQdoL2awS9F8UooYSnEm2ybI-f5pwK5f7IeQ2QY_Q8Z2rVk_bkDy_iBqXE-JpvJxFtCzsoMQ1_1kLuZo6-yXp4/s320/image012-715493.png" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653461039321954" /></a><o:p></o:p></b></p> <p class="MsoNormal" style="text-align:justify"><b><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsQVE8ZfS8diZ6wwmCgYPiolBo-92rGLbW4st_3Gg9ohzslOl4y5XUOLwqq6qd14HlodZeSJIRoCeHuYMAVtWxsIVFO9uNsP1tUKM3odHgINb77HLTp5DzsiLwYLYNxcdTG_cCdytffns/s1600/image013-718190.jpg"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsQVE8ZfS8diZ6wwmCgYPiolBo-92rGLbW4st_3Gg9ohzslOl4y5XUOLwqq6qd14HlodZeSJIRoCeHuYMAVtWxsIVFO9uNsP1tUKM3odHgINb77HLTp5DzsiLwYLYNxcdTG_cCdytffns/s320/image013-718190.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653473143827314" /></a><o:p></o:p></b></p> <p class="MsoNormal" style="text-align:justify"><b>Step3: </b>Click on Schedule and select the workbook. (here I am selecting the workbook ' IDCO UK 7 Buckets Aging Report ')<o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><o:p> </o:p></p> <p class="MsoNormal" style="text-align:justify"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjg1USrrQOofEdYCOJNn7ap5C1ni6C4vXK0DDMOk_PH4QoHz3Z58AlXgcfDfwllxtRv2p1lQ7zCBeXqUdF1RyOFn19Ps2P1nhZjZQYpHw152RBykTTJyRGcaJ1PjGBTKYxYZ1XyP8-15E0/s1600/image014-720197.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjg1USrrQOofEdYCOJNn7ap5C1ni6C4vXK0DDMOk_PH4QoHz3Z58AlXgcfDfwllxtRv2p1lQ7zCBeXqUdF1RyOFn19Ps2P1nhZjZQYpHw152RBykTTJyRGcaJ1PjGBTKYxYZ1XyP8-15E0/s320/image014-720197.png" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653480084326866" /></a><o:p></o:p></p> <p class="MsoNormal" style="text-align:justify">Step4: <o:p></o:p></p> <p class="MsoListParagraphCxSpFirst" style="text-align:justify;text-indent:-.25in;mso-list:l0 level1 lfo2"> <![if !supportLists]><span style="mso-list:Ignore">1.<span style="font:7.0pt "Times New Roman""> </span></span><![endif]>Enter the name for scheduled work book, (this should be unique, if you have entered any name that was being used by any other user, it will ask you to rename.)<o:p></o:p></p> <p class="MsoListParagraphCxSpLast" style="text-align:justify;text-indent:-.25in;mso-list:l0 level1 lfo2"> <![if !supportLists]><span style="mso-list:Ignore">2.<span style="font:7.0pt "Times New Roman""> </span></span><![endif]>Select the work sheets to be run on the work book and click on Next<o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg4U2ULeBORcOasik9UkddIi3tblur9qXagap7infSbD_fTxArsGJSMytnB0XvgXvevx5eIvqVVkRB_A_4JNafe1yclDAU9KPSc4mgFDIogp9W444MWMGG5kuLxJCNaRoCVH2e0rrU5Fac/s1600/image015-722725.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg4U2ULeBORcOasik9UkddIi3tblur9qXagap7infSbD_fTxArsGJSMytnB0XvgXvevx5eIvqVVkRB_A_4JNafe1yclDAU9KPSc4mgFDIogp9W444MWMGG5kuLxJCNaRoCVH2e0rrU5Fac/s320/image015-722725.png" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653489841113762" /></a><o:p></o:p></p> <p class="MsoNormal" style="text-align:justify">Step5: Enter the Parameters and click on Next.<o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJY4ZiBxdqJD6rLgQx18sArIjYgMuUQGW8K25kvLmob69QwGhrsS5Bp90zjtx5bZxaz504MlNAlJa0NrRuDlCm7o7QXhauHwEe_LyjhNpeWlkJF0rMpaItxnnWdtfn5DrExo9I1uV9r3Q/s1600/image016-725486.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJY4ZiBxdqJD6rLgQx18sArIjYgMuUQGW8K25kvLmob69QwGhrsS5Bp90zjtx5bZxaz504MlNAlJa0NrRuDlCm7o7QXhauHwEe_LyjhNpeWlkJF0rMpaItxnnWdtfn5DrExo9I1uV9r3Q/s320/image016-725486.png" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653503537115698" /></a> <o:p></o:p></p> <p class="MsoNormal" style="text-align:justify">Step 6: Schedule the report as per your requirement and Finish. (for demo, I am choosing to run the report immediately and repeat for every minute and selecting the option 'yes, Keep all results' and delete results after 7 days)<o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEheDB1t3QFmgPq59Ayze8vPqChHYMUyR_-AEN-2zneXdl1qUvGTZ1fYryoYF0wm2we5Sbt6m8Rm0JI1kpFotpxqvPgDB1PF_6sDoEKauh3Fuc6gkBduZGlup6FJ1lR3xtISVT1IqhoJBTw/s1600/image017-728036.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEheDB1t3QFmgPq59Ayze8vPqChHYMUyR_-AEN-2zneXdl1qUvGTZ1fYryoYF0wm2we5Sbt6m8Rm0JI1kpFotpxqvPgDB1PF_6sDoEKauh3Fuc6gkBduZGlup6FJ1lR3xtISVT1IqhoJBTw/s320/image017-728036.png" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653515304681458" /></a><o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgT-CAPLRiX7ERq2N8GiFscapaUNoT_DNmBadlZ0qU-Q9-tIaPXfaKwQ9ST0CSA9_eWfdwVzAGDwb1hy3Rp-wYjdYcK9zu-1bNo78-sqmDSpfyOoGd6QY3Yn7F7zhXSzse82lG0poMxZ-A/s1600/image018-731024.jpg"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgT-CAPLRiX7ERq2N8GiFscapaUNoT_DNmBadlZ0qU-Q9-tIaPXfaKwQ9ST0CSA9_eWfdwVzAGDwb1hy3Rp-wYjdYcK9zu-1bNo78-sqmDSpfyOoGd6QY3Yn7F7zhXSzse82lG0poMxZ-A/s320/image018-731024.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653528895145314" /></a><o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"> <o:p></o:p></p> <p class="MsoNormal" style="text-align:justify">Step7: Click on share and choose all the users to access this scheduled workbook and click <b>ok.<o:p></o:p></b></p> <p class="MsoNormal" style="text-align:justify"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjlHxsePzL-C5ZIC0SBsqs-IKYwv3XaoZDdPWkefPOVK5wYE7607vkuowfqhVOZ1U5SzlroFOXNceTh0CvERBFHJJbrG0BN1Va-abuelkhnAl0JTAKzcZbaaJFuJ6g-cq2UPrdDTLrwX8c/s1600/image019-733540.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjlHxsePzL-C5ZIC0SBsqs-IKYwv3XaoZDdPWkefPOVK5wYE7607vkuowfqhVOZ1U5SzlroFOXNceTh0CvERBFHJJbrG0BN1Va-abuelkhnAl0JTAKzcZbaaJFuJ6g-cq2UPrdDTLrwX8c/s320/image019-733540.png" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653539237164098" /></a><o:p></o:p></p> <p class="MsoNormal" style="text-align:justify">The scheduled reports can be unscheduled or deleted from the same window.<o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj5-Cw-yp-UPjr0gf9tOKBDBGoYdDGnpBTJ7GEGqTF6RLVT2iBeZWTCEv-xs6xJbWBLTHPHMGqXn_Qb0wIhwg2TmoFQaKY3NGdphQsID-JTihhSC-hQC-Eeggh2sNQjmJyIhLPioxwxwxk/s1600/image020-735687.jpg"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj5-Cw-yp-UPjr0gf9tOKBDBGoYdDGnpBTJ7GEGqTF6RLVT2iBeZWTCEv-xs6xJbWBLTHPHMGqXn_Qb0wIhwg2TmoFQaKY3NGdphQsID-JTihhSC-hQC-Eeggh2sNQjmJyIhLPioxwxwxk/s320/image020-735687.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653545026703570" /></a><o:p></o:p></p> <span style="font-size:11.0pt;line-height:115%;font-family:"Calibri","sans-serif""><br clear="all" style="page-break-before:always"> </span> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal" style="text-align:justify"><o:p> </o:p></p> <p class="MsoNormal" style="text-align:justify"><b><u>View the results from Application :<o:p></o:p></u></b></p> <p class="MsoNormal" style="text-align:justify">Step1: Login to application as end user and open any discoverer report as they do daily.<o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMyYTayEfkt2OPtZuCKIyN0QMj-1pkbOtJsoDEBift2MZTKk_5SYmbAXmujW5Kbjm8KJomgiYezNxQ6sdZvX-3KAJS1BGOJwTuP6gP-xC3nZaVf8DLE17ebxGJLBmcbcqUDjBTzum0rfw/s1600/image021-738356.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMyYTayEfkt2OPtZuCKIyN0QMj-1pkbOtJsoDEBift2MZTKk_5SYmbAXmujW5Kbjm8KJomgiYezNxQ6sdZvX-3KAJS1BGOJwTuP6gP-xC3nZaVf8DLE17ebxGJLBmcbcqUDjBTzum0rfw/s320/image021-738356.png" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653557347028338" /></a><o:p></o:p></p> <p class="MsoNormal" style="text-align:justify">Step2: Instead of running the report select the link "workbook" on the top and choose scheduled workbooks from the lov and click on <b>GO</b> .<o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj70IO-pmTfS9Qblm96sb3Qe8LNXVXDEO5YyGH1ZRtA6McvFtA0RBEGYy0j9FUi-gfuRxQ2DzOzvj1q1IlNeiupD4DsZaRelWbDtyj2GfsY1nC27HOcVhyphenhyphenpGXFtdkp0Q1N_yLZCXBD8xxk/s1600/image022-741671.jpg"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj70IO-pmTfS9Qblm96sb3Qe8LNXVXDEO5YyGH1ZRtA6McvFtA0RBEGYy0j9FUi-gfuRxQ2DzOzvj1q1IlNeiupD4DsZaRelWbDtyj2GfsY1nC27HOcVhyphenhyphenpGXFtdkp0Q1N_yLZCXBD8xxk/s320/image022-741671.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653574146483138" /></a><o:p></o:p></p> <p class="MsoNormal" style="text-align:justify"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhAbkjQQ2dhnW3L4SEMF6WrMn6xYqPLvdK3CCors-YSnrgJvJHxnhRJ11Lz_rIzGU3PakCDvOsZIOX3-p3CdVBFGYQf-_ExFAo3cfPX7fsetDSn7kApfFWaY-v5cB2MPOiYgjC_BjVFUo/s1600/image023-743718.jpg"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhAbkjQQ2dhnW3L4SEMF6WrMn6xYqPLvdK3CCors-YSnrgJvJHxnhRJ11Lz_rIzGU3PakCDvOsZIOX3-p3CdVBFGYQf-_ExFAo3cfPX7fsetDSn7kApfFWaY-v5cB2MPOiYgjC_BjVFUo/s320/image023-743718.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_6059653580284530354" /></a><o:p></o:p></p> <p class="MsoNormal" style="text-align:justify">Here user can see all the scheduled reports that are shared to him.<o:p></o:p></p> <p class="MsoNormal" style="text-align:justify">in this example I have scheduled only one report to run for every minute , we can schedule n number of reports and also one report with multiple parameters.<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p> </div> Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-31238527292869764942014-08-22T13:16:00.003+05:302014-08-22T13:16:33.699+05:30Oracle EBS API to change user password and prompt to change their password the first attempt to login to the applications<div dir="ltr" style="text-align: left;" trbidi="on">
<br />The following API can be used to change the Ebs user password and prompt to change the same at the first logon,<br /><br /> begin<br /> fnd_user_pkg.updateuser(<br /> x_user_name => 'username'<br /> , x_owner => 'CUST'<br /> , x_unencrypted_password => 'change123',<br /> x_password_date => to_date('2','J')<br /> );<br /> commit;<br /> end;<br /><br />If we don't want to force the user to change the password, then we can remove the x_password_date from the api.<br /><br />The standard FND_USER_PKG does this:<br /><br /> decode(x_password_date, fnd_user_pkg.null_date, null,null,u.password_date, x_password_date)<br /><br /> fnd_user_pkg.null_date is defined as to_date('2','J'), which is 02-JAN-4712.<br /><br />Hence, this decode says:<br /><br />1. If the password_date parm = 02-JAN-4712, then set password date = null.<br />2. If the password_date is null, then set password date = existing password date (in effect does nothing)<br />3. Else use the existing password date (in effect does nothing).<br /><br /><span id="fullpost">
</span></div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-74325116120122539452014-08-07T16:41:00.001+05:302014-08-07T16:41:38.688+05:30Value Set<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
Navigation Path for Value Set Creation?<br /><br />Navigation Path for Value Set Creation:<br />Application-> Validation -> Set.<br />Once we create Independent & Dependent valueset then we can attach values to the valueset by using the following Navigation.<br />Application -> -> Validation<br />-> Values (To create values for value set)<br />NOTE: Once we attach any value to Independent & Dependent we can’t delete that value, but we can disable that value. Duplicate values are not allowed in list of values.<br /><br />Develop a Report using Query and by creating valueset :Select USER_ID, USERNAME From FND_USER Where<br />USER_ID Between :X AND :Y<br />:$FLEX$ - It is One of the Oracle applications Key word which we use to get the prevents parameter value in current list of values “WHERE Clause”. We can be use Table Values in the “Where Clause Box”.<br />Query Using: $FLEX$<br /><br />Select<br /> VENDOR_SITE_ID<br />From<br /> PO_VENDOR_SITE_ALL<br />Where<br /> VENODR_ID = :$FLEX$.VEN_TABLE<br /><br /><br />Note: We can give Where Clause Condition in creation of Second Value Set.<br />Practical:Query:<br />Select *<br />From ORF_ORGANIZATION_DEFINATIONS<br />Where ORGANIZATION_ID = :P_ORG_ID<br />And BUSINESS_GROUP_ID = :P_BUSINESS_GROUP_ID<br /><br />In Where Clause write the statement as<br />Where<br />BUSINESS_GROUP_ID = :$FLEX$.BUISINESS_GROUP<br /><br /><b>Range:</b><br />When ever we have to restrict the user with in the given values we use Range. For example when ever our parameter is having “From Date and To Date” we have to use Range option to restrict the user to enter the values between Low and High.<br />Note:<br />Pre defined value set for date is “FND_DATE” and its default format is “DD-MON-YY”.Alias name is mandatory when we are specifying ‘:$FLEX$’ and Column Name in ‘Additional Column’.<br /><br /><b>What is Value Sets? What are Types Of Value Sets?</b><br /><br />Oracle Application Object Library uses values; value sets and validation tables as important components of key FLEXFIELDs, descriptive FLEXFIELDs, and Standard Request Submission. This section helps you understand, use and change values, value sets, and validation tables. When you first define your FLEXFIELDs, you choose how many segments you want to use and what order you want them to appear. You also choose how you want to validate each of your segments. The decisions you make affect how you define your value sets and your values. You define your value sets first, either before or while you define your FLEXFIELD segment structures. You typically define your individual values only after your FLEXFIELD has been completely defined (and frozen and compiled). Depending on what type of value set you use, you may not need to predefine individual values at all before you can use your FLEXFIELD.<br />You can share value sets among segments in different FLEXFIELDs, segments in different structures of the same FLEXFIELD, and even segments within the same FLEXFIELD structure. You can share value sets across key and descriptive FLEXFIELDs. You can also use value sets for report parameters for your reports that use the Standard Request Submission feature.<br />Because the conditions you specify for your value sets determine what values you can use with them, you should plan both your values and your value sets at the same time. For example, if your values are 01, 02 instead of 1, 2, you would define the value set with Right–Justify Zero–fill set to Yes.<br /><br />Value set is nothing but List of Values with validations. We can use the Value Sets when ever the Concurrent Program has parameters and while defining the Flex Fields. We have to attach the value sets to the Concurrent Program. Validations are depending on Client Requirement.<br /><br />Value sets are of 8 types.There are several validation types that affect the way users enter and use segment or parameter values:<br />1. None (not validated at all)<br />2. Independent<br />3. Dependent<br />4. Table<br />5. Special (advanced)<br />6. Pair (advanced)<br />7. Translatable Independent<br />8. Translatable Dependent<br /><br />You cannot change the validation type of an existing value set, since your changes affect all FLEXFIELDs and report parameters that use the same value set.<br /><br /><b>None:</b><br />
You use a None type value set when you want to allow users to enter any value so long as that value meets the value set formatting rules. That is, the value must not exceed the maximum length you define for your value set, and it must meet any format requirements for that value set. For example, if the value set does not allow alphabetic characters, your user could not enter the value ABC, but could enter the value 456 (for a value set with maximum length of three). The values of the segment using this value set are not otherwise validated, and they do not have descriptions. Because a NONE value set is not validated, a segment that uses this value set does not provide a list of values for your users. A segment that uses this value set (that is, a non–validated segment) cannot use FLEXFIELD value security rules to restrict the values a user can enter.<br /><br /><b>Independent :</b><br />
An Independent value set provides a predefined list of values for a segment. These values can have an associated description. For example, the value 01 could have a description of ‘Company 01’. The meaning of a value in this value set does not depend on the value of any other segment. Independent values are stored in an Oracle Application Object Library table. You define independent values using an Oracle Applications window, Segment Values.<br /><br /><b>Table :</b><br />
<b> </b> A table–validated value set provides a predefined list of values like an independent set, but its values are stored in an application table. You define which table you want to use, along with a WHERE cause to limit the values you want to use for your set. Typically, you use a table–validated set when you have a table whose values are already maintained in an application table (for example, a table of vendor names maintained by a Define Vendors form). Table validation also provides some advanced features such as allowing a segment to depend upon multiple prior segments in the same structure.<br /><br /><b>Dependent :</b><br />
A dependent value set is similar to an independent value set, except that the available values in the list and the meaning of a given value depend on which independent value was selected in a prior segment of the FLEXFIELD structure. You can think of a dependent value set as a collection of little value sets, with one little set for each independent value in the corresponding independent value set. You must define your independent value set before you define the dependent value set that depends on it. You define dependent values in the Segment Values windows, and your values are stored in an Oracle Application Object Library table.<br /><br /><b>Special and Pair Value Sets:</b><br />Special and pair value sets provide a mechanism to allow a”FLEXFIELD–within–a–FLEXFIELD”. These value sets are primarily used for Standard Request Submission parameters. You do not generally use these value sets for normal FLEXFIELD segments. Special and Pair value sets use special validation routines you define. For example, you can define validation routines to provide another FLEXFIELD as a value set for a single segment or to provide a range FLEXFIELD as a value set for a pair of segments.<br /><br /><b>Translatable Independent and Translatable Dependent :</b>A Translatable Independent value set is similar to Independent value set in that it provides a predefined list of values for a segment. However, a translated value can be used. A Translatable Dependent value set is similar to Dependent value set in that the available values in the list and the meaning of a given value depend on which independent value was selected in a prior segment of the FLEXFIELD structure. However, a translated value can be used. FLEXFIELD Value Security cannot be used with Translatable Independent or Translatable Dependent value sets. For format validation, translatable value sets must use the format type Char. The maximum size must be no greater than 150. The Number Only option and the Right–justify and Zero–Fill Numbers option cannot be used with translatable value sets. Range FLEXFIELDs cannot use Translatable Independent or Translatable Dependent value sets.<br /><br />
<span id="fullpost">
</span></div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-65417636890700445062014-08-07T16:22:00.001+05:302014-08-07T16:32:06.878+05:30XML Parsing -reading xml file in oracle pl sql example<div dir="ltr" style="text-align: left;" trbidi="on">
Please find the below stand alone script followed by sample XML file that I am using for this script.<br />
<br />
Steps to be performed.<br />
<br />
<ol style="text-align: left;">
<li>create a XML file with name OMX_Cxml.xml (by copying the XML file data provided at the end of this post).</li>
<li>Place the xml file in any server path ( in this example I have placed the file in USER_TEMP directory) directory namd and path can be seen in all_directories table</li>
<li>Execute the below script and understand how am i reading each node and attribute value also How am i handling the loops for repeating nodes .</li>
<li>Modify the script as per your xml file.</li>
</ol>
<br />
<br />
declare<br />
-- below variables are used in XML parsing or for api dbms_xmlparser<br />
L_BFILE bfile; -- this holds the binary file (from respective path)<br />
L_CLOB CLOB; -- this is to read the data from bfile into clob type<br />
l_parser dbms_xmlparser.parser; -- this variable for parsing clob value to XMLparser<br />
MY_DOC DBMS_XMLDOM.DOMDOCUMENT; -- this vairable shall hold XML parsed data<br />
CURRENT_ITEM_LIST DBMS_XMLDOM.DOMNODELIST; -- this represents the XML group of nodes (1 or repeating) and all it sub tags<br />
CURRENT_ITEM DBMS_XMLDOM.DOMNODE; -- this represents the XML group of nodes (only one instance of repeating group) and all it sub tags<br />
CURRENT_ITEM_LIST1 DBMS_XMLDOM.DOMNODELIST; -- this represents the XML group of nodes (1 or repeating) and all it sub tags<br />
CURRENT_ITEM1 DBMS_XMLDOM.DOMNODE; -- this represents the XML group of nodes (only one instance of repeating group) and all it sub tags<br />
XMLELEM XMLDOM.DOMELEMENT; --This will read the root element information. In our file rooot element is :<cXML ..><br />
NSPACE VARCHAR2(50); --This will read name sapce of root element information. In our file rooot element is :<cXML ..> and no namespaces are defined in file<br />
--- below variables are to be passed as parameters for DBMS_LOB.LOADCLOBFROMFILE<br />
L_DEST_OFFSET NUMBER :=1;<br />
L_SRC_OFFSET NUMBER :=1;<br />
L_BFILE_CSID NUMBER := 0;<br />
L_LANG_CONTEXT NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;<br />
L_WARNING NUMBER;<br />
-- Declare local variables the correspond to the XML data<br />
-- in more serious implementation , consider using RECORD type<br />
---Header information<br />
from_credential VARCHAR2(1000);<br />
from_identity VARCHAR2(1000);<br />
from_sharedsecret VARCHAR2(1000);<br />
to_credential VARCHAR2(1000);<br />
to_identity VARCHAR2(1000);<br />
to_sharedsecret VARCHAR2(1000);<br />
sender_credential VARCHAR2(1000);<br />
sender_identity VARCHAR2(1000);<br />
sender_sharedsecret VARCHAR2(1000);<br />
SENDER_USERAGENT VARCHAR2(1000);<br />
------------------<br />
---Request information<br />
L_DEPLOYMENTMODE VARCHAR2(1000);<br />
L_INVOICEID VARCHAR2(1000);<br />
L_PURPOSE VARCHAR2(1000);<br />
L_OPERATION VARCHAR2(1000);<br />
L_INVOICEDATE VARCHAR2(1000);<br />
L_ISACCOUNTINGINLINE VARCHAR2(1000);<br />
L_PAYINNUMBEROFDAYS NUMBER;<br />
L_PERCENTAGERATE NUMBER;<br />
L_EXTRINSIC_NAME VARCHAR2(1000);<br />
L_EXTRINSIC_value VARCHAR2(1000);<br />
--invoice partner information<br />
L_CONTACT_ROLE VARCHAR2(1000);<br />
L_CONTACT_NAME VARCHAR2(1000);<br />
L_CONTACT_STREET1 VARCHAR2(1000);<br />
L_CONTACT_STREET2 VARCHAR2(1000);<br />
L_CONTACT_CITY VARCHAR2(1000);<br />
L_CONTACT_STATE VARCHAR2(1000);<br />
L_CONTACT_POSTALCODE VARCHAR2(1000);<br />
L_CONTACT_COUNTRY VARCHAR2(1000);<br />
L_CONTACT_FROM_NAME VARCHAR2(1000);<br />
L_CONTACT_FROM_STREET1 VARCHAR2(1000);<br />
L_CONTACT_FROM_STREET2 VARCHAR2(1000);<br />
L_CONTACT_FROM_CITY VARCHAR2(1000);<br />
L_CONTACT_FROM_STATE VARCHAR2(1000);<br />
L_CONTACT_FROM_POSTALCODE VARCHAR2(1000);<br />
L_CONTACT_FROM_COUNTRY VARCHAR2(1000);<br />
L_CONTACT_SOLDTO_NAME VARCHAR2(1000);<br />
L_CONTACT_SOLDTO_STREET1 VARCHAR2(1000);<br />
L_CONTACT_SOLDTO_STREET2 VARCHAR2(1000);<br />
L_CONTACT_SOLDTO_CITY VARCHAR2(1000);<br />
L_CONTACT_SOLDTO_STATE VARCHAR2(1000);<br />
L_CONTACT_SOLDTO_POSTALCODE VARCHAR2(1000);<br />
L_CONTACT_SOLDTO_COUNTRY VARCHAR2(1000);<br />
L_CONTACT_BILLTO_NAME VARCHAR2(1000);<br />
L_CONTACT_BILLTO_STREET1 VARCHAR2(1000);<br />
L_CONTACT_BILLTO_STREET2 VARCHAR2(1000);<br />
L_CONTACT_BILLTO_CITY VARCHAR2(1000);<br />
L_CONTACT_BILLTO_STATE VARCHAR2(1000);<br />
L_CONTACT_BILLTO_POSTALCODE VARCHAR2(1000);<br />
L_CONTACT_BILLTO_COUNTRY VARCHAR2(1000);<br />
L_CONTACT_RMTTO_NAME VARCHAR2(1000);<br />
L_CONTACT_RMTTO_STREET1 VARCHAR2(1000);<br />
L_CONTACT_RMTTO_STREET2 VARCHAR2(1000);<br />
L_CONTACT_RMTTO_CITY VARCHAR2(1000);<br />
L_CONTACT_RMTTO_STATE VARCHAR2(1000);<br />
L_CONTACT_RMTTO_POSTALCODE VARCHAR2(1000);<br />
L_CONTACT_RMTTO_COUNTRY VARCHAR2(1000);<br />
L_CONTACT_SHIPROLE VARCHAR2(1000);<br />
L_CONTACT_SHIPFROM_NAME VARCHAR2(1000);<br />
L_CONTACT_SHIPFROM_STREET1 VARCHAR2(1000);<br />
L_CONTACT_SHIPFROM_STREET2 VARCHAR2(1000);<br />
L_CONTACT_SHIPFROM_CITY VARCHAR2(1000);<br />
L_CONTACT_SHIPFROM_STATE VARCHAR2(1000);<br />
L_CONTACT_SHIPFROM_POSTALCODE VARCHAR2(1000);<br />
L_CONTACT_SHIPFROM_COUNTRY VARCHAR2(1000);<br />
L_CONTACT_SHIPTO_NAME VARCHAR2(1000);<br />
L_CONTACT_SHIPTO_STREET1 VARCHAR2(1000);<br />
L_CONTACT_SHIPTO_STREET2 VARCHAR2(1000);<br />
L_CONTACT_SHIPTO_CITY VARCHAR2(1000);<br />
L_CONTACT_SHIPTO_STATE VARCHAR2(1000);<br />
L_CONTACT_SHIPTO_POSTALCODE VARCHAR2(1000);<br />
L_CONTACT_SHIPTO_COUNTRY VARCHAR2(1000);<br />
L_ORDER_ID NUMBER;<br />
L_PAYLOADID VARCHAR2(1000);<br />
L_INVOICELINENUMBER NUMBER;<br />
L_QUANTITY NUMBER;<br />
L_UNITOFMEASURE VARCHAR2(100);<br />
L_CURRENCY VARCHAR2(100);<br />
L_UNITPRICE VARCHAR2(100);<br />
L_INV_DET_ITEM_REF_LINENUM NUMBER;<br />
L_SUPPLIERPARTID VARCHAR2(100);<br />
L_DESCRIPTION VARCHAR2(1000);<br />
L_MANUFACTURERPARTID VARCHAR2(1000);<br />
L_MANUFACTURERNAME VARCHAR2(1000);<br />
L_SUBTOTALAMOUNT_CUR VARCHAR2(1000);<br />
L_SUBTOTALAMOUNT NUMBER;<br />
L_GROSSAMOUNT_CUR VARCHAR2(1000);<br />
L_GROSSAMOUNT NUMBER;<br />
L_NETAMOUNT_CUR VARCHAR2(1000);<br />
L_NETAMOUNT NUMBER;<br />
L_SRY_SUBTOTAL_CUR VARCHAR2(1000);<br />
L_SRY_SUBTOTAL_AMOUNT NUMBER;<br />
L_SRY_TAX_CUR VARCHAR2(1000);<br />
L_SRY_TAX_AMOUNT NUMBER;<br />
L_SRY_TAX_PURPOSE VARCHAR2(1000);<br />
L_SRY_TAX_CATEGORY VARCHAR2(1000);<br />
L_SRY_TAX_RATE VARCHAR2(1000);<br />
L_SRY_TAXABLE_CUR VARCHAR2(1000);<br />
L_SRY_TAXABLE_AMOUNT NUMBER;<br />
L_SRY_TAX_CUR1 VARCHAR2(1000);<br />
L_SRY_TAX_AMOUNT1 NUMBER;<br />
L_SRY_GROSS_CUR VARCHAR2(1000);<br />
L_SRY_GROSS_AMOUNT NUMBER;<br />
L_SRY_NET_CUR VARCHAR2(1000);<br />
L_SRY_NET_AMOUNT NUMBER;<br />
L_SRY_DUE_CUR VARCHAR2(1000);<br />
L_SRY_DUE_AMOUNT NUMBER;<br />
BEGIN<br />
DBMS_OUTPUT.PUT_LINE('begin :');<br />
-- Point to the XML file called 'OMX_Cxml.xml'which is located in the directory_path pointed at USER_TEMP in table all_directories<br />
L_BFILE := BFILENAME('USER_TEMP', 'OMX_Cxml.xml');<br />
-- creating a temporary space for clob variable with out cache so that it will not have any performance impact<br />
DBMS_LOB.CREATETEMPORARY(L_CLOB, cache=>false);<br />
-- Open the XML file in read mode<br />
dbms_lob.open(l_bfile, dbms_lob.lob_readonly);<br />
-- tranfer the file contents to CLOB variable<br />
--DBMS_LOB.LOADFROMFILE(DEST_LOB => L_CLOB, SRC_LOB => L_BFILE, AMOUNT => DBMS_LOB.GETLENGTH(L_BFILE));<br />
dbms_lob.loadclobfromfile(dest_lob => l_clob, src_bfile => L_BFILE, AMOUNT => DBMS_LOB.GETLENGTH(L_BFILE), DEST_OFFSET => L_DEST_OFFSET, SRC_OFFSET => L_SRC_OFFSET, BFILE_CSID => DBMS_LOB.DEFAULT_CSID, LANG_CONTEXT => L_LANG_CONTEXT, warning => l_warning );<br />
--close the bfile instance<br />
dbms_lob.close(l_bfile);<br />
-- This ends file manipulation and the file contents are now in a CLOB vairable<br />
-- This entry is important to deal with DATE format in the XML data<br />
dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MON-YYYY''');<br />
-- Starting XML processing business<br />
-- Create a parser.<br />
L_PARSER := DBMS_XMLPARSER.NEWPARSER;<br />
-- Parse the document and create a new DOM document.<br />
-- temporarily I am prting the XML data for testing<br />
--DBMS_OUTPUT.PUT_LINE('parser : l_clob---'||L_CLOB);<br />
-- parse the xml data from clob variabl to XML_parser<br />
--DBMS_XMLPARSER.PARSECLOB(L_PARSER,L_CLOB);<br />
DBMS_XMLPARSER.PARSECLOB(L_PARSER, REGEXP_REPLACE(L_CLOB,'<!DOCTYPE cXML SYSTEM "http://xml.cxml.org/schemas/cXML/1.2.009/InvoiceDetail.dtd">',''));<br />
-- The xml parsed CLOB is transfered to a variable called my_doc<br />
my_doc := dbms_xmlparser.getdocument(l_parser);<br />
xmlelem := xmldom.getdocumentelement(my_doc);<br />
nspace := xmldom.getnamespace(xmlelem);<br />
DBMS_OUTPUT.PUT_LINE('XML Root element information');<br />
DBMS_OUTPUT.PUT_LINE('Qualified Name: ' || DBMS_XMLDOM.GETQUALIFIEDNAME(XMLELEM));<br />
DBMS_OUTPUT.PUT_LINE('Local Name: ' || DBMS_XMLDOM.GETLOCALNAME(XMLELEM));<br />
DBMS_OUTPUT.PUT_LINE('Namespace: ' || NSPACE);<br />
DBMS_OUTPUT.PUT_LINE('Expanded Name: ' || DBMS_XMLDOM.GETEXPANDEDNAME(XMLELEM));<br />
-- Free resources associated with the CLOB and Parser now they are no longer needed.<br />
dbms_lob.freetemporary(l_clob);<br />
DBMS_XMLPARSER.FREEPARSER(L_PARSER);<br />
dbms_output.put_line('parser free:');<br />
-- The following statement shall search for a node starting with 'cXML/Header'<br />
-- Then it shall get the no of entries in this node. In our example the 'cXML/Header' contains only one<br />
-- entry, but a big one, because it contains from to and sender XML (there is ony one <Header> </Header><br />
current_item_list := dbms_xslprocessor.selectnodes(dbms_xmldom.makenode(my_doc),'cXML/Header');<br />
-- note no slash at end<br />
DBMS_OUTPUT.PUT_LINE('############# header loop begin #################');<br />
dbms_output.put_line('Count of cXML/Header : '||dbms_xmldom.getLength(current_item_list)); -- find the no of entries in this node<br />
FOR CUR_ENT IN 0 .. DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)-1<br />
LOOP -- it should loop only once because the Header is only one instance<br />
<br />
current_item := dbms_xmldom.item(current_item_list, cur_ent); --READ the current item from current entry of <Header><br />
DBMS_OUTPUT.PUT_LINE('current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM));<br />
--dbms_output.put_line('current_item:'|| dbms_xmldom.getnodevalue(current_item)); --this will not work in our case<br />
--#############-------------Header/From----------------##############<br />
dbms_output.put_line('---------------From----------------');<br />
dbms_xslprocessor.valueof(current_item,'From/Credential/@domain',from_credential);<br />
dbms_output.put_line('From/Credential/@domain: '||from_credential);<br />
dbms_xslprocessor.valueof(current_item,'From/Credential/Identity/text()',from_identity); <br />
dbms_output.put_line('From/Credential/Identity:'||from_identity);<br />
dbms_xslprocessor.valueof(current_item,'From/Credential/SharedSecret/text()',from_sharedsecret);<br />
DBMS_OUTPUT.PUT_LINE('From/Credential/SharedSecret:'||FROM_SHAREDSECRET);<br />
--#############-------------Header/To----------------##############<br />
dbms_output.put_line('---------------To----------------');<br />
dbms_xslprocessor.valueof(current_item,'To/Credential/@domain',to_credential);<br />
dbms_output.put_line('From/Credential/@domain: '||to_credential);<br />
dbms_xslprocessor.valueof(current_item,'To/Credential/Identity/text()',to_identity); <br />
dbms_output.put_line('To/Credential/Identity:'||to_identity);<br />
dbms_xslprocessor.valueof(current_item,'To/Credential/SharedSecret/text()',to_sharedsecret);<br />
DBMS_OUTPUT.PUT_LINE('To/Credential/SharedSecret:'||TO_SHAREDSECRET);<br />
--#############-------------Header/Sender----------------##############<br />
dbms_output.put_line('---------------Sender----------------');<br />
dbms_xslprocessor.valueof(current_item,'Sender/Credential/@domain',Sender_credential);<br />
dbms_output.put_line('Sender/Credential/@domain: '||Sender_credential);<br />
dbms_xslprocessor.valueof(current_item,'Sender/Credential/Identity/text()',sender_identity); <br />
dbms_output.put_line('Sender/Credential/Identity:'||sender_identity);<br />
dbms_xslprocessor.valueof(current_item,'Sender/Credential/SharedSecret/text()',sender_sharedsecret);<br />
dbms_output.put_line('Sender/Credential/SharedSecret:'||sender_sharedsecret);<br />
dbms_xslprocessor.valueof(current_item,'Sender/UserAgent/text()',sender_UserAgent);<br />
DBMS_OUTPUT.PUT_LINE('Sender/UserAgent:'||SENDER_USERAGENT);<br />
END LOOP;<br />
--Now the following statement shall search for a node starting with 'cXML/Request'<br />
-- Then it shall get the no of entries in this node. In our example the 'cXML/Request' contains only one<br />
-- entry, but a big one, because it contains from to and sender XML (there is ony one <Request> </Request><br />
current_item_list := dbms_xslprocessor.selectnodes(dbms_xmldom.makenode(my_doc),'cXML/Request');<br />
-- note no slash at end<br />
DBMS_OUTPUT.PUT_LINE('############# Request loop begin #################');<br />
dbms_output.put_line('Count of cXML/Request : '||dbms_xmldom.getLength(current_item_list)); -- find the no of entries in this node<br />
FOR CUR_ENT IN 0 .. DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)-1<br />
LOOP -- it should loop only once because the Request is only one instance<br />
<br />
current_item := dbms_xmldom.item(current_item_list, cur_ent); --READ the current item from current entry of <Header><br />
DBMS_OUTPUT.PUT_LINE('current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM));<br />
--dbms_output.put_line('current_item:'|| dbms_xmldom.getnodevalue(current_item)); --this will not work in our case<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'@deploymentMode',L_DEPLOYMENTMODE);<br />
DBMS_OUTPUT.PUT_LINE('Request/@domain: '||l_deploymentMode);<br />
CURRENT_ITEM_LIST := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(MY_DOC),'cXML/Request/InvoiceDetailRequest');<br />
-- note no slash at end<br />
DBMS_OUTPUT.PUT_LINE('############# InvoiceDetailRequest loop begin #################');<br />
dbms_output.put_line('Count of cXML/Request/InvoiceDetailRequest: '||dbms_xmldom.getLength(current_item_list)); -- find the no of entries in this node<br />
FOR CUR_ENT IN 0 .. DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)-1<br />
LOOP<br />
<br />
CURRENT_ITEM := DBMS_XMLDOM.ITEM(CURRENT_ITEM_LIST, CUR_ENT);<br />
DBMS_OUTPUT.PUT_LINE('current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM));<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/@invoiceID',L_INVOICEID); <br />
dbms_output.put_line('InvoiceDetailRequestHeader/@invoiceID:'||L_INVOICEID );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/@purpose',L_PURPOSE); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailRequestHeader/@purpose:'||L_PURPOSE );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/@operation',L_OPERATION ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailRequestHeader/@operation:'||L_OPERATION );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/@invoiceDate',L_INVOICEDATE ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailRequestHeader/@invoiceDate:'||L_INVOICEDATE );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/InvoiceDetailLineIndicator/@isAccountingInLine',L_ISACCOUNTINGINLINE ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailLineIndicator/@isAccountingInLine:'||L_ISACCOUNTINGINLINE );<br />
--- retriving the InvoiceDetailPaymentTerm and Extrinsic details before re-initialinf current item list for retriving InvoicePartner & InvoiceDetailShipping<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/InvoiceDetailPaymentTerm/@payInNumberOfDays',l_payInNumberOfDays ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailPaymentTerm/@payInNumberOfDays:'||l_payInNumberOfDays );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/InvoiceDetailPaymentTerm/@percentageRate',l_percentageRate ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailPaymentTerm/@percentageRate:'||l_percentageRate );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/Extrinsic/@name',l_Extrinsic_name ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailRequestHeader/Extrinsic/@name'||L_EXTRINSIC_NAME );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/Extrinsic/text()',l_Extrinsic_value ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailPaymentTerm/@percentageRate:'||l_Extrinsic_value );<br />
CURRENT_ITEM_LIST := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(MY_DOC),'cXML/Request/InvoiceDetailRequest/InvoiceDetailRequestHeader/InvoicePartner');<br />
-- note no slash at end<br />
<br />
DBMS_OUTPUT.PUT_LINE('############# InvoicePartner loop begin #################');<br />
DBMS_OUTPUT.PUT_LINE('Count of cXML/Request/InvoiceDetailRequest/InvoiceDetailRequestHeader/InvoicePartner'||DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)); -- find the no of entries in this node<br />
FOR CUR_ENT IN 0 .. DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)-1<br />
LOOP<br />
<br />
CURRENT_ITEM := DBMS_XMLDOM.ITEM(CURRENT_ITEM_LIST, CUR_ENT);<br />
DBMS_OUTPUT.PUT_LINE('##############current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM)||' - ' ||CUR_ENT);<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/@role',L_CONTACT_ROLE ); <br />
DBMS_OUTPUT.PUT_LINE('Contact/@role : '||L_CONTACT_ROLE );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/Name/text()', L_CONTACT_NAME); <br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/Street[position()=1]/text()', L_CONTACT_STREET1); <br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/Street[position()=2]/text()', L_CONTACT_STREET2); <br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/City/text()', L_CONTACT_CITY); <br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/State/text()', L_CONTACT_STATE); <br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/PostalCode/text()', L_CONTACT_POSTALCODE); <br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/Country/text()', L_CONTACT_COUNTRY); <br />
DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' Name: '|| L_CONTACT_NAME);<br />
DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' City: '|| L_CONTACT_CITY);<br />
DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' State: '|| L_CONTACT_STATE);<br />
DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' Postalcode: '|| L_CONTACT_POSTALCODE);<br />
DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' Country: '|| L_CONTACT_COUNTRY);<br />
DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' Street1: '|| L_CONTACT_STREET1);<br />
DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' Street2: '|| L_CONTACT_STREET2);<br />
IF L_CONTACT_ROLE = 'from' THEN<br />
L_CONTACT_FROM_NAME := L_CONTACT_NAME;<br />
L_CONTACT_FROM_CITY := L_CONTACT_CITY;<br />
L_CONTACT_FROM_STATE := L_CONTACT_STATE;<br />
L_CONTACT_FROM_POSTALCODE := L_CONTACT_POSTALCODE;<br />
L_CONTACT_FROM_COUNTRY := L_CONTACT_COUNTRY ;<br />
L_CONTACT_FROM_Street1 := L_CONTACT_STREET1 ;<br />
elsif L_CONTACT_ROLE = 'soldTo' THEN<br />
L_CONTACT_SOLDTO_NAME := L_CONTACT_NAME;<br />
L_CONTACT_SOLDTO_CITY := L_CONTACT_CITY;<br />
L_CONTACT_SOLDTO_STATE := L_CONTACT_STATE;<br />
L_CONTACT_SOLDTO_POSTALCODE := L_CONTACT_POSTALCODE;<br />
L_CONTACT_SOLDTO_COUNTRY := L_CONTACT_COUNTRY ;<br />
L_CONTACT_SOLDTO_Street1 := L_CONTACT_STREET1 ;<br />
elsif L_CONTACT_ROLE = 'remitTo' THEN<br />
L_CONTACT_RMTTO_NAME := L_CONTACT_NAME;<br />
L_CONTACT_RMTTO_CITY := L_CONTACT_CITY;<br />
L_CONTACT_RMTTO_STATE := L_CONTACT_STATE;<br />
L_CONTACT_RMTTO_POSTALCODE := L_CONTACT_POSTALCODE;<br />
L_CONTACT_RMTTO_COUNTRY := L_CONTACT_COUNTRY ;<br />
L_CONTACT_RMTTO_Street1 := L_CONTACT_STREET1 ;<br />
elsif L_CONTACT_ROLE = 'billTo' THEN<br />
L_CONTACT_billTO_NAME := L_CONTACT_NAME;<br />
L_CONTACT_BILLTO_CITY := L_CONTACT_CITY;<br />
L_CONTACT_BILLTO_STATE := L_CONTACT_STATE;<br />
L_CONTACT_BILLTO_POSTALCODE := L_CONTACT_POSTALCODE;<br />
L_CONTACT_BILLTO_COUNTRY := L_CONTACT_COUNTRY ;<br />
L_CONTACT_BILLTO_Street1 := L_CONTACT_STREET1 ;<br />
END IF;<br />
END LOOP;<br />
CURRENT_ITEM_LIST := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(MY_DOC),'cXML/Request/InvoiceDetailRequest/InvoiceDetailRequestHeader/InvoiceDetailShipping/Contact');<br />
-- note no slash at end<br />
DBMS_OUTPUT.PUT_LINE('############# InvoiceDetailShipping loop begin #################');<br />
DBMS_OUTPUT.PUT_LINE('count of cXML/Request/InvoiceDetailRequest/InvoiceDetailRequestHeader/InvoicePartner'||DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)); -- find the no of entries in this node<br />
<br />
FOR CUR_ENT IN 0 .. DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)-1<br />
LOOP<br />
<br />
CURRENT_ITEM := DBMS_XMLDOM.ITEM(CURRENT_ITEM_LIST, CUR_ENT);<br />
DBMS_OUTPUT.PUT_LINE('##########current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM)||' - ' ||CUR_ENT);<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'@role',L_CONTACT_SHIPROLE ); <br />
DBMS_OUTPUT.PUT_LINE('Contact/@role : '||L_CONTACT_SHIPROLE );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Name/text()', L_CONTACT_NAME); <br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/Street[position()=1]/text()', L_CONTACT_STREET1); <br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/Street[position()=2]/text()', L_CONTACT_Street2); <br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/City/text()', L_CONTACT_CITY); <br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/State/text()', L_CONTACT_STATE); <br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/PostalCode/text()', L_CONTACT_POSTALCODE); <br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/Country/text()', L_CONTACT_COUNTRY); <br />
DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' Name: '|| L_CONTACT_NAME);<br />
DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' City: '|| L_CONTACT_CITY);<br />
DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' State: '|| L_CONTACT_STATE);<br />
DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' Postalcode: '|| L_CONTACT_POSTALCODE);<br />
DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' Country: '|| L_CONTACT_COUNTRY);<br />
DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' Street1: '|| L_CONTACT_STREET1);<br />
DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' Street2: '|| L_CONTACT_STREET2);<br />
IF L_CONTACT_SHIPROLE = 'shipFrom' THEN<br />
L_CONTACT_SHIPFROM_NAME := L_CONTACT_NAME;<br />
L_CONTACT_SHIPFROM_CITY := L_CONTACT_CITY;<br />
L_CONTACT_SHIPFROM_STATE := L_CONTACT_STATE;<br />
L_CONTACT_SHIPFROM_POSTALCODE := L_CONTACT_POSTALCODE;<br />
L_CONTACT_SHIPFROM_COUNTRY := L_CONTACT_COUNTRY ;<br />
L_CONTACT_SHIPFROM_STREET1 := L_CONTACT_STREET1 ;<br />
elsif L_CONTACT_SHIPROLE = 'shipTo' THEN<br />
L_CONTACT_SHIPTO_NAME := L_CONTACT_NAME;<br />
L_CONTACT_SHIPTO_CITY := L_CONTACT_CITY;<br />
L_CONTACT_SHIPTO_STATE := L_CONTACT_STATE;<br />
L_CONTACT_SHIPTO_POSTALCODE := L_CONTACT_POSTALCODE;<br />
L_CONTACT_SHIPTO_COUNTRY := L_CONTACT_COUNTRY ;<br />
L_CONTACT_SHIPTO_STREET1 := L_CONTACT_STREET1 ;<br />
END IF;<br />
END LOOP;<br />
CURRENT_ITEM_LIST := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(MY_DOC),'cXML/Request/InvoiceDetailRequest/InvoiceDetailOrder');<br />
-- note no slash at end<br />
DBMS_OUTPUT.PUT_LINE('############# InvoiceDetailOrder loop begin #################');<br />
DBMS_OUTPUT.PUT_LINE('count of cXML/Request/InvoiceDetailRequest/InvoiceDetailOrder'||DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)); -- find the no of entries in this node<br />
<br />
FOR CUR_ENT IN 0 .. DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)-1<br />
LOOP<br />
CURRENT_ITEM := DBMS_XMLDOM.ITEM(CURRENT_ITEM_LIST, CUR_ENT);<br />
DBMS_OUTPUT.PUT_LINE('#########current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM)||' - ' ||CUR_ENT);<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailOrderInfo/OrderReference/@orderID',L_ORDER_ID ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailOrderInfo/OrderReference/@orderID: '||L_ORDER_ID );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailOrderInfo/OrderReference/DocumentReference/@payloadID', L_PAYLOADID ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailOrderInfo/OrderReference/DocumentReference/@payloadID: '||L_PAYLOADID );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/@invoiceLineNumber', L_INVOICELINENUMBER ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/@invoiceLineNumber: '||L_INVOICELINENUMBER );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/@quantity', L_QUANTITY ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/@quantity: '||L_QUANTITY );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/UnitOfMeasure/text()', L_UNITOFMEASURE ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/UnitOfMeasure/text(): '||L_UNITOFMEASURE );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/UnitPrice/Money/@currency', L_CURRENCY ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/UnitPrice/Money/@currency: '||L_CURRENCY );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/UnitPrice/Money/text()', L_UNITPRICE ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/UnitPrice/Money/text(): '||L_UNITPRICE );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/InvoiceDetailItemReference/@lineNumber', L_INV_DET_ITEM_REF_LINENUM ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/InvoiceDetailItemReference/@lineNumber: '||L_INV_DET_ITEM_REF_LINENUM );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/InvoiceDetailItemReference/ItemID/SupplierPartID/text()', L_SUPPLIERPARTID ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/InvoiceDetailItemReference/ItemID/SupplierPartID/text(): '||L_SUPPLIERPARTID );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/InvoiceDetailItemReference/Description/text()', L_DESCRIPTION ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/InvoiceDetailItemReference/Description/text(): '||L_DESCRIPTION );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/InvoiceDetailItemReference/ManufacturerPartID/text()', L_MANUFACTURERPARTID ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/InvoiceDetailItemReference/ManufacturerPartID/text(): '||L_MANUFACTURERPARTID );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/InvoiceDetailItemReference/ManufacturerName/text()', L_MANUFACTURERNAME ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/InvoiceDetailItemReference/ManufacturerName/text(): '||L_MANUFACTURERNAME );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/SubtotalAmount/Money/@currency', L_SUBTOTALAMOUNT_CUR ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/SubtotalAmount/Money/@currency: '||L_SUBTOTALAMOUNT_CUR );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/SubtotalAmount/Money/text()', L_SUBTOTALAMOUNT ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/SubtotalAmount/Money/text(): '||L_SUBTOTALAMOUNT );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/GrossAmount/Money/@currency', L_GROSSAMOUNT_CUR ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/GrossAmount/Money/@currency: '||L_GROSSAMOUNT_CUR );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/GrossAmount/Money/text()', L_GROSSAMOUNT ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/GrossAmount/Money/text(): '||L_GROSSAMOUNT );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/NetAmount/Money/@currency', L_NETAMOUNT_CUR ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/NetAmount/Money/@currency: '||L_NETAMOUNT_CUR );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/NetAmount/Money/text()', L_NETAMOUNT ); <br />
DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/NetAmount/Money/text(): '||L_NETAMOUNT );<br />
end LOOP;<br />
<br />
CURRENT_ITEM_LIST := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(MY_DOC),'cXML/Request/InvoiceDetailRequest/InvoiceDetailSummary');<br />
-- note no slash at end<br />
DBMS_OUTPUT.PUT_LINE('############# InvoiceDetailSummary loop begin #################');<br />
dbms_output.put_line('Count of cXML/Request/InvoiceDetailRequest/InvoiceDetailSummary'||dbms_xmldom.getLength(current_item_list)); -- find the no of entries in this node<br />
FOR CUR_ENT IN 0 .. DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)-1<br />
LOOP<br />
CURRENT_ITEM := DBMS_XMLDOM.ITEM(CURRENT_ITEM_LIST, CUR_ENT);<br />
DBMS_OUTPUT.PUT_LINE('#######current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM)||' - ' ||CUR_ENT);<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'SubtotalAmount/Money/@currency', L_SRY_SUBTOTAL_CUR ); <br />
DBMS_OUTPUT.PUT_LINE('SubtotalAmount/Money/@currency: '||L_SRY_SUBTOTAL_CUR );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'SubtotalAmount/Money/text()', L_SRY_SUBTOTAL_AMOUNT ); <br />
DBMS_OUTPUT.PUT_LINE('SubtotalAmount/Money/text(): '||L_SRY_SUBTOTAL_AMOUNT );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/Money/@currency', L_SRY_TAX_CUR ); <br />
DBMS_OUTPUT.PUT_LINE('Tax/Money/@currency: '||L_SRY_TAX_CUR );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/Money/text()', L_SRY_TAX_AMOUNT ); <br />
DBMS_OUTPUT.PUT_LINE('Tax/Money/text(): '||L_SRY_TAX_AMOUNT );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/@purpose', L_SRY_TAX_PURPOSE ); <br />
DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/@purpose: '||L_SRY_TAX_PURPOSE );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/@category', L_SRY_TAX_CATEGORY ); <br />
DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/@category: '||L_SRY_TAX_CATEGORY );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/@percentageRate', L_SRY_TAX_RATE ); <br />
DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/@percentageRate: '||L_SRY_TAX_RATE );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/TaxableAmount/Money/@currency', L_SRY_TAXABLE_CUR ); <br />
DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/TaxableAmount/Money/@currency: '||L_SRY_TAXABLE_CUR );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/TaxableAmount/Money/text()', L_SRY_TAXABLE_AMOUNT ); <br />
DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/TaxableAmount/Money/text(): '||L_SRY_TAXABLE_AMOUNT );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/TaxAmount/Money/@currency', L_SRY_TAX_CUR1 ); <br />
DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/TaxAmount/Money/@currency: '||L_SRY_TAX_CUR1 );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/TaxAmount/Money/text()', L_SRY_TAX_AMOUNT1 ); <br />
DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/TaxAmount/Money/text(): '||L_SRY_TAX_AMOUNT1 );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'GrossAmount/Money/@currency', L_SRY_GROSS_CUR ); <br />
DBMS_OUTPUT.PUT_LINE('GrossAmount/Money/@currency: '||L_SRY_GROSS_CUR );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'GrossAmount/Money/text()', L_SRY_GROSS_AMOUNT ); <br />
DBMS_OUTPUT.PUT_LINE('GrossAmount/Money/text(): '||L_SRY_GROSS_AMOUNT );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'NetAmount/Money/@currency', L_SRY_NET_CUR ); <br />
DBMS_OUTPUT.PUT_LINE('NetAmount/Money/@currency: '||L_SRY_NET_CUR );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'NetAmount/Money/text()', L_SRY_NET_AMOUNT ); <br />
DBMS_OUTPUT.PUT_LINE('NetAmount/Money/text(): '||L_SRY_NET_AMOUNT );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'DueAmount/Money/@currency', L_SRY_DUE_CUR ); <br />
DBMS_OUTPUT.PUT_LINE('DueAmount/Money/@currency: '||L_SRY_DUE_CUR );<br />
DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'DueAmount/Money/text()', L_SRY_DUE_AMOUNT ); <br />
DBMS_OUTPUT.PUT_LINE('DueAmount/Money/text(): '||L_SRY_DUE_AMOUNT );<br />
END LOOP;<br />
END LOOP;<br />
END LOOP;<br />
dbms_xmldom.freedocument(my_doc);<br />
EXCEPTION<br />
WHEN OTHERS THEN<br />
DBMS_OUTPUT.PUT_LINE('sqlerrm :'||SQLERRM);<br />
dbms_lob.close(l_bfile);<br />
DBMS_LOB.FREETEMPORARY(L_CLOB);<br />
DBMS_XMLPARSER.FREEPARSER(L_PARSER);<br />
DBMS_XMLDOM.FREEDOCUMENT(MY_DOC);<br />
end;<br />
/<br />
<br />
<br />
-------------------XML File---------------<br />
<?xml version="1.0" encoding="UTF-8"?><br />
<!DOCTYPE cXML SYSTEM "http://xml.cxml.org/schemas/cXML/1.2.009/InvoiceDetail.dtd"><cXML version="1.2.009" payloadID="1405088578064.1105606342@qa-is82-real01.officemax.omx.com" timestamp="2014-07-11T12:00:00-00:00" xml:lang="en-US"><br />
<Header><br />
<From><br />
<Credential domain="DUNS"><br />
<Identity>178923231</Identity><br />
<SharedSecret>boise1</SharedSecret><br />
</Credential><br />
</From><br />
<To><br />
<Credential domain="DUNS"><br />
<Identity>119851215</Identity><br />
</Credential><br />
</To><br />
<Sender><br />
<Credential domain="OracleSN"><br />
<Identity>oraclesn</Identity><br />
<SharedSecret>boise1</SharedSecret><br />
</Credential><br />
<UserAgent>Oracle Supplier Network</UserAgent><br />
</Sender><br />
</Header><br />
<Request deploymentMode="test"><br />
<InvoiceDetailRequest><br />
<InvoiceDetailRequestHeader invoiceID="755055Jun2414" purpose="standard" operation="new" invoiceDate="2014-06-24T12:00:00-00:00"><br />
<InvoiceDetailHeaderIndicator/><br />
<InvoiceDetailLineIndicator isAccountingInLine="yes"/><br />
<InvoicePartner><br />
<Contact role="from"><br />
<Name xml:lang="en-US">Laura Sebesta</Name><br />
<PostalAddress><br />
<Street>800 W. Bryn Mawr Ave.</Street><br />
<Street>AMNE / AMSE cXML Profile</Street><br />
<City>Itasca</City><br />
<State>IL</State><br />
<PostalCode>60143</PostalCode><br />
<Country isoCountryCode="US">United States</Country><br />
</PostalAddress><br />
</Contact><br />
</InvoicePartner><br />
<InvoicePartner><br />
<Contact role="soldTo"><br />
<Name xml:lang="en-US">INTERACTIVE DATA </Name><br />
<PostalAddress><br />
<Street>ACCOUNTS PAYABLE </Street><br />
<Street>32 CROSBY DR </Street><br />
<City>BEDFORD </City><br />
<State>MA</State><br />
<PostalCode>01730 </PostalCode><br />
<Country isoCountryCode="US">United States</Country><br />
</PostalAddress><br />
</Contact><br />
</InvoicePartner><br />
<InvoicePartner><br />
<Contact role="remitTo"><br />
<Name xml:lang="en-US">Wachovia Bank</Name><br />
<PostalAddress><br />
<Street>P.O. Box 101705</Street><br />
<City>Atlanta</City><br />
<State>GA</State><br />
<PostalCode>303921705</PostalCode><br />
<Country isoCountryCode="US">United States</Country><br />
</PostalAddress><br />
</Contact><br />
<IdReference identifier="069984151" domain="accountID"/><br />
</InvoicePartner><br />
<InvoicePartner><br />
<Contact role="billTo" addressID="161 "><br />
<Name xml:lang="en-US">INTERACTIVE DATA CORP </Name><br />
<PostalAddress><br />
<Street>US BOXBOROUGH </Street><br />
<Street>60 CODMAN HILL RD </Street><br />
<City>BOXBOROUGH </City><br />
<State>MA</State><br />
<PostalCode>01719 </PostalCode><br />
<Country isoCountryCode="US">United States</Country><br />
</PostalAddress><br />
</Contact><br />
</InvoicePartner><br />
<InvoiceDetailShipping><br />
<Contact role="shipFrom"><br />
<Name xml:lang="en-US">OfficeMax Contract</Name><br />
<PostalAddress><br />
<Street>125 Industrial Way</Street><br />
<City>Portland</City><br />
<State>ME</State><br />
<PostalCode>04104</PostalCode><br />
<Country isoCountryCode="US">United States</Country><br />
</PostalAddress><br />
</Contact><br />
<Contact role="shipTo"><br />
<Name xml:lang="en-US">INTERACTIVE DATA CORP </Name><br />
<PostalAddress><br />
<Street>US BOXBOROUGH </Street><br />
<Street>60 CODMAN HILL RD </Street><br />
<City>BOXBOROUGH </City><br />
<State>MA</State><br />
<PostalCode>01719 </PostalCode><br />
<Country isoCountryCode="US">United States</Country><br />
</PostalAddress><br />
</Contact><br />
</InvoiceDetailShipping><br />
<InvoiceDetailPaymentTerm payInNumberOfDays="35" percentageRate="0"/><br />
<Extrinsic name="SHIPTOPARTNRIDX">161 </Extrinsic><br />
</InvoiceDetailRequestHeader><br />
<InvoiceDetailOrder><br />
<InvoiceDetailOrderInfo><br />
<OrderReference orderID="1035023685"><br />
<DocumentReference payloadID="Office Max.2503690.35088442.TEST.1035023685:0:103"/><br />
</OrderReference><br />
</InvoiceDetailOrderInfo><br />
<InvoiceDetailItem invoiceLineNumber="401" quantity="3"><br />
<UnitOfMeasure>EA</UnitOfMeasure><br />
<UnitPrice><br />
<Money currency="USD">148.00</Money><br />
</UnitPrice><br />
<InvoiceDetailItemReference lineNumber="4"><br />
<ItemID><br />
<SupplierPartID>Q7VG730M</SupplierPartID><br />
</ItemID><br />
<Description xml:lang="en-US">LCD MONITOR 17&quot;</Description><br />
<ManufacturerPartID>VG730M</ManufacturerPartID><br />
<ManufacturerName xml:lang="en-US">VIEWSO</ManufacturerName><br />
</InvoiceDetailItemReference><br />
<SubtotalAmount><br />
<Money currency="USD">00444.00</Money><br />
</SubtotalAmount><br />
<GrossAmount><br />
<Money currency="USD">444</Money><br />
</GrossAmount><br />
<NetAmount><br />
<Money currency="USD">00444.00</Money><br />
</NetAmount><br />
</InvoiceDetailItem><br />
</InvoiceDetailOrder><br />
<InvoiceDetailSummary><br />
<SubtotalAmount><br />
<Money currency="USD">444.00</Money><br />
</SubtotalAmount><br />
<Tax><br />
<Money currency="USD">27.75</Money><br />
<Description xml:lang="en-US"/><br />
<TaxDetail purpose="tax" category="sales" percentageRate="6.25"><br />
<TaxableAmount><br />
<Money currency="USD">444.00</Money><br />
</TaxableAmount><br />
<TaxAmount><br />
<Money currency="USD">27.75</Money><br />
</TaxAmount><br />
<TaxLocation xml:lang="en-US">usa</TaxLocation><br />
</TaxDetail><br />
</Tax><br />
<GrossAmount><br />
<Money currency="USD">471.75</Money><br />
</GrossAmount><br />
<NetAmount><br />
<Money currency="USD">471.75</Money><br />
</NetAmount><br />
<DueAmount><br />
<Money currency="USD">471.75</Money><br />
</DueAmount><br />
</InvoiceDetailSummary><br />
</InvoiceDetailRequest><br />
</Request><br />
</cXML><br />
------------------------------------------------------------<br />
Issues occured:<br />
<br />
1. when using this DBMS_LOB.LOADFROMFILE(DEST_LOB => L_CLOB, SRC_LOB => L_BFILE, AMOUNT => DBMS_LOB.GETLENGTH(L_BFILE));<br />
the data in xml file is reading in unknown characterset <br />
<br />
Resolved: by using DBMS_LOB.LOADCLOBFROMFILE<br />
<br />
2. when using this dbms_xmlparser.parseclob(l_parser,l_clob) getting the below error<br />
sqlerrm :ORA-31020: The operation is not allowed, Reason: For security reasons, ftp and http access over XDB repository is not allowed on server side<br />
resolved : by using <br />
dbms_xmlparser.parseclob(l_parser, REGEXP_REPLACE(l_clob,'<!DOCTYPE cXML SYSTEM "http://xml.cxml.org/schemas/cXML/1.2.009/InvoiceDetail.dtd">',''));<br />
<br />
3. could not able to read the file when file is in "/idc/dev/app/apps/apps_st/appl/zzcus/12.0.0/XMLDIR" and getting the below error. <br />
ORA-22288: file or LOB operation FILEOPEN failed <br />
No such file or directory<br />
temporarily Resolved : by placing the file in /idc/dev/csf/tmp.<br />
<br />
4. Able to read the node values but not attribute values<br />
resolved : by using<br />
for Ex: <Identity>178923231</Identity> by using: dbms_xslprocessor.valueof(current_item,'From/Credential/Identity/text()',from_identity)<br />
for Ex: <Credential domain="OracleSN"> by using: dbms_xslprocessor.valueof(current_item,'From/Credential/@domain',from_credential);<br />
<br />
<br />
<br />
<br />
<span id="fullpost">
</span></div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-21779236561036309852014-07-24T17:28:00.002+05:302014-07-24T17:28:47.139+05:30Displaying concurrent programm name dynamically on a report<div dir="ltr" style="text-align: left;" trbidi="on">
for displaying concurrent programm name dynamically in a report<br /> declare user parameter as p_conc_request_id <br /> write the below commnds in triggers as<br /> srw.user_exit('fnd srwinit') in before report trigger and <br /> srw.user_exit('fnd srwexit') in after report trigger. <br /><br />and take one place holder column in report level... so, now we r going to use this place holder column in one formaula column. <br />for that take one formula column and in that we want to write a piece of code which is shown below....<br /><br /> select fcp.user_concurrent_program_name<br /> into :cp_conc_name(place holder column name)<br /> from fnd_concurrent_requests fcr,<br /> fnd_concurrent_programs_vl fcp<br /> where fcr.concurrent_program_id=fcp.concurrent_program_id<br /> and fcr.request_id =nvl(:p_conc_request_id,fcr.request_id);<br /> and for request id we can<br /><br /> declare<br /> in same in formula column<br /> as<br /> return(fnd_profile.value(':p_conc_request_id'));<br /><br /><span id="fullpost">
</span></div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-88574823809599815872014-07-24T17:16:00.001+05:302014-07-24T17:16:32.555+05:30P2P Overview<div dir="ltr" style="text-align: left;" trbidi="on">
<!--[if gte mso 9]><xml>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>X-NONE</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:DontVertAlignCellWithSp/>
<w:DontBreakConstrainedForcedTables/>
<w:DontVertAlignInTxbx/>
<w:Word11KerningPairs/>
<w:CachedColBalance/>
</w:Compatibility>
<w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>
<m:mathPr>
<m:mathFont m:val="Cambria Math"/>
<m:brkBin m:val="before"/>
<m:brkBinSub m:val="--"/>
<m:smallFrac m:val="off"/>
<m:dispDef/>
<m:lMargin m:val="0"/>
<m:rMargin m:val="0"/>
<m:defJc m:val="centerGroup"/>
<m:wrapIndent m:val="1440"/>
<m:intLim m:val="subSup"/>
<m:naryLim m:val="undOvr"/>
</m:mathPr></w:WordDocument>
</xml><![endif]-->
<div style="mso-outline-level: 3;">
<b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">Procure to Pay:</span></b></div>
<span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">First let’s see what the heading itself means? Procure to Pay means <b>Procuring
Raw Materials </b>required to manufacture the final or finished Goods to <b>Paying
the Supplier </b>from<b> </b>whom the material was purchased. But this is not
just two steps. It involves many steps. Let’s see the steps and Oracle
Application involved in performing those steps.</span><br />
<div style="tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">1.</span><span lang="EN" style="color: #494949; font-size: 7.0pt; mso-ansi-language: EN;">
</span><b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">Oracle Purchasing:</span></b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;"> You
enter <b>Suppliers </b>of different materials and products you want to purchase
to manufacture a finished good that your organization plans to sell. </span></div>
<div style="tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">2.</span><span lang="EN" style="color: #494949; font-size: 7.0pt; mso-ansi-language: EN;">
</span><b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">Oracle Purchasing:</span></b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;"> You
prepare a <b>Request for Quotation</b> <b>(RFQ)</b> and send it to different
suppliers to get the best and/or economical price for the product. </span></div>
<div style="tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">3.</span><span lang="EN" style="color: #494949; font-size: 7.0pt; mso-ansi-language: EN;">
</span><b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">Oracle Purchasing:</span></b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">
Suppliers sends their quotations and you upload those quotations in Oracle
Purchasing to get the best three quotes and further to get the one best quote. </span></div>
<div style="tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">4.</span><span lang="EN" style="color: #494949; font-size: 7.0pt; mso-ansi-language: EN;">
</span><b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">Oracle Purchasing:</span></b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;"> You
prepare a <b>Purchase Order(PO)</b> against the best RFQ to buy the goods from
the supplier who quoted the suitable price and sends the PO to that supplier </span></div>
<div style="tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">5.</span><span lang="EN" style="color: #494949; font-size: 7.0pt; mso-ansi-language: EN;">
</span><b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">Oracle Purchasing:</span></b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;"> The
supplier receives the confirmation of purchase from PO and ships the ordered
goods. You receive the goods enter a <b>Goods Received Note (GRN) </b>in Oracle
Purchasing. </span></div>
<div style="tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">6.</span><span lang="EN" style="color: #494949; font-size: 7.0pt; mso-ansi-language: EN;">
</span><b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">Oracle Inventory / Oracle Assets:</span></b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">It’s up to you whether you want to receive the goods at your head office or
you Inventory directly. In either case you move the received goods to your
different Raw Material Inventory from Oracle Purchasing to Oracle Inventory and
the Item Count increases. If the item is Asset Type then it will move to Oracle
Assets at the time of Invoice creation in Oracle Payables. </span></div>
<div style="tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">7.</span><span lang="EN" style="color: #494949; font-size: 7.0pt; mso-ansi-language: EN;">
</span><b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">Oracle General Ledger:</span></b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">Once
you move the goods to Oracle Inventory, it sends the Material Accounting to
Oracle General Ledger. </span></div>
<div style="tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">8.</span><span lang="EN" style="color: #494949; font-size: 7.0pt; mso-ansi-language: EN;">
</span><b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">Oracle Payables:</span></b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;"> After
this the supplier sends you the invoice for the purchased goods and you Enter
or Match the invoice against the PO from Oracle Purchasing in Oracle Payables.
As said before, if the item is Asset in nature then it will move to Oracle
Asset. </span></div>
<div style="tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">9.</span><span lang="EN" style="color: #494949; font-size: 7.0pt; mso-ansi-language: EN;">
</span><b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">Oracle General Ledger:</span></b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">When
you enter the invoice it means that you have created a Liability against that
supplier and also you have recorded the expense incurred or asset purchased.
Oracle Payables sends the invoice accounting to Oracle General Ledger. </span></div>
<div style="tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">10.</span><span lang="EN" style="color: #494949; font-size: 7.0pt; mso-ansi-language: EN;"> </span><b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">Oracle Payables:</span></b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">You pay the invoice and settle the
Liability. </span></div>
<div style="tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">11.</span><span lang="EN" style="color: #494949; font-size: 7.0pt; mso-ansi-language: EN;"> </span><b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">Oracle General Ledger:</span></b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;"> The liability is settled and your cash
movement account is updated. </span></div>
<div style="tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">12.</span><span lang="EN" style="color: #494949; font-size: 7.0pt; mso-ansi-language: EN;"> </span><b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">Oracle Cash Management:</span></b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;"> As you pay the invoice Oracle Payables
sends the payment information to Oracle Cash Management for Bank
Reconciliation. Once reconciled, Oracle Cash Management sends the updated
Bank/Cash accounting entry to Oracle General Ledger. </span></div>
<div style="tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">13.</span><span lang="EN" style="color: #494949; font-size: 7.0pt; mso-ansi-language: EN;"> </span><b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">Oracle General Ledger:</span></b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;"> Your cash at bank is updated with actual
balance. </span></div>
<div style="tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">14.</span><span lang="EN" style="color: #494949; font-size: 7.0pt; mso-ansi-language: EN;"> </span><b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">Oracle Process Manufacturing(OPM) / Oracle Discrete Manufacturing(ODM):</span></b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">You start the manufacturing of your final product. Both OPM or ODM requests
the different raw materials from you inventory organizations and manufactures a
finished good. </span></div>
<div style="tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">15.</span><span lang="EN" style="color: #494949; font-size: 7.0pt; mso-ansi-language: EN;"> </span><b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">Oracle Inventory: </span></b><span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">As the raw materials are issued to OPM and
ODM the inventory sends the issuing material accounting to General Ledger and
decreases the Item Count from the Raw Material Store. As the finished good is
prepared, Oracle Inventory receives the finished good in Finished Good Store
and increase the Item Count. </span></div>
<br />
<span lang="EN" style="color: #494949; font-family: ""serif"","serif"; mso-ansi-language: EN;">Now the final product is ready to be sold in the market and from here the
O2C cycle starts.</span><br />
<!--[if gte mso 9]><xml>
<w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
DefSemiHidden="true" DefQFormat="false" DefPriority="99"
LatentStyleCount="267">
<w:LsdException Locked="false" Priority="0" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/>
<w:LsdException Locked="false" Priority="39" Name="toc 1"/>
<w:LsdException Locked="false" Priority="39" Name="toc 2"/>
<w:LsdException Locked="false" Priority="39" Name="toc 3"/>
<w:LsdException Locked="false" Priority="39" Name="toc 4"/>
<w:LsdException Locked="false" Priority="39" Name="toc 5"/>
<w:LsdException Locked="false" Priority="39" Name="toc 6"/>
<w:LsdException Locked="false" Priority="39" Name="toc 7"/>
<w:LsdException Locked="false" Priority="39" Name="toc 8"/>
<w:LsdException Locked="false" Priority="39" Name="toc 9"/>
<w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/>
<w:LsdException Locked="false" Priority="10" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Title"/>
<w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/>
<w:LsdException Locked="false" Priority="11" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/>
<w:LsdException Locked="false" Priority="22" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Strong"/>
<w:LsdException Locked="false" Priority="20" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/>
<w:LsdException Locked="false" Priority="59" SemiHidden="false"
UnhideWhenUsed="false" Name="Table Grid"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/>
<w:LsdException Locked="false" Priority="1" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 1"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 1"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 1"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/>
<w:LsdException Locked="false" Priority="34" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/>
<w:LsdException Locked="false" Priority="29" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Quote"/>
<w:LsdException Locked="false" Priority="30" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 1"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 1"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 2"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 2"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 2"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 2"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 2"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 3"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 3"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 3"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 3"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 3"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 4"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 4"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 4"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 4"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 4"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 5"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 5"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 5"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 5"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 5"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 6"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 6"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 6"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 6"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 6"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/>
<w:LsdException Locked="false" Priority="19" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/>
<w:LsdException Locked="false" Priority="21" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/>
<w:LsdException Locked="false" Priority="31" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/>
<w:LsdException Locked="false" Priority="32" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/>
<w:LsdException Locked="false" Priority="33" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/>
<w:LsdException Locked="false" Priority="37" Name="Bibliography"/>
<w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/>
</w:LatentStyles>
</xml><![endif]--><!--[if gte mso 10]>
<style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:"Times New Roman";
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;}
</style>
<![endif]--><span id="fullpost">
</span></div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-57084664485777492542014-07-24T17:13:00.004+05:302014-07-24T17:13:47.664+05:30O2C Overview<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<br /><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 3;">
<b><span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";">Order to Cash Cycle:</span></b></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;">
<span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";">Order to Cash means <b>Customer’s Order</b> <b>Placing</b> to <b>Vendor’s Cash Receiving</b>.
When your final product is ready to be sold, you market it. The
customer gets fascinated with the marketing campaign and decides to buy
your product and from here starts the O2C cycle.</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo2; tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">1.<span style="font: 7.0pt "Times New Roman";"> </span></span></span><b><span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";">Oracle Order Management:</span></b><span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";">Customer places the order. </span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo2; tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">2.<span style="font: 7.0pt "Times New Roman";"> </span></span></span><b><span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";">Oracle Order Management:</span></b><span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";">You enter the customer order </span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo2; tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">3.<span style="font: 7.0pt "Times New Roman";"> </span></span></span><b><span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";">Oracle Inventory:</span></b><span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";"> Check the available unit and the quantity ordered by the customer. </span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo2; tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">4.<span style="font: 7.0pt "Times New Roman";"> </span></span></span><b><span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";">Oracle Order Management:</span></b><span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";">You ship the product to customer site and decreases the Finished Goods inventory. </span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo2; tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">5.<span style="font: 7.0pt "Times New Roman";"> </span></span></span><b><span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";">Oracle Receivables:</span></b><span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";">The customer receives the product and you invoice the customer. </span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo2; tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">6.<span style="font: 7.0pt "Times New Roman";"> </span></span></span><b><span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";">Oracle General Ledger:</span></b><span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";">You record your revenue and receivables. </span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo2; tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">7.<span style="font: 7.0pt "Times New Roman";"> </span></span></span><b><span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";">Oracle Receivables:</span></b><span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";">The customer pays and you receive the cash/check. </span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo2; tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">8.<span style="font: 7.0pt "Times New Roman";"> </span></span></span><b><span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";">Oracle Cash Management:</span></b><span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";">Oracle
Receivables sends the customer receipt for Bank Reconciliation. After
reconciliation, Oracle Cash Management send the actual bank balance or
Oracle General Ledger. </span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo2; tab-stops: list .5in; text-indent: -.25in;">
<span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">9.<span style="font: 7.0pt "Times New Roman";"> </span></span></span><b><span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";">Oracle General Ledger:</span></b><span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";">You have the actual bank balance. </span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;">
<span lang="EN" style="color: #494949; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-ansi-language: EN; mso-fareast-font-family: "Times New Roman";">This
is how the P2P and O2C cycle works, but this is not the only way,
obviously there are many other applications with different cycles. This
is one of them</span></div>
<br />
<span id="fullpost">
</span></div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-24327754826057624012014-07-23T14:47:00.001+05:302014-07-23T14:47:55.585+05:30Register Table, Column, Primary Key, Primary key column<div dir="ltr" style="text-align: left;" trbidi="on">
DECLARE<br /> V_APPL_SHORT_NAME varchar2 (40) := 'PER';<br /> v_tab_name VARCHAR2 (32) := 'XX_ORACLE-APPSS_BLOGSPOT_COM'; -- Change the table name if you require<br /> v_tab_type VARCHAR2 (50) := 'T';<br /> v_next_extent NUMBER := 512;<br /> v_pct_free NUMBER;<br /> v_pct_used NUMBER;<br />BEGIN<br /> -- Unregister the custom table if it exists<br /> ----- ad_dd.delete_table (p_appl_short_name => 'PER', p_tab_name => v_tab_name);<br /> -- Register the custom table<br /> FOR tab_details IN<br /> (SELECT table_name ,<br /> tablespace_name ,<br /> pct_free ,<br /> pct_used ,<br /> ini_trans ,<br /> max_trans ,<br /> initial_extent ,<br /> next_extent<br /> FROM dba_tables<br /> WHERE table_name = v_tab_name<br /> )<br /> LOOP<br /> AD_DD.REGISTER_TABLE (<br /> P_APPL_SHORT_NAME => V_APPL_SHORT_NAME, <br /> P_TAB_NAME => TAB_DETAILS.TABLE_NAME, <br /> P_TAB_TYPE => V_TAB_TYPE, <br /> P_NEXT_EXTENT => NVL (TAB_DETAILS.NEXT_EXTENT, 512), <br /> p_pct_free => NVL (tab_details.pct_free, 10),<br /> p_pct_used => NVL (tab_details.pct_used, 70) );<br /> END LOOP;<br /> -- Register the columns of custom table<br /> FOR all_tab_cols IN<br /> (SELECT column_name ,<br /> column_id ,<br /> data_type ,<br /> data_length ,<br /> nullable<br /> FROM all_tab_columns<br /> WHERE table_name = v_tab_name<br /> )<br /> LOOP<br /> AD_DD.REGISTER_COLUMN <br /> (<br /> P_APPL_SHORT_NAME => V_APPL_SHORT_NAME, <br /> P_TAB_NAME => V_TAB_NAME, <br /> P_COL_NAME => ALL_TAB_COLS.COLUMN_NAME, <br /> P_COL_SEQ => ALL_TAB_COLS.COLUMN_ID, <br /> P_COL_TYPE => ALL_TAB_COLS.DATA_TYPE, <br /> P_COL_WIDTH => ALL_TAB_COLS.DATA_LENGTH, <br /> P_NULLABLE => ALL_TAB_COLS.NULLABLE, <br /> P_TRANSLATE => 'N', <br /> P_PRECISION => null, <br /> P_SCALE => null <br /> );<br /> END LOOP;<br /> FOR all_keys IN<br /> (SELECT constraint_name,<br /> table_name,<br /> constraint_type<br /> FROM all_constraints<br /> WHERE constraint_type = 'P'<br /> AND table_name = v_tab_name<br /> )<br /> LOOP<br /> AD_DD.REGISTER_PRIMARY_KEY <br /> (P_APPL_SHORT_NAME => V_APPL_SHORT_NAME, <br /> P_KEY_NAME => ALL_KEYS.CONSTRAINT_NAME, <br /> P_TAB_NAME => ALL_KEYS.TABLE_NAME, <br /> P_DESCRIPTION => 'Register primary key', <br /> p_key_type => 'S',<br /> P_AUDIT_FLAG => 'N', <br /> p_enabled_flag => 'Y' );<br /> FOR all_columns IN<br /> (SELECT column_name,<br /> POSITION<br /> FROM dba_cons_columns<br /> WHERE table_name = all_keys.table_name<br /> AND constraint_name = all_keys.constraint_name<br /> )<br /> LOOP<br /> AD_DD.REGISTER_PRIMARY_KEY_COLUMN <br /> (P_APPL_SHORT_NAME => V_APPL_SHORT_NAME, <br /> P_KEY_NAME => ALL_KEYS.CONSTRAINT_NAME, <br /> P_TAB_NAME => ALL_KEYS.TABLE_NAME, <br /> P_COL_NAME => ALL_COLUMNS.COLUMN_NAME, <br /> P_COL_SEQUENCE => ALL_COLUMNS.POSITION <br /> );<br /> END LOOP;<br /> END LOOP;<br /> COMMIT;<br />END;<br /><br /><span id="fullpost">
</span></div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-51836420228305829872014-07-23T12:36:00.002+05:302014-07-23T12:36:27.434+05:30Employee Income Query<div dir="ltr" style="text-align: left;" trbidi="on">
SELECT ppeo.EMPLOYEE_NUMBER,<br /> ppeo.FULL_NAME NAME,<br /> ety.ELEMENT_NAME,<br /> PACT.EFFECTIVE_DATE,<br /> NVL (TO_NUMBER (RRV.RESULT_VALUE), 0)<br />FROM pay_run_results rrs,<br /> pay_run_result_values rrv,<br /> pay_element_types_f ety,<br /> pay_element_classifications cla,<br /> pay_assignment_actions asact,<br /> pay_payroll_actions pact,<br /> per_all_assignments_f assm,<br /> per_all_people_f ppeo,<br /> pay_input_values_f inv<br />WHERE rrs.RUN_RESULT_ID = rrv.RUN_RESULT_ID<br />AND rrs.ELEMENT_TYPE_ID =ety.ELEMENT_TYPE_ID<br /> -- AND rrv.RESULT_VALUE NOT IN ('0')<br /> -- and ety.ELEMENT_NAME like '%Abse%'<br /> -- AND cla.CLASSIFICATION_NAME IN ('Earnings')<br />AND ety.CLASSIFICATION_ID =cla.CLASSIFICATION_ID<br />AND rrs.ASSIGNMENT_ACTION_ID =asact.ASSIGNMENT_ACTION_ID<br />AND TO_NUMBER (pact.PAYROLL_ACTION_ID) = TO_NUMBER (asact.PAYROLL_ACTION_ID)<br />AND asact.ASSIGNMENT_ID = assm.ASSIGNMENT_ID<br />AND assm.EFFECTIVE_END_DATE > SYSDATE<br />AND assm.PERSON_ID = ppeo.PERSON_ID<br />AND ppeo.EFFECTIVE_END_DATE > SYSDATE<br />AND inv.EFFECTIVE_END_DATE > SYSDATE<br />AND RRV.INPUT_VALUE_ID = INV.INPUT_VALUE_ID<br />AND PACT.EFFECTIVE_DATE BETWEEN sysdate AND sysdate-30<br />AND inv.name LIKE'%%'<span id="fullpost">
</span></div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-42341913738373736032014-07-23T12:28:00.001+05:302014-07-23T12:28:03.840+05:30Password and Session profile options<div dir="ltr" style="text-align: left;" trbidi="on">
Password profile options<br /><br />SINCE PRODUCTION is SETUP with VERY high security PASSWORDS GET locked EASILY. <br />In a big team of developers someone might enter the old password several times and lock a username which everyone shares. After which it becomes a problem setting a new password as all the old ones cannot be reused. To fix this problem set the following profile option value to NULL.<br /><br />Signon Password Case<br />Signon Password Custom<br />Signon Password Failure Limit<br />Signon Password Hard to Guess<br />Signon Password Length<br />Signon Password No Reuse<br /><br /> <br /><br />Apps Session<br />I also like to keep a session alive for several hours on a non production instance as I keep getting in and out of the instance. Hence I change the following profile option to 300 for 300 minutes, i.e. 5 hours.<br />ICX:Session Timeout<br /><br /><span id="fullpost">
</span></div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-31802995439618515852014-07-23T12:22:00.000+05:302014-07-23T12:22:20.627+05:30R12 Login Page: How to Personalize the Logo<div dir="ltr" style="text-align: left;" trbidi="on">
To implement this there are 2 actions required. The first is to change profile option<br /><br /> Login to Applications as System Administrator<br /> Select: System Administrator > Profile - System<br /> Query profile "Corporate Branding Image for Oracle Applications"<br /> Change the SITE level value to the name of the xxx.gif file ( eg my_company_logo.gif )<br /> Save the change<br /><br />The second action is performed on the Application server.<br /><br /> Open a telnet/PTTY session to the Application server and source Applications environment<br /> Navigate to the $OA_MEDIA directory<br /> Copy the image file to this directory and make sure permissions are set properly<br /><br />Now clear your browser cache and access the environment. Now the new logo should be rendered.<span id="fullpost">
</span></div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-25513579994499697632014-07-23T12:13:00.000+05:302014-07-23T12:13:36.929+05:30How to run one report from another report<div dir="ltr" style="text-align: left;" trbidi="on">
<!--[if gte mso 9]><xml>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>X-NONE</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:DontVertAlignCellWithSp/>
<w:DontBreakConstrainedForcedTables/>
<w:DontVertAlignInTxbx/>
<w:Word11KerningPairs/>
<w:CachedColBalance/>
</w:Compatibility>
<w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>
<m:mathPr>
<m:mathFont m:val="Cambria Math"/>
<m:brkBin m:val="before"/>
<m:brkBinSub m:val="--"/>
<m:smallFrac m:val="off"/>
<m:dispDef/>
<m:lMargin m:val="0"/>
<m:rMargin m:val="0"/>
<m:defJc m:val="centerGroup"/>
<m:wrapIndent m:val="1440"/>
<m:intLim m:val="subSup"/>
<m:naryLim m:val="undOvr"/>
</m:mathPr></w:WordDocument>
</xml><![endif]-->
<div class="MsoNormal">
Running nested reports:</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
For running one report from another report the user has to
use SRW.RUN_REPORT</div>
<div class="MsoNormal">
Go to layout editor</div>
<div class="MsoNormal">
--></div>
<div class="MsoNormal">
<span style="mso-spacerun: yes;"> </span>create a button and
label it as ‘Click this button’</div>
<div class="MsoNormal">
Open the property palette of the button and set the Button Behavior:
</div>
<div class="MsoNormal">
Type=pl/sql: Pl/sql</div>
<div class="MsoNormal">
Trigger= provide the code</div>
<div class="MsoNormal">
procedure U_ButtonButtonAction is</div>
<div class="MsoNormal">
BEGIN</div>
<div class="MsoNormal">
<span style="mso-spacerun: yes;">
</span>srw.run_report('module=C:\Documents and
Settings\Administrator\Desktop\POXDETIT.rdf destype=Screen');</div>
<div class="MsoNormal">
END;</div>
<div class="MsoNormal">
<br /></div>
<!--[if gte mso 9]><xml>
<w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
DefSemiHidden="true" DefQFormat="false" DefPriority="99"
LatentStyleCount="267">
<w:LsdException Locked="false" Priority="0" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/>
<w:LsdException Locked="false" Priority="39" Name="toc 1"/>
<w:LsdException Locked="false" Priority="39" Name="toc 2"/>
<w:LsdException Locked="false" Priority="39" Name="toc 3"/>
<w:LsdException Locked="false" Priority="39" Name="toc 4"/>
<w:LsdException Locked="false" Priority="39" Name="toc 5"/>
<w:LsdException Locked="false" Priority="39" Name="toc 6"/>
<w:LsdException Locked="false" Priority="39" Name="toc 7"/>
<w:LsdException Locked="false" Priority="39" Name="toc 8"/>
<w:LsdException Locked="false" Priority="39" Name="toc 9"/>
<w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/>
<w:LsdException Locked="false" Priority="10" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Title"/>
<w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/>
<w:LsdException Locked="false" Priority="11" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/>
<w:LsdException Locked="false" Priority="22" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Strong"/>
<w:LsdException Locked="false" Priority="20" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/>
<w:LsdException Locked="false" Priority="59" SemiHidden="false"
UnhideWhenUsed="false" Name="Table Grid"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/>
<w:LsdException Locked="false" Priority="1" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 1"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 1"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 1"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/>
<w:LsdException Locked="false" Priority="34" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/>
<w:LsdException Locked="false" Priority="29" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Quote"/>
<w:LsdException Locked="false" Priority="30" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 1"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 1"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 2"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 2"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 2"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 2"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 2"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 3"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 3"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 3"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 3"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 3"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 4"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 4"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 4"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 4"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 4"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 5"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 5"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 5"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 5"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 5"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 6"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 6"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 6"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 6"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 6"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/>
<w:LsdException Locked="false" Priority="19" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/>
<w:LsdException Locked="false" Priority="21" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/>
<w:LsdException Locked="false" Priority="31" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/>
<w:LsdException Locked="false" Priority="32" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/>
<w:LsdException Locked="false" Priority="33" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/>
<w:LsdException Locked="false" Priority="37" Name="Bibliography"/>
<w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/>
</w:LatentStyles>
</xml><![endif]--><!--[if gte mso 10]>
<style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:"Times New Roman";
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;}
</style>
<![endif]--><span id="fullpost">
</span></div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-62346316198264864362014-07-23T12:04:00.004+05:302014-09-16T18:42:40.503+05:30Reports<div dir="ltr" style="text-align: left;" trbidi="on">
Reports<br />
<br />
<ol style="text-align: left;">
<li><a href="http://oracle-appss.blogspot.in/2014/07/how-to-run-one-report-from-another.html">How to run one report from another report </a></li>
<li><a href="http://oracle-appss.blogspot.in/2014/07/displaying-concurrent-programm-name.html">displaying concurrent programm name dynamically in a report</a> </li>
<li><a href="http://oracle-appss.blogspot.in/2014/09/scheduling-discoverer-reports.html" target="_blank">Scheduling the Discoverer reports</a> </li>
</ol>
<span id="fullpost">
</span></div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-59302211839297266442014-07-23T11:49:00.002+05:302014-07-23T11:49:49.616+05:30Workflow<div dir="ltr" style="text-align: left;" trbidi="on">
Concepts related to Workflow<br />
<br />
<ol style="text-align: left;">
<li><a href="http://oracle-appss.blogspot.in/2014/07/how-to-launch-workflow-from-pl-sql.html">How to Launch Workflow from PL SQL</a></li>
<li> <a href="http://oracle-appss.blogspot.in/2014/06/user-management-role-categories-roles.html">USER Management | Role Categories | Roles | Indirect Responsibilities </a></li>
</ol>
<span id="fullpost">
</span></div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-74813141148721903242014-07-23T11:46:00.000+05:302014-07-23T11:46:01.086+05:30How to Launch Workflow from PL SQL<div dir="ltr" style="text-align: left;" trbidi="on">
The below script will launch the desired workflow FROM PL/SQL code:<br /><br />DECLARE<br /> v_itemtype VARCHAR2(50);<br /> v_itemkey VARCHAR2(50);<br /> v_process VARCHAR2(50);<br /> v_userkey VARCHAR2(50);<br />BEGIN<br /> v_itemtype := 'DEMOIT';<br /> v_itemkey := '1233';<br /> v_userkey := '1233';<br /> v_process := 'DEMOPROCESS';<br /> WF_ENGINE.Threshold := -1;<br /> WF_ENGINE.CREATEPROCESS(v_itemtype, v_itemkey, v_process);<br /> wf_engine.setitemuserkey(v_itemtype, v_itemkey, v_userkey );<br /> wf_engine.setitemowner (v_itemtype, v_itemkey,'SYSADMIN');<br /> WF_ENGINE.STARTPROCESS(v_itemtype, v_itemkey);<br /> COMMIT;<br />EXCEPTION<br />WHEN OTHERS THEN<br /> dbms_output.put_line(SQLERRM);<br />END; <br /><span id="fullpost">
</span></div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-81373994963505492212014-07-23T11:35:00.003+05:302014-07-23T11:35:49.665+05:30TCA<div dir="ltr" style="text-align: left;" trbidi="on">
TCA Related information<br />
<br />
<ol style="text-align: left;">
<li><a href="http://oracle-appss.blogspot.in/2014/07/query-to-get-customer-informaton.html">Query to get Customer Information</a></li>
</ol>
<span id="fullpost">
</span></div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-11756101342587021362014-07-23T11:32:00.003+05:302014-07-23T11:32:45.804+05:30Query to get Customer Informaton<div dir="ltr" style="text-align: left;" trbidi="on">
SELECT DISTINCT hp.party_name "Customer Name",<br /> hca.account_number,<br /> hca.status,<br /> hcsu.location,<br /> hcsu.site_use_code,<br /> hcsu.status loc_stat,<br /> ps.class,<br /> hcsu.site_use_id,<br /> hcpc.name profile_name,<br /> hl.address1,<br /> hl.address2,<br /> hl.address3,<br /> hl.city,<br /> hl.state,<br /> hl.postal_code,<br /> ps.customer_id,<br /> ps.customer_site_use_id,<br /> hps.identifying_address_flag,<br /> ps.trx_date,<br /> HOU.NAME "Operating Unit"<br />FROM apps.hz_parties hp,<br /> apps.hz_party_sites hps,<br /> apps.hz_locations hl,<br /> apps.hz_cust_accounts hca,<br /> apps.hz_cust_acct_sites hcas,<br /> apps.hz_cust_site_uses hcsu,<br /> apps.hz_customer_profiles hcp,<br /> apps.hz_cust_profile_classes hcpc,<br /> apps.ar_payment_schedules_all ps,<br /> apps.hr_operating_units hou<br />WHERE hp.party_id = hca.party_id(+)<br />AND hp.party_id = hcp.party_id<br />AND hp.party_id = hps.party_id<br />AND hps.party_site_id = hcas.party_site_id<br />AND hps.location_id = hl.location_id<br />AND hca.cust_account_id = hcas.cust_account_id<br />AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id<br />AND hca.cust_account_id = hcp.cust_account_id<br />AND hca.cust_account_id = ps.customer_id<br />AND hcp.profile_class_id = hcpc.profile_class_id<br />AND ps.customer_site_use_id =hcsu.site_use_id andhcsu.org_id =hou.organization_id;<br /></div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-13703745074169550312014-07-23T11:29:00.001+05:302014-07-23T11:29:07.321+05:30Oracle Inventory Related Stuff<div dir="ltr" style="text-align: left;" trbidi="on">
<ol style="text-align: left;">
<li><a href="http://oracle-appss.blogspot.in/2014/07/get-onhand-quantity-from-table-and-api.html">Get Onhand Quantity from table and API </a></li>
</ol>
<br /></div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-33380713355165417252014-07-23T11:23:00.002+05:302014-07-23T11:23:05.947+05:30Get Onhand Quantity from table and API<div dir="ltr" style="text-align: left;" trbidi="on">
This script can be used to get the below quantities.<br />1. On-hand Quantity<br />2. Available to Reserve<br />3. Quantity Reserved<br />4. Quantity Suggested<br />5. Available to Transact<br />6. Available to Reserve<br /><br />YOU CAN ALSO GET the on-HAND QUANTITIES from the table MTL_ONHAND_QUANTITIES<br />Also from GET ON-HAND QUANTITIES API<br /><br />DECLARE<br /> x_return_status VARCHAR2 (50);<br /> x_msg_count VARCHAR2 (50);<br /> x_msg_data VARCHAR2 (50);<br /> v_item_id NUMBER;<br /> v_org_id NUMBER;<br /> v_qoh NUMBER;<br /> v_rqoh NUMBER;<br /> v_atr NUMBER;<br /> v_att NUMBER;<br /> v_qr NUMBER;<br /> v_qs NUMBER;<br /> v_lot_control_code BOOLEAN;<br /> v_serial_control_code BOOLEAN;<br />BEGIN<br /> -- Set the variable values<br /> V_ITEM_ID := '6566';<br /> V_ORG_ID := 61;<br /> v_qoh := NULL;<br /> v_rqoh := NULL;<br /> v_atr := NULL;<br /> v_lot_control_code := FALSE;<br /> V_SERIAL_CONTROL_CODE := false;<br />-- Set the org context<br /> FND_CLIENT_INFO.SET_ORG_CONTEXT (1); <br /> <br />-- Call API<br /> inv_quantity_tree_pub.query_quantities<br /> (<br /> p_api_version_number => 1.0,<br /> p_init_msg_lst => 'F',<br /> x_return_status => x_return_status,<br /> x_msg_count => x_msg_count,<br /> x_msg_data => x_msg_data,<br /> p_organization_id => v_org_id,<br /> p_inventory_item_id => v_item_id,<br /> p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,<br /> -- or 3<br /> p_is_revision_control => FALSE,<br /> p_is_lot_control => v_lot_control_code,<br /> -- is_lot_control,<br /> p_is_serial_control => v_serial_control_code,<br /> p_revision => NULL, -- p_revision,<br /> p_lot_number => NULL, -- p_lot_number,<br /> p_lot_expiration_date => SYSDATE,<br /> p_subinventory_code => NULL, -- p_subinventory_code,<br /> p_locator_id => NULL, -- p_locator_id,<br /> -- p_cost_group_id => NULL, -- cg_id,<br /> p_onhand_source => 3,<br /> x_qoh => v_qoh, -- Quantity on-hand<br /> x_rqoh => v_rqoh, --reservable quantity on-hand<br /> x_qr => v_qr,<br /> x_qs => v_qs,<br /> x_att => v_att, -- available to transact<br /> x_atr => v_atr -- available to reserve<br /> );<br /><br /> DBMS_OUTPUT.put_line ('On-Hand Quantity: ' || v_qoh);<br /> DBMS_OUTPUT.put_line ('Available to reserve: ' || v_atr);<br /> DBMS_OUTPUT.put_line ('Quantity Reserved: ' || v_qr);<br /> DBMS_OUTPUT.put_line ('Quantity Suggested: ' || v_qs);<br /> DBMS_OUTPUT.put_line ('Available to Transact: ' || v_att);<br /> DBMS_OUTPUT.put_line ('Available to Reserve: ' || v_atr);<br />EXCEPTION<br /> WHEN OTHERS<br /> THEN<br /> DBMS_OUTPUT.PUT_LINE ('ERROR: ' || SQLERRM);<br />end;<br />/<br />---GET ON-HAND QUANTITIES FROM TABLE<br /><br />SELECT * FROM MTL_ONHAND_QUANTITIES; <br /><span id="fullpost">
</span></div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0tag:blogger.com,1999:blog-6818259978713093906.post-77298043128452697892014-07-18T11:02:00.000+05:302014-08-07T16:24:12.845+05:30Unix Related<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<ol>
<li><a href="http://oracle-appss.blogspot.in/2011/09/sql-loader-using-putty.html" target="_blank">UNIX : Loading records in to table using SQL LOADER</a></li>
<li><a href="http://oracle-appss.blogspot.in/2012/05/fndload-commands.html" target="_blank">UNIX: FND_LOAD commands</a></li>
<li><a href="http://oracle-appss.blogspot.in/2011/09/usage-of-flex-in-oracle-apps.html" target="_blank">Usage of :$FLEX$ in oracle apps</a></li>
<li><a href="http://oracle-appss.blogspot.in/2011/12/steps-to-move-concurrent-program-from.html" target="_blank">Steps to Move a Concurrent program from one instance(Database) to other</a></li>
<li><a href="http://oracle-appss.blogspot.in/2014/07/apache-bounce.html">Bouncing Apache</a></li>
<li><a href="http://oracle-appss.blogspot.in/2014/08/xml-parsing-reading-xml-file-in-oracle.html">XML Parsing -reading xml file in oracle pl sql example</a> </li>
</ol>
</div>
Jithendra Kumar Bhttp://www.blogger.com/profile/15637732998793719658noreply@blogger.com0