{"id":18218,"date":"2019-04-26T13:32:22","date_gmt":"2019-04-26T16:32:22","guid":{"rendered":"https:\/\/orabr.com\/?p=18218"},"modified":"2019-04-26T14:14:02","modified_gmt":"2019-04-26T17:14:02","slug":"-duvida-oracle-ame-approvals-managment-3","status":"publish","type":"post","link":"https:\/\/orabr.virttus.com\/?p=18218","title":{"rendered":"D\u00favida Oracle AME (Approvals Managment)"},"content":{"rendered":"

<\/p>\n

 <\/span> <\/p>\n
\n
<\/p>\n
\n
\n
\n
Como sugest\u00e3o recomendo que reescreva a cl\u00e1usula where retirando as fun\u00e7\u00f5es das colunas, isso nunca \u00e9 bom do ponto de vista de performance.<\/div>\n
<\/div>\n
eu faria algo assim:<\/div>\n
…<\/div>\n
and <\/span>p.effective_start_date < trunc(sysdate+1)<\/div>\n
and <\/span>p.effective_end_date between <\/span>to_date('31\/12\/12 00:00:00', 'DD\/MM\/YY HH:MI:SS') and <\/span>to_date('31\/12\/12 23:59:59', 'DD\/MM\/YY HH:MI:SS' ) <\/div>\n
…<\/div>\n<\/div>\n

<\/p>\n

\n
Em sex, 26 de abr de 2019 \u00e0s 17:22, Maristela Dantas marisdantas@hotmail.com<\/a> <<\/a>> escreveu:<\/div>\n

<\/u> <\/p>\n

\u00a0<\/span> <\/p>\n
\n
\n
\n
Achei!<\/div>\n
<\/div>\n
Se n\u00e3o tiver a sintaxe hachurada em azul abaixo, d\u00e1 erro na execu\u00e7\u00e3o do AME qdo utiliza a query din\u00e2mica no setup do approval group.\u00a0<\/div>\n
<\/div>\n
select 'person_id:' || p.person_id<\/span>
<\/span> <\/p>\n
from <\/div>\n
\u00a0 \u00a0per_people_f p <\/div>\n
\u00a0 ,PER_all_ASSIGNMENTS_f ass <\/div>\n
\u00a0 ,PAY_PEOPLE_GROUPS pg <\/div>\n
where <\/div>\n
\u00a0 1=1 <\/div>\n
\u00a0 and ass.person_id = p.person_id <\/div>\n
\u00a0 and ass.people_group_id = pg.people_group_id <\/div>\n
\u00a0 and trunc(<\/span>p.effective_start_date) <= trunc(sysdate) <\/div>\n
\u00a0 and to_char(<\/span>p.effective_end_date) = <\/span>to_char('31\/12\/12') <\/div>\n
\u00a0 and pg.group_name = 'STN_COMPRAS'; <\/div>\n

<\/span> <\/div>\n

<\/div>\n
\n
\n
<\/div>\n

Maristela Dantas <\/p>\n

\n
+55 11 98214-5651<\/div>\n
<\/div>\n
\n

<\/p>\n<\/p><\/div>\n

<\/div>\n
<\/div>\n<\/p><\/div>\n<\/p><\/div>\n
\n
<\/div>\n
<\/div>\n
\n
De:<\/b> <\/a> <<\/a>> em nome de Maristela Dantas marisdantas@hotmail.com<\/a> <<\/a>>
Enviado:<\/b> sexta-feira, 26 de abril de 2019 09:40
Para:<\/b> <\/a>
Assunto:<\/b> RE: D\u00favida Oracle AME (Approvals Managment)<\/font> <\/p>\n
\u00a0<\/div>\n<\/p><\/div>\n
\u00a0<\/span> <\/p>\n
\n
\n
\n

\n

Bom dia!<\/div>\n
<\/div>\n
\n
Pessoal, tem uma pegadinha na query para colocar no select din\u00e2mico do AME:<\/div>\n
<\/div>\n
select\u00a0<\/span>"personid:" || <id do objeto><\/span><\/div>\n
<\/div>\n
Sem concatenar o string, n\u00e3o retorna na execu\u00e7\u00e3o do AME, mesmo que a query execute corretamente (R11 e R12 era assim!). Estou tentando pegar a sintaxe correta para poder enviar.<\/div>\n

<\/div>\n

Maristela Dantas<\/span> <\/div>\n
\n
\n
\n
+55 11 98214-5651<\/div>\n
<\/div>\n
\n

<\/p>\n<\/p><\/div>\n

<\/div>\n
<\/div>\n<\/p><\/div>\n<\/p><\/div>\n
\n
<\/div>\n
<\/div>\n
\n
De:<\/b> @..com <<\/a>> em nome de Ana Cl\u00e1udia anaclaudia.barreto@gmail.com<\/a> <<\/a>>
Enviado:<\/b> quinta-feira, 25 de abril de 2019 23:35
Para:<\/b> <\/a>
Assunto:<\/b> D\u00favida Oracle AME (Approvals Managment)<\/font> <\/p>\n
\u00a0<\/div>\n<\/p><\/div>\n
\u00a0<\/span> <\/p>\n
\n
\n
\n

\n

Ol\u00e1 <\/p>\n
<\/div>\n
Ser\u00e1 que posso tentar ajudar?<\/div>\n
<\/div>\n
Tamb\u00e9m j\u00e1 participei de um projeto com o modulo Ame.<\/div>\n
Foi necess\u00e1rio criar uma fun\u00e7\u00e3o com retorno de tabela, q\u00a0 a partir da Ordem de Compra ou Requisi\u00e7\u00e3o, fazia a busca dos respons\u00e1veis de acordo com al\u00e7ada de aprova\u00e7\u00e3o.<\/div>\n
<\/div>\n
\n

FUNCTION<\/span><\/b> GET_APPROVER<\/span><\/b>(<\/span>P_TRANSACTION_ID <\/span><\/b>NUMBER<\/span><\/b> <\/span><\/b>)<\/span> <\/span><\/b>RETURN<\/span><\/b> T_APPROVER_RETURN <\/span><\/b>PIPELINED<\/span><\/b> <\/span><\/b>IS<\/span><\/b><\/span><\/b><\/p>\n

—<\/span><\/b><\/p>\n


<\/span><\/b><\/p>\n<\/p><\/div>\n

exemplo parcial:<\/div>\n
\n

\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0<\/span><\/b>SELECT<\/span><\/b> PAPF.PERSON_ID<\/span><\/b><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/b>INTO<\/span><\/b> P_APPROVER_ID<\/span><\/b><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/b>FROM<\/span><\/b> PER_ALL_PEOPLE_F\u00a0\u00a0\u00a0\u00a0\u00a0 PAPF<\/span><\/b><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/b>,<\/span>PER_ALL_ASSIGNMENTS_F PAAF<\/span><\/b><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/b>,<\/span>HR_ALL_POSITIONS_F\u00a0\u00a0\u00a0 HAPF<\/span><\/b><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/b>WHERE<\/span><\/b> HAPF.POSITION_ID <\/span><\/b>=<\/span> P_POSITION_ID<\/span><\/b><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/b>AND<\/span><\/b> PAPF.PERSON_ID <\/span><\/b>=<\/span> PAAF.PERSON_ID<\/span><\/b><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/b>AND<\/span><\/b> PAAF.POSITION_ID <\/span><\/b>=<\/span> HAPF.POSITION_ID<\/span><\/b><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/b>AND<\/span><\/b> trunc<\/span><\/b>(<\/span>SYSDATE<\/span><\/b>)<\/span> <\/span><\/b>BETWEEN<\/span><\/b> PAPF.EFFECTIVE_START_DATE <\/span><\/b>AND<\/span><\/b> PAPF.EFFECTIVE_END_DATE<\/span><\/b><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/b>AND<\/span><\/b> trunc<\/span><\/b>(<\/span>SYSDATE<\/span><\/b>)<\/span> <\/span><\/b>BETWEEN<\/span><\/b> PAAF.EFFECTIVE_START_DATE <\/span><\/b>AND<\/span><\/b> PAAF.EFFECTIVE_END_DATE<\/span><\/b><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/b>AND<\/span><\/b> <\/span><\/b>EXISTS<\/span><\/b> <\/span><\/b>(<\/span>SELECT<\/span><\/b> EMPLOYEE_ID<\/span><\/b><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/b>FROM<\/span><\/b> FND_USER FU<\/span><\/b><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/b>WHERE<\/span><\/b> FU.EMPLOYEE_ID <\/span><\/b>=<\/span> PAPF.PERSON_ID<\/span><\/b><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><\/b>AND<\/span><\/b> NVL<\/span><\/b>(<\/span>FU.END_DATE<\/span><\/b>,<\/span> trunc<\/span><\/b>(<\/span>SYSDATE<\/span><\/b>)<\/span> <\/span><\/b>+<\/span> <\/span><\/b>1<\/span>)<\/span> <\/span><\/b>><\/span> trunc<\/span><\/b>(<\/span>SYSDATE<\/span><\/b>));<\/span><\/span><\/b><\/p>\n<\/p><\/div>\n

<\/div>\n
—<\/div>\n
\n
\"image.png\" <\/div>\n<\/p><\/div>\n
<\/div>\n
Caso prefira me adicionar no skype, anaclaudia_coutob, estou \u00e0 disposi\u00e7\u00e3o em auxiliar seu colega.<\/div>\n
<\/div>\n
<\/div>\n
Ana Claudia Barreto<\/div>\n
<\/div>\n<\/p><\/div>\n
<\/p>\n\n\n\n
<\/a><\/td>\n Livre de v\u00edrus. www.avg.com<\/a>. <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/a><\/div>\n

<\/p>\n

\n
Em qui, 25 de abr de 2019 \u00e0s 15:12, Marcelo Macedo marcelo.rmacedo@yahoo.com.br<\/a> <<\/a>> escreveu: <\/div>\n

<\/u> <\/p>\n

\u00a0<\/span> <\/p>\n
\n
\n
\n

\n

\n
Boa tarde pessoal,<\/div>\n
<\/div>\n
Um amigo est\u00e1 precisando de uma ajuda, seja a d\u00favida:<\/div>\n
<\/div>\n
"<\/span>Estou precisando fazer um select a partir de um usu\u00e1rio e obter a hierarquia de aprova\u00e7\u00e3o (com o limites para cada um) para cima e para baixo na arvore hier\u00e1rquica do Oracle AME (Approvals Managment).\u00a0<\/i><\/b> <\/p>\n
Algu\u00e9m poderia ajudar informando quais as tabelas comp\u00f5em estas informa\u00e7\u00f5es?<\/i><\/b><\/div>\n

<\/i><\/b><\/div>\n
Qualquer sugest\u00e3o \u00e9 bem vinda.\u00a0<\/i><\/b><\/div>\n

<\/i><\/b><\/div>\n
Grato"<\/i><\/b><\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
ats,<\/div>\n
<\/div>\n
Marcelo Macedo<\/div>\n

<\/span><\/div>\n<\/p><\/div>\n

\n<\/p><\/div>\n

<\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/blockquote><\/div>\n
<\/p>\n\n\n\n
<\/a><\/td>\n Livre de v\u00edrus. www.avg.com<\/a>. <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/a><\/div>\n

\n<\/p><\/div>\n

<\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n

\n<\/p><\/div>\n

<\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/div>\n<\/p><\/div>\n
<\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/div>\n<\/blockquote>\n<\/div>\n<\/div>\n<\/p><\/div>\n

<\/HTML><\/HTML><\/p>\n","protected":false},"excerpt":{"rendered":"

  Como sugest\u00e3o recomendo que reescreva a cl\u00e1usula where retirando as fun\u00e7\u00f5es das colunas, isso nunca \u00e9 bom do ponto de vista de performance. eu… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29],"tags":[],"class_list":["post-18218","post","type-post","status-publish","format-standard","hentry","category-base-de-conhecimentos"],"_links":{"self":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/posts\/18218","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=18218"}],"version-history":[{"count":0,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/posts\/18218\/revisions"}],"wp:attachment":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=18218"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=18218"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=18218"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}